option (LOOP JOIN) slow

Question

option (LOOP JOIN) slow

Edward-445599

Hall of Fame

Points: 3147
More actions
July 28, 2008 at 6:27 am

#71765

I have a query not overly complex a few joins
addresses addss with(nolock),
roles roles with(nolock),
patients patnt with(nolock)
AND addss.addss_refno = roles.addss_refno
AND roles.patnt_refno = patnt.patnt_refno
Has a distinct in the select and a few and or conditions PLUS ‘option (LOOP JOIN)’
It takes 3 minutes to run BUT if I remove the ‘option (LOOP JOIN)’ is completes in 20 seconds, what is happening what is ‘option (LOOP JOIN)’? Is obsolete if I specify inner joins? Ie
addresses addss
inner join roles roles on addss.addss_refno = roles.addss_refno
inner join patients patnt on roles.patnt_refno = patnt.patnt_refno
Many thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

It is not a best practice to add index and join hints to queries in SQL Server. With each release the Query Processor has gotten better and usually makes better choices than you can in regard to execution plans. Compare the execution plans for the 2 queries (with and without the hint) and you'll see vast differences I am sure.

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 Shaw SSC Guru Points: 1004504 More actions · Answer 2

There are three different ways that the query processor can implement joins, loop, merge, hash. They're each good in specific conditions. If you use a hint, you are forcing the optimiser to use a join type that may not be optimal for this query.

Rather stay away from any form of query hint unless you know exactly what it's doing and you are 100% sure you know better than the query optimiser.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Here is a lot to read for you.

Start at the bottom

http://blogs.msdn.com/craigfr/archive/tags/Joins/default.aspx

N 56°04'39.16"
E 12°55'05.25"

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

It takes 3 minutes to run BUT if I remove the ‘option (LOOP JOIN)’ is completes in 20 seconds, what is happening what is ‘option (LOOP JOIN)’? Is obsolete if I specify inner joins? Ie

Since you make a statement like that it is pretty obvious that you have a limited amount of knowledge of the inner workings of the optimizer. Thus I STRONGLY recommend that you do not use hints of any kind. I would even go so far as to say you should review all of the other code you have done for optimizer hints and test removing hints in them too.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 5

Ahh, the joys of trying to outguess nearly 20 years of SQL optimizer code - very rarely, if ever, can a human do better than the optimizer - there are some specific cases for trying to outguess the optimizer but even then you'll probably be wrong - best answer, simply don't. Look first at the structure of your database and indexes in place before trying to outfox the optimizer - any hint or manual optimization you can come up with will usually fail the test of time as data volumes or unexpected use cases come into play.