Query Performance Tuning – A Methodical Approach

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  February 12, 2017 at 5:35 am

  #1927987

  Aaron N. Cutshall - Friday, February 10, 2017 3:16 PM

  Thanks Gail.  Some time ago I began the habit of consolidating things that were criteria specific for the tables in the join and left joins may be the reason.

  To be blunt, if I got code for a code review that had filters in the FROM clause, I'd send it back.

  Filters belong in the WHERE clause (or HAVING if they're on aggregates), not in the FROM clause, with the sole exception of when the filter needs to be done before a LEFT JOIN. Mixing joins and filters results in a query that's harder to read, harder to figure out what is being filtered out than if the filters were all in the WHERE, and does not get you any performance benefits.

  When dealing with outer joins, filtering in the FROM or the WHERE result in two different queries with two different results, and it's not the case that one is correct and one is not.

  eg
  Customers LEFT JOIN Orders on <join condition>
  WHERE Orders.OrderValue<1000 or OrderID is null
  will return just customers who have never made an order, or if they have, only if they've made orders below R1000 (and those orders)

  Customers LEFT JOIN Orders on <join condition> and Orders.OrderValue<1000
  will return all customers and their orders if under R1000.

  Which one of those is correct depends on what the requirement is. And, tbh, the latter I'll often prefer joining to a subquery over the filter in the join. Makes it clearer when things are being executed.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass