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 «««12345

Correlated Joins Using "Apply" Expand / Collapse
Author
Message
Posted Friday, February 27, 2009 12:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 27, 2009 8:21 AM
Points: 9, 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".
Post #665937
Posted Friday, February 27, 2009 1:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 3:41 PM
Points: 147, Visits: 542
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.



Post #665970
Posted Saturday, February 28, 2009 2:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:37 AM
Points: 34, Visits: 143
This article is good, finally i solved my problem today with this solution.

Thank you
Post #666199
Posted Saturday, February 28, 2009 4:05 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 28, 2013 10:28 AM
Points: 461, 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!
Post #666212
Posted Monday, March 2, 2009 11:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
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
Post #666767
Posted Wednesday, March 4, 2009 10:06 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 9, 2013 1:26 PM
Points: 60, 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, :D


Paul DB
Post #668449
Posted Wednesday, March 4, 2009 10:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 27, 2009 8:21 AM
Points: 9, 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, :D


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.
Post #668457
Posted Tuesday, March 10, 2009 11:02 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:22 PM
Points: 537, Visits: 771
Thank you for the article. Believe it or not, I had a problem today, which I solved using your method!


Post #672987
Posted Tuesday, March 10, 2009 11:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
wow...that's great news.

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





GAJ


Gregory A Jackson MBA, CSM
Post #672989
Posted Wednesday, August 12, 2009 11:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 18, 2010 1:18 PM
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?
Post #769539
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse