I have a simple stored procedure where I have multiple WITH clauses.
Some code is this:
WITH cteRowNums AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY fcmp.EmpUserID, fcmp.WorkCellID, fcmp.ActivityTS) AS RowNumber,
fcmp.ActivityTS,
fcmp.ArtifactTypeID,
fcmp.ServerDateID,
fcmp.ServerHourID,
fcmp.EmpUserID,
fcmp.WorkCellID
FROM dbo.FactCassetteMarkingProcessing fcmp
WHERE ServerDateID >= '2007-01-01'
),
-- Make an attempt at identifying what each user did in their "session" by self-joining
cteJoinCurAndNext AS
(
SELECT
[Current Row].ArtifactTypeID,
[Current Row].ServerDateID,
[Current Row].ServerHourID,
[Current Row].EmpUserID,
[Current Row].WorkCellID
FROM cteRowNums [Current Row]
LEFT OUTER JOIN cteRowNums [Next Row] ON [Next Row].RowNumber = [Current Row].RowNumber + 1
WHERE [Current Row].ArtifactTypeID = 2
OR ([Current Row].ArtifactTypeID = 1 AND [Next Row].ArtifactTypeID = 2
AND [Current Row].EmpUserID = [Next Row].EmpUserID
AND [Current Row].WorkCellID = [Next Row].WorkCellID)
),
-- Do some aggregations
cteAggregates AS
(
SELECT
EmpUserID,
ServerDateID,
ServerHourID,
COUNT(NULLIF(ArtifactTypeID, 2)) AS SpecimensProcessedCount,
COUNT(NULLIF(ArtifactTypeID, 1)) AS BlocksProcessedCount
FROM cteJoinCurAndNext
GROUP BY EmpUserID, ServerDateID, ServerHourID
)
SELECT * FROM cteAggregates
The problem is that this takes a lot of time for aprox 2,5 milions of rows. I canceled the execution query at 40 minutes.
If I change this piece of code with temporary table, the execution is much, much faster. Is there any method to obtain almost the same performance using just CTEs ?