dwain.c (10/1/2012)
ChrisM@Work (9/28/2012)
dwain.c (9/27/2012)
Phil,I think this is a pretty snappy query too.
<<snip>>
Chris - I like what you did with the CROSS APPLY and I was able to do that in mine (to eliminate the Tally CTE) and it sped up quite dramatically.
Thanks Dwain. It took a while to figure out.
Why are you using DENSE_RANK instead of ROW_NUMBER?
Because my first test harness used a PK which excluded the startdate - it was a much more comprehensive test with different types of overlap.
dwain.c (9/30/2012)
<<snip>>
I was able to adapt Mr. Ben-Gan's approach to this problem:
<<snip>>
Shows that I must have done something right because this solution seems to win out:
------ bteraberry's query
SQL Server Execution Times:
CPU time = 15834 ms, elapsed time = 10126 ms.
------ Mark's query
SQL Server Execution Times:
CPU time = 7051 ms, elapsed time = 4544 ms.
------ Dwain's query
SQL Server Execution Times:
CPU time = 17207 ms, elapsed time = 22379 ms.
------ IBG's query (built by Dwain.C for this case)
SQL Server Execution Times:
CPU time = 718 ms, elapsed time = 756 ms.
------ ChrisM's query
SQL Server Execution Times:
CPU time = 11169 ms, elapsed time = 16211 ms.
------ bterraberry's query (2)
SQL Server Execution Times:
CPU time = 999 ms, elapsed time = 1019 ms.
Not for long ?
------ bteraberry's query
SQL Server Execution Times:
CPU time = 14914 ms, elapsed time = 11965 ms.
------ Mark's query
SQL Server Execution Times:
CPU time = 6739 ms, elapsed time = 5010 ms.
------ Dwain's query
SQL Server Execution Times:
CPU time = 17534 ms, elapsed time = 23217 ms.
------ IBG's query (built by Dwain.C for this case)
SQL Server Execution Times:
CPU time = 936 ms, elapsed time = 1005 ms.
------ IBG's query (built by ChrisM for this case)
SQL Server Execution Times:
CPU time = 952 ms, elapsed time = 949 ms.
------ ChrisM's query
SQL Server Execution Times:
CPU time = 11903 ms, elapsed time = 13277 ms.
------ bterraberry's query (2)
SQL Server Execution Times:
CPU time = 1326 ms, elapsed time = 1333 ms.
It uses a faster fish:
;WITH CTE AS (
SELECT HotelID, RoomTypeID, ts=StartDate, Type = 1
,e = NULL
,s = (2*ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY StartDate))-1
FROM #RoomDates
UNION ALL
SELECT HotelID, RoomTypeID, ts=EndDate, Type = -1
,e = (2*ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY EndDate))
,s = NULL
FROM #RoomDates
)
SELECT @HotelID=HotelID, @RoomTypeID=RoomTypeID, @SD = MIN(ts), @ED = MAX(ts)
FROM (
SELECT HotelID, RoomTypeID, ts,
Snapper = (ROW_NUMBER() OVER(ORDER BY HotelID, RoomTypeID, ts)+1)/2
FROM (
SELECT HotelID, RoomTypeID, ts, s, e,
se = ROW_NUMBER() OVER (PARTITION BY HotelID, RoomTypeID ORDER BY ts, Type DESC)
FROM CTE
) c1
WHERE se IN (e,s)
) C3
GROUP BY HotelID, RoomTypeID, Snapper
ORDER BY HotelID, RoomTypeID, Snapper
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