通过添加未使用的 WHERE 条件来延长查询运行时间 [英] Query running longer by adding unused WHERE conditions
问题描述
我遇到了一个有趣的问题(至少对我来说很有趣).以下是我的查询外观的一般概念.假设@AuthorType 是存储过程的输入，并且我在每个注释的地方都有各种特殊的条件.
I've hit an interesting snag (interesting to me at least). Below is a general idea of what my query looks like. Assume @AuthorType is an input to the stored procedure and that there are various specialized conditions each place I've put comments.
SELECT *
FROM TBooks
WHERE
(--...SOME CONDITIONS)
OR
(@AuthorType = 1 AND --...DIFFERENT CONDITIONS)
OR
(@AuthorType = 2 AND --...STILL MORE CONDITIONS)
对我来说有趣的是，如果我使用 @AuthorType = 0 执行这个 SP，它的运行速度比我删除最后两组条件(为 @AuthorType 的特殊值添加条件的条件)要慢.
What's interesting to me is that if I execute this SP with @AuthorType = 0, it runs slower than if I remove the last two sets of conditions (the ones that add conditions for specialized values of @AuthorType).
SQL Server 不应该在运行时意识到这些条件永远不会满足并完全忽略它们吗?我所经历的差异不小；它大约是查询长度的两倍(1-2 秒到 3-5 秒).
Shouldn't SQL Server realize at runtime that those conditions will never be met and ignore them entirely? The difference I'm experiencing is not small; it's approximately doubling the length of the query (1-2 seconds to 3-5 seconds).
我是否期望 SQL Server 为我优化太多?对于特殊情况，我真的需要有 3 个独立的 SP 吗?
Am I expecting SQL Server to optimize this too much for me? Do I really need to have 3 separate SPs for specialized conditions?
推荐答案
SQL Server 不应该在这些条件将运行时永远不会遇到并完全忽略它们?
Shouldn't SQL Server realize at runtime that those conditions will never be met and ignore them entirely?
不，绝对不是.这里有两个因素在起作用.
No, absolutely not. There are two factors at play here.
SQL Server不保证布尔运算符短路.请参见 关于 SQL Server 布尔运算符短路 示例清楚地显示查询优化如何反转布尔表达式评估的顺序.虽然在第一印象中，这对于像命令式 C 这样的编程思维方式来说似乎是一个错误，但对于面向 SQL 的声明式集合世界来说，这是正确的做法.
SQL Server does not guarantee boolean operator short circuit. See On SQL Server boolean operator short-circuit for an example showing clearly how query optimization can reverse the order of boolean expression evaluation. While at a first impression this seems like a bug to the imperative C like programming mind set, it is the right thing to do for declarative set oriented world of SQL.
OR 是 SQL SARGability 的敌人.SQL 语句被编译成一个执行计划，然后执行该计划.该计划在调用之间被重用(被缓存).因此，SQL 编译器必须生成一个适合所有单独 OR 情况的单一计划(@AuthorType=1 AND @AuthorType=2 AND @AuthorType=3).在生成查询计划时，从某种意义上说，就好像 @AuthorType 一次拥有所有值一样.结果几乎总是最糟糕的计划，一个对任何索引都没有好处的计划，因为各种 OR 分支相互矛盾，所以它最终会扫描整个表并逐行检查行.
OR is the enemy of SQL SARGability. SQL statements are compliled into an execution plan, then the plan is executed. The plan gets reused between invocations (is cached). As such the SQL compiler has to generate one single plan that fits all separate OR cases (@AuthorType=1 AND @AuthorType=2 AND @AuthorType=3). When it comes to generating the query plan is it exactly as if @AuthorType would have all values at once, in a sense. The result is almost always the worst possible plan, one that cannot benefit any index because the various OR branches contradict each other, so it ends up scanning the whole table and checking rows one by one.
在您的情况以及任何其他涉及布尔 OR 的情况下，最好的做法是将 @AuthorType 移到查询之外:
The bestthing to do in your case, and any other case that involves boolean OR, is to move the @AuthorType outside the query:
IF (@AuthorType = 1)
SELECT ... FROM ... WHERE ...
ELSE IF (@AuthorType = 2)
SELECT ... FROM ... WHERE ...
ELSE ...
因为每个分支都清楚地分成自己的语句，SQL 可以为每个单独的案例创建正确的访问路径.
Because each branch is clearly separated into its own statement, SQL can create the proper access path for each individual case.
下一个最好的方法是使用 UNION ALL，这是 chadhoc 已经建议的方式，并且是视图或其他需要单个语句的地方(不允许使用 IF)的正确方法.
The next best thing is to use UNION ALL, the way chadhoc already suggested, and is the right approach in views or other places where a single statement is required (no IF is permitted).
这篇关于通过添加未使用的 WHERE 条件来延长查询运行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
