Quite often I have a need to generate reports that show the days of a month with counts, sums, averages, etc by day. I also get a lot of requests to show each of the days of the month regardless of the fact there might be any data for that day.
I have gone down the route of creating a table, putting in +/- 100 years of dates or whatever and then done a join but I tried some code from this article that creates a CTE dates table for the values I specify. I have shown the code here...
This uses a recursion to generate but even when I specified a from date of '20000101' and an end date of '20100219' it ran in under a second.
Would this be a better method than holding large tables of dates on the server? Pros/cons please guys.
asked Feb 19, 2010 at 01:10 PM in Default
I would never use recursion for such a thing... let's start to look at why... (I've never posted code on this forum before so bear with me if I mess up the formatting)...
The profiler output on those 3 runs pretty much tells the story of "pros" and "cons". But there's more to it than just shear speed and resource effeciency. Let's think about this for a minute... these 3 examples are only for 365 rows. What if someone decides they want to put the Tally-on-the-fly code into a user defined function and then use it to calculate against a million row table? They find some "really cool" code like a recursive CTE and put it into their function (whatever it does). The recursive CTE is at least 38 times slower than either of the other two methods and on a million rows and in a function, that's going to make for a very large difference in performance.
My feeling is that there are simply no "pros" to using a recursive CTE for this type of thing even on very small numbers of rows because some poor slob might find the code and use it for something bigger. Yes, there are pros to the cross joined CTE method (second method) but even the Calendar table method just absolutely blows the doors off the Recursive CTE method.
Build a temporary calendar table on the fly using a CTE if you want... the pros to doing that are controllable size and you don't have to fight a DBA who may be "disk stingy". Just don't ever use recursion to do it.
A permanent table means you can join it in there, without calculations to build the dates. I would think there is some performance hit for his.
The other pro that I've seen is that you can "mark" days as business days. It's one thing to know the weekdays v weekends, but knowing accounting periods, holidays, etc. are easy to include/exclude in a join if you have flags.
In terms of cons, you have one table, you have possible maintenance, and you use space.
answered Feb 19, 2010 at 01:36 PM
Steve Jones - Editor ♦♦
Permanent calendar tables :
On-the fly method :
answered Feb 19, 2010 at 01:41 PM
Kev Riley ♦♦
It depends! Personally I tend to use an inline udf to build date ranges - because generally the reports I have written don't limit themselves to days - so I would pass in as parameters both the increment and step type - so if I wanted a result set for a week in 15 minute intervals, no problem. If I wanted a weekly set, again no problem.
However I would look at lynn pettis' article about dynamic tally tables as that uses a much more efficient method of table building using cte's - recursing once per result row is probably not the way to go...
answered Feb 19, 2010 at 03:39 PM
Matt Whitfield ♦♦