Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Creating a comma-separated list (SQL Spackle) Expand / Collapse
Author
Message
Posted Wednesday, January 12, 2011 8:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 6,544, Visits: 8,761
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1046522
Posted Wednesday, January 12, 2011 8:48 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 4,128, Visits: 5,837
IIRC there was a massive thread here on SSC about how to create comma delimited lists, with lots of code samples and benchmarking. Likewise one to do the reverse - take a delimited list and populate a table from it.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1046543
Posted Wednesday, January 12, 2011 9:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:49 PM
Points: 20,462, Visits: 14,092
Good stuff Wayne.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1046572
Posted Wednesday, January 12, 2011 9:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 5:19 PM
Points: 265, Visits: 2,538
I've been using something similar to your query for a while now, but I hadn't known about the option to convert to varchar(max) and get rid of the tokens. The tokens have caused me some amount of pain and despite doing a great deal of research, I have never seen your solution anywhere. The point is: THANKS!
Post #1046588
Posted Wednesday, January 12, 2011 10:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 6,544, Visits: 8,761
JJ B (1/12/2011)
I've been using something similar to your query for a while now, but I hadn't known about the option to convert to varchar(max) and get rid of the tokens. The tokens have caused me some amount of pain and despite doing a great deal of research, I have never seen your solution anywhere. The point is: THANKS!


JJ - You're welcome. (I have to admit that I picked it up from a post here!)


Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1046605
Posted Wednesday, January 12, 2011 10:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 6,544, Visits: 8,761
Matt, Jason - thanks!

Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1046606
Posted Wednesday, January 12, 2011 1:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:38 AM
Points: 110, Visits: 761
Wayne,
Can you provide more explanation on this part of the query:
TYPE).value('.','varchar(max)')

I have always used:
 WITH CTE AS
(
SELECT DISTINCT
AccountNumber
FROM #TestData
)
SELECT AccountNumber,
CommaList = STUFF((
SELECT ',' + Value
FROM #TestData
WHERE AccountNumber = CTE.AccountNumber
ORDER BY Value
FOR XML PATH('')),1,1,'') -- no TYPE.value
FROM CTE;

This provides the same results as your query.
Post #1046747
Posted Wednesday, January 12, 2011 1:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 6,544, Visits: 8,761
Tom Bakerman (1/12/2011)
Wayne,
Can you provide more explanation on this part of the query:
TYPE).value('.','varchar(max)')


Tom,

Put an Ampersand (&), Less than (<) or Greater than (>) in your data, and see what happens with or without it:
WITH CTE (Data) AS
(
SELECT 'Rolling Stones' UNION ALL
SELECT 'Hall & Oates' UNION ALL
SELECT 'One <> Two'
)
SELECT [WithType] = STUFF((
SELECT ',' + Data
FROM CTE
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,''),
[WithOutType] = STUFF((
SELECT ',' + Data
FROM CTE
FOR XML PATH('')),1,1,'');



Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1046759
Posted Wednesday, January 12, 2011 5:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 35,967, Visits: 30,258
WayneS (1/12/2011)
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!


Very cool! Wayne writes an excellent article, Mark provides an excellent performance enhancement in the discussion, and Wayne tests it for everyone. It doesn't get much better than that. Well done to you both.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1046939
Posted Wednesday, January 12, 2011 6:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 6,544, Visits: 8,761
Jeff Moden (1/12/2011)
Very cool! Wayne writes an excellent article, Mark provides an excellent performance enhancement in the discussion, and Wayne tests it for everyone. It doesn't get much better than that. Well done to you both.


Thanks Jeff. Now, I just have to get used to this "new" way of doing this!


Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1046943
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse