16

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';

would become:

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 FROM clause.

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.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323

3 Answers3

3

I guess this is one of the "It depends"-moments. In a regular execution there is no or at least nearly no difference.

Thought it will become more interesting once the queries are more complex. More complex scenarios are e.g.:

  • Nested Joins
  • FORCE ORDER (Query Hint)

I would have added LEFT and CROSS Joins as well, thought they are already part of your example.

About the "experience in SQL Server normalization" - I'd say, besides looking in the code, there will not be much you can hold your self on. It is often just a "It depends!"

Dennis Winter
  • 161
  • 1
  • 6
3

While the verdict is out on this topic depending on the DBMS that is in use, I can speak for SQL Server in that I've never seen a different execution plan based on whether or not the WHERE statement was in a JOIN or in the WHERE clause itself. There are a few posts around StackOverflow that discuss this in a bit more detail, but you can confirm it for yourself by reviewing various execution plans with different use-cases. The optimizer is smart enough to know what you're doing (or trying to do) and will result in the same execution plan regardless of where you apply your record filtering.

For a good source of truth, I would recommend checking out the book "Microsoft SQL Server 2012 Internals (http://www.amazon.com/Microsoft-Server-Internals-Developer-Reference/dp/0735658560)".

Note: OUTER JOINs can produce different result-sets when using a WHERE clause in an OUTER JOIN vs. using the WHERE clause explicitly. See the example here: https://ask.sqlservercentral.com/questions/47544/sql-data-filter-condition-in-join-vs-where-clause.html

Chase Marler
  • 56
  • 2
3

Another book you can check for this is "SQL Server Execution Plans, Second Edition" on https://www.red-gate.com/community/books/sql-server-execution-plans-ed-2.

Also, try the Estimated Execution Plan before actually run the query to see what it looks like and if there are some things you can review previous to the execution.

Guillermo Taylor
  • 107
  • 5