CTE query - Long Load Time

Question

Post reply

CTE query - Long Load Time

pike_nicky

SSC Enthusiast

Points: 137
More actions
December 5, 2014 at 2:16 pm

#397739

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.
Server Version: 12.0.2342.0
Enterprise: 64bit
Thanks in advance!
;WITH BuildTable
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]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

g.britton SSChampion Points: 13863 More actions · Answer 1

"The With statement executes just fine. sub 2 seconds on 974 records"

Do you mean the With clause plus the first query? Otherwise it make no sense

Gerald Britton, Pluralsight courses

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Any chance of posting the XML .SQLPLAN file, not this text plan?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

I would say that the fact the script in the CTE runs quickly doesn't mean that it will do the same when inside a CTE, especially where you have scalar functions.

What exactly is the function GetWeekStarting doing? Is it doing a look up to get a single date or just a calculation based on [GEGTH].[Changed Date]

Can you post the definition of this function.

In either case why not have the function Return a data type of DATE rather than casting back to a Date on the outside?

You are also calling the CTE three time, so you are calling the GetWeekStarting function three times for every Row of data.

How many rows of data are you running against?

_________________________________________________________________________
SSC Guide to Posting and Best Practices

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

You may also try FISRT_VALUE(), LAST_VALUE() instead of firstApply, secondApply.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Couple of questions:

Is ID the PK of either of the source tables?

Can you provide a few rows of sample data please?

Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101127 More actions · Answer 6

Please list all indexes on [BuildTable].

Also, shouldn't the SecondApply query have a condition based on FirstApply value, something like:

WHERE [BuildTable].[rownum] > [FirstApply].[rownum]

?

Edit: Added "WHERE" for clarity.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".