CODEWITHSUNDEEP
sql-serverperformance
Royi Namir
Royi Namir

Reputation: 148534

Best performance approach to history mechanism?

enter image description here

We are going to create History Mechanism for our changes in DB (DART in pic) via Triggers.

we have 600 tables.

Each record that will be changed - the trigger will insert the deleted one into XXX.

regarding to the XXX :

option 1 : clone each table in "Dart" DB and each table now will have a "sister table"

e.g. : Table1 will have Table1_History

problems :

option 2 : make a new DB (DART_2005 in pic) and the history tables will be there

option 3 : use linked server which stores the Db which will contain the history tables.

question :

1) which option gives the best performance ( I guess 3 is not - but is it 1 or 2 or same ?)

2) Does option 2 is acting like "linked server" ( in queries we will need to select from both DB's...)

3) What is the best practice approach ?

Upvotes: 2

Views: 207

Answers (2)

Filip De Vos
Filip De Vos

Reputation: 11908

All three approaches are viable and have similar performance based on your network speed, but each one will cause you a lot of headaches on a system with many concurrent users.

Since your will be inserting/updating multiple tables in one transaction with a very different access pattern (source table is random, history table is sequential) you will end up with blocking and or deadlocks.

If the existing table schema can not be changed

If you want to have a history system in place driven by your database ideally you will queue your history updates to prevent blocking problems.

  • Fire a trigger on update of your table
  • The trigger will submit a message containing the information from the inserted/deleted tables to a SQL Server Service Broker Queue
  • An activation stored procedure can pull the information from the queue and write it to the appropriate history table
  • On failure, a new message is sent to an "error queue" where a retry mechanism can re-submit to the original queue (make sure to include a retry counter in the message)

This way your history updates will be non-blocking and can not get lost.

Note: when working with SQL Server Service broker, make sure you completely understand the "Poison message" concept.

If the existing table schema can be changed

When this is an option, I recommend working with a "Record versioning" system where every update will create a new record & your application will correctly query the most recent version of the data. To ensure proper performance the table can be partitioned to the keep the most recent version of the data in a partition and the older versions in an archive partition. (I usually have a field end_date or expiration_date which is set to 9999/12/31 for the currently valid record.)

This approach of course requires considerable code changes in your data model and the existing application which might be not very cost effective.

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29629

1 and 2 will have similar performance; option 3 might be faster, if you are currently limited by some resource on the database server (e.g. disk IO), and you have a very fast network available to you.

Option 1 will lead to longer back-up times for your DART database - this may be a concern.

In general, I believe that if your application domain needs the concept of "history", you should build it in as a first-class feature. There are several approaches to this - check out the links in question How to create a point in time architecture in MySQL.

Again, in general, I dislike the use of triggers for this kind of requirement. Your trigger either has to be very simple - in which case it's not always easy to use the data it creates in your history table - or it has to be smart, in which case your trigger does a lot of work, which may make evolving your database schema harder in future.

Upvotes: 1

Related Questions