question

thevillageidiot avatar image
thevillageidiot asked

How is the performance affected by placing filters in where clause or in join

We had some debate few days ago regarding placement of filtering conditions in our queries. A lot of our tables have `status` column which can be `(I)nactive` or `(A)ctive`. We are used to specify the queries as follows: SELECT t1.column1, t2.column1, t2.column3 FROM table1 t1 INNER JOIN table2 t2 ON ( t1.id = t2.t1_fk AND t1.status='A' AND t2.status='A') But we are skeptical if putting the status check on separate `Where` clause will provide any benifits: SELECT t1.column1, t2.column1, t2.column3 FROM table1 t1 INNER JOIN table2 t2 ON ( t1.id = t2.t1_fk) WHERE t1.status='A' AND t2.status='A' We tried some testing and results were inconsistent. For some tables it speed up fetching rows with separate `Where` clause but for others it degraded performance considerably.
t-sqlperformancewherejoin hints
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
In theory, it shouldn't matter where you place that criteria, as long as we're talking about inner joins. But in practice, as you've already seen, it can affect performance. As a general rule, I recommend that you put JOIN criteria in the ON statement and filtering criteria in the WHERE clause and that you don't mix the two. You need to look at the execution plans. If you're getting a timeout on the plan, then the optimizer didn't have enough opportunity to rearrange the criteria to find an optimal solution.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

In following Grant's advice you also gain in clearer code. Month's later you or someoone new to it will be able to quickly see which are the JOIN compnents and which are the filters.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.