SQL Server Performance Forum – Threads Archive

The order of Table joins execution

Hi everyone, I have been developing SQL for about 1 – 2 years now, but theres a basic question I still dont get… (and cant find in Faqs). If I have three tables being joined (Table A, Table B and Table C) using a statement like this: SELECT TableA.Columns, TableB.Columns, TableC.Columns
FROM TableA
JOIN TableB ON (TableA.Column1 = TableB.Column1)
JOIN TableC ON (TableC.Column1 = TableB.Column1) what executes first? Does this give me all the columns from A and B in a table that Joins to C? or do all joins execute similtaneously? There seems to be no perfomance loss or data loss in just three tables, but we have a fairly large database with only 2 programmer / dba’s and I suspect I am losing performance because of the order I am joining my tables (especially when using outer joins and large numbers of tables). Query analyser Execution plan isnt helping much… Thanks for any help you can give.
Signitures are for people with to much time.

Welcome to the forum[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. I’m not developer but reading the end of your post I can’t to avoid this one. You have signitures!!![<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Luis Martin Moderator SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason. Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Don’t worry too much about what executes first. In case of exclusively using INNER JOINs (or CROSS JOINs or FULL OUTER JOINs, for that matter)in a query, the table order doesn’t affect the result of the query and the query optimiser will try to find the most effective way to return the result. Now when you have a mixture of JOIN types (including OUTER JOINs) in your query, the table order does affect the logical result of the query. In that case you’ll sometimes notice a significant difference in performance when rearranging tables. Try to write such a query that the first JOIN condition narrows down the desired resultset as much as possible, but without changing the logical meaning of the query. That way SQL Server will have to do less work and can finish faster. As for multitable JOINs. SQL Server has a cost-based optimiser. That means that SQL Server will try to find the "best" execution until a certain threshold is reached. Then it will execute the cheapest plan found so far. When you JOIN many tables there are many permutations in the way a query can be executed possible, that it’s likely that this threshold is reached and SQL Server uses an execution plan that might be non-optimal, but the best under the given conditions. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

On top of that, if you want to force the join order you can use "force order" hint. It helps sometimes, but generally it is better to leave the decision to query optimizer.

Thanks mmarovic and FrankKalis, makes me breathe a lot easier… might have to look into how the query optimiser works hey? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Signitures are for people with to much time.

Actually, the goal of cost based optimization is to allow you to specify only the information you want to read and not to warry about how it will be done. However, understanding of execution plans is useful when you are in position to troubleshoot/tune problematic queries.

]]>