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

问题描述：

我碰到了一个有趣的障碍（至少对我来说很有意思）。以下是我的查询的一般概念。假设@AuthorType是存储过程的输入，并且每个放置注释的地方都有各种专用条件。通过添加未使用的WHERE条件查询运行时间更长

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

有趣的对我来说，如果我执行这个SP与@AuthorType = 0，它的运行速度比如果我删除最后两组条件（即添加条件@AuthorType专门的值的）慢。

不应该SQL Server在运行时意识到这些条件永远不会被满足，并完全忽略它们吗？我正在经历的差距不小;它大约是查询长度的两倍（1-2秒到3-5秒）。

我是否期待SQL Server为我优化这个太多？我是否真的需要针对特定条件拥有3个独立的SP？

答

不应该SQL Server将在运行时意识到，这些条件将从未得到满足，完全忽略它们？

不，绝对不是。这里有两个因素。

SQL Server会不保证布尔运算符短路。查看On SQL Server boolean operator short-circuit的示例清楚地显示了查询优化如何反转布尔表达式评估的顺序。虽然在第一印象中，这似乎是像编程思维集合这样的命令式C的缺陷，但对于面向声明式集合的SQL来说，这是正确的。 OR是SQL SARGability的敌人。 SQL语句被编译成执行计划，然后计划被执行。该计划在调用之间被重用（被缓存）。因此，SQL编译器必须生成适合所有独立OR情况（@ AuthorType = 1 AND @ AuthorType = 2 AND @ AuthorType = 3）的单个计划。当涉及到生成查询计划时，就好像@AuthorType在一定意义上一次具有所有值。其结果几乎总是最糟糕的计划，因为各个OR分支相互矛盾，所以它不能使任何索引受益，所以它最终扫描整个表并逐一检查行。

你的情况做的bestthing，以及涉及布尔OR，是移动@AuthorType查询以外的任何其他情况：

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

因为每个分支都明显地分为了自己语句，SQL可以为每个个案创建适当的访问路径。

下一个最好的事情是使用UNION ALL，chadhoc已经提出的方法，并且在需要单个语句（不允许IF）的视图或其他地方使用正确的方法。

答

它要归功于优化器处理“OR”型逻辑的难度，以及与parameter sniffing一起处理issues的难度。尝试将上面的查询更改为联合方法，如后here中所述。也就是说，你将结束多个语句联合，只有一个@AuthorType = x AND，允许优化器排除AND逻辑与给定的@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 
...

我欣赏技术和链接的SO页面。两者都会让我思考。也许大卫B是对的，我应该反对减少重复的冲动......但是男人，那真的不适合我。 – 2009-11-10 17:12:06

答

我应该打击减少重复的冲动......但是男人，那真的不适合我。

这种“感觉”会好吗？

 
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只是获取PK的WHERE子句。然后将其加回到原始表格（以及其他任何需要的表格）以获得“演示文稿”。这样可以避免重复每个联合全部中的表示列

您可以进一步将PK插入临时表中，然后将其加入到其他表中以用于表示方面。

我们为非常大的表执行此操作，用户在查询内容时有很多选择。

 
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一个:)]，包括那些晦涩难懂这是（可以说）不是在原来的过滤器，打造@MyTempTable。

创建@MyTempTable的目的是为了使任何参数都可用。也许如果@LastName和@Country都可用，填充表格比其中任何一个效率高得多，那么我们为该场景创建一个案例。

缩放问题？查看正在进行的实际查询，并为可以改进的查询添加案例。

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

相关推荐