June 18, 2012 at 12:38 pm
Howdy,
I’m looking for thoughts on the Select All option for Multi-Valued parameters. Since selecting all creates a concatenated string with all available values, the string can get pretty big.
Do you prefer to pass the concat string, or a surrogate value like ‘0’ (since you can’t pass a null), and add to the where in the stored procedure like: “ where ( @MvP = ‘0’ or MyCol in (select items from Splitter(@mvp,',')) ) “ (or something along those lines – table join, etc.)
As with anything, I’m sure the answer is either “It depends” or 42. I am assuming that with several mvps each with several thousand possible values it would be far less performant to do the string comparison, but I don’t have a good environment to test it out thoroughly.
Anyone care to opine? Thanks!
🙂
June 18, 2012 at 1:54 pm
Given the code snippet I am guessing you are using some sort of looping splitter. I am already thinking you can vastly improve performance by reading the article in my signature about splitting strings.
You are probably correct that "it depends". It sounds like to some extent you are doing a similar type of thing to a catch all query. Gail has an excellent post on her blog about how to do this type of query here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]. You should be able to gain some insight on a good way to handle what you are in your report using the techniques she covers.
_______________________________________________________________
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/
June 18, 2012 at 2:25 pm
Thanks Sean
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply