Post reply

Choose of join algorithm - Query Optimizer

  • April 30, 2014 at 1:14 pm

    #290776

    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.

  • April 30, 2014 at 1:18 pm

    #1709991

    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?

  • April 30, 2014 at 1:25 pm

    #1709994

    My question is specifically if there is kind of rule like high selectivity uses hash and others .

  • April 30, 2014 at 1:45 pm

    #1710002

    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, 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

  • April 30, 2014 at 3:49 pm

    #1710027

    Thank you Gail in other words there an infinity of variables to study. 😉

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

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