Gent (ge***********@trustasc.com) writes:
am using FOR UPDATE triggers to audit a table that has 67 fields. My
problem is that this slows down the system significantly. I have
narrowed down the problem to the size (Lines of code) that need to be
compiled after the trigger has been fired. There is about 67 IF
Update(fieldName) inside the trigger and a not very complex select
statement inside the if followed by an insert to the audit table. When
I leave only a few IF-s in the trigger and comment the rest of the
code performance increased dramatically. It seems like it is checking
every single UPdate() statement. Assuming that this was slowing down
due to doing a select for every update i tried to do to seperate
selects in the beginning from Deleted and Inserted and assigning
columns name to specific variables and instead of doing
Which you can't do, because if someone performs a set-based operation,
you will only log one row.
Also, IF UPDATE is quite useless, it tells you whether the column was
mentioned in the UPDATE statement - not that it was actually changed.
I would consider a radical redesign. If this is a singular table you
audit - audit full image instead. If you do this on a great scale,
consider evaluating 3rd party software. I usually mention two - have
little or no experience of them myself. Lumigent's Entegra goes through
the transaction log for a solution which probably gives the best
performance. Red Matrix as has SQLAudit which does it with triggers.
Also, beware of that the inserted/deleted tables are slow. Don't mention
them all over the place in the trigger, but copy to table variables
(probably indexed for the primary key) and work from them.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp