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

Improve this question
4
  • 1
    Did you compare the query plans?
    – Oded
    Commented Feb 1, 2012 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
    Commented Feb 1, 2012 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
    – user1822
    Commented Feb 2, 2012 at 14:33
  • Related: dba.stackexchange.com/questions/5038/…
    – Mark Storey-Smith
    Commented Feb 8, 2012 at 8:14

2 Answers 2

Reset to default
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.

Improve this answer
0
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)

Improve this answer
4
  • 1
    INNER JOIN is not commutative in SQL (where did you get that idea? <g>), 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
    Commented Feb 9, 2012 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ᵀᴹ
    Commented Feb 9, 2012 at 13:48
  • Unlike the relational model.
    – ypercubeᵀᴹ
    Commented Feb 9, 2012 at 14:14
  • 1
    you are correct on all points. Your answer superseded mine so I deleted mine and upvoted yours :)
    – onedaywhen
    Commented Feb 9, 2012 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.