Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Feb 2 '12 at 14:18

This question came from our site for professional and enthusiast programmers.

1  
Did you compare the query plans? –  Oded Feb 1 '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 1 '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 –  a_horse_with_no_name Feb 2 '12 at 14:33
    
add comment

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
    
+1 Spot on answer. –  Nick Vaccaro Feb 1 '12 at 16:38
add comment

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.

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

share|improve this answer
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 Feb 9 '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 9 '12 at 13:48
    
Unlike the relational model. –  ypercube Feb 9 '12 at 14:14
    
you are correct on all points. Your answer superseded mine so I deleted mine and upvoted yours :) –  onedaywhen Feb 9 '12 at 14:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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