Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite
1

My database is extremely large and growing at a rate of ~20m rows/day. I have timestamp data that is important but most of the reporting is based on date ranges and week over week or month over month comparisons. Time is displayed in the result sets occasionally but never used as a criteria. Given this, I'm thinking that I'd save considerable storage space with an index on date alone vs a combined datetime field. I'm not sure if I would also see performance gains in my selects or if there are any disadvantages to splitting into 2 fields.

share|improve this question
1  
I see only pros and no cons - with your requirements - but I'm sure you'll get more thorough answers. – ypercube Jun 27 '12 at 12:38
1  
Agreeing with @ypercube I would push forward and consider adding week and month fields as well. – dezso Jun 27 '12 at 12:47
One disadvantage of splitting is if anyone wants to map the same table in an ORM mapper, and if that mapper doesn't support one or both of those datatypes. – JM Hicks Dec 28 '12 at 0:01
As far as the mention about narrower index width, you wouldn't consider that alone, but also whether dropping the unneeded time component from the index rows would perhaps greatly reduce the total number of unique index rows that need to be stored in the index. E.g., if the index is defined primarily by the timestamp data, and the 20 mil. rows are well enough spread over the time component, then eliminating the time component could reduce the number of index rows per day by a ratio of 1000's or 10's of 1000's to 1. – JM Hicks Dec 28 '12 at 0:18

2 Answers

up vote 12 down vote

For reporting purposes splitting the field out into date and time has some benefits. Some possible benefits you could realise include:

  • You can make a date reference table (much the same as a date dimension in a data warehouse) with your breakdown into weeks, months etc. This can be keyed on the date and used with a join.

  • Analysis by time of day is easier with a separate time field. You can also round the time to an appropriate grain and make a reference table.

  • The index would be slightly narrower although each leaf row still has a (IIRC) 6 byte page reference, so it's not such a great saving overall.

For your application you might get a win from a date reference table (make a clustered PK on date for efficient lookup), which will probably be more efficient than de-normalising the week and month onto your large table.

share|improve this answer
up vote 2 down vote

The index performance should not change.

In a sorted array or tree structure (i.e. the index), asking for "all entries where is equal to " requires a lookup of the first and last entries in the range, same thing as when asking for "all entries where is greater or equal midnight on and smaller than midnight on ".

What might be interesting is an index on MONTH(datetimecol>) and other commonly used expressions in your queries, which will permit using an index scan to find all rows with a matching month, if you want to trade the extra space for this index for elevated performance.

From a storage space POV, I doubt it matters compared to the size of the data tables.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.