CODEWITHSUNDEEP
sqldateindexing
Tankske
Tankske

Reputation: 95

Is converting a date to a number more performant in queries?

I want to improve performance on querying a subset based on date or datetime.

I wonder if a query would have better performance if I would create a second column next to the date-typed or datetime-typed column storing the date as a number.

An int (4 bytes long) should be sufficient and the format would be YYYYMMDD. I'm assuming indexing numbers is more efficient, but am not sure as DB technology probably optimises the types for indexing.

I understand date/datetime-types are stored as numbers opposed to a reference date. So the proposed alternative would avoid the overhead of mapping to a reference date.

Upvotes: 0

Views: 253

Answers (1)

cliffordheath
cliffordheath

Reputation: 2606

Use the built-in DATE or DATETIME or TIMESTAMP or whatever thing your DMBS supports. It will almost certainly be using an integer or a series of integers internally (referenced to some epoch), but basically you don't need to and shouldn't care. Any performance difference will be microscopic compared to problems you might cause by issues in your design, and by computing a string from a date you risk making things worse or introducing errors.

If you're worried about performance, focus especially on appropriate indexing and use of clustering if necessary.

Upvotes: 1

Related Questions