Multiple Optional Parameters

  • 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?

  • 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.

  • 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
  • Read This


    * Noel

  • 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.

  • 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply