WHERE clause better execute before IN and JOIN or after

Just re-reading Paul White's excellent series on the Query Optimiser and remembered this question.

It is possible to use an undocumented command to disable specific transformation rules and get some insight into the transformations applied.

For (hopefully!) obvious reasons only try this on a development instance and remember to re-enable them and remove any suboptimal plans from the cache.

USE AdventureWorks2008;

/*Disable the rules*/
DBCC RULEOFF ('SELonJN');
DBCC RULEOFF ('BuildSpool');


 SELECT  P.ProductNumber, 
         P.ProductID, 
        I.Quantity
 FROM    Production.Product P
 JOIN    Production.ProductInventory I
         ON  I.ProductID = P.ProductID
WHERE I.ProductID < 3
OPTION (RECOMPILE)

You can see with those two rules disabled it does a cartesian join and filter after.

Rules Off Plan

/*Re-enable them*/   
DBCC RULEON ('SELonJN');
DBCC RULEON ('BuildSpool');

 SELECT  P.ProductNumber, 
         P.ProductID, 
        I.Quantity
 FROM    Production.Product P
 JOIN    Production.ProductInventory I
         ON  I.ProductID = P.ProductID
WHERE I.ProductID < 3
OPTION (RECOMPILE)

With them enabled the predicate is pushed right down into the index seek and so reduces the number of rows processed by the join operation.

Rules on Plan


There is no defined order. The SQL engine determines what order to perform the operations based on the execution strategy chosen by its optimizer.


In the case of an INNER JOIN or a table on the left in a LEFT JOIN, in many cases, the optimizer will find that it is better to perform any filtering first (highest selectivity) before actually performing whatever type of physical join - so there are obviously physical order of operations which are better.

To some extent you can sometimes control this (or interfere with this) with your SQL, for instance, with aggregates in subqueries.

The logical order of processing the constraints in the query can only be transformed according to known invariant transformations.

So:

SELECT *
FROM a
INNER JOIN b
    ON a.id = b.id
WHERE a.something = something
    AND b.something = something

is still logically equivalent to:

SELECT *
FROM a
INNER JOIN b
    ON a.id = b.id
    AND a.something = something
    AND b.something = something

and they will generally have the same execution plan.

On the other hand:

SELECT *
FROM a
LEFT JOIN b
    ON a.id = b.id
WHERE a.something = something
    AND b.something = something

is NOT equivalent to:

SELECT *
FROM a
LEFT JOIN b
    ON a.id = b.id
    AND a.something = something
    AND b.something = something

and so the optimizer isn't going to transform them into the same execution plan.

The optimizer is very smart and is able to move things around pretty successfully, including collapsing views and inline table-valued functions as well as even pushing things down through certain kinds of aggregates fairly successfully.

Typically, when you write SQL, it needs to be understandable, maintainable and correct. As far as efficiency in execution, if the optimizer is having difficulty turning the declarative SQL into an execution plan with acceptable performance, the code can sometimes be simplified or appropriate indexes or hints added or broken down into steps which should perform more quickly - all in successive orders of invasiveness.


It doesn't matter

Logical processing order is always honoured: regardless of actual processing order

INNER JOINs and WHERE conditions are effectively associative and commutative (hence the ANSI-89 "join in the where" syntax) so actual order doesn't matter

Logical order becomes important with outer joins and more complex queries: applying WHERE on an OUTER table changes the logic completely.

Again, it doesn't matter how the optimiser does it internally so long as the query semantics are maintained by following logical processing order.

And the key word here is "optimiser": it does exactly what it says

Tags:

Sql Server

Recent Posts