bendlam
3/20/2007 8:01:00 PM
I recently re-wrote a simple sp that given a start and end date it
will spit out all the days that are not weekend days or holidays as
specified in a holiday table.
This function originally did a WHILE loop incrementing the days then
checking each individual day if it was a holiday or weekend, then
inserted it into a table variable, then increment the day and continue
looping.
i.e.
WHILE @day < @endDate
IF NotHoliday(@day)
INSERT INTO @tempTable
DATEADD(d, 1, @day)
I changed this to now return all days using a CTE and then doing a
DELETE to remove the holidays
WITH CTE(Day)
AS
(
SELECT @startDate
UNION ALL
SELECT DATEADD(d, 1, Day)
FROM CTE
WHERE Day < @endDate
)
INSERT INTO @tempTable
SELECT * FROM CTE
DELETE @tempTable
WHERE NotHoliday(Day) = False
So the CTE outperforms the old method considerbly but the BIG QUESTION
is WHY?
Is it because the recursive nature of CTE's are done in memory? What
is the main difference when using CTE's over Loops that make it that
much more efficient? It seems from the profiler it the CTE does more
reads but the duration is much longer. I know reads are a better
indication of query performance but the difference in duration is too
dramatic to ignore. Can somebody explain in such scenarios where a
rewrite of an sp to use recursive CTE's would perform better than a
traditional looping method?
Thank you