SQLServerCentral / Discuss Content Posted by Gregory Jackson / Article Discussions / Article Discussions by Author / Correlated Joins Using "Apply" / Latest Posts

RE: Correlated Joins Using "Apply"

frazleo — Wed, 12 Aug 2009 11:49:40 GMT

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_ActualFROM 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.rowNoWHERE (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_ActualFROM 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_ActualFROM 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.rowNoAny body explain me what is better approach?

RE: Correlated Joins Using "Apply"

GregoryAJackson — Tue, 10 Mar 2009 23:06:55 GMT

wow...that's great news.whole reason for the article....Mission accomplished !:-)GAJ

RE: Correlated Joins Using "Apply"

mishaluba — Tue, 10 Mar 2009 23:02:08 GMT

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

RE: Correlated Joins Using "Apply"

mdonnelly — Wed, 04 Mar 2009 10:13:35 GMT

[quote][b]Paul DB (3/4/2009)[/b][hr][quote][b]Matt Miller (6/16/2008)[/b][hr]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...)[/quote]Can someone comment on the veracity of Matt's claims?Or perhaps Matt, do you have evidence (tests we can run, etc.)?Thanks, :D[/quote]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.

RE: Correlated Joins Using "Apply"

Paul DB — Wed, 04 Mar 2009 10:06:23 GMT

[quote][b]Matt Miller (6/16/2008)[/b][hr]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...)[/quote]Can someone comment on the veracity of Matt's claims?Or perhaps Matt, do you have evidence (tests we can run, etc.)?Thanks, :D

RE: Correlated Joins Using "Apply"

GregoryAJackson — Mon, 02 Mar 2009 11:14:58 GMT

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

RE: Correlated Joins Using "Apply"

salman.samad — Sat, 28 Feb 2009 16:05:10 GMT

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!

RE: Correlated Joins Using "Apply"

Belal s h — Sat, 28 Feb 2009 14:37:47 GMT

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

RE: Correlated Joins Using "Apply"

Andy DBA — Fri, 27 Feb 2009 13:08:32 GMT

[quote][b]mdonnelly (2/27/2009)[/b][hr][quote][b]David McKinney (2/27/2009)[/b][hr][quote][b]mdonnelly (2/27/2009)[/b][hr][quote][b]Peter E. Kierstead (6/16/2008)[/b][hr]I'll index this table appropriately, and [b]wallah![/b], home-grown full-text index...[/quote]This would have been on the bubble of being excusable had the original article not had a big, bold [b]Viola'[/b] in it.[/quote]While I'm being pedantic...Viola is a musical instrument while voilà is French - but très amusant! Bravo.[/quote]Recognizing my own short comings, this is exactly why I would have ignored it had it read "vallah".[/quote]Yeah, thanks David. I didn't think I saw any stringed instruments in the original article!Ooo eee, ooo ah ah ting tangvoilà voilà, bing bangNice 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.

RE: Correlated Joins Using "Apply"

mdonnelly — Fri, 27 Feb 2009 12:13:35 GMT

[quote][b]David McKinney (2/27/2009)[/b][hr][quote][b]mdonnelly (2/27/2009)[/b][hr][quote][b]Peter E. Kierstead (6/16/2008)[/b][hr]I'll index this table appropriately, and [b]wallah![/b], home-grown full-text index...[/quote]This would have been on the bubble of being excusable had the original article not had a big, bold [b]Viola'[/b] in it.[/quote]While I'm being pedantic...Viola is a musical instrument while voilà is French - but très amusant! Bravo.[/quote]Recognizing my own short comings, this is exactly why I would have ignored it had it read "vallah".

RE: Correlated Joins Using "Apply"

David McKinney — Fri, 27 Feb 2009 12:04:36 GMT

[quote][b]mdonnelly (2/27/2009)[/b][hr][quote][b]Peter E. Kierstead (6/16/2008)[/b][hr]I'll index this table appropriately, and [b]wallah![/b], home-grown full-text index...[/quote]This would have been on the bubble of being excusable had the original article not had a big, bold [b]Viola'[/b] in it.[/quote]While I'm being pedantic...Viola is a musical instrument while voilà is French - but très amusant! Bravo.

RE: Correlated Joins Using "Apply"

David McKinney — Fri, 27 Feb 2009 11:59:21 GMT

[quote][b]Alex Ionine (6/16/2008)[/b][hr]it could be a descent article had it been written 2 years ago.[/quote]or even an ascent article! (no s in decent.)

RE: Correlated Joins Using "Apply"

mdonnelly — Fri, 27 Feb 2009 11:58:41 GMT

[quote][b]don_goodman (2/27/2009)[/b][hr]If you are going to write a piece of code then you have an obligation to the user. Why? This is a service business. The user experience is the most important part of the service business. If your code runs slower than another piece of code and both return the same result, you should use the other code. An article explaining the new "feature" runs poorer than the old "feature" may be providing a public service. But, I would really like to see code samples that help me provide the user with a better experience instead of a worse one.[/quote]...which does make you wonder if APPLY is intended for some other predicament

RE: Correlated Joins Using "Apply"

timothyawiseman — Fri, 27 Feb 2009 11:23:36 GMT

[quote][b]Alex Ionine (6/16/2008)[/b][hr]it could be a descent article had it been written 2 years ago.[/quote]I have to disagree. Given SQL Server 2005's age now, I would not have emphasized the "newness" of the feature personally, but it is a good article at any time. It highlights a seldom used feature which I suspect many developers do not know of and provides a good explanation and good examples. It then provides an alternate solution to the same problem (also using a technique I suspect many developers are not familiar with) and discusses why one might be preferable over the other. I found it informative personally.As a side note, you might want to think about refactoring the partitioned solution at the bottom with a CTE. I know it is a matter of opinion, but personally, I find it much easier to read when the subqueries are separated from the main query and it provides a more clear delineation as tot he different parts of the query if someone is skimming over it quickly.

RE: Correlated Joins Using "Apply"

mdonnelly — Fri, 27 Feb 2009 09:44:22 GMT

[quote][b]Peter E. Kierstead (6/16/2008)[/b][hr]I'll index this table appropriately, and [b]wallah![/b], home-grown full-text index...[/quote]This would have been on the bubble of being excusable had the original article not had a big, bold [b]Viola'[/b] in it.

RE: Correlated Joins Using "Apply"

mdonnelly — Fri, 27 Feb 2009 09:37:26 GMT

for what it's worth, I can give you a little simplification. The partitioning query doesn't have to be stated twice:select acct.[Name], cont.fullname, cont.createdon FROM [AccountBase] acct JOIN (SELECT accountid, fullname, createdon , ROW_NUMBER() OVER (PARTITION BY [AccountId] ORDER BY createdon Desc) ROW_NUM FROM [ContactBase]) cont on cont.[AccountId] = acct.[AccountId] where cont.ROW_NUM <= 3 order by acct.[Name], cont.fullnameYou could have joined lvt directly to the sub query removing the middle level of nesting.I'm betting this is vastly superior in performance to APPLY.

RE: Correlated Joins Using "Apply"

mmastro63 — Fri, 27 Feb 2009 09:21:12 GMT

Alright, now I'm feeling like a rookie. I didn't notice the table alias' for the lvc on the output list. Sorry for wasting your time!

RE: Correlated Joins Using "Apply"

GSquared — Fri, 27 Feb 2009 09:15:48 GMT

Good article.I've used Cross and Outer Apply a few times very successfully. They work better with sub-queries than with UDFs. Even a single-select UDF has a performance hit of a few milliseconds at least, compared to having the query directly in the calling routine.For something like "top 3 of each", row_number is generally significantly faster. But for more complex queries, Apply works just fine.Just make sure the outer query has as few rows as possible, because it is essentially RBAR at that point. Can still be fast, but keep it to only what you actually need.

RE: Correlated Joins Using "Apply"

Andy Lennon — Fri, 27 Feb 2009 09:12:07 GMT

you've almost got it m. unfortunately, once you switch to using the IN condition, you can no longer pull the other data from LVCalls (disposition, call date) because they're still not part of the from statement.you really do have to use one of the techniques mentioned (apply, row_number, CTE). :(

RE: Correlated Joins Using "Apply"

mmastro63 — Fri, 27 Feb 2009 08:58:40 GMT

Take it from the top! Ok. Sorry, I had a minor error in the WHERE Clause. I meant:SELECTlvt.ID,lvt.PhoneNumber,lvt.CreateDate,lvc.CallWindowStart,lvc.CallWindowEnd,lvc.LVCallDispositionID FROMLVTransaction lvtWHERE lvt.ID in ( --<<<<<<<<<<<<<<< THIS IS WHAT I MEANTSELECT top 3 lvtTransactionID --<<<<<<<<<<<<<<<<<<< ANOTHER correction ... :) FROM LVCall WHERE lvtransactionID = lvt.ID Order By CreateDate DESC) as lvc

RE: Correlated Joins Using "Apply"

mmastro63 — Fri, 27 Feb 2009 08:56:46 GMT

Ok. Sorry, I had a minor error in the WHERE Clause. I meant:SELECTlvt.ID,lvt.PhoneNumber,lvt.CreateDate,lvc.CallWindowStart,lvc.CallWindowEnd,lvc.LVCallDispositionID FROMLVTransaction lvtWHERE lvt.ID in ( --<<<<<<<<<<<<<<< THIS IS WHAT I MEANTSELECT top 3 * FROM LVCall WHERE lvtransactionID = lvt.ID Order By CreateDate DESC) as lvcTry this. It is essentially the same thing.create table #T1 (pk int identity(1,1), C1 int)create table #T2 (pk int identity(1,1), C2 int)insert #T1 (C1) values (1)insert #T1 (C1) values (2)insert #T2 (C2) values (1)insert #T2 (C2) values (2)select *from #T1 T1where pk in (select pk from #T2 T2 where T1.pk = T2.pk)drop table #T1drop table #T2

RE: Correlated Joins Using "Apply"

Andy Lennon — Fri, 27 Feb 2009 08:47:34 GMT

you're still looking for lvt.ID in a query that only has LVCalls in the from statement

RE: Correlated Joins Using "Apply"

mmastro63 — Fri, 27 Feb 2009 08:42:23 GMT

Andy, I didn't do the same thing noted in the article:--Correlated INNER JOIN AttemptSELECT lvt.ID, lvt.PhoneNumber, lvt.CreateDate, lvc.CallWindowStart, lvc.CallWindowEnd, lvc.LVCallDispositionID FROM LVTransaction lvtINNER JOIN (SELECT TOP 3 * FROM LVCall WHERE LVTransactionID = lvt.ID ORDER BY CreateDate DESC) lvc on lvc.LVTransactionID = lvt.ID -- <<<<<<<<<<<<<<<< mine DOESN'T have this.Mine isn't a correlated inner join, it's a simple correlated subquery. Note the <<<<<<<<<<<'s. In mine, the inner query will reference the outside query for each row in the outside query. There is no "JOIN.... ON... "

RE: Correlated Joins Using "Apply"

Andy Lennon — Fri, 27 Feb 2009 08:31:36 GMT

nope. you'd get this error as stated in the article:Msg 4104, Level 16, State 1, Line 2The multi-part identifier "lvt.ID" could not be bound.Hence the use of apply (or row_number).

RE: Correlated Joins Using "Apply"

mmastro63 — Fri, 27 Feb 2009 08:22:28 GMT

Does a regular correlated sub-query perform the same thing?SELECT lvt.ID, lvt.PhoneNumber, lvt.CreateDate, lvc.CallWindowStart, lvc.CallWindowEnd, lvc.LVCallDispositionID FROM LVTransaction lvtOUTER APPLY --<<<<<<<<<<<<<< Replace OUTER APPLY with WHERE.( SELECT top 3 * FROM LVCall WHERE lvtransactionID = lvt.ID Order By CreateDate DESC ) as lvc

RE: Correlated Joins Using "Apply"

Andy Lennon — Fri, 27 Feb 2009 08:15:13 GMT

P.S. great article!

RE: Correlated Joins Using "Apply"

Andy Lennon — Fri, 27 Feb 2009 08:14:22 GMT

my first thought was to set up a CTE for the transactions, then join that to the Call data and use TOP 3. If the data can be returned on multiple rows i'd think this would be pretty fast (haven't tested it).

RE: Correlated Joins Using "Apply"

don_goodman — Fri, 27 Feb 2009 08:11:55 GMT

If you are going to write a piece of code then you have an obligation to the user. Why? This is a service business. The user experience is the most important part of the service business. If your code runs slower than another piece of code and both return the same result, you should use the other code. An article explaining the new "feature" runs poorer than the old "feature" may be providing a public service. But, I would really like to see code samples that help me provide the user with a better experience instead of a worse one.

RE: Correlated Joins Using "Apply"

Gatekeeper — Fri, 27 Feb 2009 08:02:38 GMT

We've been using the ROW_NUMBER() method for a while now and it's great for reporting purposes. Not to mention, its speed is great compared to using UDFs or inserting into a temp table within a loop.To get the top X records per groupSELECT * FROM(SELECT ROW_NUMBER() OVER (PARTITION BY ChowID ORDER BY ChowID, EndDate) as RowNumber, ChowID, EndDate, Name FROM StatLog) as aWHERE a.RowNumber <= @TopCount ORDER BY a.Name, a.EndDate

RE: Correlated Joins Using "Apply"

noeld — Fri, 27 Feb 2009 07:57:39 GMT

OOps! I didn't realized this was and old post.

RE: Correlated Joins Using "Apply"

Bård Romstad — Fri, 27 Feb 2009 07:11:09 GMT

From the tests below it seems like the ROW_NUMBER method is faster than the OUTER APPLY.How much faster depends on the number of calls per transaction (the difference is smaller if there are few calls per transaction).[code]--Create transaction tableCREATE TABLE #table1 (ID INT IDENTITY ,PhoneNumber VARCHAR(12) ,CreateDate DATETIME)--Create a call tableCREATE TABLE #table2 (LVTransactionID INT ,CallWindowStart DATETIME ,CallWindowEnd DATETIME ,CallNumber INT)--Populate transactions with 8 unique phone numbersINSERT INTO #table1SELECT '310 404 1001', '20090227 01:00:00'UNION ALLSELECT '310 404 1002', '20090227 02:00:00'UNION ALLSELECT '310 404 1003', '20090227 03:00:00'UNION ALLSELECT '310 404 1004', '20090227 04:00:00'UNION ALLSELECT '310 404 1005', '20090227 05:00:00'UNION ALLSELECT '310 404 1006', '20090227 06:00:00'UNION ALLSELECT '310 404 1007', '20090227 07:00:00'UNION ALLSELECT '310 404 1008', '20090227 08:00:00'--For performance testing assume that these phone numbers were used in a large number of transactions eachINSERT INTO #table1SELECT a.PhoneNumber, a.CreateDate FROM #table1 a, #table1 b, #table1 c, #table1 d--Populate table2 with one call for each transactionINSERT INTO #table2SELECT ID, CreateDate, DATEADD(mi,1,CreateDate), 1FROM #table1--A minute later a new call is placed for all but the first transactionINSERT INTO #table2SELECT LVTransactionID, DATEADD(mi,1,CallWindowStart), DATEADD(mi,1,CallWindowEnd), CallNumber + 1FROM #table2WHERE LVTransactionID > 1 AND CallNumber = 1--A minute later a new call is placed for all but the two first transactionsINSERT INTO #table2SELECT LVTransactionID, DATEADD(mi,1,CallWindowStart), DATEADD(mi,1,CallWindowEnd), CallNumber + 1FROM #table2WHERE LVTransactionID > 2 AND CallNumber = 2--A minute later a new call is placed for all but the three first transactionsINSERT INTO #table2SELECT LVTransactionID, DATEADD(mi,1,CallWindowStart), DATEADD(mi,1,CallWindowEnd), CallNumber + 1FROM #table2WHERE LVTransactionID > 3 AND CallNumber = 3--A minute later a new call is placed for all but the four first transactionsINSERT INTO #table2SELECT LVTransactionID, DATEADD(mi,1,CallWindowStart), DATEADD(mi,1,CallWindowEnd), CallNumber + 1FROM #table2WHERE LVTransactionID > 4 AND CallNumber = 4--We now have data for testing the result and performance of different methodsSELECT 'Testing performance: '+CAST((SELECT COUNT(*) FROM #table1) AS varchar(10))+' transactions and '+CAST((SELECT COUNT(*) FROM #table2) AS varchar(10))+' calls'DECLARE @StartTime DATETIME--Test CTE/ROW_NUMBER methodSET @StartTime = GETDATE();WITH lvc AS ( SELECT LVTransactionID , CallWindowStart , CallWindowEnd , CallNumber , ROW_Num = ROW_NUMBER() OVER (PARTITION BY LVTransactionID ORDER BY CallWindowStart DESC) FROM #table2)SELECT lvt.ID ,lvt.PhoneNumber , lvt.CreateDate , lvc.CallWindowStart , lvc.CallWindowEnd , lvc.CallNumberFROM #table1 lvtINNER JOIN lvc ON lvt.ID=lvc.LVTransactionIDWHERE ROW_Num <= 3SELECT DATEDIFF(millisecond, @StartTime, GETDATE()) AS [ExecutionTime CTE/ROW_NUMBER]--Test the OUTER APPLY methodSET @StartTime = GETDATE();SELECT lvt.ID, lvt.PhoneNumber, lvt.CreateDate, lvc.CallWindowStart, lvc.CallWindowEnd, lvc.CallNumberFROM #table1 lvtOUTER APPLY ( SELECT top 3 * FROM #table2 WHERE LVTransactionID = lvt.ID ORDER BY CallWindowStart DESC ) AS lvcSELECT DATEDIFF(millisecond, @StartTime, GETDATE()) AS [ExecutionTime OUTER APPLY]DROP TABLE #table1DROP TABLE #table2GO[/code]

