CROSS APPLY Too Slow to Run Everything - TSQL

Question

Please see my code below as it works too slowly with CROSS APPLY.

How can I remove CROSS APPLY and add something else that will work faster? Please note that I am using SQL Server 2008 R2.

;WITH MyCTE AS ( SELECT R.NetWinCURRENCYValue AS NetWin ,dD.[Date] AS TheDay FROM dimPlayer AS P JOIN dbo.factRevenue AS R ON P.playerKey = R.playerKey JOIN dbo.vw_Date AS dD ON Dd.dateKey = R.dateKey WHERE P.CustomerID = 12345) SELECT A.TheDay AS [Date] ,ISNULL(A.NetWin, 0) AS NetWin ,rt.runningTotal AS CumulativeNetWin FROM MyCTE AS A CROSS APPLY (SELECT SUM(NetWin) AS runningTotal FROM MyCTE WHERE TheDay <= A.TheDay) AS rt ORDER BY A.TheDay 

Answer 1

 CREATE TABLE #temp (NetWin money, TheDay datetime) insert into #temp SELECT R.NetWinCURRENCYValue AS NetWin ,dD.[Date] AS TheDay FROM dimPlayer AS P JOIN dbo.factRevenue AS R ON P.playerKey = R.playerKey JOIN dbo.vw_Date AS dD ON Dd.dateKey = R.dateKey WHERE P.CustomerID = 12345; SELECT A.TheDay AS [Date] ,ISNULL(A.NetWin, 0) AS NetWin ,SUM(B.NetWin) AS CumulativeNetWin FROM #temp AS A JOIN #temp AS B ON A.TheDay >= B.TheDay GROUP BY A.TheDay, ISNULL(A.NetWin, 0); 

Answer 2

Here fooobar.com/questions/30362 / ... is suggested to use a recursive CTE.

 ;WITH MyCTE AS ( SELECT R.NetWinCURRENCYValue AS NetWin ,dD.[Date] AS TheDay ,ROW_NUMBER() OVER (ORDER BY dD.[Date]) AS RN FROM dimPlayer AS P JOIN dbo.factRevenue AS R ON P.playerKey = R.playerKey JOIN dbo.vw_Date AS dD ON Dd.dateKey = R.dateKey WHERE P.CustomerID = 12345 ) , MyCTERec AS ( SELECT C.TheDay AS [Date] ,ISNULL(C.NetWin, 0) AS NetWin ,ISNULL(C.NetWin, 0) AS CumulativeNetWin ,C.RN FROM MyCTE AS C WHERE C.RN = 1 UNION ALL SELECT C.TheDay AS [Date] ,ISNULL(C.NetWin, 0) AS NetWin ,P.CumulativeNetWin + ISNULL(C.NetWin, 0) AS CumulativeNetWin ,C.RN FROM MyCTERec P INNER JOIN MyCTE AS C ON C.RN = P.RN + 1 ) SELECT * FROM MyCTERec ORDER BY RN OPTION (MAXRECURSION 0) 

Please note that this query will work if you have 1 entry == 1 day! If you have multiple entries in one day, the results will be different from others.

Answer 3

As I said here , if you want a really quick calculation, put it in a temporary table with a sequential primary key, and then calculate the total number of impressions:

 create table #Temp ( ID bigint identity(1, 1) primary key, [Date] date, NetWin decimal(29, 10) ) insert into #Temp ([Date], NetWin) select dD.[Date], sum(R.NetWinCURRENCYValue) as NetWin, from dbo.dimPlayer as P inner join dbo.factRevenue as R on P.playerKey = R.playerKey inner join dbo.vw_Date as dD on Dd.dateKey = R.dateKey where P.CustomerID = 12345 group by dD.[Date] order by dD.[Date] ;with cte as ( select T.ID, T.[Date], T.NetWin, T.NetWin as CumulativeNetWin from #Temp as T where T.ID = 1 union all select T.ID, T.[Date], T.NetWin, T.NetWin + C.CumulativeNetWin as CumulativeNetWin from cte as C inner join #Temp as T on T.ID = C.ID + 1 ) select C.[Date], C.NetWin, C.CumulativeNetWin from cte as C order by C.[Date] 

I assume that you might have duplicate dates in the input, but you don't need duplicate dates in the output, so I grouped the data before inserting it into the table.