Looking at "SQL Server 2008 Internals"1 by Kalen Delaney, on page 13, it states the following:
"The first step in producing such a plan is to normalize each query, which potentially breaks down a single query into multiple, fine-grained queries. After the Query Optimizer normalizes a query, it optimizes it, which means that it determines a plan for executing that query."
It's been suggested to me by another DBA that performance can be improved for certain queries by moving
WHERE clause predicates into the
FROM clause, such that:
SELECT * FROM dbo.table1 t1 INNER JOIN dbo.table3 t3 ON t1.ID = t3.ID LEFT OUTER JOIN dbo.table2 t2 ON t1.ID = t2.ID WHERE t1.CreateDate >= '2015-07-31 00:00:00';
SELECT * FROM dbo.table1 t1 INNER JOIN dbo.table3 t3 ON t1.ID = t3.ID AND t1.CreateDate >= '2015-07-31 00:00:00' LEFT OUTER JOIN dbo.table2 t2 ON t1.ID = t2.ID;
Apparently the implication for the first example is the query optimizer will perform the
JOIN first, then apply the
WHERE clause, which for complicated queries with large tables will not perform as well as if we manually re-write the query into the form presented in the second example. Apparently this only applies to outer joins, i.e. if this were an inner join, the normalizer would in fact move the
WHERE predicate into the
Can someone confirm this behavior? Is there some documentation somewhere I can look at? Can I see this in the execution plan?
1 - yes, I know this is ancient.