代码之家 › 专栏 › 技术社区 › Jon Smock

通过添加未使用的WHERE条件,查询运行时间更长

where-clause sql-server sql

Jon Smock · 技术社区 · 16 年前

SELECT *
FROM TBooks
WHERE
(--...SOME CONDITIONS)
OR
(@AuthorType = 1 AND --...DIFFERENT CONDITIONS)
OR
(@AuthorType = 2 AND --...STILL MORE CONDITIONS)

我感兴趣的是,如果在@AuthorType=0的情况下执行此SP,它的运行速度会比删除最后两组条件(为@AuthorType的专用值添加条件的条件)的运行速度慢。

SQL Server难道不应该在运行时意识到这些条件永远不会得到满足,而完全忽略它们吗?我所经历的差异并不小;这大约是查询长度的两倍(1-2秒到3-5秒)。

我是否期望SQL Server对此进行过多优化?对于特殊情况,我真的需要3个单独的SP吗?

3 回复 | 直到 16 年前

Remus Rusanu 16 年前

SQL Server不应该在永远不会遇到,完全忽略它们?

不,绝对不是。这里有两个因素在起作用。

SQL Server确实如此不保证布尔运算符短路。看见 On SQL Server boolean operator short-circuit 下面的示例清楚地显示了查询优化如何颠倒布尔表达式求值的顺序。虽然在第一印象中,这似乎是命令式C语言编程思维模式的一个缺陷,但对于面向声明集的SQL世界来说,这是正确的做法。

在您的情况下,以及涉及布尔或的任何其他情况下,最好将@AuthorType移到查询之外:

IF (@AuthorType = 1)
  SELECT ... FROM ... WHERE ...
ELSE IF (@AuthorType = 2)
  SELECT ... FROM ... WHERE ...
ELSE ...

下一个最好的方法是使用UNIONALL,正如chadhoc已经建议的那样,在视图或其他需要一条语句的地方(如果允许的话,则不使用),这是正确的方法。

Community Mohan Dere 9 年前

这是由于优化器处理“或”类型逻辑以及 issues 与 parameter sniffing . 尝试将上面的查询更改为文章中提到的联合方法 here . i、 e.您将得到多个语句,它们联合在一起,只有一个@AuthorType=x,并允许优化器排除和逻辑与给定@AuthorType不匹配的部分,然后依次查找适当的索引。。。看起来像这样:

SELECT *
FROM TBooks
WHERE
(--...SOME CONDITIONS)
AND @AuthorType = 1 AND --...DIFFERENT CONDITIONS)
union all
SELECT *
FROM TBooks
WHERE
(--...SOME CONDITIONS)
AND @AuthorType = 2 AND --...DIFFERENT CONDITIONS)
union all
...

Kristen 16 年前

我应该抵制减少重复的冲动……但是,老兄,我觉得这真的不对。

SELECT ... lots of columns and complicated stuff ...
FROM 
(
    SELECT MyPK
    FROM TBooks
    WHERE 
    (--...SOME CONDITIONS) 
    AND @AuthorType = 1 AND --...DIFFERENT CONDITIONS) 
    union all 
    SELECT MyPK
    FROM TBooks
    WHERE 
    (--...SOME CONDITIONS) 
    AND @AuthorType = 2 AND --...DIFFERENT CONDITIONS) 
    union all 
    ... 
) AS B1
JOIN TBooks AS B2
    ON B2.MyPK = B1.MyPK
JOIN ... other tables ...

伪表B1只是获取PKs的WHERE子句。然后将其连接回原始表(以及任何其他需要的表)以获得“演示”。这样可以避免在每个UNION ALL中重复显示列

我们对非常大的表执行此操作,其中用户可以选择查询什么。

DECLARE @MyTempTable TABLE
(
    MyPK int NOT NULL,
    PRIMARY KEY
    (
        MyPK
    )
)

IF @LastName IS NOT NULL
BEGIN
   INSERT INTO @MyTempTable
   (
        MyPK
   )
   SELECT MyPK
   FROM MyNamesTable
   WHERE LastName = @LastName -- Lets say we have an efficient index for this
END
ELSE
IF @Country IS NOT NULL
BEGIN
   INSERT INTO @MyTempTable
   (
        MyPK
   )
   SELECT MyPK
   FROM MyNamesTable
   WHERE Country = @Country -- Got an index on this one too
END

... etc

SELECT ... presentation columns
FROM @MyTempTable AS T
    JOIN MyNamesTable AS N
        ON N.MyPK = T.MyPK -- a PK join, V. efficient
    JOIN ... other tables ...
        ON ....
WHERE     (@LastName IS NULL OR Lastname @LastName)
      AND (@Country IS NULL OR Country @Country)

请注意,所有测试都是重复的[从技术上讲,您不需要@Lastname one:)],包括(比方说)不在原始过滤器中创建@mytentable的模糊测试。

缩放问题?回顾正在进行的实际查询,并添加可以改进的案例。