Function Running Slow 1

[Signit]

I've written several functions similar to the one below, which work quickly and efficently. However, this particular function runs incredibly slow when a large set of data is thrown against it. There are indexes on all of the keyed columns that I am working with. Is there something here that I am missing that is causing this fucntion to perform terribly?

create    function fn_node_address (@machine_sk int)

returns varchar(4000)

as

begin


  declare @f_node_address varchar(50),
          @node_address_list varchar(4000)
  
  select @node_address_list = '' -- initialize the variable to something other than null
  
  declare node_address_cursor cursor for
   select c.node_address
     from machine a,
          net_interface b,
          node_address c
    where a.machine_sk = @machine_sk
      and a.machine_sk = b.machine_sk
      and b.net_interface_sk = c.net_interface_sk
   order by 1
  
     open node_address_cursor
    fetch node_address_cursor
     into @f_node_address
  
  while (@@fetch_status <> -1)
    begin
      set @node_address_list = @node_address_list + rtrim(@f_node_address) + ', '
      
      fetch node_address_cursor
       into @f_node_address
    end

  close node_address_cursor
  deallocate node_address_cursor

  if len(@node_address_list) > 0
    return substring(@node_address_list,1,len(@node_address_list)-1) -- remove the trailing comma

  return @node_address_list
end

 

[mrdenny]

What happens when you pull the execution plan for this?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[vongrunt]

Hewlett-Packard is yer friend or what?

For starters, get rid of cursor.

Then - slow performance is partially caused by caller query (lotsa rows returned, function gets called once for every row). Is that correct?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Signit]

Mrdenny,
The execution plan shows a 100% cost at machine.x_machine which then points to a compute scalar which points to the select, which both have a 0% cost.

vongrunt,
My intent with this function is to return all of the ip addresses associated with a machine in one row of a recordset. If I remove the cursor how would that be accomplished?

Yes the function is called for every row. An example of how I am calling the function is below. Is there a better method of doing performing this function call?

select a.machine,
       dbo.fn_node_address(a.machine_sk)
  from machine a

 

[DrSql]

Create an Index on that field and see what happens.

Dr.Sql
Good Luck.

 

[vongrunt]

This is classic example of "runtime" denormalization. There is one-to-many pair of tables; for each parent pull out comma-separated list of children or something, typically for display purposes.

There are two common approaches: a) with user-defined function (w/o cursor), b) with temp table. Both are explained here: thread183-1047450.

Btw. performance for a) heavily depends on number of rows in [machine] table. Fewer rows = fewer UDF calls = faster. If you can narrow results somehow (WHERE, TOP) that would be nice. Fine tuning of SELECT used in UDF also won't hurt - it gets called for every row in [machine] table, so any small detail matters.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Signit]

I really like the explanation of "runtime" denormalization. From your exmaple thread I utilized the coalesce command thereby eliminiating the need for my cursor. The new function is below. Thank you to everyone who took the time to reply to my thread.

create function fn_node_address_test (@machine_sk int)
returns varchar(4000)

as

begin
  declare @node_address_list varchar(4000)
  
  select @node_address_list = coalesce(@node_address_list+', ', '') + node_address
    from machine a,
         net_interface b,
         node_address c
   where a.machine_sk = @machine_sk
     and a.machine_sk = b.machine_sk
     and b.net_interface_sk = c.net_interface_sk

  if len(@node_address_list) > 0
    return substring(@node_address_list,1,len(@node_address_list)-1) -- remove the trailing comma

  return @node_address_list
end

 

[Signit]

Didn't quite understand exactly how coalesce was working so this line of code has been changed.

if len(@node_address_list) > 0

if substring(@node_address_list, len(@node_address_list), 1) = ','

 

[vongrunt]

Unless node_address values contain some commas, I think code for checking trailing comma is not necessary at all. Try it.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)