Our application has numerous tables that contain outdated data, some dating as far back as 2010. Despite having indexes defined on these tables, many of the index seeks retrieve old records, which is time-consuming and unnecessary. As a result, we plan to archive or delete the old records, retaining data from the past five years. What are the recommended practices for archiving or removing data from databases? Should we simply move the records to a new table for archiving, and delete them entirely for removal?
Add a comment
|
2 Answers
Indexes do not seek data, the query does. If your query check old data, then it is written incorrectly.
A simple example:
select year, sum(amount) from sales where shop=123 group by year
select year, sum(amount) from sales where shop=123 and year>2015 group by year
The first query will check all data including the old one. Even if shop=123 did not exist in 2014. The second query will immediately exclude old records. Of course, you need to have the year field in the index.
But if you really want to ensure that old data will not be touched, there are several ways to do the cleanup of unneeded data:
Just delete the old data. You will of course lose it completely, but it wont interfere with current data anymore.
Separating work data by view. Just add a set of views which would have
select * from tbl where datediff(year, modifydate, getdate())<5. Ensure you have an index on themodifydatefield. And write your actual queries against these views, not against the real tables. Advantages - very easy to setup, disadvantage - can be confusing (read from one object, update another object).Partitioning - use the partitioning mechanism provided by the server. Advantages - easy and reliable. Old data is still available, but if request requires data from only one partition - it will go to just that partition.
Archiving in a table - partially manual process: Create a separate table in the same database, move (copy-delete) the old data from the work table into archive one, define a view with a
unionof work table and archive tables. Main disadvantage: you would have to have three separate queries for current-old-combined data. Another possible problem - if you decided to change the structure of the work table with current data - do not forget to update the archive table and combining view also. And you, of course, would have to setup a copy-delete procedure to be run regularly (manually or from some scheduler).Two-server solution - one server has just the current data, another has all the data. Requires ETL between them. Requires second connection to read history. The client application have to be smart enough to combine history and work data. But on a plus side - the db structures between work and archive servers can be very different and be as much normalized or denormalized as you need. This approach often expanded into three-server solution: work data (current, highly normalized), history dump (absolutely anything), analytic-reporting data (highly denormalized, partial data which can exclude many fields unneeded in standard reporting).
many of the index seeks retrieve old records, which is time-consuming
Doesn't make a ton of sense to me. Assuming you're not forcing index seeks with query hints, the seeks will be based off the value in the predicates of your queries. If your queries are selecting data from 10+ years ago, you should adjust your queries / predicates to not do that. No need to archive data. - user150011
Agreed, unless you need the space on expensive disks then it's just a matter of tuning your queries. Possibly partitioning may help. - user220697