Turning date to integer for performance? (1 Viewer)

[Fuga]

Hi,

I´m going to include a real monster of a table into an already large database. The table will hold at least 10 million rows, provided it fits with access limitations.

One of the fields is a date field, and I was thinking of turning it into an integer to save space and gain performance.

The datefield would change from 2009-08-27 18:00:00 to 90827180000.

Is anything actually won by this, or is it just a stupid idea?

Thanks!

Fuga.

 

[boblarson]

Dates are already stored as doubles so I don't see the benefit myself, unless there is some inherent benefit of integer over double.

 

[Banana]

Two things.

1) Time component of the date are stored as fractional part, so you would lose that information. IF you only want dates, that'll be ok.

2) While a long integer is 4 bytes, double is 8 bytes, so you get half the storage requirement. However you are trading it off for additional computation in casting the integer back into date and vice versa between the display and user's inputs. Traditionally, disk I/O is the bottleneck, not CPU, but this is something to be aware of. Also recall that even though there is some computation going on with dates' storage & display, it's done at a lower level (e.g. compiled C++ code, probably) whereas we have to use VBA to direct the computation, and VBA is interpreted so it's going to be slower doing so. I'd bet it could be alleviated somehow if it was primarily done via queries using native functions, though.

It may be good to experiment. I'd love to hear how it goes.

 

[Banana]

Addendum:

As an interesting point, the range of dates is 1/1/100 to 12/31/9999. Converted to integer, that would be -657435 to 2958465. Any number outside this bound will cause a overflow error when we attempt to convert the integer into date.

It's unlikely that we'll have dates outside this bound, but wanted to pointed out that it's not a exact equivalence.