SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Correlated Joins Using "Apply"


Correlated Joins Using "Apply"

Author
Message
mdonnelly
mdonnelly
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 14
David McKinney (2/27/2009)
mdonnelly (2/27/2009)
Peter E. Kierstead (6/16/2008)


I'll index this table appropriately, and wallah!, home-grown full-text index...



This would have been on the bubble of being excusable had the original article not had a big, bold Viola' in it.


While I'm being pedantic...Viola is a musical instrument while voilà is French - but très amusant! Bravo.

Recognizing my own short comings, this is exactly why I would have ignored it had it read "vallah".
Andy DBA
Andy DBA
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 762
mdonnelly (2/27/2009)
David McKinney (2/27/2009)
mdonnelly (2/27/2009)
Peter E. Kierstead (6/16/2008)


I'll index this table appropriately, and wallah!, home-grown full-text index...



This would have been on the bubble of being excusable had the original article not had a big, bold Viola' in it.


While I'm being pedantic...Viola is a musical instrument while voilà is French - but très amusant! Bravo.

Recognizing my own short comings, this is exactly why I would have ignored it had it read "vallah".


Yeah, thanks David. I didn't think I saw any stringed instruments in the original article!

Ooo eee, ooo ah ah
ting tang
voilà voilà, bing bang

Nice article, and great discussion. I must admit, I've been so buried in other work, I never had time to explore how powerful ROW_NUMBER() is. Might not have even paid attention to it 2 years ago.

And thanks McD for the example with the middle nesting level removed. The original looked more complicated than it had to be.



Belal s h
Belal s h
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 161
This article is good, finally i solved my problem today with this solution.

Thank you
salman.samad
salman.samad
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 230
Great article Gregory!

The one thing that really jumps out to me is the fact that you moved away from using UDFs as most of the articles out there explain APPLY statements using UDFs.

If only I had come across this article earlier, I wouldn't have taken so long to wrap my head around the real difference between the logical JOINs (OUTER and INNER) and the APPLY statements.

It is unfortunate however that a lot of people do not know about the APPLY functionality in SQL 2005 or are simply unaware that such a thing (or ROW_NUMBER) exists and that these would eliminate a lot of hair pulling and teeth gritting!
GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 506
Thanks Everyone,
I've been in Vegas the past few days for the NASCAR so I'm a little late in replying.

Thanks for the comments and discussion.


Greg J

Gregory A Jackson MBA, CSM
Paul DB
Paul DB
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 258
Matt Miller (6/16/2008)
Having done some amount of testing on this - while this scenario is certainly convenient in some scenarios, it has a tendency to perform on par with CSQ's, so it will suffer rather severely on large datasets. It unfortunately just seems to be a cleaner way to write a Correlated sub-query, but with the same kinds of perf challenges the CSQ's had (it forces row-by-row evaluation, the logical reads tend to get out of control, etc...)

Can someone comment on the veracity of Matt's claims?
Or perhaps Matt, do you have evidence (tests we can run, etc.)?

Thanks, BigGrin

Paul DB
mdonnelly
mdonnelly
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 14
Paul DB (3/4/2009)
Matt Miller (6/16/2008)
Having done some amount of testing on this - while this scenario is certainly convenient in some scenarios, it has a tendency to perform on par with CSQ's, so it will suffer rather severely on large datasets. It unfortunately just seems to be a cleaner way to write a Correlated sub-query, but with the same kinds of perf challenges the CSQ's had (it forces row-by-row evaluation, the logical reads tend to get out of control, etc...)

Can someone comment on the veracity of Matt's claims?
Or perhaps Matt, do you have evidence (tests we can run, etc.)?

Thanks, BigGrin


There's certainly no reason NOT to believe. It's clearly syntax to subset per row (table function) which is what a correlated sub-query is. The OP merely removed the table function.

You will let us know when you compile your test results.
Misha_SQL
Misha_SQL
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1003
Thank you for the article. Believe it or not, I had a problem today, which I solved using your method!



GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 506
wow...that's great news.

whole reason for the article....Mission accomplished !


:-)


GAJ

Gregory A Jackson MBA, CSM
frazleo
frazleo
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 142
Thank You,

I have used first time "Outer Apply" Clause. It seems to be it is very expensive than regular Join with Drive Table. It totally make sense to make a reference from out query to inner query so we dont scan all rows.
But According to Query Analyzer it seems to be it 26 % versus 74%.


---
21%
-----
SELECT T.CO, T.TR, S.rowNo, S.ST, T.PC, T.ST, T.ET, S.AATime AS S_Actual,
DRV_Seg.AATime AS derivedtbl_1_Actual
FROM Table1 AS T INNER JOIN
Table2AS S ON T.TR = S.TR AND S.CO = T.CO LEFT OUTER JOIN

(SELECT CO, TR, rowNo, AATime
FROM NADataSource.Segment
WHERE (ST = '1')
AND (LEFT(PC, 1) <> 'B') AND (PC <> 'lhe')
AND (PC <> 'lhy')) AS DRV_Seg ON
S.TR = DRV_Seg.TR AND DRV_Seg.CO = S.CO AND DRV_Seg.rowNo = S.rowNo
WHERE (T.CO = N'338') AND (T.TR = '9935')
ORDER BY S.rowNo


----------
----61%
-----------
SELECT T.CO, T.TR, S.rowNo, S.ST, T.PC, T.ST, T.ET, S.AATime AS S_Actual,
DRV_Seg.AATime AS derivedtbl_1_Actual
FROM Table1 AS T INNER JOIN
Table2AS S ON T.TR = S.TR AND S.CO = T.CO

OUTER APPLY

(SELECT CO, TR, rowNo, AATime
FROM Table2S1
WHERE S.TR = S1.TR
AND S.CO = S1.CO
AND S1.rowNo = S.rowNo
AND (ST = '1')
AND (LEFT(PC, 1) <> 'B')
AND (PC <> 'lhe')
AND (PC <> 'lhy')
)AS DRV_Seg


WHERE (T.CO = N'338') AND (T.TR = '9935')
ORDER BY S.rowNo



------Cheapest one
---18%
SELECT T.CO, T.TR, S.rowNo, S.ST, T.PC, T.ST, T.ET, S.AATime AS S_Actual,
S1.AATime AS derivedtbl_1_Actual
FROM Table1AS T
INNER JOIN
Table2 AS S ON T.TR = S.TR AND S.CO = T.CO

LEFT OUTER JOIN

Table2 AS S1 ON T.TR = S1.TR AND S1.CO = T.CO
AND s1.ST = '1' and S.rowNo = S1.rowNo




WHERE (T.CO = N'338') AND (T.TR = '9935')
ORDER BY S.rowNo



Any body explain me what is better approach?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search