Avoid dynamic SQL

Question

Avoid dynamic SQL

Viewing 5 posts - 76 through 79 (of 79 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

Rob Schripsema (3/2/2010)
I do know that SSRS passes field(parameter?) lists as a string of comma-separated values. That doesn't require dynamic SQL or even a cursor/while loop to split it apart quickly, though many think that it does.
OK -- I'll bite. How do you split apart a string of comma separated values quickly in T-SQL without a while loop?

Start from the beginning. Read the following article on how the technique Lynn used actually works...

http://www.sqlservercentral.com/articles/T-SQL/62867/

Then, peruse the tread Lynn suggested for a wad of alternatives.

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 2

Performance results for that (huge) thread were summarized very nicely by Florian Reischl on his blog:

http://florianreischl.blogspot.com/2009_09_01_archive.html

It covers all the main methods, including WHILE loops, Tally tables, XML, and CLR.

Paul

Spoiler: CLR wins.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

....

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

Paul White (3/2/2010)
Performance results for that (huge) thread were summarized very nicely by Florian Reischl on his blog:
http://florianreischl.blogspot.com/2009_09_01_archive.html
It covers all the main methods, including WHILE loops, Tally tables, XML, and CLR.
Paul
Spoiler: CLR wins.

No doubt the CLR wins. I do have to get back to that thread, though. A lot of the tests were done with rows of data where all of the delimiters neatly aligned from row to row (almost like fixed field rows) and the optimizer figured that out in many cases which, of course, slants some of the test results. I tried to interject that fact a time or two in the thread but I believe it was missed simply because of the volume of posts there.

No matter what happens, revisiting that thread will be fun. 😛

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 5

Jeff Moden
No doubt the CLR wins.

I only mention it because no-one else ever does 😛

Jeff Moden
A lot of the tests were done with rows of data where all of the delimiters neatly aligned from row to row (almost like fixed field rows) and the optimizer figured that out in many cases which, of course, slants some of the test results.

IIRC that affected the twin-carb tally, though I can't be certain. Again IIRC, the optimizer threw in a table spool to avoid having to do the same splits over and over again. This is a clever optimization, and perfectly valid. If your data happens to include lots of duplicates, there's no getting around that this is a great performance booster. It was an interesting thread, but I'm not sure too much has changed since then.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi