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

Choose of join algorithm - Query Optimizer Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 1:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:20 PM
Points: 44, Visits: 129
Hi all,

Is there a sort of pattern for the query optimizer take a decision about the best algorithm for joins (hash, merge or loop) ?

Thanks.
Post #1566574
Posted Wednesday, April 30, 2014 1:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 284, Visits: 620
It's complicated. Lots of things to consider, e.g.

1. Can any indices be used to join on?
2. What do the statistics say?
3. Is the predicate(s) simple or not-so-much and are its values covered by the indices?
Post #1566578
Posted Wednesday, April 30, 2014 1:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:20 PM
Points: 44, Visits: 129
My question is specifically if there is kind of rule like high selectivity uses hash and others .

Post #1566581
Posted Wednesday, April 30, 2014 1:45 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
Nothing so simple. It's a combination of estimated row counts, row order, whether it's a 1-1, 1-many or many-many join, parallelism, etc, etc. There are a lot of optimiser rules.

Basically the various plans get evaluated and the one with the lowest estimated cost is chosen



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1566590
Posted Wednesday, April 30, 2014 3:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:20 PM
Points: 44, Visits: 129
Thank you Gail in other words there an infinity of variables to study.
Post #1566619
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse