Triggers run slow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Triggers run slow

Author

Topic

gmetaj
Starting Member

33 Posts

Posted - 2004-08-09 : 17:22:12

I 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 = DelField1,
@DelField2 = DelField2,
...
@DelField3 = DelField3
From Deleted

Declare @InsField1 varchar
Declare @InsField2 varchar
.
.
Declare @InsField67 varchar

Select
@insField1 = InsField1,
@insField2 = InsField2,
...
@InsField3 = InsField3
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.

Thanks,

Gent

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 17:25:04

This isn't going to work:

Select
@DelField1 = DelField1,
@DelField2 = DelField2,
...
@DelField3 = DelField3
From Deleted

The above statement will only work for the last row in the deleted table. Triggers do NOT fire for each row. They fire once per DML statement. So if your DELETE statement deleted 20 rows, the trigger fires once. You can not capture more than one value at a time in a variable. You will only get the last row in deleted in the AUDIT table due to this.

Tara

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-09 : 17:35:53

Is there some reason you can't use:

INSERT INTO audit
   SELECT * FROM inserted

-PatP

gmetaj
Starting Member

33 Posts

Posted - 2004-08-09 : 17:37:32

Below is part of my code, I have declared the variables
@fkBranch, @AcctCode, @AcctName1, @AcctName2 in the first scenario. I do not have a problem with triggers not doing what they are supposed to do the problem is with taking to long on updates. Scenario 1 commented on every specific IF

In scenario 2
I have done

SELECT
@InsfkBran = Branch,
@InsAcctCode = AccCode,
@InsAcctName1 = AcctName1,
@InsAcctName2 = ActtName2
From Inserted

SELECT
@DelfkBran = Branch,
@DelAcctCode = AccCode,
@DelAcctName1 = AcctName1,
@DelAcctName2 = ActtName2
From Inserted

---I am having speed issues. Inserts work a lot faster though. THe code in the insert is slightly different but inserts work a lot faster and I only have problem with the updates, i'm assuming there is an issue with the IFs or somehow it's recompiling the trigger code every time and not running the execution plan.

Thank you for your input!

if @InsfkBranch <> @DelfkBranch
--If Update(fkBranch)
begin
Insert Into Audit (TableID, TableName, Tables_Fields, Tables_Fields_English, Identifier_Code, FieldDesc, Initials, ServerDate,
[Action], OldValue, NewValue)

(Select i.ID, 'Account', 'Account.AcctCode, Branch.BranchName', 'Account Code, Branch Name',
(SELECT i.AcctCode + ', ' +
(Select Branch.BranchName from Branch where Branch.ID = i.fkBranch and d.fkBranch <> i.fkBranch) ),
'Branch Name', i.Initials, @Date, 2,
(Select Branch.BranchName from Branch where Branch.ID = d.fkBranch and d.fkBranch <> i.fkBranch) ,
(Select Branch.BranchName from Branch where Branch.ID = i.fkBranch and d.fkBranch <> i.fkBranch)
From Inserted i INNER JOIN Deleted d on i.ID = d.ID
Where d.fkBranch <> i.fkBranch) --This will keep us from getting the extra rows

end

if @InsAcctCode <> @DelAcctCode
--If Update(AcctCode)
begin

Insert Into Audit (TableID, TableName, Tables_Fields, Tables_Fields_English, Identifier_Code, FieldDesc, Initials, ServerDate,
[Action], OldValue, NewValue)
(Select i.ID, 'Account', 'Account.AcctCode', 'Account Code', i.AcctCode, 'Account Code', i.Initials, @Date,
2, d.AcctCode , i.AcctCode
From Inserted i INNER JOIN Deleted d on i.ID = d.ID
Where d.AcctCode <> i.AcctCode) --This will keep us from getting the extra rows

end

if @InsAcctName1 <> @DelAcctName1
--If Update(AcctName1)
begin

Insert Into Audit (TableID, TableName, Tables_Fields, Tables_Fields_English, Identifier_Code, FieldDesc, Initials, ServerDate,
[Action], OldValue, NewValue)
(Select i.ID, 'Account', 'Account.AcctCode', 'Account Code', i.AcctCode, 'Account Name', i.Initials, @Date,
2, d.AcctName1 , i.AcctName1
From Inserted i INNER JOIN Deleted d on i.ID = d.ID
Where d.AcctName1 <> i.AcctName1) --This will keep us from getting the extra rows

end

if @InsAcctName2 <> @DelAcctName2
--If Update(AcctName2)
begin

Insert Into Audit (TableID, TableName, Tables_Fields, Tables_Fields_English, Identifier_Code, FieldDesc, Initials, ServerDate,
[Action], OldValue, NewValue)
(Select i.ID, 'Account', 'Account.AcctCode', 'Account Code', i.AcctCode, 'Account Long Name', i.Initials, @Date,
2, d.AcctName2 , i.AcctName2
From Inserted i INNER JOIN Deleted d on i.ID = d.ID
Where d.AcctName2 <> i.AcctName2) --This will keep us from getting the extra rows

end

quote:
Originally posted by tduggan

This isn't going to work:

Select
@DelField1 = DelField1,
@DelField2 = DelField2,
...
@DelField3 = DelField3
From Deleted

The above statement will only work for the last row in the deleted table. Triggers do NOT fire for each row. They fire once per DML statement. So if your DELETE statement deleted 20 rows, the trigger fires once. You can not capture more than one value at a time in a variable. You will only get the last row in deleted in the AUDIT table due to this.

Tara

gmetaj
Starting Member

33 Posts

Posted - 2004-08-09 : 17:41:53

quote:
Originally posted by Pat Phelan

Is there some reason you can't use:
INSERT INTO audit
SELECT * FROM inserted
-PatP

Because we are trying to make the audit table easy to read by our users. Some of those fields are not needed. I insert one record in the audit table for every field that is updated. Even when I update a single field i have performance issues. Somehow the trigger is going through all the ifs checking every single one of them and parsing/compiling hte code inside if. Commeting about 60 ifs improves performance dramatically even though all those 60 if comented return false and are not inserting to the audit table.

Thank you for your input!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 17:52:23

Why work on a performance problem if it isn't going to work for more than one row?

Tara

gmetaj
Starting Member

33 Posts

Posted - 2004-08-09 : 18:01:53

What do you mean it's not going to work for more than one row. If you think that case 2 will not work consider example below

--if @InsfkBranch <> @DelfkBranch
If Update(fkBranch)
begin
Insert Into Audit (TableID, TableName, Tables_Fields, Tables_Fields_English, Identifier_Code, FieldDesc, Initials, ServerDate,
[Action], OldValue, NewValue)

(Select i.ID, 'Account', 'Account.AcctCode, Branch.BranchName', 'Account Code, Branch Name',
(SELECT i.AcctCode + ', ' +
(Select Branch.BranchName from Branch where Branch.ID = i.fkBranch and d.fkBranch <> i.fkBranch) ),
'Branch Name', i.Initials, @Date, 2,
(Select Branch.BranchName from Branch where Branch.ID = d.fkBranch and d.fkBranch <> i.fkBranch) ,
(Select Branch.BranchName from Branch where Branch.ID = i.fkBranch and d.fkBranch <> i.fkBranch)
From Inserted i INNER JOIN Deleted d on i.ID = d.ID
Where d.fkBranch <> i.fkBranch) --This will keep us from getting the extra rows

end

--if @InsAcctCode <> @DelAcctCode
If Update(AcctCode)
begin

Insert Into Audit (TableID, TableName, Tables_Fields, Tables_Fields_English, Identifier_Code, FieldDesc, Initials, ServerDate,
[Action], OldValue, NewValue)
(Select i.ID, 'Account', 'Account.AcctCode', 'Account Code', i.AcctCode, 'Account Code', i.Initials, @Date,
2, d.AcctCode , i.AcctCode
From Inserted i INNER JOIN Deleted d on i.ID = d.ID
Where d.AcctCode <> i.AcctCode) --This will keep us from getting the extra rows

end

--if @InsAcctName1 <> @DelAcctName1
If Update(AcctName1)
begin

Insert Into Audit (TableID, TableName, Tables_Fields, Tables_Fields_English, Identifier_Code, FieldDesc, Initials, ServerDate,
[Action], OldValue, NewValue)
(Select i.ID, 'Account', 'Account.AcctCode', 'Account Code', i.AcctCode, 'Account Name', i.Initials, @Date,
2, d.AcctName1 , i.AcctName1
From Inserted i INNER JOIN Deleted d on i.ID = d.ID
Where d.AcctName1 <> i.AcctName1) --This will keep us from getting the extra rows

end

--if @InsAcctName2 <> @DelAcctName2
If Update(AcctName2)
begin

Insert Into Audit (TableID, TableName, Tables_Fields, Tables_Fields_English, Identifier_Code, FieldDesc, Initials, ServerDate,
[Action], OldValue, NewValue)
(Select i.ID, 'Account', 'Account.AcctCode', 'Account Code', i.AcctCode, 'Account Long Name', i.Initials, @Date,
2, d.AcctName2 , i.AcctName2
From Inserted i INNER JOIN Deleted d on i.ID = d.ID
Where d.AcctName2 <> i.AcctName2) --This will keep us from getting the extra rows

end

--if @InsPrincReserve <> @DelPrincReserve
If Update(PrincReserve)
begin

Insert Into Audit (TableID, TableName, Tables_Fields, Tables_Fields_English, Identifier_Code, FieldDesc, Initials, ServerDate,
[Action], OldValue, NewValue)
(Select i.ID, 'Account', 'Account.AcctCode', 'Account Code', i.AcctCode, 'Principal Reserve', i.Initials, @Date,
2, d.PrincReserve , i.PrincReserve
From Inserted i INNER JOIN Deleted d on i.ID = d.ID
Where d.PrincReserve <> i.PrincReserve) --This will keep us from getting the extra rows

end

There is about 60 more IF statements there. This is my issue. Any idea?

quote:
Originally posted by tduggan

Why work on a performance problem if it isn't going to work for more than one row?

Tara

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-09 : 18:06:50

If you are trying to get things so that they are readable by non-SQL types, then I'd take a completely different approach. I'd archive the whole lot, within the trigger then sort the mess out later. Something like:

CREATE UPDATE TRIGGER tutSchtuff ON tSchtuff
AS

INSERT INTO tAuditSchtuff
   SELECT 'i', GetDate(), suser_sname(), *
      FROM inserted

INSERT INTO tAuditSchtuff
   SELECT 'd', GetDate(), suser_sname(), *
      FROM deleted

GO

This will trap everything, allowing you to sort out the mess later. You could then create a view something like:

CREATE VIEW vAuditSchtuff
AS SELECT
   asof, loginame  -- these are the GetDate() and suser_sname() values
,  'Whatever suits you'
   FROM tAuditSchtuff AS i
   LEFT JOIN tAuditSchtuff AS d
      ON (d.asof = i.asof
      AND d.loginame = i.loginame
      AND 'd' = i.rowtype)
   WHERE  'i' = i.rowtype -- this is the constant from the trigger
      AND i.col23 <> d.col23
   UNION ALL SELECT
   asof, loginame  -- these are the GetDate() and suser_sname() values
,  'Whatever suits you'
   FROM tAuditSchtuff AS i
   LEFT JOIN tAuditSchtuff AS d
      ON (d.asof = i.asof
      AND d.loginame = i.loginame
      AND 'd' = i.rowtype)
   WHERE  'i' = i.rowtype -- this is the constant from the trigger
      AND i.col46 <> d.col46

This puts the CPU burden on the list processing (which could be at night) rather than the trigger which happens in real time.

-PatP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources