SQL Query return results in a comma separated string (Optimised)

Question

I want to run a stored procedure that returns a whole bunch of stuff, one of which is a comma separated string of results.

I have read about the COALESCE and created a function that does it like this:

DECLARE @ListOfStuff NVARCHAR(4000)

SELECT  @ListOfStuff = COALESCE(@ListOfStuff + ', ', '') + Z.SingleStuff
FROM    X 
INNER JOIN Y ON X.SomeId = Y.Id
INNER JOIN Z ON Y.SomeId = Z.id
WHERE --Some condition

RETURN ISNULL(@ListOfStuff, '')

And then in my main SELECT I call this function and it works as expected. However, the procedure now takes a really long time to run and it often times out. What I'm wondering is, whether someone has an idea how to optimise it. I believe getting rid of the function and incorporating this logic within the procedure will make it run a lot faster but I couldn't figure out a way to have a sub select that uses COALESCE this way.

Any ideas how to optimise this logic? Many thanks in advance

what RDBMS and version are you using? – JNK May 01 '12 at 18:51 — JNK, May 01 '12 at 18:51

score 2 · Answer 1 · answered May 01 '12 at 21:19

You could dispense of the COALESCE altogether by just declaring @ListOfStuff as '', then handling the string concatenation and final return value slightly differently. As it stands you're evaluating it on every row.

DECLARE @ListOfStuff NVARCHAR(4000) = ''

SELECT  @ListOfStuff = @ListOfStuff + Z.SingleStuff + ','
FROM    X 
INNER JOIN Y ON X.SomeId = Y.Id
INNER JOIN Z ON Y.SomeId = Z.id
WHERE --Some condition

IF LEN(@ListOfStuff) > 0
BEGIN
    SELECT @ListOfStuff = LEFT(@ListOfStuff, LEN(@ListOfStuff) - 1)
END
RETURN @ListOfStuff

Possible optimizations would be to ensure there are indexes on all the columns used in the join, and if you can use varchar rather than nvarchar (depending on the contents and data type of z.SingleStuff, of course) that might help a little. But I'm not sure what else you could do without a possibly significant reworking of the stored procedure that calls it.

If this function is to be called for every row of a SELECT, you might have some luck with either a table-valued function or CROSS APPLY (depending on how you're using it, and your RDBMS).

score 0 · Answer 2 · answered May 01 '12 at 21:00

COALESCE isn't really doing the work here (it just sorts out the nulls - see http://msdn.microsoft.com/en-us/library/ms190349.aspx)

The concatenation of the values, with commas, is being done using the @ListOfStuff variable and does seems to be a good (the only?) way to do it, i.e. if you get rid of the function you'll still need to so this in your stored procedure. Having a function shouldn't in itself result in the performance hit you are seeing.

So, I'm not sure you can optimise this other than ensuring that your join columns (id, SomeId) are indexed.

score 0 · Answer 3 · edited May 23 '17 at 11:56

Without seeing your SP I can't propose an actual working solution, however if your using SQL-Server you can use the FOR XML PATH approach, which is featured in a number of questions on SO. I recently answered a question breaking down the logic behind this approach here. I much prefer this to a function approach as functions veer away from the preferred set based approach of SQL.

KM pointed out a flaw with my approach with a link to a better solution here, the same logic still applies however KM has avoided the problems with reserved characters appearing within the text to concatenate.

My best guess at a solution you are looking for is as follows:

SELECT  X.SomeColumn,
        Y.SomeColumn,
        STUFF(( SELECT  ', ' + Z.SingleStuff
                FROM    Z
                WHERE   Z.ID = Y.SomeID
                FOR XML PATH(''), TYPE
            ).Value('.', 'VARCHAR(MAX)'), 1, 2, '') [ListOfStuff]
FROM    X 
        INNER JOIN Y 
            ON X.SomeId = Y.Id
WHERE --Some condition

SQL Query return results in a comma separated string (Optimised)

3 Answers3