The Right Kind Of Join III

  • Paul White

    SSC Guru

    Points: 150442

    Comments posted to this topic are about the item The Right Kind of Join III[/url]

    SELECT COUNT_BIG(*)

    FROM @a

    RIGHT JOIN @B ON

    [@B].b = [@A].a

    OPTION (LOOP JOIN)

    Previous questions in this series:

    The Right Kind of Join II[/url]

    The Right Kind of Join I[/url]

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks again Paul. Excellent explanation.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Very nice question series. By the way, you could make the query fail using

    SET FORCEPLAN ON

    as well.

    Best Regards,

    Chris BΓΌttner

  • Toreador

    SSChampion

    Points: 11256

    As with the others in this series, I didn't have the foggiest idea, so wrongly guessed that it would be the answer not already used by the other 2 questions πŸ™‚

  • Paul White

    SSC Guru

    Points: 150442

    Toreador (9/8/2011)


    As with the others in this series, I didn't have the foggiest idea, so wrongly guessed that it would be the answer not already used by the other 2 questions πŸ™‚

    Just for you then:

    DECLARE @a TABLE (a INT NOT NULL)

    DECLARE @B TABLE (b INT NOT NULL)

    SELECT COUNT_BIG(*)

    FROM @a

    RIGHT JOIN @B ON

    @B.b = @a.a

    OPTION (LOOP JOIN)

    Msg 137, Level 16, State 1, Line 7

    Must declare the scalar variable "@B".

    Msg 137, Level 16, State 1, Line 7

    Must declare the scalar variable "@A".

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    i agree with you!!!!

    Great question and excellent explanation!!!!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • TomThomson

    SSC Guru

    Points: 104773

    Good question and answer.

    But the behaviour still seems bizarre to me. Why should the loop join hint be taken to imply the (Force Order) option - if I want that option, I can specify it, if I want a loop join in whatever order is best why should I have (FORCE ORDER) rammed down my throat if I don't specify it? I guess that's a question for Microsoft though, not a question for Paul.

    Tom

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Tom.Thomson (9/8/2011)


    Good question and answer.

    But the behaviour still seems bizarre to me. Why should the loop join hint be taken to imply the (Force Order) option - if I want that option, I can specify it, if I want a loop join in whatever order is best why should I have (FORCE ORDER) rammed down my throat if I don't specify it? I guess that's a question for Microsoft though, not a question for Paul.

    Somehow I'm sure he knows the answer anyways! πŸ˜€

  • Paul White

    SSC Guru

    Points: 150442

    Tom.Thomson (9/8/2011)


    Good question and answer.

    But the behaviour still seems bizarre to me. Why should the loop join hint be taken to imply the (Force Order) option - if I want that option, I can specify it, if I want a loop join in whatever order is best why should I have (FORCE ORDER) rammed down my throat if I don't specify it? I guess that's a question for Microsoft though, not a question for Paul.

    It is bizarre, and entirely counter-intuitive to most people (though it is at least documented these days). There is often considerable consternation when an attempt to enforce a particular physical join type between two tables results in a completely stupid plan because of the implied FORCE ORDER.

    The query optimizer in SQL Server uses the Cascades framework - a cost-based top-down optimizer that works by matching patterns (logical tree fragments) using rules. There are nearly 400 of these rules - some of which implement logical operations as physical operators (e.g. implementing a relational JOIN using sort-merge), some of which generate logically-equivalent relational alternatives (e.g. pushing an aggregate below a join or considering a different join order), and still others that remove redundancies (e.g. removing empty tables or aggregates where the input is known to be unique).

    The second factor is how to interpret A INNER LOOP JOIN B, or C RIGHT HASH JOIN D. Did the query writer intend to drive the query from table A in the first case, and to build the hash table on input C in the second case? In most cases where the query writer wishes to enforce a physical join type, this heuristic is a good one. To meet the writer's expectation, SQL Server needs to guarantee that it will produce a plan where A drives a nested loops join to B, and the hash join between C and D has C as the build input and D as the probe.

    Now, in principle the optimizer could make that guarantee in one of two ways. First, it could somehow fix the join in place and try to optimize around it. That just isn't practical for an extensible general-purpose optimizer based on Cascades - all kinds of horribly specific and hard-to-maintain code would have to be written. Instead, SQL Server guarantees the physical join type (LOOP, HASH, MERGE, REMOTE) by setting a required physical property on the logical JOIN in the query tree. This allows other physical join type rules to match elsewhere in the final plan, but only the one specified physical implementation can match the required property on the join in question.

    Meeting the input-order requirement is more difficult to generalize. One consideration is that the inputs to the join may be an arbitrarily complex tree rather than a simple table. Anyway, rather than deal with all the complexity, SQL Server uses a Big Hammer to enforce join order: all the simplification and transformation rules that could change the join order from that shown in the logical tree produced by the parser and algebrizer, are disabled. Not surprisingly, this can have all sorts of unexpected effects on the final plan, but it does guarantee the join order.

    As an example, consider this join:

    SELECT th.ProductID

    FROM Production.Product AS p

    JOIN Production.TransactionHistory AS th ON

    th.ProductID = p.ProductID

    That results in a simple scan of the history table, because there are constraints that guarantee that a single matching product record exists. If we now force a join type:

    SELECT th.ProductID

    FROM Production.Product AS p

    INNER LOOP

    JOIN Production.TransactionHistory AS th ON

    th.ProductID = p.ProductID

    The resulting plan contains a nested loops join as we specified, and the pointless join can no longer be eliminated (that simplification rule is switched off temporarily). To see that the effect is plan-wide, we can UNION ALL the query to itself, specifying the join hint in only one part:

    SELECT th.ProductID

    FROM Production.Product AS p

    INNER LOOP

    JOIN Production.TransactionHistory AS th ON

    th.ProductID = p.ProductID

    UNION ALL

    SELECT th.ProductID

    FROM Production.Product AS p

    JOIN Production.TransactionHistory AS th ON

    th.ProductID = p.ProductID

    Now, neither query is simplified, even though there's logically no reason not to in the second case. A serial plan shows a loops join for the top query and a merge join for the lower one; a parallel plan chooses a hash join instead of merge.

  • adb2303

    SSCertifiable

    Points: 7586

    SQLkiwi (9/8/2011)


    Tom.Thomson (9/8/2011)


    Good question and answer.

    But the behaviour still seems bizarre to me. Why should the loop join hint be taken to imply the (Force Order) option - if I want that option, I can specify it, if I want a loop join in whatever order is best why should I have (FORCE ORDER) rammed down my throat if I don't specify it? I guess that's a question for Microsoft though, not a question for Paul.

    It is bizarre, and entirely counter-intuitive to most people (though it is at least documented these days). There is often considerable consternation when an attempt to enforce a particular physical join type between two tables results in a completely stupid plan because of the implied FORCE ORDER.

    The query optimizer in SQL Server uses the Cascades framework - a cost-based top-down optimizer that works by matching patterns (logical tree fragments) using rules. There are nearly 400 of these rules - some of which implement logical operations as physical operators (e.g. implementing a relational JOIN using sort-merge), some of which generate logically-equivalent relational alternatives (e.g. pushing an aggregate below a join or considering a different join order), and still others that remove redundancies (e.g. removing empty tables or aggregates where the input is known to be unique).

    The second factor is how to interpret A INNER LOOP JOIN B, or C RIGHT HASH JOIN D. Did the query writer intend to drive the query from table A in the first case, and to build the hash table on input C in the second case? In most cases where the query writer wishes to enforce a physical join type, this heuristic is a good one. To meet the writer's expectation, SQL Server needs to guarantee that it will produce a plan where A drives a nested loops join to B, and the hash join between C and D has C as the build input and D as the probe.

    Now, in principle the optimizer could make that guarantee in one of two ways. First, it could somehow fix the join in place and try to optimize around it. That just isn't practical for an extensible general-purpose optimizer based on Cascades - all kinds of horribly specific and hard-to-maintain code would have to be written. Instead, SQL Server guarantees the physical join type (LOOP, HASH, MERGE, REMOTE) by setting a required physical property on the logical JOIN in the query tree. This allows other physical join type rules to match elsewhere in the final plan, but only the one specified physical implementation can match the required property on the join in question.

    Meeting the input-order requirement is more difficult to generalize. One consideration is that the inputs to the join may be an arbitrarily complex tree rather than a simple table. Anyway, rather than deal with all the complexity, SQL Server uses a Big Hammer to enforce join order: all the simplification and transformation rules that could change the join order from that shown in the logical tree produced by the parser and algebrizer, are disabled. Not surprisingly, this can have all sorts of unexpected effects on the final plan, but it does guarantee the join order.

    As an example, consider this join:

    SELECT th.ProductID

    FROM Production.Product AS p

    JOIN Production.TransactionHistory AS th ON

    th.ProductID = p.ProductID

    That results in a simple scan of the history table, because there are constraints that guarantee that a single matching product record exists. If we now force a join type:

    SELECT th.ProductID

    FROM Production.Product AS p

    INNER LOOP

    JOIN Production.TransactionHistory AS th ON

    th.ProductID = p.ProductID

    The resulting plan contains a nested loops join as we specified, and the pointless join can no longer be eliminated (that simplification rule is switched off temporarily). To see that the effect is plan-wide, we can UNION ALL the query to itself, specifying the join hint in only one part:

    SELECT th.ProductID

    FROM Production.Product AS p

    INNER LOOP

    JOIN Production.TransactionHistory AS th ON

    th.ProductID = p.ProductID

    UNION ALL

    SELECT th.ProductID

    FROM Production.Product AS p

    JOIN Production.TransactionHistory AS th ON

    th.ProductID = p.ProductID

    Now, neither query is simplified, even though there's logically no reason not to in the second case. A serial plan shows a loops join for the top query and a merge join for the lower one; a parallel plan chooses a hash join instead of merge.

    Yeah, that's what I was going to say... πŸ˜‰

  • TomThomson

    SSC Guru

    Points: 104773

    Paul, thanks for that excellent explanation and information about the optimiser.

    Tom

  • Britt Cluff

    SSCertifiable

    Points: 5083

    Good question. Thanks.

    http://brittcluff.blogspot.com/

Viewing 12 posts - 1 through 12 (of 12 total)

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