Table Join Order

[Mark Price]

We have an application that utilizes many queries that are quite lengthy.
I’ve heard in the past that the order in which you join your tables should
remain consistent from one query to another, and that this can impact
performance. Is this true? Are there any tools available that can help
determine the best/correct join order?

Thanks,
Mark

[Erland Sommarskog]

Mark Price (Mark...@discussions.microsoft.com) writes:
> We have an application that utilizes many queries that are quite lengthy.

> I�ve heard in the past that the order in which you join your tables should

> remain consistent from one query to another, and that this can impact
> performance. Is this true? Are there any tools available that can help
> determine the best/correct join order?

You mean if it matters if you write:

A JOIN B ON
B JOIN C ON

or

C JOIN B ON
B JOIN A ON

it doesn't. The optimizer works with an abstract query tree, and as long
as you don't add hints that force the join order, the resulting plan is
the same, no matter the order.

I don't know where you've gotten this idea from, but maybe it is a
remnant from the days of SQL 6.5 and earlier, where the optimizer would
only consider four tables at a time in a query. That is, if you had

FROM a, b, c, d e

the optimizer would consider all permuations of a, b, c & d and then all
permutaitons of b, c, d & e. But that was long ago.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Russell Fields]

Mark,

Perhaps you have also heard the idea that controlling table order helps to
avoid deadlocks. If so, this thread has some insight from both Erland
Sommarskog and Hugo Kornelis.
http://www.phwinfo.com/forum/comp-db-ms-sqlserver/343050-deadlock-avoidance-foreign-keys-dictating-table-access-order.html

RLF

"Mark Price" <Mark...@discussions.microsoft.com> wrote in message
news:EB55542B-C8E3-4D2D...@microsoft.com...

[Ruben Garrigos]

Hi Mark,

The optimizer will almost always get a good join order. When the number
of joined tables is very high or when the optimizer have to consider
lots of different alternatives you can use parenthesis or rewrite your
query to help the optimizer.

I use to write the join clauses following a "logical" data path as if I
was going to do the join process "by hand". Doing that helps you to
avoid "monster" intermediate join results that will end heavily filtered
afterwards.

Regards,

Rubén Garrigós
Solid Quality Mentors

Blog: http://blogs.solidq.com/es/elrincondeldba

Mark Price escribió: