Home Forums Article Discussions Article Discussions by Author Discuss content posted by Wayne Sheffield Creating a comma-separated list (SQL Spackle) RE: Creating a comma-separated list (SQL Spackle)

  • January 12, 2011 at 8:15 am

    #1272398

    Mark-101232 (1/12/2011)

    Changing

    value('.','varchar(max)'),1,1,'')

    to

    value('(./text())[1]','varchar(max)'),1,1,'')

    appears to give a better query plan

    Thanks for the information... I'll have to test this out.

    Edit: WOW! What a difference! Identical queries, with just that one change. The way in the article gets:

    (1000 row(s) affected)

    Table '#TestData'. Scan count 1001, logical reads 2411, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 234 ms, elapsed time = 3175 ms.

    Your suggested change gets:

    (1000 row(s) affected)

    Table '#TestData'. Scan count 1001, logical reads 2411, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 172 ms, elapsed time = 561 ms.

    Looking at the execution plans (both queries run together), the method in the article uses 99% and your solution 1%.

    Thanks for the tip!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2