Correlated Joins Using "Apply"

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Gregory Jackson » Correlated Joins Using "Apply"

50 posts, Page 5 of 5

«««1 2 3 4 5

Correlated Joins Using "Apply"

Rate Topic

Display Mode

Topic Options

Author

Message

mdonnelly

Posted Friday, February 27, 2009 12:13 PM

Forum 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

Andy DBA

Posted Friday, February 27, 2009 1:08 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 10, 2013 11:14 AM
Points: 137, Visits: 444

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

Belal s h

Posted Saturday, February 28, 2009 2:37 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, July 05, 2012 12:17 AM
Points: 28, Visits: 129

This article is good, finally i solved my problem today with this solution.

Thank you

Post #666199

salman.samad

Posted Saturday, February 28, 2009 4:05 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 04, 2010 9:41 AM
Points: 461, Visits: 229

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

GregoryAJackson

Posted Monday, March 02, 2009 11:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

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

Paul DB

Posted Wednesday, March 04, 2009 10:06 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:07 PM
Points: 60, Visits: 257

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

mdonnelly

Posted Wednesday, March 04, 2009 10:13 AM

Forum 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)

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

mishaluba

Posted Tuesday, March 10, 2009 11:02 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, June 15, 2013 2:08 PM
Points: 525, Visits: 624

Thank you for the article. Believe it or not, I had a problem today, which I solved using your method!

Post #672987

GregoryAJackson

Posted Tuesday, March 10, 2009 11:06 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

wow...that's great news.

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

Smile

GAJ

Gregory A Jackson MBA, CSM

Post #672989

frazleo

Posted Wednesday, August 12, 2009 11:49 AM

Grasshopper

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 »

50 posts, Page 5 of 5

«««1 2 3 4 5

Permissions