Sending a Variable Number of Elements in a Parameter
-
To send a table from .NET, you need to use DataTable. I wrote number of applications using this technique, now issues were found. If I have any issues, then I would include the problem in this article.
-
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 apologize for my harsh comments as well. I think I sort of just piled on with Dwain.
I guess I would have preferred to see using the DelimitedSplit8K as a function to maximize the performance and re-usability.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
dwain.c (2/5/2014)
- 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?
This brings up an interesting point, maybe there are there only so many ways to use a tally table to split a string? And in that case, would Jeff have been the only person to figure out how to split a string with a tally table? It sort of parallels my gripe with software patents, can code of this size and admittedly simple task requirements really demand the tribute the comments in this thread seem to imply?
Now, I admit every time I split a string with a tally table, I know I learned of the method from Jeff's articles on that and the tally table, even when I posted my sql 2000 version of the task (which implies maybe the author could have tried a 2000 compatible version instead of the loop-de-loop). But just because I didn't independently discover this, does this mean that Jeff is the only one able to do so? And obviously for me, this sort of reinforces my quandry of SQL's particular quirks in this area, that instead of requiring straightforward logical problem solving skills to implement relatively simple tasks, we need geniuses of Jeff's calibre to do even the simplest things that a more regular procedural language makes mince meat of.
Or maybe splitting a string with a tally table is more obvious than we think?
In any case, I for one regret the tone of some of these post replies, thus my comment.
-
Alex,
All listed criticisms listed above aside ( I do not necessarily agree with them), I thought your article was interesting to read. I have used delimited strings and passing a table to stored procedures. I am moving towards the xml method for some processes similar to what service broker does. The methods met the needs at the time. It is nice to see that others are willing to share their insight.
My only criticism is on the structure of the article, some of the code blocks are empty.
Thanks for sharing.
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
-
Alex Grinberg-230981 (2/6/2014)
To send a table from .NET, you need to use DataTable. I wrote number of applications using this technique, now issues were found. If I have any issues, then I would include the problem in this article.+1
I think that is one of the best usage examples for UDDTs around. I have implemented that myself many times and it saves lots of headaches.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thanks for the article.
I agree with Phil that the Title of the article seemed to be unclear in the intended topic.
I think the article would also benefit from better formatting. Many of the code blocks do not contain code, and the code just falls after the code block.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
I would like to thank Alex for this article. I know that a lot of people can get an idea of the options listed in here.
I agree, however, that it could have improvements. Using the DelimitedSplit8K instead of the code being written on each procedure (not because Jeff is the only one that can write a great delimiter, because the article and the discussion clearly states that he wasn't the only one involved) to provide more flexibility and use a well tested method that won't cause criticism.
It seems that formatting is a common issue with new writers in here, so maybe a better guide on how to do it or an editorial check could help.
It would be nice to comment that SSRS won't need any of this methods as it can do it on its own (and that's a common mistake that I've made.)
I'm sure that people in here aren't trying to discourage you to write more, but they're pointing out things that you can do better next time. I hope that you continue to contribute with this community.
-
David I want to thank you for posting this comment. What you said about these kind of scrutiny exercises was right on the money. First time and long time posters can't help but feel a bit intimidated about posting an article with some valid points only to have it ripped to shreds. I am right with you about wanting to get some shreds of good ideas without needing the reams and reams of supporting performance data. I am a show me kinda guy and when the time comes to implement some of the ideas I ALWAYS performance test them in my environment before I give them my blessing.
I do however also see the other side of the coin. Often times the comments are just as important as the original article. It allows others to add their 2 cents, both good and bad, sharing personal experiences of some pain felt out in the wild. However just like in real work life most of us have deadlines to meet, and products to build that simply do one thing; make money (well we hope so). Even in the real world you run into people who have the purest view yet it does not translate to the real world of making money or ever getting completely implemented. Everything is a balance. I am always grateful to those who take the time to post, even if the post is far from complete. After all, it takes guts to get up on the stage but you must be ready for your audience to comment both good and bad as they leave the show.
Wow I sound kind of flip floppy on my position, which is far from my normal stance, but I think both sides of the coin are important.
Thank you for an interesting article. Keep up the good work.
-
Alex,
I've gone back to have a closer look at the article. It could be that I've misunderstood something, but I really think the first example could be improved. I reckon that the string-splitting routine was fine before we had XML stuffed into SQL in SQL 2005 but really an XML-based string-splitting solution is twice the speed of the string-split and far more economical because it doesn't need a supporting function and a number table.
Here is my solution.
[font="Courier New"]CREATE PROC ListProductNames
@CategoryList NVARCHAR(500),
@Separator VARCHAR(10) =','
/*
List all products that are of a list of categories.
Execute ListProductNames @CategoryList='Condiments,Grains/Cereals,Dairy Products'
*/
AS
DECLARE @XMLList XML
SELECT @XMLlist='<list><y>'+REPLACE(@CategoryList,@Separator,'</y><y>')+'</y></list>';
SELECT dbo.Categories.CategoryName, dbo.Products.ProductName, dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice
FROM dbo.Categories
JOIN dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID
JOIN ( SELECT x.y.value('.','varchar(80)') AS IDs
FROM @XMLList.nodes('/list/y/text()') AS x ( y )
) AS Chosen_Categories(item)
ON Categories.CategoryName = Chosen_Categories.Item;
GO [/font]
You can see the timings of the string-splitting techniques here in Using XML to pass lists as parameters in SQL Server[/url] I've just noticed that some of the SQL has got mangled when the blog post was converted to WordPress, but it is easy to unmangle it.
I really think there isn't any further need for the old number-table version of the string-split.
Best wishes,
Phil Factor -
Alex,
Thank you for a very interesting article.
-
Phil Factor (2/6/2014)
I really think there isn't any further need for the old number-table version of the string-split.
Phil, did you do any comparable testing between DelimitedSplit8k and XML based attribute splitting where you included the conversion of the list to XML in the timings, rather than precalculating the xml?
It seems that it might be a bit of an unfair comparison to leave that potentially rather slow string manipulation out of the picture - I mean if one were to split a million row table of 4000 character lists, those REPLACEs and conversion from string to XML would surely factor in there quite heavily, at least I would imagine they would...
Thanks for the link to your blog post - it's one I hadn't read yet and as someone that has to deal with xml in sql fairly regularly I found it very interesting.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0); - Forum Etiquette: How to post Reporting Services problems [/url]
- Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden [/url]
- How to Post Performance Problems - by Gail Shaw [/url]
Since my name and my mantra have come up at least once on this thread, I thought I take the time to respond.
Some good folks have stated that they always test things before they use them and I bow deeply to them for that. Some of those same folks have also made statements about getting the job done on time and I, again, bow deeply to them for that.
But that's part of the problem with articles that don't have any or, at best, have only unsupported claims of performance. There are a huge number of people that are under the gun to get the job done right away and may not be equipped to solve a problem like this on their own nor do they have the time (or, possibly, the skills) to test the various solutions published in any given article. Of course, they turn to the internet and find articles like this one and see a solution that works and they blindly go ahead and use it.
You might ask, "If all the solutions work, what does it really matter which one they pick to get their job done"?
I'm a DBA and maybe I take my job a little too seriously. My primary concern is to protect data even if it's not my data and has no effect on me or the company I work for. Since all of the code works, in this case, no problem. The other thing is to protect Developers. I don't know of any group of people who are more under the gun to get things done than Developers whether they're GUI Developers, Database Developers, or people that have to do both. I also try to protect companies from themselves and the mistakes that Developers make.
To wit, slow code can cost a company a serious black eye if even one customer bad mouths another for writing code that bogged down during critical times or as the data scaled up. If enough people believe in that black eye, that company might lose customers. If the company loses customers, it might not be able to support the Developers it has and start laying good people off and could even go out of business. All of that because some manager was pushing Developers to get the job done and they did.
So, personally, I'm really disappointed when I see an article that advertises multiple methods with no performance tests. It doesn't take "reams and reams" of test code (although I'm certainly guilty there) and, as a reader, you can chose to ignore such testing. But for Joe-bag-o-donuts trying to squeak out a living, such performance tests might make the difference between him keeping a job or being laid off.
The early responders to this article have been chastised for being a bit harsh about the whole thing. Perhaps they could have chosen different words or tones but the bottom line, in my humble opinion, is that they were absolutely right in speaking up about the lack of testing in this article. Performance of code should only be second to accuracy and it shouldn't be very far behind in importance.
Remember that there are people out there that "want it real bad". As a community, let's try to not give it to them that way. 
--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)
Bill Talada (2/6/2014)
Thanks to the article author for braving this group. There is a clique in these forums that just can't seem to let it go when talking about string parsing. In my opinion they have ruined a good forum with all their petty griping and claiming there is only one right way to do things. I feel sorry for any new members who get bashed by these guys. Most newbies just want to learn the next step in their path. Teaching calculus to a second grader is absurd.
I've found that the "next step" in the path for a newbie is frequently the wrong step. While I absolutely agree that teaching calculus to a second grader is absurd, not teaching math at all or praising little Johnny for coming up with the wrong answer is even worse. I agree that you don't have to bash Johnny for coming up with the wrong answer but you do have to continue to try to teach him the right way and that also means letting him know that he "didn't do it the right way but let me show you how again". The so called "clique" in these forums has demonstrated a high level of concern in the area of teaching newbies and others how to write good SQL and why. In my opinion, they are absolutely necessary to keep nay sayers from ruining a good forum with all their petty griping about good-enough methods that actually aren't.
--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)
Thanks @Phil for posting that link to the XML splitter.
That was some good information and cool to learn.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Qualitas Software (2/6/2014)
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...
That's absolutely true. The CLR will smoke everything including even the latest version of DelimitedSplit8K or any other T-SQL only method, for that matter. If you can use SQLCLR in your environment, I'd recommend it because it also has no performance penalty for going over 8K.
Proof of that can be found in the following article and the article also contains an SQLCLR that Paul White wrote for me that anyone can use. You don't need to know a thing about C# nor even have it installed if you'd like to use such a splitter. (The code is contained in the files in the "Resources" section near the bottom of the article.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--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)
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply