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 12»»

The Right Kind Of Join III Expand / Collapse
Author
Message
Posted Wednesday, September 7, 2011 9:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 11,194, Visits: 11,136
Comments posted to this topic are about the item The Right Kind of Join III

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
The Right Kind of Join I




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
QotD.png (216 views, 13.56 KB)
Post #1171501
Posted Wednesday, September 7, 2011 9:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 21,620, Visits: 15,278
Thanks again Paul. Excellent explanation.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1171502
Posted Thursday, September 8, 2011 12:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:12 AM
Points: 2,840, Visits: 3,867
Very nice question series. By the way, you could make the query fail using
SET FORCEPLAN ON
as well.


Best Regards,
Chris Büttner
Post #1171542
Posted Thursday, September 8, 2011 2:04 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 1,736, Visits: 6,335
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
Post #1171563
Posted Thursday, September 8, 2011 2:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 11,194, Visits: 11,136
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".




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1171571
Posted Thursday, September 8, 2011 5:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:04 AM
Points: 1,256, Visits: 13,552
i agree with you!!!!

Great question and excellent explanation!!!!



rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1171642
Posted Thursday, September 8, 2011 9:44 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 8,683, Visits: 9,211
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
Post #1171881
Posted Thursday, September 8, 2011 9:46 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 2:45 AM
Points: 21,397, Visits: 9,610
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!
Post #1171883
Posted Thursday, September 8, 2011 11:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 11,194, Visits: 11,136
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.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1172003
Posted Thursday, September 8, 2011 2:45 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 832, Visits: 2,437
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...
Post #1172143
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse