SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Optional Parameters


Multiple Optional Parameters

Author
Message
Goldie Lesser
Goldie Lesser
Right there with Babe
Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)

Group: General Forum Members
Points: 780 Visits: 1501
I am in the process of creating a stored procedure which will be used for searching database tables.
The user can search on approximately 20 fields from a combination of 6 tables.
Currently I have a stored procedure which gets passed all 20 fields and then I have a query with a WHERE clause which looks something like this:


WHERE ((@Parameter1 IS NULL) OR (@Field1 = @Parameter1))
AND ((@Parameter2 IS NULL) OR (@Field2 = @Parameter1))
AND ((@Parameter3 IS NULL) OR (@Field3 = @Parameter1))
AND ((@Parameter4 IS NULL) OR (@Field4 = @Parameter1))
AND ((@Parameter5 IS NULL) OR (@Field5 = @Parameter1))
AND ((@Parameter6 IS NULL) OR (@Field6 = @Parameter1))
AND ((@Parameter7 IS NULL) OR (@Field7 = @Parameter1))
AND ((@Parameter8 IS NULL) OR (@Field8 = @Parameter1))
AND ((@Parameter9 IS NULL) OR (@Field9 = @Parameter1))
AND ((@Parameter10 IS NULL) OR (@Field10 = @Parameter1))
...



This doesn't seem to be performing too well. Any suggestions on how to accomplish the above task and achieve better performance?
Goldie Lesser
Goldie Lesser
Right there with Babe
Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)

Group: General Forum Members
Points: 780 Visits: 1501
I can post sample DDL and DATA if people think that would be useful.
However, I'm really looking to see if someone has a different approach to the problem.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87093 Visits: 45267
That kind of catch-all query doesn't perform well at all. On SQL 2000 there's no way to make it perform well

Have a look at this thread - http://www.sqlservercentral.com/Forums/Topic599065-65-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9556 Visits: 2048
Read This


* Noel
Goldie Lesser
Goldie Lesser
Right there with Babe
Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)

Group: General Forum Members
Points: 780 Visits: 1501
Thanks to both of you for those references. They were both extremely helpful.
Noel, that article is AMAZING. Here's the part that makes the biggest difference:


Not all search routines are as complex our search_orders. Sometimes the dynamic search is confined to alternate keys, of which the user always supplies one. In this case, it is not too painful to use IF statements to handle the three different cases. But maybe the column list in the SELECT statement contains complex expressions that you don't want to repeat. You know already that this won't do:

WHERE (key1 = @key1 OR @key1 IS NULL)
AND (key2 = @key2 OR @key2 IS NULL)
AND (key3 = @key3 OR @key3 IS NULL)

As you have seen this will yield a table scan. But what do you think about this:

WHERE (key1 = @key1 AND @key1 IS NOT NULL)
OR (key2 = @key2 AND @key2 IS NOT NULL)
OR (key3 = @key3 AND @key3 IS NOT NULL)

The logic is here the reverse: give me all rows that matche any of the given conditions. From a performance point of view, this may look like the same thing, but this can in fact perform very well. The odds are very good that SQL Server will generate a plan which seeks the three key indexes and then merges the result either by index concatenation or some other method. Here is the real big scoop: thanks to the condition, @x IS NOT NULL, SQL Server adds a filter with a startup expression to the plan, so if the corresponding variable is NULL, SQL Server will not access that index at all.

Tim Januario-145496
Tim Januario-145496
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 185
Goldie Lesser (12/5/2008)
Thanks to both of you for those references. They were both extremely helpful.
Noel, that article is AMAZING. Here's the part that makes the biggest difference:


Not all search routines are as complex our search_orders. Sometimes the dynamic search is confined to alternate keys, of which the user always supplies one. In this case, it is not too painful to use IF statements to handle the three different cases. But maybe the column list in the SELECT statement contains complex expressions that you don't want to repeat. You know already that this won't do:

WHERE (key1 = @key1 OR @key1 IS NULL)
AND (key2 = @key2 OR @key2 IS NULL)
AND (key3 = @key3 OR @key3 IS NULL)

As you have seen this will yield a table scan. But what do you think about this:

WHERE (key1 = @key1 AND @key1 IS NOT NULL)
OR (key2 = @key2 AND @key2 IS NOT NULL)
OR (key3 = @key3 AND @key3 IS NOT NULL)

The logic is here the reverse: give me all rows that matche any of the given conditions. From a performance point of view, this may look like the same thing, but this can in fact perform very well. The odds are very good that SQL Server will generate a plan which seeks the three key indexes and then merges the result either by index concatenation or some other method. Here is the real big scoop: thanks to the condition, @x IS NOT NULL, SQL Server adds a filter with a startup expression to the plan, so if the corresponding variable is NULL, SQL Server will not access that index at all.




I know that this is an old post but I found it interesting and thought maybe it solved a pretty significant problem with optional parameters until I realized that there was a problem with the logic. The two queries above are not identical in the results that are returned. They are only equal if the user is limited to passing in a single parameter (all others having to remain null). If more than one @keyN is passed in, the results are different because the first query will require that all of the parameters return true for each row while the second will return rows if any single parameter matches. This is like a query that says WHERE gender = 'male' AND lastName = 'Smith' vs WHERE gender = 'male' OR lastName = 'Smith'. We can't compare the query plans between the two because the queries are not equal. While I hate dynamic sql for so many reasons, it still seems that it is the best option to perform a query where any number of parameters (0 - N) might be passed in.

Tim Januario
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