Triggers running slow (Update Trigger)

Gent

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
if Update(fieldName) i did
if @DelFieldName <> @InsFieldName
begin
INSERT INTO AUDIT
SELECT WHAT I NEED
END

This did not improve performance. If you have any ideas on how to get
around this issue please let me know.

Below is an example of what my triggers look like.

------------------------------------
Trigger 1 -- this was my original design
CREATE trigger1 on Table
FOR UPDATE
AS

if update(field1)
begin
insert into Audit
SELECT What I need
END

if update(field2)
begin
insert into Audit
SELECT What I need
END

..
..
.. Repeated about 65 more times

if update(field67)
insert into Audit
SELECT What I need
END
---------------------------------------
------------------------------------
Trigger 2 -- this is what i tried but did not improve performance
CREATE trigger2 on Table
FOR UPDATE
AS

Declare @DelField1 varchar
Declare @DelField2 varchar
..
..
Declare @DelField67 varchar

Select
@DelField1 = Field1,
@DelField2 = Field2,
....
@DelField67 = Field67
From Deleted
Declare @InsField1 varchar
Declare @InsField2 varchar
..
..
Declare @InsField67 varchar

Select
@insField1 = Field1,
@insField2 = Field2,
....
@InsField67 = Field67
From Inserted

-- I do not do if Update() but instead compare variables

if @DelField1 <> InsField1
begin
Insert into AUDIT
SELECT what I need
end

if @DelField2 <> InsField2
begin
Insert into AUDIT
SELECT what I need
end
....
....
....

if @DelField67 <> InsField67
begin
Insert into AUDIT
SELECT what I need
end

----------------------------------------------
IF you have any idea how to optimize this please let me know. Any
input is greatly appreciated. I do not have a problem with triggers
doing what they are supposed to, they are very slow this is my
concern. The reason I gave you two examples is because i suspect it
has something to do with the enormouse amount of code inside the
trigger. both examples perform about the same whether i use the two
huge selects from the Inserted and Deleted or not.

Thanks,

Gent

Jul 20 '05 #1

Erland Sommarskog

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

Jul 20 '05 #2