Forcing Query Plans to use LOOP, MERGE, and HASH Joins

Most of us just type INNER JOIN without thinking too much about what happens when the query is processed, and in most cases that is exactly what we should be doing. But sometimes that's not enough, and we have to use a hint to force SQL Server to take a different approach. One way is to add a hint directly to the join, but often a simpler approach is to override the join type used for every join in the queryusing OPTION (type JOIN).

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

akkinapr on 9/23/2010

It was very well presented and excellent information.

Charlie Bruno on 9/23/2010

Excellent presentation.

Michael Miller on 9/23/2010

Very interesting!

Bill Portman on 9/23/2010

Awesome!

C Boos on 9/23/2010

When viewing profiler, the number of reads always seems to be used to indicate a more efficient query but and explanation of the duration would be good since, in this example, the number of reads was lower but the duration was higher, which would indicate to me that it was not the best choice

May Lee on 9/23/2010

like it

Craig on 9/23/2010

Fantastic!

B767C8D05B on 9/24/2010

I've been writing SQL code for years and never knew it was that easy to apply optimizing hints. Thanks.

khurram bhatti on 9/25/2010

very good

Jeromy Kimani on 9/25/2010

Good job Andy

SQLSharma on 9/29/2010

Extremely useful and concentrated knowledge presented beautifully. Thanks Andy!

SQLSharma on 9/29/2010

Cool idea!

Ranjith on 10/2/2010

Good Explanation. Generally optimizer picks Loop Join: - Small Data Sets - The innser data set is indexed Merge Join: - Medium Datasets - Eachside of the join result set is naturally sorted on a key HASH Join: - Huge datasets - No Indexes

Martin Miller on 10/7/2010

Thanks Andy

Aaron on 10/21/2010

As always, youre full of helpful information!

vijay on 11/9/2010

Good demo

Kuljit Singh on 2/3/2012

Good demo for query tuning.

ryan on 5/27/2012

he's not measuring the efficiency of the query correctly. profiler reads isn't a sufficient measure.