Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Optimizer Join Methods Expand / Collapse
Author
Message
Posted Thursday, February 3, 2005 2:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 14, 2007 1:40 PM
Points: 42, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/RDyess/optimizerjoinmethods.asp


Post #159513
Posted Wednesday, February 9, 2005 5:07 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:17 AM
Points: 295, Visits: 284

I really like these types of articles because it is interesting information that I wouldn't normally go looking for, though I probably should. I might be able to get this information from BOL but has anyone ever sat down and just read BOL? Not very fun.

Is there any chance of a sequel that demonstrates how one might modify a query to get different results? I know most of it relates to indexing but if there is any way to give the SQL Server engine some subtle clues as to how to perform the join rather than using query hints, that would be pretty cool.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Post #160517
Posted Wednesday, February 9, 2005 10:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 14, 2007 1:40 PM
Points: 42, Visits: 1

Bryant,

You are right about the indexes being one of the keys. Another is size of the data inputs. It is hard (nearly impossible) to "push" the optimizer to choose one join over the other without adding/removing indexes or dramatically growing/decreasing the size of the result set.

I usually scan for the join type and if it is not the one I think the optimizer should be using (my best guess) then I try to figure out why. Most of the time hash joins show up when I think merge joins should have been used. I can usually trace these back to lack of indexes or out-dated statistics. Every now and then I find a nested loop join when I think the optimizer should be doing a merge. (My guess is usually based on result set size.) I sometimes try to push the optimizer into a merge, but in the end this does not really buy me much in optimization.

 

Randy

  

 




Post #160587
Posted Friday, February 10, 2006 11:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 3, 2009 5:41 PM
Points: 10, Visits: 12

Randy,

This is a great article on a difficult subject, and you have explained it well. IMHO however, it seems that a discussion on table/column/index statistics and the influence they play on the optimizer (or even a mention) was missing.... I come from the Oracle world and the optimizer is a favorite subject (see http://www.ioug.org/select/articles/05q2/index.cfm for an article I wrote).

Regards,

John Kanagaraj

Post #257634
Posted Wednesday, May 19, 2010 1:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 10, 2013 8:00 AM
Points: 9, Visits: 51
I have a question... Is it possible to tell SQL Server to use a particular join method? Or do you always rely on the Query Optimizer to choose the most appropriate method?
Post #924621
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse