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

Multiple Optional Parameters Expand / Collapse
Author
Message
Posted Thursday, December 4, 2008 2:18 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 7:16 AM
Points: 478, Visits: 1,415
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?
Post #614104
Posted Thursday, December 4, 2008 2:20 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 7:16 AM
Points: 478, Visits: 1,415
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.
Post #614107
Posted Thursday, December 4, 2008 2:27 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #614111
Posted Thursday, December 4, 2008 2:28 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:47 AM
Points: 6,266, Visits: 2,029
Read This


* Noel
Post #614113
Posted Friday, December 5, 2008 9:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 7:16 AM
Points: 478, Visits: 1,415
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.


Post #614664
Posted Monday, January 7, 2013 3:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:16 PM
Points: 150, Visits: 148
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
Post #1403896
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse