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
