|
我们来看看这两个告诉：
IF (CONDITION 1) OR (CONDITION 2)
...
IF (CONDITION 3) AND (CONDITION 4)
...
假如前提1为TRUE,是否会搜查前提2？
假如前提3为假,将搜查前提4吗？
WHERE上的前提怎么样？SQL Server引擎是否优化了WHERE子句中的全部前提？措施员是否应该按正确的次序安排前提以确保SQL Server优化器以正确的方法理会它？
添加：
感激Jack的链接,来自t-sql代码的惊喜：
IF 1/0 = 1 OR 1 = 1
SELECT 'True' AS result
ELSE
SELECT 'False' AS result
IF 1/0 = 1 AND 1 = 0
SELECT 'True' AS result
ELSE
SELECT 'False' AS result
在这种环境下,没有提出除零除外.
结论：
假如C/C++#/ VB有短路,为什么SQL Server不能拥有它？
To truly answer this let’s take a look at how both work with
conditions. C++/C#/VB all have short circuiting defined in the
language specifications to speed up code execution. Why bother
evaluating N OR conditions when the first one is already true or M
AND conditions when the first one is already false.
We as developers have to be aware that SQL Server works differently.
It is a cost based system. To get the optimal execution plan for our
query the query processor has to evaluate every where condition and
assign it a cost. These costs are then evaluated as a whole to form a
threshold that must be lower than the defined threshold SQL Server has
for a good plan. If the cost is lower than the defined threshold the
plan is used,if not the whole process is repeated again with a
different mix of condition costs. Cost here is either a scan or a seek
or a merge join or a hash join etc… Because of this the
short-circuiting as is available in C++/C#/VB simply isn’t possible.
You might think that forcing use of index on a column counts as short
circuiting but it doesn’t. It only forces the use of that index and
with that shortens the list of possible execution plans. The system is
still cost based.
As a developer you must be aware that SQL Server does not do
short-circuiting like it is done in other programming languages and
there’s nothing you can do to force it to.
办理要领
在SQL Server中无法担保在WHERE子句中处理赏罚语句的次序或次序.应承语句短路的单个表达式是CASE-WHEN.以下是我在Stackoverflow上宣布的谜底：
How SQL Server short-circuits WHERE condition evaluation
It does when it feels like it,but not in the way you immediately think of.
As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there’s nothing you can do to force it to.
有关具体信息,请查察上述博客条目中的第一个链接,该链接指向另一个博客：
Does SQL Server Short-Circuit?
The final verdict? Well,I don’t really have one yet,but it is probably safe to say that the only time you can ensure a specific short-circuit is when you express multiple WHEN conditions in a CASE expression. With standard boolean expressions,the optimizer will move things around as it sees fit based on the tables,indexes and data you are querying.
（编辑：湖南网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
