YTD Alternatives part 2

Yesterday, I wrote about the use of a table function as an alternative to a date transformation table in the calculation of YTD. Mischievously, I used filter conditions to restrict my selection to a single product and store over an April until now window. The results I captured show little difference between the three techniques I used retrieve the data for the YTD calculation. And of course to be scrupulously unfair I did not use David’s technique at all.
Well to redress things I have now looked at a similar query to David’s; that is total sales all stores all products over a 1 month period (YDT with an input date of 1-may-2006, my year started on April 1)

My date transform table has a lot more complexity than David’s example but the main similarity is that for each possible date I have a list of all dates in that year less than or equal to that date. The table is suitably indexed.

To add a bit more fun I also turned query rewrite on to see how it affected things.

The tests are documented here, but in summary:

Transformation table

Query time with no rewrite 24 seconds
Query time with rewrite 8.6 seconds
But, for the table-function approach
Query time with rewrite disabled 10 minutes
And rewrite did not happen!

So David’s approach seems vindicated, which is a good job as I have been using it for years!