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.

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

1

Did you compare the query plans?
–
OdedFeb 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.
–
YuckFeb 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_nameFeb 2 '12 at 14:33

2 Answers
2

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.

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.

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.
–
onedaywhenFeb 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.
–
ypercubeFeb 9 '12 at 13:48