There were these transaction tables
(only a few) in my database for trade data (trade, trade legs etc) growing
bigger and bigger over the years. To a certain point, it SLOWED down noticeable
on updating any trade data. Although it was easy to blame the database server
was not powerful enough, there are better designs to avoid this.
How can I keep up the read/write
performance for these ever-growing tables? There are many ways from
database-level technologies, such as partitioning tables. But I want to a
simpler, tangible, easy-to-maintain, and all-in-control design.
I figure that an easy way to boost
the performance is to create one archive table and one active table for each
growing table. I created the jobs to archive the records over night. Any
records older than one day will be pushed over to archive table. Now Read
operations are against a view which union the active tables and the archive
tables. CRUD operations are on active table. It worked out really well. Atlas,
until I needed to update the archived table.
UPDATE, hmm....can it be just
read-only? No. So, how to solve this problem? Since insert is much faster than
update, can we change the UPDATE operations against archived data into INSERT
operations? Yes but before doing so, version mechanism has to be introduced for
the row records. In my solution, I simply introduced an IDENTITY (SQL Server)
column. Now I can have the same record with multiple reversions. For the update
and delete actions, I choose to archive the records using triggers. For the
insert actions, I choose to update the unique record ID with PK if it is the
first revision. The last catchy point I have to consider is that the archiving
job might break the integrity of the active tables. In the case when I insert
to active tables, I need to clean up the active tables before the insert
action. In the end, this solution worked out pretty
well.
