如何优化“OR”的使用与参数一起使用时的子句 (SQL Server 2008)

发布于 2024-08-19 18:23:52 字数 1203 浏览 9 评论 0原文

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

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

根据这篇文章如何在与参数一起使用时优化“OR”子句的使用，作者：Preethiviraj Kulasingham：

即使 WHERE 子句中的列被索引覆盖，SQL Server 也无法使用这些索引。这就提出了一个问题：是否有什么东西“阻止”了索引的使用？这个问题的答案是肯定的——罪魁祸首是参数和 OR 条件。
这些参数未被索引覆盖，这意味着 SQL Server 无法使用任何索引来计算 @key1=0（该条件也适用于 @key2=0代码>).
实际上，这意味着 SQL Server 无法使用索引来计算子句 @key1=0 OR Key1= @key1 （因为 OR 子句是所覆盖行的并集通过这两个条件）。同样的原则也适用于其他子句（re.key2）。这导致 SQL Server 得出结论，没有索引可用于提取行，从而使 SQL Server 使用下一个最佳方法 - 聚集索引扫描

如您所见，如果谓词 <，SQL 优化器将不会在列上使用索引在 WHERE 子句中进行 code>OR 运算。此问题的一种解决方案是使用 IF 子句对所有可能的参数组合进行单独查询。

现在我的问题是 - 如果可能的组合不仅仅是三个或四个，我们该怎么办？为每个组合编写单独的查询似乎不是一个合理的解决方案。

对于这个问题还有其他解决方法吗？

原文

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

According to this article How to Optimize the Use of the "OR" Clause When Used with Parameters by Preethiviraj Kulasingham:

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 ORed in the WHERE clause. One solution for this problem, is to separate queries with IF clause for all possible combination of parameters.

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

依靠 2024-08-26 18:23:52

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

使用这个：

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

回复收藏 0 原文

水波映月 2024-08-26 18:23:52

MSSQL 2008有条件简化的优化语法，这里是

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

这将优化常量的使用

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

回复收藏 0 原文

一笑百媚生 2024-08-26 18:23:52

是的 - 仔细使用动态 sql 将解决这个问题。有两种方法可以做到这一点：

如果您是存储过程的“纯粹主义者”，则在存储过程中编写自定义查询字符串并执行该字符串。然后，可以在每次执行时动态编写特定查询，以仅包含相关条件。
如果您对此 SQL 的位置很灵活，则可以（再次小心）在应用程序中编写查询字符串并将其传递到服务器。
当然，危险在于 SQL 注入。因此，您必须非常小心如何将数据从客户端传递到动态 sql 语句中。

来自 Erland Sommarskog 的真正彻底和全面的文章：

回复收藏 0 原文

殊姿 2024-08-26 18:23:52

您尝试过表值函数吗？

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)

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)

回复收藏 0 原文

~没有更多了~