SQL Server 条件流 [英] SQL Server Conditional Flow

问题描述

如果我在 IF EXISTS 条件中写了两个 SELECT 语句，在这些选择查询之间有一个 AND 子句，两个查询是否都被执行即使第一个 SELECT 返回 false?

If I write two SELECT statements in a IF EXISTS condition with a AND clause in between these select queries, does both queries get executed even if the first SELECT returns false?

IF EXISTS (SELECT....) AND EXISTS(SELECT ....)
BEGIN

END

在这种情况下，SQL Server 引擎是否同时执行 SQL 语句?

Does the SQL Server Engine execute both the SQL Statement in this scenario?

谢谢克里斯

推荐答案

I would rewrite the test as

I would rewrite the test as

IF CASE
     WHEN EXISTS (SELECT ...) THEN CASE
                                   WHEN EXISTS (SELECT ...) THEN 1
                                 END
   END = 1  

这保证了短路如此处所述 但确实意味着您需要选择最便宜的来进行预先评估，而不是让优化者来决定.

This guarantees short circuiting as described here but does mean you need to select the cheapest one to evaluate up front rather than leaving it up to the optimiser.

在我下面极其有限的测试中，以下内容在测试时似乎成立

In my extremely limited tests below the following seemed to hold true when testing

EXISTS AND EXISTS 版本似乎问题最大.这将一些外部半连接链接在一起.在任何情况下，它都没有重新安排测试的顺序以尝试先进行更便宜的测试(本博文后半部分讨论的问题).在 IF ... 版本中，如果它没有短路，它不会有任何区别.然而，当这个组合谓词放在 WHERE 子句中时，计划会发生变化，并且它确实短路，因此重新排列可能是有益的.

The EXISTS AND EXISTS version seems most problematic. This chains together some outer semi joins. In none of the cases did it re-arrange the order of the tests to try and do the cheaper one first (an issue discussed in the second half of this blog post). In the IF ... version it wouldn't have made any difference if it had as it did not short circuit. However when this combined predicate is put in a WHERE clause the plan changes and it does short circuit so that rearrangement could have been beneficial.

/*All tests are testing "If False And False"*/

IF EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
AND EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1)
PRINT 'Y'
/*
Table 'spt_values'. Scan count 1, logical reads 9
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

IF EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1) 
AND EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2) 
PRINT 'Y'
/*
Table 'spt_monitor'. Scan count 1, logical reads 1
Table 'spt_values'. Scan count 1, logical reads 9
*/

SELECT 1
WHERE  EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
AND EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1)
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

SELECT 1
WHERE  EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1) 
AND EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2) 
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_values'. Scan count 1, logical reads 9

*/

所有这些的计划看起来非常相似.SELECT 1 WHERE ... 版本和 IF ... 版本之间行为差异的原因是，对于前者，如果条件为假，则正确的行为是不返回任何结果，因此它只是链接 OUTER SEMI JOINS，如果一个为假，则零行结转到下一个.

The plans for all these appear very similar. The reason for the difference in behaviour between the SELECT 1 WHERE ... version and the IF ... version is that for the former one if the condition is false then the correct behaviour is to return no result so it just chains the OUTER SEMI JOINS and if one is false then zero rows carry forward to the next one.

然而，IF 版本总是需要返回 1 或零的结果.如果 EXISTS 测试未通过(而不是简单地丢弃该行)，则此计划在其外部联接中使用探测列并将其设置为 false.这意味着总是有 1 行进入下一个 Join 并且它总是被执行.

However the IF version always needs to return a result of 1 or zero. This plan uses a probe column in its outer joins and sets this to false if the EXISTS test is not passed (rather than simply discarding the row). This means that there is always 1 row feeding into the next Join and it always gets executed.

CASE 版本有一个非常相似的计划，但它使用了一个 PASSTHRU 谓词，如果前面的 THEN 不满足条件.我不确定为什么组合 AND 不会使用相同的方法.

The CASE version has a very similar plan but it uses a PASSTHRU predicate which it uses to skip execution of the JOIN if the previous THEN condition was not met. I'm not sure why combined ANDs wouldn't use the same approach.

EXISTS OR EXISTS 版本使用连接 (UNION ALL) 运算符作为外部半连接的内部输入.这种安排意味着它可以在第一个返回后立即停止从内侧请求行(即它可以有效地短路)所有 4 个查询都以相同的计划结束，其中首先评估更便宜的谓词.

The EXISTS OR EXISTS version used a concatenation (UNION ALL) operator as the inner input to an outer semi join. This arrangement means that it can stop requesting rows from the inner side as soon as the first one is returned (i.e. it can effectively short circuit) All 4 queries ended up with the same plan where the cheaper predicate was evaluated first.

/*All tests are testing "If True Or True"*/

IF EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=1)  
OR EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)<>1)
PRINT 'Y'
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

IF EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)<>1) 
OR EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)= 1) 
PRINT 'Y'
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

SELECT 1
WHERE  EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)= 1)  
OR EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)<>1)
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

SELECT 1
WHERE  EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)<>1) 
OR EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=1) 
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

3.添加 ELSE

我确实想到尝试使用德摩根定律将 AND 转换为 OR 并看看这是否有什么不同.转换第一个查询给出

3. Adding an ELSE

It did occur to me to try De Morgan's law to convert AND to OR and see if that made any difference. Converting the first query gives

IF NOT ((NOT EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
OR NOT EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1)))
PRINT 'Y'
ELSE
PRINT 'N'
/*
Table 'spt_monitor'. Scan count 1, logical reads 1
Table 'spt_values'. Scan count 1, logical reads 9
*/

所以这对短路行为仍然没有任何影响.但是，如果您删除 NOT 并反转 IF ... ELSE 条件的顺序，它现在确实短路！

So this still doesn't make any difference to the short circuiting behaviour. However if you remove the NOT and reverse the order of the IF ... ELSE conditions it now does short circuit!

IF (NOT EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
OR NOT EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1))
PRINT 'N'
ELSE
PRINT 'Y'
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

这篇关于SQL Server 条件流的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！