SQL Server – Logical Operators OR AND in Condition and Order of Conditions in WHERE

conditionoptimizationsql serversql-server-2008t-sql

Let's examine these two statements:

IF (CONDITION 1) OR (CONDITION 2)
...

IF (CONDITION 3) AND (CONDITION 4)
...

If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will CONDITION 4 be checked?

What about conditions on WHERE: does the SQL Server engine optimize all conditions in a WHERE clause? Should programmers place conditions in the right order to be sure that the SQL Server optimizer resolves it in the right manner?

ADDED:

Thank to Jack for link, surprise from t-sql code:

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

There is not raise a Divide by zero exception in this case.

CONCLUSION:

If C++/C#/VB has short-circuiting why can't SQL Server have it?

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.

Best Answer

There's no guarantee in SQL Server if or in which order the statements will be processed in a WHERE clause. The single expression that allows statement short-circuiting is CASE-WHEN. The following is from an answer I posted on 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.

For further details check the first link in the above blog entry, which is leading to another blog:

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.

Related Solutions

Sql-server – Is it correct, order of where clause doesn’t matter when it is used with join

The order of items in the where clause should not make a difference, especially if you use the preferred join syntax as follows:

select a.col1, 
       b.col2 
 from table1 a 
 join table2 b on b.col1 = a.col1
where a.col3 = 10

This keeps the join conditions separated from the filters.

Sql-server – Bitwise operators affecting performance

Short circuiting does not always function the way you might expect in SQL Server. You really have very little say in what order your where clauses are evaluated; even if you do this filtering in a subquery or CTE first, the optimizer still might evaluate it in an order you don't expect. The only two options I've seen to possibly overcome this:

(a) dump the intermediate filtered results to a #temp table.

(b) try adding the bitwise operator to the ON condition, instead of the WHERE clause, and use FORCE ORDER to ensure that the joins are evaluated in the order that you write them. I don't understand the differences between your two queries, and I think the question contains a lot of unnecessary code, but I would try this variation:

FROM 
  dbo.admanAdvertiserAdGroup_tbl AS AA 
INNER JOIN 
  dbo.admanAdvertiserCreative_tbl AS AC 
    ON AA.AdvertiserAdGroupId = AC.AdvertiserAdGroupId 
    AND (AC.Dirty & @dirtyReason > 0)
INNER JOIN 
  dbo.admanMasterAdGroup_tbl AS MA  
    ON MA.MasterAdGroupId = AA.MasterAdGroupId 
...
WHERE AA.AdvertiserId = @AdvertiserId 
...
OPTION (FORCE ORDER);

(I've also taken the liberty to add dbo. to your table references, which you should always be using, and AS for readability. Sorry, I am a little meticulous about this.)

No guarantees, obviously, but worth a try. This is assuming that your conclusion about short circuiting is even correct: how did you come to this conclusion? Can you show the execution plans and what led you to short circuiting as a possible culprit? Is the bulk of the query dealing with way too many rows that theoretically should have been filtered out by the bitwise operator?

EDIT

I've blogged about this scenario: http://www.sqlperformance.com/2012/08/t-sql-queries/dry-principle

Best Answer

Related Solutions

Sql-server – Is it correct, order of where clause doesn’t matter when it is used with join

Sql-server – Bitwise operators affecting performance

Related Question