If you have an index on the table:
DECLARE @Planning_Proposal_Temp TABLE (object varchar(10), dateFrom datetime2, dateTo datetime2
INDEX IX_Planning_Proposal_Temp CLUSTERED(object, dateFrom, dateTo));
Or better still, use a temporary table with an index on it:
CREATE TABLE #Planning_Proposal_Temp (object char(1), dateFrom datetime2, dateTo datetime2)
CREATE INDEX IX_#Planning_Proposal_Temp_1 ON #Planning_Proposal_Temp(object,DateFrom) INCLUDE (dateTo)
Then use the following query (which is a modified version of my previous query but with an additional filter to get only the first row from the recursive expression) instead, it will perform much faster.
;WITH rCTE AS
(
SELECT a.object,
a.dateFrom,
a.dateTo,
CONVERT(bigint,1) RowNum
FROM #Planning_Proposal_Temp a
WHERE a.dateFrom = (SELECT MIN(b.dateFrom)
FROM #Planning_Proposal_Temp b
WHERE b.object = a.object)
UNION ALL
SELECT *
FROM (SELECT a.object,
a.dateFrom,
a.dateTo,
ROW_NUMBER() OVER (ORDER BY a.DateFrom) RowNum
FROM #Planning_Proposal_Temp a
INNER JOIN rCTE r
ON r.dateTo <= a.dateFrom
AND r.object = a.object
AND r.RowNum = 1
) x
WHERE x.RowNum = 1
)
INSERT INTO @Planning_Proposal_Temp_Grouped
(
object,
dateFrom,
dateTo
)
SELECT x.object,
x.dateFrom,
x.dateTo
FROM rCTE x
WHERE x.RowNum = 1
This will perform seeks on the table so might be faster than your original cursor query.
November 9, 2019 at 8:55 pm
It's much better, perf are really near the cursor, it seems to be a little bit much better. Thanks !
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply