Integer representation of a date

Question

In the recent project, we had an issue with the performance of few queries that relied heavily on ordering the results by datetime field (MSSQL 2008 database).

When we executed the queries with ORDER BY RecordDate DESC (or ASC) the queries executed 10x slower than without that. Ordering by any other field didn't produce such slow results.

We tried all the indexing options, used the tuning wizard, nothing really made any difference.

One of the suggested solutions was converting the datetime field to the integer field representing the number of seconds or miliseconds in that datetime field. It would be calculated by a simple algorithm, something like "get me the number of seconds from RecordDate to 1980-01-01". This value would be stored at insertion, and the all the sorting would be done on the integer field, and not on the datetime field.

We never tried it, but I'm curious what do you guys think?

Answer 1

Are the inserts coming from .Net Code...

You could store the DateTime.Ticks value in a bigint column on the DB and index on that.

In terms of updating your existing Database, it should be relatively trivial to write a CLR Function for converting existing DateTimes to TickCount along the lines of

ALTER TABLE dbo.MyTable ADD TickCount BigInt Null

Update dbo.MyTable Set TickCount = CLRFunction(DateTimeColumn)

It definitely feasible and would dramatically improve your sorting abilility

Answer 2

I always store dates as ints, using the standardised unix timestamp as most languages I program in use that as a default date-time representation. Obviously, this makes sorting on a date much more efficient.

So, yes, I recommend it :)

Answer 3

Eoin, yes, it's coming from the .Net.

What you're suggestion is just more precision and simpler "calculation" of that integer.

I'm curious about the idea altogether.

Answer 4

Aren't datetimes stored as a number already?

Answer 5

I think basically that's how the SQL datetime datatype is stored behind the scenes in SQL Server, so I'd be surprised about these results.

Can you replicate the slowness in Northwinds or Pubs - if so it might be worth a call to MS as it shouldn't be 10x slower. If not then there maybe something odd about your table.

If you are using SQL 2008 and you only need to store dates (not the time portion) you could try using the new date datatype. This has less precision and so should be quicker to sort.

Answer 6

The only sensible way to store dates is as Julian days - unix timestamps are way to short in scope.

By sensible I mean really in the code - it's generally (but not always) better to store dates in the database as datetime.

The database problem that you are experiencing sounds like a different problem. I doubt that changing the field type is going to make a massive difference.

It is hard to be specific without seeing detailed information such as the queries, the amount of records etc, but general advice would be to restructure the order and method of the query to reduce the number of records being ordered - as that can impact massively on performance.

Answer 7

Do you actually need the DateTime or more specifically, the 'time' part? If not, I would investigate storing the date either as the integer or string representation of an ISO date format (YYYYMMDD) and see if this gives you the require performance boost. Storing ticks/time_t values etc would give you the ability to store the time as well, but I wouldn't really bother with this unless you really need the time component as well. Plus, the added value of storing a humanly readable date is that it is somewhat easier to debug data-related problems simply because you can read and understand the data your program in operating on.

Answer 8

I don't really understand why indexing doesn't help, if SQL behind the covers stores the date as integer representation.

Sorting by the ID columns produces excellent results, or by any other indexed field.

Answer 9

I believe the datetime is physically stored as float so the improvement would be the same as when converting float to INT.

I would rather use indexes as that is what they are designed for, and the datatime is designed for storing dates with times. There is a set of functions associated with the datetime so if you decide to use custom storage type you will need to take care of that yourself.

Answer 10

So, whats the verdict?

Should I create another bigint field just for sorting purposes, or experiment more with indexing (in what direction)?

Answer 11

I vote indexing. As I said in the comments above, your dates are stored as two int's behind the scenes anyway (sql 2000 anyway). I can't see this making a difference. Hard to say what the real problem is w/o more info, but my gut feeling is that this isn't the problem. If you have a dev environemnt (and you should :) ), try making the int field there and running the raw queries. It shouldn't be difficult to do, and you'll have conclusive results on that idea.

Answer 12

Is your RecordDate one of the fields in the WHERE clause? Also, is RecordDate your only ORDER BY criteria? Thirdly, is your Query a multi-table join or a single table query? If you are not SELECTING on RecordDate, and using it as the ORDER BY criteria, this could be the cause of the performance issue, as the indexes would not really contribute to the sort in this case. The indexes would try to solve the join issues, and then the sort would happen afterwards.

If this is the case, then changing the data-type of your RecordDate may not help you much, as you are still applying a sort on a recordset after the fact.

Answer 13

I've seen a BI database where the dates are stored as an integer in YYYMMDD format. A separate table is used to relate these ints to the equivalent datetime, formatted string, year number, quarter number, month number, day of week, holiday status, etc. All you have to do is join to that table to get anything date related that you need. Very handy.

Answer 14

I would advise you to use a Julian date as used in Excel (link text). All financial applications are using this representation to gain performance and it provides a relatively good range of values.

Answer 15

We actually used this technique in our application, not for performace reasons but to avoid the complications related date time conversion in different culture formats. If the date is represented as int, then we dont have bother about the culture setting in Database/local machine etc.

A specific utility class in the application can convert the int to the user specifc date time zone/format.