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 Monday, June 16, 2008 9:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:56 PM
Points: 7,075, Visits: 15,325
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?
Post #517637
Posted Monday, June 16, 2008 10:19 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,375, Visits: 1,391
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!!!



Post #517678
Posted Monday, June 16, 2008 10:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:44 PM
Points: 3,475, Visits: 583
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

Post #517716
Posted Tuesday, June 17, 2008 10:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 9:00 AM
Points: 1, Visits: 6
Was not it easy to use more simple sql, like this one:

Select

?
Post #518423
Posted Tuesday, June 17, 2008 10:41 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
Mark,

Could you post your code again?



GAJ


Gregory A Jackson MBA, CSM
Post #518428
Posted Wednesday, June 18, 2008 1:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 7, 2012 12:30 PM
Points: 37, 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.
Post #518792
Posted Wednesday, June 18, 2008 8:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:24 AM
Points: 58, Visits: 137
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


Post #519119
Posted Friday, February 27, 2009 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 7:43 AM
Points: 11, Visits: 35
How is APPLY any different from LEFT OUTER JOIN?
Post #665652
Posted Friday, February 27, 2009 7:08 AM


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: Yesterday @ 8:41 AM
Points: 504, Visits: 2,031
Well, once again, I've been enlightened. I've printed this article, and I bet I use it soon.

Thanks.
Post #665701
Posted Friday, February 27, 2009 7:11 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, December 19, 2013 1:32 AM
Points: 140, Visits: 151
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

Post #665704
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse