Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sending a Variable Number of Elements in a Parameter


Sending a Variable Number of Elements in a Parameter

Author
Message
Alex Grinberg-230981
Alex Grinberg-230981
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 402
Comments posted to this topic are about the item Sending a Variable Number of Elements in a Parameter
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4237 Visits: 6431
Alex,

Unfortunately, I must offer two criticisms of this article. At the end you said:

Alex Grinberg (1/15/2014)
My preference is the User-Defined Table type. I found that this technique is better optimized than the other methods discussed in this article.


Normally, when you make a statement like that it is customary to provide some benchmarking information regarding specifically what you mean. Fortunately, in this case SQL MVP Erland Sommarskog has got your back in his definitive treatises on this concept:

http://www.sommarskog.se/arrays-in-sql.html

The second criticism is about the way that you've chosen to split your delimited list. Pretty well known around here is DelimitedSplit8K, championed by SQL MVP Jeff Moden in this article:

Tally OH! An Improved SQL 8K “CSV Splitter” Function

It is going to be orders of magnitude faster than the string splitter you've included.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16515 Visits: 16988
Dwain you hit both things I was thinking myself.

To be fair there are two splitters posted. The first is the looping version. The second one is inside the usp_DelimitedString looks to be a complete "borrowing" of the DelimitedSplit8K function. Not sure exactly why the delimiter isn't passed in instead of being hardcoded in the proc.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4237 Visits: 6431
Sean Lange (2/5/2014)
Dwain you hit both things I was thinking myself.

To be fair there are two splitters posted. The first is the looping version. The second one is inside the usp_DelimitedString looks to be a complete "borrowing" of the DelimitedSplit8K function. Not sure exactly why the delimiter isn't passed in instead of being hardcoded in the proc.



Missed that. So now I'll have to add two more:

- There are probably sufficient reasons to say this "borrowing" was not direct plagiarism (e.g., it operates on a much shorter string and the delimiter is hard-coded) but an attribution to Jeff would probably have been good nonetheless.
- Why would you embed utility code in the SP when an external function as fast as DelimitedSplit8K is a much more effective approach, particularly from a reusability standpoint?

My apologies for being a tough audience today.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Qualitas Software
Qualitas Software
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 4
Another option or variation on the solutions offered would be to parse the string using CLR ?

We have gone down this route before and, without digging up the benchmarking(sorry), we found it quicker on CPU.

Further if the list of variables were fixed, such as int (ie- 4byte), our data can be sent using the varbinary(max) datatype and split (again using CLR).

Just some ideas... Smile
David McKinney
David McKinney
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 2090
Damned if you do, damned if you don't!

I find the criticism somewhat harsh. Am I alone in thinking that articles don't have to be exhaustive? I can see both sides of the argument, however I often do like to read articles that don't have 10 pages of benchmarking etc.. I'd rather just hear the idea sometimes. I'm intelligent enough to know that it mightn't be the only idea, the best idea, or even sometimes a good idea.

Also, sorry but I find the allusion to plagiarism a little facile. This code is pretty much public domain by now, and while a heads up to Mr Moden would have been nice, I really don't feel that this is in the same ballpark as proper examples of the P word.

As someone that's followed SSC for a number of years, I think there's a danger of the articles becoming a private members club, where the uninitiated are put off from submitting their ideas. I know I haven't written for quite a while, in part for these reasons.

Go easy on me! And have a good day.
Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)

Group: General Forum Members
Points: 731 Visits: 2937
Many thanks to Alex for writing this.
I think the only confusion for me as a reader of this article was that the title didn't make clear enough whether it was about passing 'tables' as parameters to routines, or about variable numbers of parameters. The 'delimited string' option seemed to demonstrate the latter, and the other examples the former.


Best wishes,

Phil Factor
Simple Talk
peter-757102
peter-757102
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 2549
David McKinney (2/6/2014)
Damned if you do, damned if you don't!

I find the criticism somewhat harsh. Am I alone in thinking that articles don't have to be exhaustive? I can see both sides of the argument, however I often do like to read articles that don't have 10 pages of benchmarking etc.. I'd rather just hear the idea sometimes. I'm intelligent enough to know that it mightn't be the only idea, the best idea, or even sometimes a good idea.

Also, sorry but I find the allusion to plagiarism a little facile. This code is pretty much public domain by now, and while a heads up to Mr Moden would have been nice, I really don't feel that this is in the same ballpark as proper examples of the P word.

As someone that's followed SSC for a number of years, I think there's a danger of the articles becoming a private members club, where the uninitiated are put off from submitting their ideas. I know I haven't written for quite a while, in part for these reasons.

Go easy on me! And have a good day.


I share this sentiment and will read the article in full at a later moment.
The solutions I will compare to what I have in the works myself and are based on CLR with some twists.
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4237 Visits: 6431
David McKinney (2/6/2014)
Damned if you do, damned if you don't!

I find the criticism somewhat harsh. ...

Also, sorry but I find the allusion to plagiarism a little facile. This code is pretty much public domain by now, and while a heads up to Mr Moden would have been nice, I really don't feel that this is in the same ballpark as proper examples of the P word.

...


Well I did at least apologize for my harshness. Maybe I'm just having a bad day.

dwain.c (2/5/2014)

...
My apologies for being a tough audience today.


I did say that I didn't consider it plagiarism also, although I understand how simply mentioning that harsh word may be interpreted as thinking it was.

I think the thing that fried my liver the most was that looping string splitter. Seeing that published again and again, when there are so much faster methods just rubs me the wrong way.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
ian.pettman
ian.pettman
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 30
Interesting, Very interesting... In addition to the comments above I'd also take issue with your xml implementation.

<Product>
<ProductID>1</ProductID>
<ProductName>Chai</ProductName>
<CompanyName>Lehmanns Marktstand</CompanyName>
<ShippersName>Speedy Express</ShippersName>
<OrderDate>12/07/2013</OrderDate>
<UnitPrice>18.0000</UnitPrice>
<Quantity>40</Quantity>
<Discount>0.15</Discount>
</Product>

XML also supports attributes, If you use attributes, then you XML will be half the string size,

e.g. <Product ProductID="1" ProductName="Chai" />

Also you should have a user defined function to escape the four XML sequences
also you should alias the Attribute Names

e.g. <Prod ID="1" PN="Chai" /> shorter again....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search