Share via

Conor vs. Optional Parameters

  • Article

One question I received at SQL Bits had to do with a reasonable programming practice that has an unintended performance impact.

 

SELECT <something> FROM T WHERE (@p is NULL or col = @p)

 

This pattern is common and we call it the “optional parameter problem”  (we name our problems :)).  In this case, a developer doesn’t know if they have a parameter and so they add this construct to not restrict rows unless the parameter is passed.  The query is semantically legal but this has a problem – the predicate is not SARGable.  So, no index matching in the default case.

The issue is that SQL Server will match conjunctions (Cond1 AND Cond2 AND Cond3 AND …) because each of those can be independently applied to the index when deciding how to create an index seek.  This predicate is a disjunction (Cond1 OR Cond2 OR Cond3 OR …) and the SARGability rules are more restrictive for these. 

It might be that you don’t run the query enough to care about the performance (in which case you can go back to whatever else you were doing before coming to my blog today).  However, if you find that the performance is unacceptable, then you might try seeing if splitting the two cases into two different queries fixes the issue:

 

if (@p is NULL)

SELECT <something> FROM T

else

SELECT <something> FROM T WHERE col = @p

With this shape, an index on col has a better chance of being matched (assuming you have an index and the query would pick it.  

 

The downside is extra code maintenance, obviously.  We are aware of this drawback and hope to improve the experience in this area in a future release of the product. 

 

Homework: Does adding OPTION(RECOMPILE) fix this issue?  (Hint: make sure that <something> in the examples you try are covered by the index columns so that the index will be picked in more cases)

 

Happy Querying!

 

Conor

Comments

  • Anonymous
    April 22, 2010
    It seems the best reference is here: http://www.sommarskog.se/dyn-search-2008.html

  • Anonymous
    April 25, 2010
    Hi! Another option would be to use dynamic sql (especially when there's more than one parameter...) In SQL Server 2008 RTM and post SP1-CU5, However, optional parameters are ignored when @p = NULL is true if you use OPTION(RECOMPILE) - which helps boost performance. In 2008 RTM a bug was found so that feature was removed in SP1. Cheers, S. Neumann