Tuesday, November 4, 2008

Triggers in SaaS accounting solution Exact Online

In Conor Cunningham's blog on SQL Server he asks companies who has experiences with Stored Procedures and Triggers. Therefor I want to share some of our experiences with the way we have implemented triggers. My company Exact Software has builded Exact Online which is a Software as a Service accounting solution. Currently this solution, with more than 6000 subscriptions, is available in the Netherlands and Belgium. Exact Online is builded on the latest .NET Framework. All data is stored in multiple SQL 2005 databases. We make use of triggers to update the balance table in which we store the Balance and Profit & Loss sheet. This balance table is based on the financial transactions created by the users.

Why did we choose to use triggers to update the balance table?
Advantages:
  1. Consistency between transaction table and balance table.
  2. High performance due to the efficiency of SQL Server triggers
  3. Apart from the triggers themselves, no additional code is needed to keep the balance table up to date.

Disadvantages:
  1. Chance of locking errors when 2 transactions update different transaction records, which related to the same balance record.

Implementation issues:
  1. Define 3 triggers: One for insert, one for update and one for delete. One trigger for update, insert and delete resulted in our solution to locking errors.
  2. Start every trigger with the statement IF @@ROWCOUNT = 0 RETURN. This is done for performance reasons, if no inserts, updates or deletes took place at all, do not do anything.
  3. To limit locking situations:
    1) Keep database transactions as short as possible.
    2) Do not delete the balance record if all underlying transactions are deleted. In most situations this balance record will be used when new transactions are created.
  4. In the past we have used indexed views, however because of performance reasons we moved to the trigger solution.
We are using the triggers, in the Exact Online solution, on the balance table for more than 3 years. Till today  we are very enthousiastics about the performance and consistency of this implementation.  According the statistics of SQL 2005 the triggers are fired 7 times per second on every database.

2 comments:

Ronald Voets said...

Andre,
good posting. To mitigate the risk of locking during update triggers (where records in your balances table get updated), you could

1. consider that the update trigger is inserting records rather then updating existing ones. Then build a mechanism (periodic, i.e. once a day during the night) that compresses all the inserted records in a way that serves the business purpose. For example, if you only need to know what the balance is on a general ledger for a certain period/year combination, you could compress all the inserted records up to that level.

2. another way to prevent locking is instead of having a delete trigger delete existing balance records, you could again just insert records where the inserted record offsets the original record. Then run a periodic process during off-peak times that compresses the records in your balance table.

Ronald

Anonymous said...

What isolation level are you using?