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 II Expand / Collapse
Author
Message
Posted Wednesday, August 31, 2011 8:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 11,194, Visits: 11,141
Comments posted to this topic are about the item The Right Kind Of Join II

The previous question in this series is here: The Right Kind Of Join




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1168460
Posted Wednesday, August 31, 2011 8:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 11,194, Visits: 11,141
I couldn't include a graphic in the answer, so here's the query and execution plan:

DECLARE @A TABLE (a INT NOT NULL UNIQUE)
DECLARE @B TABLE (b INT NOT NULL)

SELECT COUNT_BIG(*)
FROM @A
RIGHT LOOP JOIN @B ON
[@B].b = [@A].a



Notice the plan does not touch table @A.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
QotD.png (269 views, 6.42 KB)
Post #1168463
Posted Thursday, September 1, 2011 12:44 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
Nice question. Better than the question is the explanation given.

Thanks Paul.




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 #1168486
Posted Thursday, September 1, 2011 1:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:16 AM
Points: 1,632, Visits: 5,585
This is one of those questions where the common sense answer (e.g. both tables have no data in, so of course the answer will be zero rows!) is actually right...I knew nothing about the stuff explained in the answer, so I learned something today.
Post #1168489
Posted Thursday, September 1, 2011 1:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:53 AM
Points: 1,541, Visits: 8,206
I normally don't comment on QOD articles, but the explanation and screen-shot of the execution plan deserves merit.

BrainDonor
Linkedin
Blog Site
Post #1168498
Posted Thursday, September 1, 2011 1:50 AM
SSC Eights!

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

Group: General Forum Members
Last Login: Yesterday @ 5:25 AM
Points: 939, Visits: 885
Indeed, the question and the explanation are great.

Thank you,
Iulian
Post #1168507
Posted Thursday, September 1, 2011 3:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 4:51 AM
Points: 1,854, Visits: 3,451
Got it wrong (yes, I didn't run the code)
Excellent question which shows how the query optimizer works.

A similar example is how the INNER JOIN is removed (resulting in only a scan on table "child") in the following example, due to the fact that parent_id cannot be NULL and has a FK reference to table "parent".

create table parent(id int primary key)
create table child(id int primary key, parent_id int not null references parent(id))

select c.*
from dbo.child c
inner join parent p on p.id = c.parent_id



  Post Attachments 
exec_plan.png (227 views, 5.27 KB)
Post #1168537
Posted Thursday, September 1, 2011 3:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 11,194, Visits: 11,141
Nils Gustav Stråbø (9/1/2011)
A similar example is how the INNER JOIN is removed (resulting in only a scan on table "child") in the following example, due to the fact that parent_id cannot be NULL and has a FK reference to table "parent".

There's an interesting limitation here: this simplification does not work if the FK relationship uses a compound key (another good reason to use surrogate keys, some would say).

https://connect.microsoft.com/SQLServer/feedback/details/683411/dri-referential-integrity-does-not-optimize-if-compound-key-is-used-for-the-key-relation




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1168552
Posted Thursday, September 1, 2011 3:50 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 8,726, Visits: 9,277
Excellent question and explanation.

But it makes the behaviour in the first right join question seem even more bizarre than it seemed before. In this case there's a query that by very simple but not blindingly obvious reasoning can be shown to be equivalent to the original query and eliminates right loop join, and despite that lack of blinding obviousness the optimiser does the transformation and provides a plan. In the first case there's a blindingly obvious equivalent query (using left join) that doesn't run into the "no loop with right join" restriction, and the optimiser doesn't do the transformation despite the total obviousness. Not only is the statement in BoL misleading, and the actual behaviour with right loop inconsistent, but also the optimiser's choice of when to produce a plan and when not seems perverse! Or actually, it's the whole concept of "no loop with right join" that is ridiculous and perverse, because every right join is (trivially) equivalent to a left join, and there's no "no loop with left join" restriction - to make any sense at all the restriction would have to be purely syntactic, with no semantic content, but this QoTD demonstrates that it is not a syntactic restriction.


Tom
Post #1168561
Posted Thursday, September 1, 2011 3:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 11,194, Visits: 11,141
Hi Tom,

The third question in this series will be published next week (8 September).
I promise your question will be answered then.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1168564
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse