Jeff's SQL Server Blog

# 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

# izmir evden eve

thanks you.. byeee

# 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.

# 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.

# 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?

# 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.

# 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

# 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