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

Sending a Variable Number of Elements in a Parameter Expand / Collapse
Author
Message
Posted Wednesday, February 5, 2014 9:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:03 PM
Points: 27, Visits: 307
Comments posted to this topic are about the item Sending a Variable Number of Elements in a Parameter
Post #1538458
Posted Wednesday, February 5, 2014 10:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1538473
Posted Wednesday, February 5, 2014 11:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:56 PM
Points: 13,078, Visits: 12,529
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)
Post #1538477
Posted Wednesday, February 5, 2014 11:20 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1538480
Posted Thursday, February 6, 2014 12:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 10, 2014 2:33 AM
Points: 1, 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... :)
Post #1538495
Posted Thursday, February 6, 2014 1:13 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:54 AM
Points: 648, Visits: 1,876
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.
Post #1538503
Posted Thursday, February 6, 2014 2:42 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:26 AM
Points: 587, Visits: 2,547
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
Post #1538530
Posted Thursday, February 6, 2014 2:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 3:06 AM
Points: 330, Visits: 2,249
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.
Post #1538533
Posted Thursday, February 6, 2014 3:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1538545
Posted Thursday, February 6, 2014 4:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 9:46 AM
Points: 3, Visits: 9
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....
Post #1538565
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse