Ok... let me show you what I mean using your example. Of course, the math-only methods are absolutely the best way to do this but, for a moment, we'll digress to the idea of creating a rowset to solve this problem.
Here's a test harness that uses your rCTE and my Inline Tally Table Structure. Before each test, it drops cache and buffers and then runs each test 5 times just to see if there's any nasty compile time problems (there weren't) and to see what the consistency of the runs is.
--===== carsten.saastamoinen's Recursive CTE ============================================
SELECT CAST('20180301' AS DATE) AS WeekdayDate
SELECT DATEADD(DAY, 1, WeekdayDate)
WHERE WeekdayDate < '20300430'
WHERE DATEPART(dw, WeekdayDate) IN (1,7)
OPTION (MAXRECURSION 0);
SET STATISTICS TIME OFF;
--===== Jeff's Inline Tally Table =======================================================
DECLARE @SDate DATETIME = '20180301'
,@EDate DATETIME = '20300430'
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,DW(D) AS (SELECT TOP (DATEDIFF(dd,@SDate,@EDate)+1)
DATEPART(dw,DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@SDate))
FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j)
SELECT Weekdays = SUM(CASE WHEN D IN (1,7) THEN 0 ELSE 1 END)
,WeekEndDays = SUM(CASE WHEN D IN (1,7) THEN 1 ELSE 0 END)
Here are the results from SQL Profiler (I still like it) on my little i5 laptop... CPU is in milliseconds and duration is in micro seconds.
The code I wrote is anywhere from 4 to 20 times faster than your rCTE and it settles out to usually 20 times faster. Hmmmm... seems to me that 20 times slower was a big concern of yours. :Whistling:
It's also interesting to note that, like I pointed out in the article I sited, your method settles out at using 39,997 reads. That's equivalent to 312 Megabytes of memory IO... for something that was supposed to be all CPU and returns a single value??? :blink:
Speaking of CPU... that 31ms you're so proud of is an eternity for this especially after the code has settled. The Tally method that you've been badmouthing takes less than 1/2 ms (if it was more than that, it would show as 1 ms instead of 0 ms). Being conservative, that's 61 times less CPU than your rCTE.
You also brought up the subject of newbies learning different ways. I absolutely agree... they need to learn the recursive CTE method and compare it to other methods so they know how bad it really is even on small stuff. :sick:
Now, understanding that I've already said that the WHERE IN (1,7) thing isn't the right way to do this and neither is any kind of rowset method for this particular problem, you were saying something about using the exact same argument for my bad code? 😀 Please drive through.
p.s. Do you like pork chops?
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)