Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Does SQL Server Short-Circuit?

by Jeff Smith in  ,

I got an email recently regarding one of my early blog posts from the olden days:

Steve Kass wrote about your post:"there is no guarantee that WHERE <filter 1> OR <filter 2> will be optimized so that the filters are evaluated in the order typed".

I am not certain that optimization changes the priority of the expressions, but I do not think so. We can force evaluation so that it is done in a certain order by enclosing the first expression to evaluate in parentesis, since enclosed in parentesis expressions are evaluated first,  like this:
((<filter 1>) OR <filter 2>) AND
((<filter 3>) OR <filter 4>)

In this case filter 1 is evaluated before than filter 2 and filter 3 is evaluated before than filter 4.

Thanks in advance

In that original post, I claimed that using efficient OR logic will not only make your code simpler and cleaner, but also more efficient because in theory SQL Server can "short-circuit" on the first part of an OR if it is true, without the need to evaluate the second part. 

However, in one of the comments, it was correctly pointed out that this is not true; you really have no control over how SQL Server will evaluate something -- it will generally do what it thinks is best regardless of the order in which you specify your conditions. 

After getting that email I did some research to determine the truth behind this, and came up with a couple of good resources:
  • This SQLTeam forum thread from last year explores the topic and is a good read.

  • This blog post from Mark Cohen discusses the topic as well, but the best thing to read here is the very last comment from "sc0rp10n" which explains things pretty well. (Plus, with a "l33t" hacker name like that, you know he must know what he is talking about!  These are assigned by some committee somewhere after a vigorous review process, right?)

  • In this TechNet chat, Nigel Ellis, the development manager for the SQL Server Query Processor team, says about halfway into it that SQL Server does indeed short-circuit, but it is not specified if this is something users can control based on how they write their boolean expressions.
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.

Anyone have any thoughts, feedback, further info or links regarding this topic?

Legacy Comments
shamshudheen
2008-06-12		 re: Does SQL Server Short-Circuit?
i am working as a database developer, my onsight developer suggests us split the query into two for "and" condition alone in one query and "or" condition alone in another query. as per his suggestion "or" condition will cause performance issue.

i am also not sure about his suggestion . any information related to this will be appreciatable

thanks in advance
görkem
2008-07-16		 izmir evden eve
thanks you.. byeee
Chris J
2008-11-05		 re: Does SQL Server Short-Circuit?
IME you need to give SQL Server as much help as you can by simplfying conditions, splitting stuff into UNIONs, and generally making sure it doesn't do any unessacery work. Heck, I've gone so far as karnaugh maps to simplify some stuff.

Also, I generally avoid 'OR', unless the OR is working on the same column (in which case it's just a version of col1 IN ('','',...) ). Main reason? Performance. If querying against a single table, you'll end up with table scans instead of seeks - depending on what you're doing this may or may not be aceptable.

But don't use OR between two tables: I've had to fix a few stored procs where someone's done something similar to:
SELECT ... FROM tableA INNER JOIN tableB ON ... WHERE tableA.col1 = '' OR tableB.col1 = '' ... and I've seen everything: from SQL Server doing internal cross joins, to massive parallelisms to a whole lot of pain, that I generally split them down:

SELECT ... FROM tableA INNER JOIN tableB ON ... WHERE tableA.col1 = ''
UNION
SELECT ... FROM tableA INNER JOIN tableB ON ... WHERE tableB.col1 = ''

which then gets the best index in both cases. Even if it does been lengthier and more annoying SQL.
John Kauffman
2009-04-21		 re: Does SQL Server Short-Circuit?
I have also assumed that SQL would short circuit CASE statements. However, that does not seem to be the case in a recent query I wrote, which I think I'll need to abandon. I tested a simplistic version of a case statement with 6 when statements. I get a resultset in @1 second. When I expand the number of when statements to 136 (don't ask), the same query returns results in 1.5 minutes, even if the first statement evaluates to true.
Chris S
2009-05-20		 re: Does SQL Server Short-Circuit?
Here is some code that might indicate that short-circuting does happen:

DECLARE @tmpTab TABLE (
column1 int,
column2 int
)

declare @param1 int
declare @result int

set @param1 = 3

insert into @tmpTab VALUES(1,1)
insert into @tmpTab VALUES(1,1)
insert into @tmpTab VALUES(1,2)
insert into @tmpTab VALUES(1,2)
insert into @tmpTab VALUES(1,3)
insert into @tmpTab VALUES(1,3)
insert into @tmpTab VALUES(1,0) -- This should generate a divide-by-zero error

select *
from @tmpTab
where column1 = column1
OR (column1/column2 = 1)


If you switch the clause around then yes it will give an error indicating short-circut but I guess I still ask the question does this happen all the time?
D Bullen
2010-01-07		 re: Does SQL Server Short-Circuit?
Like John Kauffman, I have found evidence that the CASE expression does not short circuit. In the following example, the execution plan shows that test_tab_2 is scanned, even though the expression should short circuit after the first line ("WHEN 1 THEN 1").

CREATE TABLE test.test_tab (col1 INT)
GO

INSERT INTO test.test_tab VALUES (1)

CREATE TABLE test.test_tab_2 (col1 INT)
GO

INSERT INTO test.test_tab_2 VALUES (999)


SELECT * FROM test.test_tab

SELECT
CASE col1
WHEN 1 THEN 1
ELSE (SELECT col1 FROM test.test_tab_2)
END
FROM test.test_tab

I've also done a test like he described, adding many unnecessary WHEN scenarios, and the more I add, the longer the query takes.
Paul White NZ
2010-07-30		 re: Does SQL Server Short-Circuit?
SQL Server does short-circuit sometimes, but the rules are complex, undocumented, and subject to change at any time. It seems unwise to depend on such things.

In general, predicate evaluation order isn't guaranteed either:

blogs.msdn.com/.../596401.aspx

Paul
www.sqlblog.com/blogs/paul_white
Paul White NZ
2010-07-30		 re: Does SQL Server Short-Circuit?
To D Bullen:

Take a look at the execution plan for your query, and notice that the Nested Loops join has a 'Pass Through' condition. The subquery is built into the plan for correctness and reusability reasons, but if col1 = 1, the outer input to the join is not actually executed (SQL Server 2008 build 2775).

Paul