查询优化 TSQL 等
假设我有一个如下所示的 sql 语句，其中变量 @FOO 在代码中的较早位置设置：
SELECT FIELDLIST
FROM TABLE
WHERE
(FIELD = @FOO OR @FOO IS NULL)
查询优化器是否足够智能，可以先执行 OR 的第二侧（@FOO IS NULL），因为（另一个假设）它更快进行空检查比进行字段比较？
我已经做了一些非正式的基准测试，不仅没有看到差异，而且在尝试中得到了不同的结果时间，这使得无法进行正确的比较。
Assume I've a sql statement like the following with the variable @FOO set somewhere earlier in code:
SELECT FIELDLIST
FROM TABLE
WHERE
(FIELD = @FOO OR @FOO IS NULL)
Is the query optimizer smart enough to do the second side of the OR first (@FOO IS NULL) because (another assumption) it is faster to do a null check than it is to do a field comparison?
I've done some informal benchmarking and not only do I not see a difference, I get different result times on my attempts which throws off the ability to do a proper comparison.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
根据我的经验，有时使用两个查询和“UNION”而不是“OR”子句甚至更快。
这种方法的缺点是重复 SELECT 语句，但 1500% 的性能提升证明了它的合理性。 当然，这取决于数据库结构（就我而言，它非常糟糕，我无法更改它）。
In my experience, sometimes is even faster to use two queries and an "UNION" instead of an "OR" clause.
This approach has the drawback of duplicating the SELECT statement, but the 1500% performance increase was justifying it. Of course, this depends on the database structure (in my case it was quite bad, and I could not change it).
它足够聪明，可以首先应用更快的条件，假设它可以判断在一般情况下哪个比较会更快。 在这种情况下，NULL 检查几乎总是更快，因为它必须比较表达式每一侧的最多一个字节并可以将其分解出来。
It's smart enough to apply the faster condition first, assuming it can judge which comparison will be faster in the general case. In this instance, the NULL check will almost always be faster because it has to compare at most one byte from each side of the expression and can factor it out.
如果此查询位于存储过程内，则此处可能起作用的一个因素是“参数嗅探”。 这可能会导致查询响应时间不一致。 要解决此问题，请在存储过程中声明一个内部变量并将该变量分配给参数值，然后在 where 子句中使用该内部变量或使用 RECOMPILE 存储过程中的子句。 关于这个主题有很多链接。
One factor that may be in play here if this query is inside a sproc is "parameter sniffing". This can lead to inconsistent query response times. To fix this declare an internal variable in your sproc and assign this variable to the parameter value and then use the internal variable in your where clause or use the RECOMPILE clause in your sproc. There are many links on this subject.
简短的回答...
是的，优化器足够聪明。
更长的答案...
SQL 是声明性的而不是命令性的：您的查询是对结果必须满足的条件的描述，它不是关于如何生成这些结果的分步说明。
优化器以最有效的顺序执行查询。 它不保证以任何特定顺序评估您的子句，甚至根本不评估它们 - 如果它可以在不评估特定子句的情况下获得正确的结果，那为什么还要麻烦呢？
任何特定查询的实际评估顺序是实现细节，并且可以随着时间的推移而改变（例如，随着表上的统计数据的改变）。
在实践中，优化器偶尔会出错，但在这种特殊情况下 - 将变量与 NULL 进行比较与从表或索引中读取 - 我认为搞砸的可能性不大，尽管您可能想要 考虑使用
OPTION(RECOMPILE)或OPTION(OPTIMIZE对于...）。The short answer...
Yes, the optimiser is smart enough.
The longer answer...
SQL is declarative rather than imperative: Your query is a description of the criteria that your results must meet, it is not step-by-step instructions on how to generate those results.
The optimiser executes the query in the most efficient order. It doesn't guarantee to evaluate your clauses in any particular order, or even evaluate them at all - if it can get the correct results without evaluating a particular clause then why would it bother?
The actual evaluation order of any particular query is an implementation detail and can change over time (eg, as the statistics on the table change).
In practice, the optimiser will occasionally get things wrong, but in this particular case - comparing a variable to NULL versus reading from a table or index - I don't think there's much chance of it screwing up, although you may want to consider using
OPTION(RECOMPILE)orOPTION(OPTIMIZE FOR ...).尝试以相反的顺序使用子句进行测试：
您可能会发现第一个测试会使第二个测试短路，但反之则不然。
Try testing it with the clauses in the opposite order:
You may find that the first test will short-circuit the second, but not vice-versa.