August 5, 2010 at 8:04 am
Stefan Krzywicki (8/5/2010)
Don't look at me, when it comes to changing settings I need to look things up and I can't find the documentation on DBCC TimeWarp OR Metric time! : -)
It'll be written. Eventually. We just need to find someone like Merlin, who remembers the future, in order to get it done
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
August 5, 2010 at 8:11 am
Chris Morris-439714 (8/5/2010)
What creeps me out is when I left-join a child table to a parent, then inner join child to a grandchild, with no reference to either the child or the grandchild in the WHERE clause, and the join between child and parent automagically becomes an inner join.I can't predict whether or not the join between the parent and child will remain as an outer join, which it sometimes appears to do, or convert to an inner join, as it seems to, erm... when it feels like it. When I'm working with a query which does this conversion, I'll often set the child/grandchild inner join up in a derived table and then left join to parent, after comparing performance against the alternative which is to outer join child and grandchild.
/ creep over. Am I being dumb here?
Nope. I've seen it myself. For some reason I have yet to determine, SQL cuts out records if you put INNER JOINS after OUTER JOINS. So when I have Table1 and Table2 INNER JOINed, then Table3 OUTER JOINed, if I want to join Table4 (which has nothing to do with T1 or T2) to T3 AND make sure I get all my records, I have to use an OUTER JOIN between T3 & T4.
Thing is, the behavior isn't consistent enough for me to pin-point a specific cause. Sometimes I get all my records if I INNER JOIN T3 & T4, sometimes I don't and I have to OUTER JOIN them. So I've just gotten into the habit of using OUTER JOIN at that point.
August 5, 2010 at 8:11 am
GilaMonster (8/5/2010)
Grant Fritchey (8/5/2010)
Praveen Goud Kotha (8/5/2010)
Thank you all the members for their replies/suggestions who participated in my topic..especially for Fritchey..
If any other reasons for "Reasons for difference in execution plans"..
Please dont forget to post here..
what i mean to say is: i have solved my issue(it is because of difference in service packs(2 and 3) )..
but more answers are welcomed to help others too...
Not especially me, especially Gail. She had the definitive list.
but, but, but.... You're the *celebrity*
(sorry, couldn't resist)
Now you're the one whose lucky they're in a different hemisphere. If I could drive over...
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 5, 2010 at 8:13 am
Chris Morris-439714 (8/5/2010)
Jack Corbett (8/5/2010)
...Where you place the criteria for OUTER JOIN's DOES make a difference. Whenever you place criteria in the WHERE clause that references a column in your OUTER table SQL Server will convert that JOIN to an INNER JOIN...
It makes perfect sense, especially when you consider this as well:
LEFT JOIN TableB b ON b.SomethingOrOther = a.SomethingOrOther
AND b.SomethingOrOther IS NULL
Oh, almost forgot. This is a REALLY bad example to use. @=) If you're going to have that secondary ON statement, you might as well just drop it and make that statement a RIGHT JOIN instead of a LEFT JOIN.
EDIT: DOH! Nevermind. I read your tables backwards. Sorry.
August 5, 2010 at 8:21 am
Brandie Tarvin (8/5/2010)
Chris Morris-439714 (8/5/2010)
Jack Corbett (8/5/2010)
...Where you place the criteria for OUTER JOIN's DOES make a difference. Whenever you place criteria in the WHERE clause that references a column in your OUTER table SQL Server will convert that JOIN to an INNER JOIN...
It makes perfect sense, especially when you consider this as well:
LEFT JOIN TableB b ON b.SomethingOrOther = a.SomethingOrOther
AND b.SomethingOrOther IS NULL
Oh, almost forgot. This is a REALLY bad example to use. @=) If you're going to have that secondary ON statement, you might as well just drop it and make that statement a RIGHT JOIN instead of a LEFT JOIN.
EDIT: DOH! Nevermind. I read your tables backwards. Sorry.
It was a really cr@p example 'cos it was plain wrong. I was intending to show NOT EXISTS matching rows of the outer-joined table by using a left join and IS NULL in the WHERE clause.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 5, 2010 at 8:22 am
Wow, a nice technical discussion on The Thread.
Who'd have thought?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 5, 2010 at 8:24 am
Brandie Tarvin (8/5/2010)
Chris Morris-439714 (8/5/2010)
What creeps me out is when I left-join a child table to a parent, then inner join child to a grandchild, with no reference to either the child or the grandchild in the WHERE clause, and the join between child and parent automagically becomes an inner join.I can't predict whether or not the join between the parent and child will remain as an outer join, which it sometimes appears to do, or convert to an inner join, as it seems to, erm... when it feels like it. When I'm working with a query which does this conversion, I'll often set the child/grandchild inner join up in a derived table and then left join to parent, after comparing performance against the alternative which is to outer join child and grandchild.
/ creep over. Am I being dumb here?
Nope. I've seen it myself. For some reason I have yet to determine, SQL cuts out records if you put INNER JOINS after OUTER JOINS. So when I have Table1 and Table2 INNER JOINed, then Table3 OUTER JOINed, if I want to join Table4 (which has nothing to do with T1 or T2) to T3 AND make sure I get all my records, I have to use an OUTER JOIN between T3 & T4.
Thing is, the behavior isn't consistent enough for me to pin-point a specific cause. Sometimes I get all my records if I INNER JOIN T3 & T4, sometimes I don't and I have to OUTER JOIN them. So I've just gotten into the habit of using OUTER JOIN at that point.
Brandie, this is the first corroboration I've ever seen and I can't thank you enough. It makes it worthwhile to spend some time working on a test case. Cheers.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 5, 2010 at 8:25 am
Steve Jones - Editor (8/5/2010)
Wow, a nice technical discussion on The Thread.Who'd have thought?
Heh sorry Steve, I'll go out and buy some brie.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 5, 2010 at 8:26 am
Wow, a nice technical discussion on The Thread.
Who'd have thought?
SACRILEGE!! HERESY!! CURSORS!! :w00t::w00t:
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 5, 2010 at 9:31 am
Chris Morris-439714 (8/5/2010)
Jack Corbett (8/5/2010)
...Where you place the criteria for OUTER JOIN's DOES make a difference. Whenever you place criteria in the WHERE clause that references a column in your OUTER table SQL Server will convert that JOIN to an INNER JOIN...
It makes perfect sense, especially when you consider this as well:
LEFT JOIN TableB b ON b.SomethingOrOther = a.SomethingOrOther
WHERE b.SomethingOrOther IS NULL
Having any expression referencing a column of the outer-joined table, other than a NULL-check, in the WHERE clause, forces it to match rows from the outer-joined table. Write it as an inner-join instead. But we all know that.
What creeps me out is when I left-join a child table to a parent, then inner join child to a grandchild, with no reference to either the child or the grandchild in the WHERE clause, and the join between child and parent automagically becomes an inner join.
I can't predict whether or not the join between the parent and child will remain as an outer join, which it sometimes appears to do, or convert to an inner join, as it seems to, erm... when it feels like it. When I'm working with a query which does this conversion, I'll often set the child/grandchild inner join up in a derived table and then left join to parent, after comparing performance against the alternative which is to outer join child and grandchild.
/ creep over. Am I being dumb here?
Edit: right brain on work, left brain on SSC
You aren't being dumb. It has to do with join evaluation. I used to always do all LEFT OUTER JOINs once I had one because of this exact issue. I learned at the SUMMIT last year in Itzik's Tips and Tricks session, that you can do this by changing the order of your ON clause. Like this:
CREATE TABLE #persons
(
person_id INT IDENTITY(1,1) PRIMARY KEY,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL
);
CREATE TABLE #relationships
(
relationship_id INT IDENTITY(1,1),
person_id INT NOT NULL,
relationship_type_id INT NOT NULL
);
CREATE TABLE #relationship_types
(
relationship_type_Id INT IDENTITY(1,1),
relationship_type_desc VARCHAR(20) NOT NULL
);
GO
INSERT INTO #persons
( last_name, first_name )
VALUES ( 'Corbett', -- last_name - varchar(20)
'Jack' -- first_name - varchar(20)
),
( 'Corbett', -- last_name - varchar(20)
'Joan' -- first_name - varchar(20)
) ;
Go
INSERT INTO #relationship_types
( relationship_type_desc
)
VALUES
('test');
Go
INSERT INTO #relationships
( person_id ,
relationship_type_id
)
SELECT
P.person_id,
RT.relationship_type_Id
FROM
#persons AS P CROSS JOIN
#relationship_types AS RT
WHERE
P.first_name = 'Jack';
GO
/* Give me all persons their relationship and the type
note the second join is INNER and the ON for it is defined first and the
ON Clause for the LEFT JOIN is defined second which means
SQL Server does the INNER JOIN first and then the OUTER JOIN
on that Virtual Table */
SELECT
*
FROM
#persons AS P LEFT JOIN
#relationships AS R JOIN
#relationship_types AS RT
ON R.relationship_type_id = RT.relationship_type_Id
ON P.person_id = R.person_id;
DROP TABLE #persons;
DROP TABLE #relationship_types;
DROP TABLE #relationships;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 5, 2010 at 9:45 am
The Dixie Flatline (8/5/2010)
Wow, a nice technical discussion on The Thread.
Who'd have thought?
SACRILEGE!! HERESY!! CURSORS!! :w00t::w00t:
Doesn't Rule #9 (not sure about the number) say something about no technical discussions in the Thread? 😉
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 5, 2010 at 9:52 am
Alvin Ramard (8/5/2010)
The Dixie Flatline (8/5/2010)
Wow, a nice technical discussion on The Thread.
Who'd have thought?
SACRILEGE!! HERESY!! CURSORS!! :w00t::w00t:
Doesn't Rule #9 (not sure about the number) say something about no technical discussions in the Thread? 😉
Yes.
A few weeks back Paul W cleared a smokescreen with a definition of a type of join, something like a 'stick' or 'branch' join, where the ON predicates were not immediately after the tables to which they related - similar to the elegant example Jack has generously provided above. I don't mind setting up a new thread for this if someone can remember and post that pesky join-type name!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 5, 2010 at 9:56 am
Chris Morris-439714 (8/5/2010)
Alvin Ramard (8/5/2010)
The Dixie Flatline (8/5/2010)
Wow, a nice technical discussion on The Thread.
Who'd have thought?
SACRILEGE!! HERESY!! CURSORS!! :w00t::w00t:
Doesn't Rule #9 (not sure about the number) say something about no technical discussions in the Thread? 😉
Yes.
A few weeks back Paul W cleared a smokescreen with a definition of a type of join, something like a 'stick' or 'branch' join, where the ON predicates were not immediately after the tables to which they related - similar to the elegant example Jack has generously provided above. I don't mind setting up a new thread for this if someone can remember and post that pesky join-type name!
There's a good reason for "moving" technical discussion out of the Thread. Others are more likely to benefit from the discussion if they're in a proper thread.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 5, 2010 at 10:14 am
Chris Morris-439714 (8/5/2010)
A few weeks back Paul W cleared a smokescreen with a definition of a type of join, something like a 'stick' or 'branch' join, where the ON predicates were not immediately after the tables to which they related - similar to the elegant example Jack has generously provided above.
Hmmm I think that was more than a few weeks ago. I was banging on about bushy plans and such I think. Anyway, yes the whole bracketed-joins / multiple ON clause thing is all very interesting, and so is the LEFT vs INNER vs ON vs WHERE debate. (I'm going to carefully avoid patronising anyone by mentioning basic relational algebra - close shave). I feel a blog entry coming on...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 5, 2010 at 10:19 am
Paul White NZ (8/5/2010)
Chris Morris-439714 (8/5/2010)
A few weeks back Paul W cleared a smokescreen with a definition of a type of join, something like a 'stick' or 'branch' join, where the ON predicates were not immediately after the tables to which they related - similar to the elegant example Jack has generously provided above.Hmmm I think that was more than a few weeks ago. I was banging on about bushy plans and such I think. Anyway, yes the whole bracketed-joins / multiple ON clause thing is all very interesting, and so is the LEFT vs INNER vs ON vs WHERE debate. (I'm going to carefully avoid patronising anyone by mentioning basic relational algebra - close shave). I feel a blog entry coming on...
BUSHY PLANS!! Thank you, Paul. Time to give that another read.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 17,131 through 17,145 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply