SQL Server JOIN Hints

I think I read somewhere, and want to confirm, that if I use a hint on a JOIN statement, that hint takes precendence on ALL JOINS. Thus if I state:
SELECT * FROM A MERGE JOIN B ON A.col1 = B.col1 JOIN C ON A.col1 = C.col1 JOIN D ON A.col2 = D.col2
then ALL JOINS are MERGE JOINS. Is that true? If so, by default, then can I supply a different type of HINT on any of the other join statements, thus state:
SELECT * FROM A MERGE JOIN B ON A.col1 = B.col1 JOIN C ON A.col1 = C.col1 HASH JOIN D ON A.col2 = D.col2
And if I can do that, do 'subsequent joins take the same hint as predecessors, or am I completely off on all my suppositions.

Before you go off on me as to why hints should not be used, I am with you, and am trying to gather information in support of why they should not be used.
LVL 15
dbbishopAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
A hint is then used by sql to construct the execution plan.
I'm not clear on what you mean by subsequent, I.e. If you have a join that then joins another and another whether the hint in the first is used in the subsequent joins within the same query?
0
dbbishopAuthor Commented:
Basically as I recall reading (and it was some time ago) if a hint is used on a join then that hint is used on all joins in the query, thus if I use a merge hint on a join, and there are other joins, they will all use the merge hint.  Guess I could try it and see, but if it did it on one test that is not necessarily conclusive evidence that it is the car consistently.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think there is a confusion with the terms.
MERGE, INNER, OUTER, CROSS, LEFT and RIGHT are types of joins.
Then you can have query and table hints that are different of what you're talking about. You can see those hints in this MSDN article. Please give a good look on the caution section.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Scott PletcherSenior DBACommented:
No, the JOIN hint does not apply to all joins, only to the one on which it is coded.  Thus, in your original example:

SELECT * FROM A MERGE JOIN B ON A.col1 = B.col1 JOIN C ON A.col1 = C.col1 JOIN D ON A.col2 = D.col2

SQL would perform a merge join, if at all possible, when joining from A to B, but SQL itself would decide the specific type of join for B to C and C to D.

Sometimes JOIN hints are needed to get good performance, particularly explicitly specifying a HASH join when SQL would, by default, use a LOOP join.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbbishopAuthor Commented:
Scott, thanks.
Vitor, MERGE is a hint (see this MSDN article)
0
Scott PletcherSenior DBACommented:
Using a join hint can be somewhat confusing as to what is being forced/restricted.  BOL has the relevant explanation, but you have to read it carefully (emphasis added by me):
"
Join hints enforce a join strategy between two tables.
If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the position of the ON keywords.
"

The join strategy (MERGE, LOOP, etc.) is between two tables, not all tables.

What is restricted on the other tables is the join order, not the join type/method.  That is, first A is MERGE-joined to B (because of the MERGE hint), then B is <sql-chosen> joined to C, and finally C is <sql-chosen> joined to D, in that order (perhaps, check the query plan to be sure), but the join strategies for others joins are still up to the SQL optimizer.
0
dbbishopAuthor Commented:
Thanks for the clarification. I think this is basically what I had in the back of my mind that I had read at one time.
I am trying to come up with arguments NOT to use hints even though it appears to improve the query at that time. There is some underlying reason why the optimizer is not chosing the correct join type, and that is what needs to be addressed.
0
Scott PletcherSenior DBACommented:
You're right in that you normally wouldn't want to use hints to override something as critical to performance as join strategy.  But several times I've had to force a HASH join over a LOOP join to dramatically improve performance: sometime the pay off is so big you have to do it.
0
dbbishopAuthor Commented:
I guess my question is, if statistics are up to date and indexes have been rebuilt recently, why is the optimizer making the wrong choice. We've been there too, where the performance increase is phenomenal with the hint.
0
arnoldCommented:
That could mean that your indexes and etc. are not correct.  You can use SQL server running advisoro to analyze a trace of requests and see what if any suggestions it makes to add indexes and/or statistics for .......

I.e you have a person driving from point a to point G by way c d e f g at the time those points are of lowest cost using good roads, but these days d to e and f to g are under major construction, unless you adjust the weights making those paths undesirable the person will continue going by that route unless a hint to take b i j h route to g.

The impact might be as data grew the prior construct has become overloaded for the little data that it provides.
0
Scott PletcherSenior DBACommented:
Your stats can be good but that doesn't mean they're perfect.  Then SQL has to determine/"guess" how many rows meet a particular WHERE condition.  So it's easy to be a little bit off in its row estimates.  Normally that's not a big deal, but if the query is complex, particularly with many levels, the amount off gets magnified, and can produce a non-ideal plan.

Somewhat like a flight navigator being 0.1% off in plotting direction.  Not a big deal on a 10-20 km flight, very big deal on a 10,000 km flight.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.