Faster DB access
Posted on 2011-03-01
Have a table that has an identiy key starting at 1.
During processing we delete entries from this table and create
a file which is bulk inserted into this table at the end of processing.
This file contains the current posting date data.
Because records are added at the end of the table, the most current data
is posted at the end of the database. Clients use mostly the recent data
while rarely going back to search older records.
The more records we add the slower the response time as far
as accesing the rows.
Question: As far as performance is concerned wouldn't it be better to delete
the identity key at the end of processing and sort the table by posting date desc
so that the current date is on top and then apply the identity key.
This way the records with the current posting date will have
an identity key starting at 1.
Also since records are deleted duiring procesing there will be gaps
in the identity key numbers. Does this cause perfomance issues?