2

Possible Duplicate:
SQL Server - Query Short-Circuiting?
Is the SQL WHERE clause short-circuit evaluated?

I have a question regarding performance of logical OR operators in T-SQL (SQL Server 2005).

I have searched around a little but I couldn't find anything on the subject.

If you have the following query:

SELECT * FROM Table WHERE (randomboolean OR HeavyToEvaluateCondition)

Wouldn't the procedure interpreter go as far as the randomboolean and skip evaluation of the heavy condition in order to save performance given that the first condition is true?

Since one of the values in an OR statement is true it would be unnecessary to evaluate the second condition since we already know that the first condition is met!

I know it works like this in C# but I want to know if I can count on it in T-SQL too.

Community
  • 1
  • 1
DOOMDUDEMX
  • 644
  • 1
  • 8
  • 24

3 Answers3

5

You can't count on short circuit evaluation in TSQL.

The optimiser is free to evaluate the conditions in which ever order it sees fit and may in some circumstances evaluate both parts of an expression even when the second evaluation cannot change the result of the expression (Example).

That is not to say it never does short circuit evaluation however. You may well get a start up predicate on the expensive condition so it is only executed when required.

Additionally the presence of the OR in your query can convert a sargable search condition into an unsargable one meaning that indexes are not used optimally. Especially in SQL Server 2005 (In 2008 OPTION (RECOMPILE) can help here).

For example compare the plans for the following. The version with OR ends up doing a full index scan rather than an index seek to the specific values.

DECLARE @number INT;
SET number  = 0;

SELECT COUNT(*)
FROM master..spt_values
WHERE @number IS NULL OR number = 0

SELECT COUNT(*)
FROM master..spt_values
WHERE  number = 0

Plan

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    It's worth explaining that a lot of this is due to parallelism - SQL Server is checking both conditions at the exact same time in separate threads, which is still more efficient than checking the shortest first. – JNK Dec 15 '11 at 13:26
  • does that mean evaluation of the second condition ends if the first one is done evaluating before the other? – DOOMDUDEMX Dec 15 '11 at 13:29
  • 1
    @JNK - Good point that it could evaluate these expressions in parallel. Even on serial plans though it still might evaluate all sub expressions unnecessarily. – Martin Smith Dec 15 '11 at 13:29
  • @user1099885 - it depends on statistics and other factors. There's no guarantee, basically. And again it could check both conditions no matter what. – JNK Dec 15 '11 at 13:30
  • @JNK sorry. I wrote the comment a bit slow so i read your reply too late. Please read my edit... – DOOMDUDEMX Dec 15 '11 at 13:32
  • @user1099885 - probably not, but MAYBE. Again, it depends. – JNK Dec 15 '11 at 13:34
1

Its called short-circuiting. And yes SQL Server does do it in certain cases. In what order depends on many factors and forms part of the execution plan optimisation.

However, there are details online that this is limitted to JOIN conditions, CASE statements, etc.

See this SO post... SQL Server - Query Short-Circuiting?

Community
  • 1
  • 1
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • short-circuiting usual refers to a form where predicates are evaluated in a strict order, until such time as the entire result is known, and then no other predicates are evaluated (e.g. left to right evaluation). But as you observe, the order in which predicates are evaluated in SQL aren't strictly defined, so I wouldn't describe its behaviour as short-circuiting – Damien_The_Unbeliever Dec 15 '11 at 13:20
-4

Firstly where condition is executed than OR operator is executed when control goes to first condition.if first condition is true than it is not check the second condition .if you are given 100 condition and in this scenario first condition is false then it check next condition.

Pradeep Sharma
  • 35
  • 1
  • 7
  • 3
    -1 SQL Server does not guarantee left to right evaluation as you suggest. – Martin Smith Dec 15 '11 at 13:22
  • 4
    @JNK - Sure he could... Firstly the query is parsed and the number of characters in each condition determines the order of evaluation... SQL prefers purple, so the expression closest to that colour always evaluates first... Recent tests at the LHC show that certain information can travel faster than light, so the optimiser sends the results back in time to re-inform itself as to the most efficient execution plan. – MatBailie Dec 15 '11 at 13:46
  • @Dems - good points. Also it depends on what phase the moon is in. – JNK Dec 15 '11 at 13:49