如何优化“OR”的使用与参数一起使用时的子句（SQL Server 2008） [英] How to Optimize the Use of the "OR" Clause When Used with Parameters (SQL Server 2008)

问题描述

我想知道是否有任何明智的方法可以重写以下查询，以便优化器使用列上的索引？

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

即使WHERE子句中的列被索引覆盖，SQL Server也无法使用这些索引。这提出了一个问题，即是否有任何东西阻止索引的使用。这个问题的答案是肯定的 - 罪魁祸首是参数和或条件。索引不包含这些参数，这意味着SQL Server无法使用任何索引来评估@ key1 = 0（同样适用于@ key2 = 0的条件）。实际上，这意味着SQL Server无法使用索引来评估子句@ key1 = 0 OR Key1 = @ key1（因为OR子句是两个条件所涵盖的行的并集）。同样的原则也适用于其他条款（re.key2）。这导致SQL Server得出结论，没有索引可用于提取行，使SQL Server使用下一个最佳方法 - 聚簇索引扫描

如您所见，如果WHERE子句中的谓词为OR，则SQL优化器将不使用列上的索引。这个问题的一个解决方案是使用IF子句为所有可能的参数组合分离查询。

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.

请阅读这篇简短的文章以更好地了解问题：< a href =http://www.sql-server-performance.com/articles/per/optimize_or_clause_p1.aspx =noreferrer> http://www.sql-server-performance.com/articles/per/ optimize_or_clause_p1.aspx

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?

推荐答案

SQL Server 是在优化 OR 谓词时不是很好。

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

使用此：

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 将在执行查询之前查看变量的值，并优化冗余查询。

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.

这篇关于如何优化“OR”的使用与参数一起使用时的子句（SQL Server 2008）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！