Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Billpl
Yak Posting Veteran
71 Posts |
 Posted - 2006-10-31 : 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 AdvanceBill |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-31 : 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=35296I 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 - soWHERE DATEDIFF(Day, MyDateColumn, GetDate()) > 2will 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)
7020 Posts |
Posted - 2006-10-31 : 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=61519CODO ERGO SUM |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-10-31 : 20:12:10
|
| http://sql-server-performance.com/fk_datetime.aspMadhivananFailing to plan is Planning to fail |
|
|
|
Billpl
Yak Posting Veteran
71 Posts |
Posted - 2006-11-04 : 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 stuffthanks againBillYou know there's a lot of great stuff dug deep into this forum. It would make for a great Wiki |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-11-04 : 04:09:08
|
>>so I'm going to "simply" copy your stuffSo you have simple mind?  MadhivananFailing to plan is Planning to fail |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-04 : 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 stuffthanks againBillYou 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 Linkshttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762CODO ERGO SUM |
|
|
|
|
|
|
|
|
Dates in Queries