| Author |
 Topic  |
|
|
Billpl
Yak Posting Veteran
USA
68 Posts |
 Posted - 10/31/2006 : 14:53:34
|
Hi All,
I've been working on this one project for quite a while and it's about a optimized as it can get, but it's never been run under really heavy loads. One major concern is that about 90% of the output reports require date manipulation of some sort. Tons of Year(), DatePart(), DateDiff() functions. However, one set of queries works off of a "Month Number" field which is simply the month count (number) starting from the year 1990. It's really clean and simple. Just predetermine the "Month Numbers" and fire away at the various ranges. The queries become straight "Int to Int" comparisons. It's so simple and clean I'm thinking of converting the whole system over, but only if it's really worthwhile.
Question being, would this have a significant enough performance gain or does SQL's built in handling of dates already adequate to manage all these date functions?
Thanks in Advance
Bill |
|
|
Kristen
Test
United Kingdom
19300 Posts |
Posted - 10/31/2006 : 17:34:48
|
"does SQL's built in handling of dates already adequate to manage all these date functions?"
I see lots of date comparison that uses CONVERT via VARCHAR to get rid of the Time component, or extract the month, or somesuch. Some timings here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35296
I reckon you need to trial some bulk processing of date functions and see how fast/slow they are - compared to INT comparisons.
One issue is that functions will generally prevent use of indexes - so
WHERE DATEDIFF(Day, MyDateColumn, GetDate()) > 2
will not use any index on MyDateColumn whereas:
WHERE MyDateColumn >= DATEADD(Day, -2, GetDate())
will (assuming sufficient granularity of the index etc.)
Kristen |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6257 Posts |
Posted - 10/31/2006 : 19:43:28
|
You nay want to use a pre-built date dimension table for what you are talking about. The table produced by the function in the link below has sequential integer columns for years, quarters, months, weeks, and days, as well as many other ways to slice and dice dates. You can use this function to load a date table that you can join to any date column.
Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 10/31/2006 19:44:40 |
|
|
|
madhivanan
Premature Yak Congratulator
India
20440 Posts |
|
|
Billpl
Yak Posting Veteran
USA
68 Posts |
Posted - 11/04/2006 : 02:53:24
|
Thanks much guys.
According to the article from madhivavan: "The first 4-byte being the elapsed number days since SQL Server's base date of 19000101." So I'm thinking that SQL has date about a optimized as it gets. No need to tear everything apart just quite yet but.......
Michael, you managed to jump one step ahead of me. That's what I was going to lead up to. We already use some simple table joins such as a table with rows 1-12 to generate monthly sub-totals as apposed to group by datepart( mm, myDate ). I was thinking the of the exact same thing as your deal, (only about a tenth as elaborate).
As you stated "simple minds copy ideas, great minds steal them" ... so I'm going to "simply" copy your stuff
thanks again
Bill
You know there's a lot of great stuff dug deep into this forum. It would make for a great Wiki |
|
|
|
madhivanan
Premature Yak Congratulator
India
20440 Posts |
Posted - 11/04/2006 : 04:09:08
|
>>so I'm going to "simply" copy your stuff
So you have simple mind? 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6257 Posts |
Posted - 11/04/2006 : 11:45:41
|
quote:
Originally posted by Billpl
Thanks much guys.
According to the article from madhivavan: "The first 4-byte being the elapsed number days since SQL Server's base date of 19000101." So I'm thinking that SQL has date about a optimized as it gets. No need to tear everything apart just quite yet but.......
Michael, you managed to jump one step ahead of me. That's what I was going to lead up to. We already use some simple table joins such as a table with rows 1-12 to generate monthly sub-totals as apposed to group by datepart( mm, myDate ). I was thinking the of the exact same thing as your deal, (only about a tenth as elaborate).
As you stated "simple minds copy ideas, great minds steal them" ... so I'm going to "simply" copy your stuff
thanks again
Bill
You know there's a lot of great stuff dug deep into this forum. It would make for a great Wiki
If you are doing a lot of work with dates, there is a good chance you will find something you can use in the link below. It has links to many datetime related functions that I and others have written, and has links to many other datetime resources on SQLTeam and other sites.
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
CODO ERGO SUM |
|
|
| |
Topic |
|
Dates in Queries
