Databases
StackOverflow2013
Postsdbo
POHow to Optimize the Use of the "OR" Clause When Used with Parameters (SQL Server 2008)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to Optimize the Use of the "OR" Clause When Used with Parameters (SQL Server 2008)
    primarykey
    data
    text
    <p>I wonder if there is any wise way to rewrite the following query so that the indexes on columns get used by optimizer?</p> <pre><code>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 </code></pre> <p><em>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</em></p> <p>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.</p> <p>Please read this short article to get a better view of the problem: <a href="http://www.sql-server-performance.com/articles/per/optimize_or_clause_p1.aspx" rel="noreferrer">http://www.sql-server-performance.com/articles/per/optimize_or_clause_p1.aspx</a></p> <p>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?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USMeysam
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to Optimize the Use of the "OR" Clause When Used with Parameters (SQL Server 2008)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to Optimize the Use of the "OR" Clause When Used with Parameters (SQL Server 2008)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to Optimize the Use of the "OR" Clause When Used with Parameters (SQL Server 2008)
      1. USLucero
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload