The Right Kind Of Join III
-
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 -
Very nice question series. By the way, you could make the query fail using
SET FORCEPLAN ON
as well.
Best Regards,
Chris Büttner
-
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 🙂
-
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-2 TABLE (b INT NOT NULL)
SELECT COUNT_BIG(*)
FROM @a
RIGHT JOIN @b-2 ON
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".
-
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! -
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
-
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! 😀
-
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.
-
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... 😉
-
Paul, thanks for that excellent explanation and information about the optimiser.
Tom
-
Good question. Thanks.
http://brittcluff.blogspot.com/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply