SQL Server 2005 - Order of Inner Joins

Question

I have a query containing three inner join statements in the Where clause. The query takes roughly 2 minutes to execute. If I simply change the order of two of the inner joins, performance drops to 40 seconds.

How can doing nothing but changing the order of the inner joins have such a drastic impact of query performance? I would have thought the optimizer would figure all this out.

Answer 1

SQL is declarative, that is, the JOIN order should not matter.

However it can in practice, say, if it's a complex query when the optimiser does not explore all options (which in theory could take months).

Another option is that it's a very different query if you reorder and you get different results, but this is usually with OUTER JOINs.

And it could also be the way the ON clause is specified It has to change if you reorder the FROM clause. Unless you are using the older (and bad) JOIN-in-the-WHERE-clause.

Finally, if it's a concern you could use parenthesis to change evaluation order to make your intentions clear, say, filter on a large table first to generate a derived table.

Answer 2

Because by changing the order of the joins, SQL Server is coming up with a different execution plan for your query (chances are it's changing the way it's filtering the tables based on your joins).

In this case, I'm guessing you have several large tables...one of which performs the majority of the filtering.

In one query, your joins are joining several of the large tables together and then filtering the records at the end.

In the other, you are filtering the first table down to a much smaller sub-set of the data...and then joining the rest of the tables in. Since that initial table got filtered before joining the other large recordsets, performance is much better.

You could always verify but running the query with the 'Show query plan' option enabled and see what the query plan is for the two different join orders.