Do the order of JOINs make a difference?

Question

Say I have a query like the one below:

SELECT t1.id, t1.Name
FROM Table1 as t1 --800,000 records
INNER JOIN Table2 as t2 --500,000 records
ON t1.fkID = t2.id
INNER JOIN Table3 as t3 -- 1,000 records
ON t1.OtherId = t3.id

Would i see a performance improvement if I changed the order of my joins on Table2 and Table3. See below:

SELECT t1.id, t1.Name
FROM Table1 as t1 --800,000 records
INNER JOIN Table3 as t3 -- 1,000 records
ON t1.OtherId = t3.id
INNER JOIN Table2 as t2 --500,000 records
ON t1.fkID = t2.id

I've heard that the Query Optimizer will try to determine the best order but doesn't always work. Does the version of SQL Server you are using make a difference?

Answer 1

The order of joins makes no difference.

What does make a difference is ensuring your statistics are up to date.

One way to check your statistics is to run a query in SSMS and include the Actual execution plan. If the Estimated number of rows is very different to the Actual number of rows used by any part of the execution plan, then your statistics are out of date.

Statistics are rebuilt when the related indexes are rebuilt. If your production maintenance window allows, I would update statistics every night.

This will update statistics for all tables in a database:

exec sp_MSforeachtable "UPDATE STATISTICS ?"

Answer 2

The Query Optimizer should easily handle these as exactly the same query, and work out the best way of doing it.

A lot of it is more about the statistics than the number of records. For example, if the vast majority of values in t1.fkID are identical, this information can influence the QO a lot.

Answer 3

The order of joins makes a difference only if you specify OPTION (FORCE ORDER). Otherwise, the optimizer will rearrange your query in whichever way it deems most efficient.

There actually are certain instances where I find that I need to use FORCE ORDER, but of course they are few and far between. If you aren't sure, just SET STATISTICS [TIME|IO] ON and see for yourself. You'll probably find that your version runs slower than the optimized version in most if not all cases.