August 13, 2010 at 11:47 pm
Adam Gojdas (8/13/2010)
Jeff Moden (8/12/2010)
Someone already said it... I'd love to read that article. Would you post the URL on this thread so we can take a peek? Thanks, Joe.
Maybe this is the article he was referring to:
http://www.simple-talk.com/sql/learn-sql-server/values%28%29-and-long-parameter-lists/
Thanks, Adam.
--Jeff Moden
August 15, 2010 at 7:29 am
SQLZ (8/13/2010)
This was an interesting article with an even more interesting discussion.The thing I'd like to pick up on, that drew my attention, was your reasons for moving away from the loop method. You commented that you found there was too much coding for so little functionality.
The interesting thing though, is that the loop method performs as much as 5 times faster than the alternative method you provided. This becomes apparent when you consider 4000 different states.
Now I come across this a lot. I'll look at a piece of code that a developer has produced and I'll wonder why they've adopted a certain approach - very often the answer is "it's a neater solution". My approach is, "sure, but is it a fast solution"?
Not withstanding the already mentioned solutions that others have commented on that improve on the loop method, and not withstanding that your intention was probably never to deal with 100's or thousands of states, in my book, if a procedure has 5 lines more code but gives me performance gain by a factor of 5 then it's worth keeping those extra lines. Heck, for 5-times the performance, give me 100 extra lines of code ;).
I was about to answer Jeff about performance question and your post lights a little the arguments a wanted to expose.
One thing I've learned with this my first writting experience is the range of levels your readers have, and the different approach everyone has when reading some other point of view.
Having said this, let me tell you why I did not insist on performance issue. As someone stated, Erland's work on arrays is the ultimate reference on methods to split a string of parameters. No substantial things can be added on this question, I think. Moreover, the method I expose is really one of the worst, if you read his work.
As I teach SQL from the last year on, the big trouble I've seen in my students is to adopt the set based thinking. They all come from procedural languages, they all are clever and should find how to split a string by the loop method I discard. Why I do it - to show them how to think in SQL. It took me several months to get it, and I think that's the key point to leverage their SQL skills.
That is, performance is a concept every programmer, procedural or not, can adquire naturally. SQL thinking is not so straight forward, though. Sadly, in my writting all this considerations where implicit, as I had only my point of view in mind. That's why I thank from heart received criticism, particularly those of Jeff, a little radical in style but right in the bottom.
OFF TOPIC> I'm on holidays, I've survived an interoceanic/intercontinental flight between Madrid and San Francisco, via New York - only 18 hours, worth the pay for living two weeks away from SQL. I'll try to check email but it will not be easy - mainly / it's so cool to be on vacation, you know 😀 ... See you all, and thank for your inputs.\
Diego Buendia
Barcelona Spain
August 15, 2010 at 11:55 am
dbuendiab (8/15/2010)
SQLZ (8/13/2010)
This was an interesting article with an even more interesting discussion.The thing I'd like to pick up on, that drew my attention, was your reasons for moving away from the loop method. You commented that you found there was too much coding for so little functionality.
The interesting thing though, is that the loop method performs as much as 5 times faster than the alternative method you provided. This becomes apparent when you consider 4000 different states.
Now I come across this a lot. I'll look at a piece of code that a developer has produced and I'll wonder why they've adopted a certain approach - very often the answer is "it's a neater solution". My approach is, "sure, but is it a fast solution"?
Not withstanding the already mentioned solutions that others have commented on that improve on the loop method, and not withstanding that your intention was probably never to deal with 100's or thousands of states, in my book, if a procedure has 5 lines more code but gives me performance gain by a factor of 5 then it's worth keeping those extra lines. Heck, for 5-times the performance, give me 100 extra lines of code ;).
I was about to answer Jeff about performance question and your post lights a little the arguments a wanted to expose.
One thing I've learned with this my first writting experience is the range of levels your readers have, and the different approach everyone has when reading some other point of view.
Having said this, let me tell you why I did not insist on performance issue. As someone stated, Erland's work on arrays is the ultimate reference on methods to split a string of parameters. No substantial things can be added on this question, I think. Moreover, the method I expose is really one of the worst, if you read his work.
As I teach SQL from the last year on, the big trouble I've seen in my students is to adopt the set based thinking. They all come from procedural languages, they all are clever and should find how to split a string by the loop method I discard. Why I do it - to show them how to think in SQL. It took me several months to get it, and I think that's the key point to leverage their SQL skills.
That is, performance is a concept every programmer, procedural or not, can adquire naturally. SQL thinking is not so straight forward, though. Sadly, in my writting all this considerations where implicit, as I had only my point of view in mind. That's why I thank from heart received criticism, particularly those of Jeff, a little radical in style but right in the bottom.
OFF TOPIC> I'm on holidays, I've survived an interoceanic/intercontinental flight between Madrid and San Francisco, via New York - only 18 hours, worth the pay for living two weeks away from SQL. I'll try to check email but it will not be easy - mainly / it's so cool to be on vacation, you know 😀 ... See you all, and thank for your inputs.\
Diego Buendia
Barcelona Spain
Very cool. Thanks Diego.
--Jeff Moden
November 1, 2012 at 10:49 pm
This solution can be useful when we already know the input parameter values. when we do not have fix information for parameter then this may not be applied.
November 1, 2012 at 10:58 pm
Guys,
I must be missing a WOW factor here, but why do we need to re-invent the wheel?
The most ROBUST solution wiould be a SINGLE strongly typed XML parameter with bound XSD schema. This would provide a strong typing for any data passed to stored procedure.:cool:
November 2, 2012 at 2:46 am
Hey Diego nice article. I understand what you meant about the implied performance concerns and I found Jeff to be very abrasive virtually to the point of being offensive.
Although Jeff I feel fell foul of making his own assumptions too.
The assumption was that you have to validate data because users might be calling this and what-if-this-what-if-that.
Missing the obvious case where the DB is only access via an app/product (in our case written in .NET) that accesses the SQL Server DB, the user will never call the SP directly only via the app.
In the case of the example given of US states if we were implementing such a thing the user would pick from a list and therefore the SP would not need to validate the input because the app would do that and not allow for free-form user entry. Developer/unit testing and QA testing would in this simple case pick up mistakes.
November 2, 2012 at 5:10 am
Post deleted
November 2, 2012 at 5:46 am
I've seen this on SQL Server Central before, so I squirreled away the code where I could always find it when needed. I typically use it in a stored procedure with the parameter as a csv list. It works well when I have a multiple-select dropdown list where, as the developer, I'm building the csv parameter string coming back, so I know that it's well formed, and I don't need any validation. The nice thing about it is that I don't even have to worry about removing the trailing comma. There are three variations that work and if you use a convert(varchar,FieldName) then you can even check integers instead of just strings:
declare @statelist varchar(50)
select @statelist = '1,2,3,4,10,'
select stateid, stateabbrev, statename
from states
1. where charindex(',' + convert(varchar, stateid) + ',', ',' + @statelist + ',') > 0
2. where patindex('%,' + convert(varchar, stateid) + ',%', ',' + @statelist + ',') > 0
3. where ',' + @statelist + ',' like '%,' + convert(varchar, stateid) + ',%' ;
I've never had an issue with performance, but I really don't think I've used it on large tables.
November 2, 2012 at 7:52 am
Good post on the XML. This is how I prefer to do it since you can just select from the nodes. I find this extremely useful when writing SSRS reports that contain a mutli-value parameter.
November 2, 2012 at 7:56 am
CELKO (8/12/2010)
jhood (8/10/2010)
I see what you are saying Jeff, but would you want to do validation if the application did not allow the end user to send invalid data? perhaps using a drop down list..LOL! Why of course nobody would ever send garbage to a database! All users are perfect and the DB programmer should never have to worry about data integrity in his schema. 😉 Want to get rid of all constraints:-P while we are at it?:-P
I did an article on using a long parameter list instead of trying to write a home-made parser in T-SQL or contaminating the schema with XML and CLR code. I am still working on Part II with some examples of how the home-made parsers produce different results than the T-SQL compiler parameter parser.
So true. The user (client, customer, or whatever) may always be right when one is in the business of marketing, website design or bussing tables at Waffle House. However, from a database engineer's perspective, what gets stored in a database on the back end should be objective, not subjective.
Joe, it's been a couple of years since your comment above about XML contaminating the schema or transaction SQL processing of a database, has this stance softened any at all, at least in terms of using XML for passing input parameters?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 2, 2012 at 7:56 am
This seems fraught with error, as well as inefficient.
What if you're doing high volume transaction processing? How will you keep one set of parameters separate from others? Another column with a GUID? Ask the calling code to pass in a unique identifier of its own (user ID or something)?
Is each instance going to create a new temporary table and do its own inserts, read them right back, and drop the table?
How can this possibly be more efficient than simply processing the values in a loop and using no table (temporary or otherwise) at all?
Also, you're going to need validation somewhere, like stripping leading and/or trailing spaces, making sure there are no missing parameters (AZ,,ME), etc., which would be easier to catch in "for each parameter" a loop rather than a ."for each row" loop.
Do you recommend making a solution "pure SQL" simply because procedural code "has coodies" or something?
November 2, 2012 at 8:34 am
Here is what I've done in the past for supplying complex multi-values parameters to a reporting stored procedure.
The user specifies various options for ad-hoc reporting into a form. The application inserts a REPORTRUN table (one row for each report run) and other related tables like REPORTRUN_RULE, which contains one row for each filtering option chosen by the user from a dropdown listbox. What gets passed to the stored procedure that queries a resultset for the report is a single @reportrun_id parameter. I'll also contain things like the start time, end time, and even the actual SQL (which is generated dynamically because it's an ad-hoc report) in REPORTRUN columns. From an operational perspective, I have a historical record of useful stuff like how a report arrived at it's result, utilization, and runtime duration.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 2, 2012 at 8:55 am
There is another article from 2008, which covers the same scenario : Multivalue Parameters With SQL SP
It addition of the 2 methods posted here Comma separated text and SQL Server table the other article explains also Bitwise numbers and XML
November 2, 2012 at 9:03 am
...
November 2, 2012 at 9:35 am
Peter-359208 (11/2/2012)
There is another article from 2008, which covers the same scenario : Multivalue Parameters With SQL SPIt addition of the 2 methods posted here Comma separated text and SQL Server table the other article explains also Bitwise numbers and XML
I have to warn you that the fnTextToTable is a multi-line table valued function with a WHILE loop (it's a "nibbler" splitter). That's going to be relatively slow (3 times as slow, in fact). Please see the following article for a much faster 8k splitter.
http://qa.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Viewing 15 posts - 91 through 105 (of 124 total)
You must be logged in to reply to this topic. Login to reply