5

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000.

I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead of the end.

In reference of http://sql-4-life.blogspot.com/2009/03/order-of-inner-joins.html

Taryn
  • 9,676
  • 3
  • 45
  • 74
Adam Paquette
  • 161
  • 3
  • 6
  • 1
    Did you compare the query plans? –  Feb 01 '12 at 16:33
  • 1
    Please post relevant code sections on this site. Don't expect external references to always be available. Without the link, this question is unanswerable. – Yuck Feb 01 '12 at 16:34
  • 2
    I wouldn't be surprised if the performance gain was due to caching on the server side when you ran the statement the second time. Change it back and re-check the execution time –  Feb 02 '12 at 14:33
  • Related: http://dba.stackexchange.com/questions/5038/sql-server-join-where-processing-order – Mark Storey-Smith Feb 08 '12 at 08:14

2 Answers2

7

The optimal sequence (the query plan) is determined by the query executor, based on the SQL code (operators you use), data statistics (statistical distribution of values in columns, data volume, etc.) and database structure (availability of indexes, datatypes, etc.). So, there are rules, but first not all of them are under your total control and second the result is detemined by a combination of many factors.

To evaluate performance, you should always take a look at the estimated query plan and compare the changes to the plan caused by the changes in your SQL code, to undertand why a query run faster or slower than another.

Andrea Colleoni
  • 196
  • 4
3

The order you write the JOINs is not relevant. Most, if not all, optimizers (and certainly SQL-Server's optimizer) know that JOIN is associative and commutative.

Finding the best execution plan (which means join order, join algorithm for each join, choice of indices to use, etc), is a very hard problem which is exactly what query optimizers were built to solve and they use various and complex heuristics and techniques. But the order we write the joins in our queries is not taken into consideration, unless (maybe) as a starting point.

Choosing between the (possibly millions or trillions) ways of ordering the joins is far from trivial. It's like being thrown in a planet with a terrain full of mountain peaks and bottomless pits with the aim to find the lowest point. The starting position has negligible effect on this quest except maybe in very complex queries where the optimizer obviously cannot spend eternity considering all possible plans.

If I am not wrong, Simulated annealing is one of the heuristics/techniques used by The SQL Server Query Optimizer.
 (I was wrong)

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
  • 1
    INNER JOIN is not commutative in SQL (where did you get that idea? ), strictly speaking, because of column ordering e.g. if it were true then the following would result in a single row, however it generates a type conversion error: WITH T1 AS (SELECT * FROM (VALUES(1, 2)) AS T (c1, c2)), T2 AS (SELECT * FROM (VALUES(1, 'x')) AS T (c1, c3)) SELECT * FROM T1 INNER JOIN T2 ON T1.c1 = T2.c1 UNION SELECT * FROM T2 INNER JOIN T1 ON T1.c1 = T2.c1; -- of course this problem can be mitigated using explicit projection. – onedaywhen Feb 09 '12 at 10:16
  • @onedaywhen: Had you had an answer and then deleted it? I could swear there was on here, yesterday. Off course you are right, that INNER JOIN is not commutative in SQL but only because the columns have an order. – ypercubeᵀᴹ Feb 09 '12 at 13:48
  • Unlike the relational model. – ypercubeᵀᴹ Feb 09 '12 at 14:14
  • 1
    you are correct on all points. Your answer superseded mine so I deleted mine and upvoted yours :) – onedaywhen Feb 09 '12 at 14:33