Take the 2-minute tour ×

In books I read that it is a real performance booster if you split the time into separate column. e.g day,month,year and so on...

  1. Do databases already have some smart approch for handling indicies over time columns, so that splitting the time and adding millions of index variantes is obsolete?

  2. Any experiance in performance difference?

A possible query would be sales on monday morning between 13:00-14:00 o'clock.

share|improve this question

4 Answers 4

up vote 2 down vote accepted

Take a look at this SO question/answer.

share|improve this answer

The specific scenario you outline (13:00-14:00 every Monday) cannot be properly served by normal indexes against datetime data.

It would require a lot of dissecting of the datetime data into the day of week + the time portion to get at that info. For this scenario, breaking it into a column for day of week and another for time of day (hour) will work a lot better and can be indexed separately or as a composite (across both).

Performance is very different - instead of looking at 1/168th of the data (theoretical average) or more realistically about 1/50th of the data (working hours) using indexes on day-of-week + time-of-day, the query would otherwise have to run 2 transformations (to get day-of-week + time-of-day components) then run that through a filter.

share|improve this answer

It is useful, in many star schemas, to have a time dimension. In that dimension table, it can be useful to have the day of the week, the month, and so on explicitly laid out. Many of these attributes can be accessed by built in functions in your dialect of SQL. And it takes less disk I/O if you use the functions than if you materialize this data. But it makes the art of composing reports over given time slices so much easier if calendar functions just look like data.

Where this can be really helpful is is your enterprise has a peculiar "company canlendar" where dates can belong to units called "fiscal quarters" that are not easliy mapped into day-month-year. If you put all the calendar quirks into a single program that generates the time dimension table, it can make the rest of your warehouse code a whole lot cleaner.

As with any dimension table, it's very important to set the granularity right. If you only want one row per day, you can store ten years worth of dates with just over 3,650 rows, a tiny table by today's standards. In some cases, a "shift" (an 8 hour period) turns out to be the right granularity. It depends on the uses of the data.

No matter which way you go, be prepared for your data to undergo a "metamorphosis" when you set up the warehouse, and be prepared to face a "trial" when faced with unexpected requirements.

share|improve this answer

A function-based index is one possible option. An indexed view is another.

Just creating a new attribute isn't the thing that improves performance. Any performance difference is due to underlying changes to the way the data is stored and indexed. So it's misleading and very over-simplistic to say that creating separate date and time columns is a performance booster. However, creating a separate time column may well be a good idea for other reasons, for example: clarity, simplifying query logic or taking best advantage of DBMS date/time types and other features.

share|improve this answer

Your Answer


