Does JOIN order affect performance?

[Hexonx]

If I join a few tables together, some of which have few records, some with many records, does the order of the join statements affect performance? If so, what is a good strategy for determining join order?

Thanks in advance.

 

[foxdev]

In theory, the execution plan query optimizer will find the optimum execution path regardless of the order you've specified them. And in practice, whenever I've played around with various wording when working with large tables and joins, I've not seen any difference.

But there may be some odd situation where the order does make a difference, but I've not tripped over it.

One thing to keep in mind, though, when deciding where to put your join clauses (from BOL):

the ability to specify Transact-SQL outer joins in the WHERE clause is not recommended, is no longer documented, and will be dropped in a future release. -----------------
Robert Bradley
use coupon code "TEKTIPS" for 15% off at:

http://www.lowcostprints.com

http://www.foxdev.com

 

[Hexonx]

Thanks, FoxDev. I tried a few different combinations and it made no difference. I did learn some new thngs about studying the execution plan in Query Analyzer, though.

Again, Thanks.