优化记录历史数据库的最佳方法 [英] Best approach to optimize a record history database

问题描述

我有一个保存记录历史的数据库。对于记录的每次更新，系统将通过将状态列设置为0来停用上一条记录（及其所有子项）。

I have a database that keeps record history. For each update to a record, the system will "deactivate" the previous record (along with all it's children), by setting the "Status" column to "0".

现在它不是一个问题...但最终这个系统将有很多记录，历史比速度更重要的是现在。但是插入的记录越多，搜索就越慢。

Now it's not a problem yet...but eventually this system is going to have a lot of records, and history is more important than speed right now. But the more records inserted, the slower searches become.

归档记录的最佳方法是什么？我建议创建一个克隆的归档数据库来保存数据。我也想过将所有以前的记录存储到一个xml文件中，以后如果我们需要挖掘存档记录，可以读取/加载。

What is the best approach to archive the records? I've had suggestions to create a cloned archive database to hold the data. I've also had the idea to storing all previous records into a xml file, that can be read / loaded later if we need to dig up archived records.

推荐答案

如果DBMS支持，您可以创建一个单独的分区，只包含活动记录。您还可以向状态添加索引，以便select ... from tbl where status = 1不会非常慢。

You could create a separate partition containing only the active record if your DBMS supports it. You can also add an index to Status so that the select ... from tbl where status=1 isn't incredibly slow.

http://msdn.microsoft.com/en-us/library/ms187802.aspx

这篇关于优化记录历史数据库的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！