Order of Joins and Performance

[GM]

Does the order in which Joins are peformed (from left to right) matter
whether inner or outer when it comes to performance. Assuming that all
indexes are the same but the size of the tables is different?

For example let's assume we have 5 tables, TABLE1, TABLE2, TABLE3,
TABLE4, TABLE5.

For example:
SELECT Smth from TABLE1 INNER JOIN TABLE2 on Condition1
INNER JOIN TABLE3 on Condition2
LEFT JOIN TABLE4 on Condition3
INNER JOIN TABLE5 on Condition4.

Does SQL optimize the query and finds the best way to inner join or it
starts doing the JOINS from left to right?
Thank you for your your feedback.

Gent

[Erland Sommarskog]

GM (ge***********@trustasc.com) writes:

Does the order in which Joins are peformed (from left to right) matter
whether inner or outer when it comes to performance. Assuming that all
indexes are the same but the size of the tables is different?

For example let's assume we have 5 tables, TABLE1, TABLE2, TABLE3,
TABLE4, TABLE5.

For example:
SELECT Smth from TABLE1 INNER JOIN TABLE2 on Condition1
INNER JOIN TABLE3 on Condition2
LEFT JOIN TABLE4 on Condition3
INNER JOIN TABLE5 on Condition4.

Does SQL optimize the query and finds the best way to inner join or it
starts doing the JOINS from left to right?
Thank you for your your feedback.

The former. This is a very important concept for modern relational DBMSs.
You specify the logic to be done. The optimizer finds out how to do the
computation. A good optimizer can do heavy rearranging of your queries,
as long as the result is the same.

Sometimes you know better than the optimizer, and you really want it to
do computation in a certain order, and, yes, most optimizers does permit
you to impose a computation order. But that is a feature to use only in
exceptional cases.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Rawheiser]

So what is your opinion of the value of query diagramming to ease the
optimizer along in finding the best plan?

http://www.oreilly.com/catalog/sqltuning/

Even if the DB does its own query plan, I'd like to kind of understand what
its having to go thru and help it out as best I can (given that I 'may' have
a better understanding of the data design).
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

GM (ge***********@trustasc.com) writes:

Does the order in which Joins are peformed (from left to right) matter
whether inner or outer when it comes to performance. Assuming that all
indexes are the same but the size of the tables is different?

For example let's assume we have 5 tables, TABLE1, TABLE2, TABLE3,
TABLE4, TABLE5.

For example:
SELECT Smth from TABLE1 INNER JOIN TABLE2 on Condition1
INNER JOIN TABLE3 on Condition2
LEFT JOIN TABLE4 on Condition3
INNER JOIN TABLE5 on Condition4.

Does SQL optimize the query and finds the best way to inner join or it
starts doing the JOINS from left to right?
Thank you for your your feedback.

The former. This is a very important concept for modern relational DBMSs.
You specify the logic to be done. The optimizer finds out how to do the
computation. A good optimizer can do heavy rearranging of your queries,
as long as the result is the same.

Sometimes you know better than the optimizer, and you really want it to
do computation in a certain order, and, yes, most optimizers does permit
you to impose a computation order. But that is a feature to use only in
exceptional cases.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

Technically speaking, the inifxed JOIN notation is done from left to
right in the FROM clause, as modified by parens. This order matters
when your have OUTER JOINs, but INNER JOINs commute and can be
re-arranged.

The optimizer is free to do the joins in any order or in parallel, if
the original result is obtained. Most modern optimizers will look for
the smallest tables (so they can fit into main storage), statistical
distrtibutions and bunch of other things to make this decision.

The truth is that in smaller SQL products, the optimizers start to
choke after five tables -- there are 120 ways to arrange five tables
and the smaller optimizers don't have the time or resources to do the
best algorithms. This is not a big deal unless you have some really
weird data distributions and very large tables.

The bottom line, is that you need to write the code correctly, then
"Trust in the Optimizer, Luke!"

[Erland Sommarskog]

David Rawheiser (ra*******@hotmail.com) writes:

So what is your opinion of the value of query diagramming to ease the
optimizer along in finding the best plan?

http://www.oreilly.com/catalog/sqltuning/

Even if the DB does its own query plan, I'd like to kind of understand
what its having to go thru and help it out as best I can (given that I
'may' have a better understanding of the data design).

It's certainly a good idea of having some understanding how a good plan
should look like. Not the least in those situations when the optimizer
goes lost, and produces a plan which takes three hours to run.

For queries that just a couple of joins, it is not that difficult to get
an idea of how would you do, if you had to do it manually. But more
complex queries with correlated subqueries can be more difficult to grasp.

I not read the book you are referring to, nor heard of any opinions on it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp