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


Correlated Joins Using "Apply"


Correlated Joins Using "Apply"

Author
Message
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28465 Visits: 18999
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...)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9649 Visits: 1407
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!!!



Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8680 Visits: 600
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 @strUpdate
Worked fine and reliable, I had to use it only twice, so not performance concerns, but I spent a lot of time writing it.

Yelena


Regards,
Yelena Varshal

mark rubanovich
mark rubanovich
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
Was not it easy to use more simple sql, like this one:

Select

?
GregoryAJackson
GregoryAJackson
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 506
Mark,

Could you post your code again?



GAJ

Gregory A Jackson MBA, CSM
Gerhard Schmeusser
Gerhard Schmeusser
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 136
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.
rani-779216
rani-779216
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 140
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 lvt
INNER 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.CreateDate

But it doesn't show as good preformance as the others (Cross Aplly & Row Number).

Rani_w
mmay-729099
mmay-729099
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: 50
How is APPLY any different from LEFT OUTER JOIN?
sing4you
sing4you
Right there with Babe
Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)

Group: General Forum Members
Points: 778 Visits: 2093
Well, once again, I've been enlightened. I've printed this article, and I bet I use it soon.

Thanks.
Bård Romstad
Bård Romstad
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 154
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).


--Create transaction table
CREATE TABLE #table1 (ID INT IDENTITY
,PhoneNumber VARCHAR(12)
,CreateDate DATETIME)
--Create a call table
CREATE TABLE #table2 (LVTransactionID INT
,CallWindowStart DATETIME
,CallWindowEnd DATETIME
,CallNumber INT)

--Populate transactions with 8 unique phone numbers
INSERT INTO #table1
SELECT '310 404 1001', '20090227 01:00:00'
UNION ALL
SELECT '310 404 1002', '20090227 02:00:00'
UNION ALL
SELECT '310 404 1003', '20090227 03:00:00'
UNION ALL
SELECT '310 404 1004', '20090227 04:00:00'
UNION ALL
SELECT '310 404 1005', '20090227 05:00:00'
UNION ALL
SELECT '310 404 1006', '20090227 06:00:00'
UNION ALL
SELECT '310 404 1007', '20090227 07:00:00'
UNION ALL
SELECT '310 404 1008', '20090227 08:00:00'

--For performance testing assume that these phone numbers were used in a large number of transactions each
INSERT INTO #table1
SELECT a.PhoneNumber, a.CreateDate FROM #table1 a, #table1 b, #table1 c, #table1 d

--Populate table2 with one call for each transaction
INSERT INTO #table2
SELECT ID, CreateDate, DATEADD(mi,1,CreateDate), 1
FROM #table1
--A minute later a new call is placed for all but the first transaction
INSERT INTO #table2
SELECT LVTransactionID, DATEADD(mi,1,CallWindowStart), DATEADD(mi,1,CallWindowEnd), CallNumber + 1
FROM #table2
WHERE LVTransactionID > 1 AND CallNumber = 1
--A minute later a new call is placed for all but the two first transactions
INSERT INTO #table2
SELECT LVTransactionID, DATEADD(mi,1,CallWindowStart), DATEADD(mi,1,CallWindowEnd), CallNumber + 1
FROM #table2
WHERE LVTransactionID > 2 AND CallNumber = 2
--A minute later a new call is placed for all but the three first transactions
INSERT INTO #table2
SELECT LVTransactionID, DATEADD(mi,1,CallWindowStart), DATEADD(mi,1,CallWindowEnd), CallNumber + 1
FROM #table2
WHERE LVTransactionID > 3 AND CallNumber = 3
--A minute later a new call is placed for all but the four first transactions
INSERT INTO #table2
SELECT LVTransactionID, DATEADD(mi,1,CallWindowStart), DATEADD(mi,1,CallWindowEnd), CallNumber + 1
FROM #table2
WHERE LVTransactionID > 4 AND CallNumber = 4

--We now have data for testing the result and performance of different methods
SELECT '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 method
SET @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.CallNumber
FROM #table1 lvt
INNER JOIN lvc ON lvt.ID=lvc.LVTransactionID
WHERE ROW_Num <= 3

SELECT DATEDIFF(millisecond, @StartTime, GETDATE()) AS [ExecutionTime CTE/ROW_NUMBER]

--Test the OUTER APPLY method
SET @StartTime = GETDATE();
SELECT
lvt.ID,
lvt.PhoneNumber,
lvt.CreateDate,
lvc.CallWindowStart,
lvc.CallWindowEnd,
lvc.CallNumber
FROM
#table1 lvt
OUTER APPLY
(
SELECT top 3 *
FROM #table2
WHERE LVTransactionID = lvt.ID
ORDER BY CallWindowStart DESC
) AS lvc
SELECT DATEDIFF(millisecond, @StartTime, GETDATE()) AS [ExecutionTime OUTER APPLY]

DROP TABLE #table1
DROP TABLE #table2
GO


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