December 5, 2014 at 1:57 pm
I have a CTE query that is used to fill in nulls on a history table. The With statement executes just fine. sub 2 seconds on 974 records, however the main query is what's turning the whole query into a turtle. I know that it's the looping that it's doing there that is causing the slow down, but I'm just not sure how to fix it. I've tried inserting it into a temp table, refactored the code a hundred times, but nothing seems to be working. I'm sure I'm missing a good optimization step, I'm just not smart enough to see it.
Code is below and the execution plan is attached.
Thanks in advance!
;WITHBuildTable
AS ( SELECT [GEGTH].[ID]
, [GEGTH].[Changed By]
, CAST( [dbo].[GetWeekStarting] ([GEGTH].[Changed Date] , 2 ) AS DATE) AS WeekOf
, [GEGT].[Title]
, SUM([GEGTH].[Completed Work]) AS [Hours Worked]
, ROW_NUMBER() OVER ( ORDER BY [GEGTH].[ID] ) rownum
FROM[dbo].[GNS_EDGE_GenericTemplate_History] AS [GEGTH]
RIGHT JOIN [dbo].[GNS_EDGE_GenericTemplate] AS [GEGT]
ON [GEGT].[ID] = [GEGTH].[ID]
WHERE[GEGT].[Team] LIKE '%GNSGSGO%'
GROUP BY [GEGTH].[ID]
, [GEGTH].[Changed Date]
, [GEGTH].[Changed By]
, [GEGT].[Title]
)
SELECT [CleanedView].[ID]
, [CleanedView].[Title]
, COALESCE(SUM([CleanedView].[Hours Worked]),0) AS [Hours for Week]
, [CleanedView].[Changed By]
, ISNULL([FirstApply].[Changed By] , [SecondApply].[Changed By]) AS [Changed By New]
, [CleanedView].[WeekOf]
FROM[BuildTable] CleanedView
OUTER APPLY ( SELECT TOP 1 *
FROM[BuildTable]
WHERE[BuildTable].[rownum] <= [CleanedView].[rownum]
AND [BuildTable].[Changed By] IS NOT NULL
ORDER BY[BuildTable].[rownum] DESC
) FirstApply
OUTER APPLY ( SELECT TOP 1 *
FROM[BuildTable]
WHERE[BuildTable].[Changed By] IS NOT NULL
ORDER BY[BuildTable].[rownum]
) SecondApply
GROUP BY [CleanedView].[WeekOf]
, [CleanedView].[Changed By]
, [FirstApply].[Changed By]
, [SecondApply].[Changed By]
, [CleanedView].[Title]
, [CleanedView].[ID]
ORDER BY [CleanedView].[ID]
December 5, 2014 at 2:11 pm
My apologies. I wasn't paying attention when I posted this.
Moving to the 2012 Development forum.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply