CODEWITHSUNDEEP
sql-serverjoin
Craig
Craig

Reputation: 18734

Query performance - INNER Join filtering

I would like to work out which query would be faster. I think that the JOINS are done first, and then the WHERE cause.

So, that sounds like this:

SELECT * 
FROM Table1 t1
INNER JOIN Table2 t2
  ON t1.field = t2.field
  AND t2.Deleted = 0
INNER JOIN Table2 t3
  ON t2.field = t3.field
  AND t3.Deleted = 0
WHERE t1.Deleted = 0

Would be faster than:

SELECT * 
FROM Table1 t1
INNER JOIN Table2 t2
  ON t1.field = t2.field
INNER JOIN Table2 t3
  ON t2.field = t3.field
WHERE
  t1.Deleted = 0 AND
  t2.Deleted = 0 AND
  t3.Deleted = 0

The joins in the first query would filter out the data earlier, and hence, less joining.

(I understand thi8s can be different when you have LEFT joins)

Upvotes: 2

Views: 208

Answers (1)

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

Note that the WHERE clause you provide is in the form called Conjunctive Normal Form. Whenever your query has simple comparison or equality terms joined in Conjunctive Normal Form the optimizer would be completely negligent if it did not perform the transformation from your Style 2 to your Style 1. I believe it can be safely assumed that the optimizers for ALL mature SQL products are quite capable of efficiently optimizing simple predicates in either Disjunctive or Conjunctive Normal Form.

However, at a certain point the optimizer has to stop analyzing the query and begin constructing the optimized query. In cases where your filter clauses are complex, there can be benefit in helping the optimizer out by locating clauses in the correct sub-query.

However, this step is not usually performed unless the query actually tests as non-performant. Human labour is much more expensive than CPU cycles, and that includes the time spent by humans in reading code as part of code review and maintenance. Until a query has been proved non-performant to the point of requiring manual tuning, it is best to keep your filtering terms in the WHERE clause, where they can be readily identified and verified

Upvotes: 2

Related Questions