RE: Correlated Joins Using "Apply"

sing4you — Fri, 27 Feb 2009 07:08:14 GMT

Well, once again, I've been enlightened. I've printed this article, and I bet I use it soon.Thanks.

RE: Correlated Joins Using "Apply"

mmay-729099 — Fri, 27 Feb 2009 06:19:36 GMT

How is APPLY any different from LEFT OUTER JOIN?

RE: Correlated Joins Using "Apply"

rani-779216 — Wed, 18 Jun 2008 08:39:09 GMT

Hi,If i understood the problem (you did not show the result set)I think that you can also write this query this way (sql 2000/2005):SELECT lvt.ID, lvt.PhoneNumber, lvt.CreateDate, lvc.CallWindowStart, lvc.CallWindowEnd, lvc.LVCallDispositionID FROM LVTransaction lvtINNER JOIN LVCall lvc on (lvc.LVTransactionID = lvt.ID and lvc.CreateDate in (select top 3 CreateDate from LVCall lvc1 where lvc1.LVTransactionID = lvc.LVTransactionID Order By CreateDate DESC))order by lvt.ID,lvt.CreateDateBut it doesn't show as good preformance as the others (Cross Aplly & Row Number). Rani_w

RE: Correlated Joins Using "Apply"

Gerhard Schmeusser — Wed, 18 Jun 2008 01:14:44 GMT

I had this problem too and solved it by row numbering like in the article. Although I had read about the APPLY function it did not come to my mind. I therefore think the article is indeed valuable, even if it is not the newest feature, because the use of the APPLY function is little known. Would be interesting if somebody could shed some more light on the performance. Thanks.

RE: Correlated Joins Using "Apply"

GregoryAJackson — Tue, 17 Jun 2008 10:41:44 GMT

Mark,Could you post your code again?GAJ

RE: Correlated Joins Using "Apply"

mark rubanovich — Tue, 17 Jun 2008 10:32:20 GMT

Was not it easy to use more simple sql, like this one:Select ?

RE: Correlated Joins Using "Apply"

Yelena Varshal — Mon, 16 Jun 2008 10:53:19 GMT

This is an excellent article by itself and even better that it suggests alternative solutions by other authors. I will try them all.When you need to do something quick, you normally use most familiar tools and if you don't have to run this query often, performance does not matter. I needed to get top 10 calls for each contact and pivot call dates to the output columns. Well, I used s cursor with the outer loop going from contact to contact and the inner loop moving from call to call. The line number for the call for a certain customer also served as a part of the column name in the update statement:select @strUpdate ='update ##TempTableCalls set Call_'+ convert(nvarchar(10),@LINE_NO) + ' = ' (the rest of the line going here) The temp table with the columns like Call_1, Call_2 was created in advance and populated with something additional before adding calls.After the update string for a call was composed, I used Exec sp_executesql @strUpdateWorked fine and reliable, I had to use it only twice, so not performance concerns, but I spent a lot of time writing it.Yelena

RE: Correlated Joins Using "Apply"

Anipaul — Mon, 16 Jun 2008 10:19:41 GMT

Yes I agree you can always learn new things. Even you can learn new things from your juniors. Some people may know these feature but this article is good for many people.Cheers!!!

RE: Correlated Joins Using "Apply"

Matt Miller (#4) — Mon, 16 Jun 2008 09:15:49 GMT

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