How to Optimize the Use of the "OR" Clause When Used with Parameters (SQL Server 2008)

Question

I wonder if there is any wise way to rewrite the following query so that the indexes on columns get used by optimizer?

Create Procedure select_Proc1
    @Key1 int=0,
    @Key2 int=0
As
BEGIN
    Select key3
    From Or_Table
    Where (@key1 =0 OR Key1 =@Key1) AND
          (@key2 =0 OR Key2 =@Key2)
END
GO

Even though columns in the WHERE clauses are covered by indexes, SQL Server is unable to use these indexes. This raises the question as to whether anything is “blocking” the use of the indexes. The answer to this question is yes -- the culprits are the parameters and the “OR” condition. The parameters are not covered by indexes, which means SQL Server cannot use any of the indexes to evaluate “@key1=0” (a condition which also applies to @key2=0). Effectively, this means SQL Server cannot use indexes to evaluate the clause “@key1=0 OR Key1= @key1” (as the “OR” clause is the union of rows covered by both conditions). The same principle applies to the other clause (re. key2) as well. This leads SQL Server to conclude that no indexes can be used to extract the rows, leaving SQL Server to utilize the next best approach -- a clustered index scan

As you see, the SQL optimizer will not use indexes on columns if the predicates are "OR"ed in the WHERE clause. One solution for this problem, is to separate queries with IF clause for all possible combination of parameters.

Please read this short article to get a better view of the problem: http://www.sql-server-performance.com/articles/per/optimize_or_clause_p1.aspx

Now my question is, what should we do if the possible combinations are more that just three or four? Writing a separate query for each combination does not seem a rational solution. Is there any other workaround for this problem?

Answer 1

SQL Server is not very good in optimizing the OR predicates.

Use this:

SELECT  key3
FROM    or_table
WHERE   @key1 = 0
        AND @key2 = 0
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key1 = 0
        AND @key2 <> 0
        AND key2 = @key2
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key2 = 0
        AND @key1 <> 0
        AND key1 = @key1
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key1 <> 0
        AND @key2 <> 0
        AND key1 = @key1
        AND key2 = @key2

SQL Server will look to the values of the variables prior to executing the queries and will optimize the redundant queries out.

This means that only one query of four will be actually executed.

Answer 2

MSSQL 2008 has optimization syntax of condition simplification, here it is

 Where (@key1 =0 OR Key1 =@Key1) AND
      (@key2 =0 OR Key2 =@Key2) option(recompile)

This will optimize usage of constants

Answer 3

Have you tries a table valued function?

CREATE FUNCTION select_func1 (  
    @Key1 int=0,
    @Key2 int=0
)
RETURNS TABLE 
AS RETURN (
    Select key3
    From Or_Table
    Where (@key1 =0 OR Key1 =@Key1) AND
          (@key2 =0 OR Key2 =@Key2)
)


select * from select_func1(1,2)

Answer 4

Yes - careful use of dynamic sql will solve this problem. There are two ways to do it:

a. If you are a "purist" about stored procs, then compose a custom query string inside a stored proc and execute the string. The specific query then can be dynamically written per execution to include only the relevant criteria.

b. If you are flexible about the location of this SQL, you can (again CAREFULLY) compose the query string in your application and pass it to the server.

The danger, of course, is around SQL injection. So you have to be very careful how the data is passed from client into the dynamic sql statement.

Comprehensive information from Erland Sommarskog: http://www.sommarskog.se/dynamic_sql.html and http://www.sommarskog.se/dyn-search.html