March 30, 2009 at 6:45 pm
tfifield (3/30/2009)
I wouldn't use a numbers table on this type of query. I'd rather use a properly indexed calendar type table. I've found that they are very useful for many applications and you don't have to use any sort of calculated dates in a WHERE clause, thus avoiding not being able to use any indexes on date columns.
Todd, that was one of the issues I had with the original query - we weren't able to find an easy way to combine Top x...order by with a sequence of months. In addition the query didn't take advantage of a clustered index on the date field and instead was doing a full scan. However my big epiphany in figuring this out was use of "cross apply". I already knew how I wanted the execution plan to work, I just couldn't find an elegant way to get it into T-SQL.
However the query you provided performs near identical to the one with the #nums table on my machine. For the example I provided, the index field is still being used on the date field for the #temp table - the date calculation is done on the number tally field which has no performance loss or gain. We both have the same idea, just programmed it a little differently. 🙂
Thank you for the help. Same with you Jeff, I didn't notice the 590 records... hopefully QA would've caught that 🙂
March 30, 2009 at 6:48 pm
Also, I just noticed I don't have it in the code above, but there's a clustered index on the #nums table. That might be where the problem came in.
March 30, 2009 at 7:27 pm
Gabriel,
Mine probably could execute faster if I had included the Month Description in the dates index. It probably wouldn't have to do any bookmark lookup then.
I've found that using this type of calendar table makes life a lot easier. You don't have to DATEADD anything. Just subtract 1 or add 1 to the ID column to get the previous/next month and let the start/end dates do all the work for you. You can also put in a description for the period like I did for 'Jan-00' and so forth.
They become invaluable when you're dealing with a company that has the 13 week quarter accounting system. These don't begin and end on any sort of calendar month - ugh! You set the table up with start/end dates for each quarter then all transactions between those dates belong to that quarter.
Calendar tables are especially useful when doing summary reports by period. You join the transactions to the table where the transaction date falls between the start and end of the period and use the period to group by.
Try one out - you'll be sold. I've been meaning to write an article about them.
Todd Fifield
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply