Rules For Effecient Joins?

Question

Post reply

Rules For Effecient Joins?

Gail Wanabee

SSCrazy Eights

Points: 8471
More actions
January 2, 2009 at 7:51 am

#199562

I have read that the order of multiple joins in a query can significantly effect the efficiency and speed of query execution, but I've been unable to locate any clear statement of rules that can be used as guidelines for best implementations.
Can anyone direct me to, or provide, a set of best practices rules for implementations of joins?
Thanks,
LC

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi,

As far as I am aware the join order will make no difference as the optimizer decides how to do them.

Unless you use the Query Hint Option 'FORCE ORDER'

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

This is my understanding as well.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 3

Gail Wanabee

SSCrazy Eights

Points: 8471

January 2, 2009 at 8:23 am

#920300

Thanks, guys.

LC

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 4

I may as well be the dissenting opinion.

It depends on the complexity of the joins. When we're talking about simple, say fewer than 15 or so, tables joined together, the order doesn't matter. As you get into more complex joins, the order can make a difference. It's not because it makes a fundamental difference. It's because the optimizer can only try so many different combinations in a given period of time and the optimizer is limited in the amount of time it spends on a query.

So when you're dealing with really complex queries, you might consider trying to adjust the order. Generally getting all the INNER queries first and the OUTER queries later is the best approach. When you've got very complex operations going on, anything you can do to help out the optimizer is worth doing.

I've actually seen a few instances (80 tables joins) where the FORCE ORDER hint made a positive difference.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

Thanks for the update Grant 🙂

I've seen a query which joins two views (ok those views where based on other views) and the FORCE OPTION changed the query from +30secs to 2secs 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

I also have to agree with Grant, but in my experience you will rarely be joining enough tables where the order will be important. Also, in my opinion, you should do INNER JOIN's first and OUTER JOIN's last for readability and to make sure you are getting the results you expect. Mixing INNER and OUTER JOIN's can create unexpected results.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 7

Thank you for all of your additional comments. I'll file them in my DBA memory banks for future use.

Sincerely,

LC

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 8

Jack Corbett (1/2/2009)
I also have to agree with Grant, but in my experience you will rarely be joining enough tables where the order will be important. Also, in my opinion, you should do INNER JOIN's first and OUTER JOIN's last for readability and to make sure you are getting the results you expect. Mixing INNER and OUTER JOIN's can create unexpected results.

I agree with Jack.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Jack Corbett (1/2/2009)
I also have to agree with Grant, but in my experience you will rarely be joining enough tables where the order will be important. Also, in my opinion, you should do INNER JOIN's first and OUTER JOIN's last for readability and to make sure you are getting the results you expect. Mixing INNER and OUTER JOIN's can create unexpected results.

No kidding.

How often have you seen this "unexpectedly" turn into a full join between m & c, forcing you to write a (slow) left join between c2 and c when you know it should be a fast inner join?

SELECT stuff

FROM MasterTable m

LEFT JOIN ChildTable c ON c.somecolumn = m.somecolumn

INNER JOIN ChildTable c2 ON c2.anothercolumn = c.anothercolumn

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden