473,396 Members | 2,037 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

Triggers running slow (Update Trigger)

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
1 6113
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Hank | last post by:
I have two SQL Server 2000 machines (server_A and server_B). I've used sp_addlinkedserver to link them both, the link seems to behave fine. I can execute remote queries and do all types of neat...
11
by: raulgz | last post by:
I need audit triggers that change columns value in the same record that fire trigger. I need how to do.. Thanks..
3
by: Jonathan Bishop | last post by:
Hi. We are using MSDE2000 on a Point of Sale application. We need to keep a copy of a few key tables as up to date as possible for backup purposes. We are looking at using triggers over the...
1
by: Derek Erb | last post by:
SQL Server 2000 : I have a series of tables which all have the same structure. When any of these tables are modified I need to syncrhonise all of those modifications with one other table wich is a...
2
by: JA | last post by:
Hi, Newbie here. I have a mailing list program that I really like. I also have a new membership program. The membership program has mailing list signups built-in, but it isn't nearly as robust...
1
by: Mark Flippin | last post by:
I'm evidently not understanding nested triggers and I'm looking for some help. I've an Invoice table (see below) that I want to enforce two actions via after triggers. The first trigger...
0
debasisdas
by: debasisdas | last post by:
trigger sample code Ex#10 ======================= INSTEAD OF TRIGGER ---------------------------------------- create or replace trigger mytrig instead of delete or insert or update on eview...
0
debasisdas
by: debasisdas | last post by:
This thread contains some useful tips/sample codes regarding TRIGGERS in oracle, that the forum members may find useful. TRIGGERS: =============== Database trigger is a PL/SQL block that is...
3
by: | last post by:
Hi - I can update a second field based on update of the the first field of the same table. It works fine in the Enterprise Manager (EM), but I can't seem to make the trigger fire when I alter the...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.