SQL Server 2008R2 Custom Function so slow

Question

Here is my function:

-- DECLARE @cur CURSOR
DECLARE @line int
declare @return varchar(255)

DECLARE myCursor CURSOR FOR
SELECT DISTINCT (quote_ln_no) as quote_ln_no
  FROM dbo.quote_line_bom
 WHERE quote_no = @quote_no AND component_mat_no = @mat_no
ORDER BY quote_ln_no

set @return = ''

OPEN myCursor

FETCH NEXT FROM myCursor
INTO @line
WHILE @@FETCH_STATUS = 0
BEGIN
  set @return = @return + convert(varchar, @line) + ', '
  FETCH NEXT FROM myCursor 
  INTO @line
END

CLOSE myCursor
DEALLOCATE myCursor 

if len(@return) > 0 
set @return = substring(@return, 1, len(@return)-1)

return @return

When I use this function in a query which returns over 3000 records, function adds 20 seconds.

Please let me know what is wrong with it or is there a way to make it run faster?

It is slow because it is a) multi-statement function, as opposed to an inline one, and b) it uses a cursor. If you want `group_concat()` aggregate function for strings, either [create an actual aggregate](http://stackoverflow.com/q/4374709/11683), use [`for xml path('')`](http://stackoverflow.com/a/5196474/11683) or simply [dump it into a variable](http://stackoverflow.com/a/6755403/11683). — GSerg, Mar 24 '14 at 17:11

Dave Mason · Answer 1 · 2014-03-24T17:20:42.490

1

I think this will be faster. It's set-based and doesn't use a cursor.

DECLARE @return VARCHAR(255) = ''

SELECT @return = @return + CAST(quote_ln_no AS VARCHAR) + ','
FROM dbo.quote_line_bom
WHERE quote_no = @quote_no AND component_mat_no = @mat_no
AND quote_ln_no IS NOT NULL
GROUP BY quote_ln_no
ORDER BY quote_ln_no

IF LEN(@return) > 0 
    set @return = SUBSTRING(@return, 1, LEN(@return)-1)

SELECT @return

--This is for a user defined function, right?
--RETURN @return

edited Mar 24 '14 at 17:20

answered Mar 24 '14 at 17:13

Dave Mason

4,746
2
23
24

1

+1, this will work, FOR XML is slightly faster http://stackoverflow.com/a/1785923/215752 – Hogan Mar 24 '14 at 17:17

Andrew · Answer 2 · 2014-03-24T18:48:04.403

0

You can just use COALESCE instead of a cursor:

declare @return varchar (255) = ''

select
@return = coalesce(@return + ' ', '') + quote_ln_no
FROM dbo.quote_line_bom
WHERE quote_no = @quote_no AND component_mat_no = @mat_no
ORDER BY quote_ln_no

You can join to it however you need to, I just used cross apply because I'm lazy. Here's a very crude example: SQLFiddle

EDIT: I wasn't clever enough to work out how to use the coalesce in a subquery, but I did work out how to do it using an XML method.

Your query will read something like this:

select <some stuff>
from
<some tables>
cross apply
(select quote_ln_no + ' '
 FROM dbo.quote_line_bom
    WHERE quote_no = @quote_no AND component_mat_no = @mat_no
) t1 (quote_ln_no)
...

Another crude example: XML example

edited Mar 24 '14 at 18:48

answered Mar 24 '14 at 17:19

Andrew

8,445
3
28
46

DMason It did run a little bit faster -1 second, after a few runs, first run was slower +4 seconds. This function adds 20 seconds to a total run time. – user3456419 Mar 24 '14 at 17:43
I think maybe you meant to respond to DMason's post? – Andrew Mar 24 '14 at 18:00
Yes, my comment was for DMason, Interesting UI :) Andrew Using it in a function, got the same result as DMason's suggestion. is there a way to use coalesce inline? – user3456419 Mar 24 '14 at 18:05
Do you mean a nested query (subselect, derived table, whatever)? – Andrew Mar 24 '14 at 18:14
Yes, sub select looks like runs much faster than function. – user3456419 Mar 24 '14 at 18:35
Modified my answer, see if that helps. – Andrew Mar 24 '14 at 18:49

SQL Server 2008R2 Custom Function so slow

2 Answers2