Inconsistent Performance from Triggers

[MadJock]

Hi,

I have a trigger that does some auditing on INSERT, DELETE and UPDATE. The current logic means that the trigger is enabled but is not actually doing any work (i.e. for each row: return).

On a set operation affecting 10000 rows, UPDATE and DELETE takes approx 0.4 seconds longer to run when the trigger is enabled (but still not doing any work). However, when invoked from an INSERT statement, the trigger takes 1.2 seconds longer.

Does anyone have any ideas why a trigger would take longer on an INSERT than a DELETE or UPDATE?

Code available if required.

Thanks in advance,

Graeme

"Just beacuse you're paranoid, don't mean they're not after you

 

[Thargy]

Madjock,

my first thoughts are, maybe it's nothing to do with the trigger at all.

Could the difference be due to an index on an affected column being updated by the insert action? The update and delete might be doing subtly different things behind the scenes.

May I suggest that you temporarily delet the trigger(s) in question, (obviously only after you've saved them somewhere handy. Then rerun your timing tests with the trigger out of the way, and see if there's still a difference.

Regards

Tharg

Grinding away at things Oracular

 

[MadJock]

Thanks Tharg,

My main concern is the INSERT statement. This has three possible cases:

(a) trigger disabled: 0.359 sec
(b) trigger enabled but logic disabled: 1.2 secs
(c) trigger enabled and logic enabled: 4.6 secs

I'm curious as to why the difference between (a) and (b) is so big (approx 0.8 sec) when the difference between the same cases for UPDATE or DELETE is around 0.4 secs.

Hope this makes a bit more sense,

Graeme

"Just beacuse you're paranoid, don't mean they're not after you