Re: Trigger performance

In article <7rlpju$mls$1_at_nnrp1.deja.com>,   Alex A. <albu_at_mailcity.com> wrote:
> What is the performance impact of an insert trigger?
> I have a table
> ACTIONS(ACTION_TYPE, SOURCE, DESTINATION)
> and depending on ACTION_TYPE, DESTINATION may or may not
> be null. Is the cost of adding a trigger to enforce this
> significant (there are only about 10 action types)? If
> the overhead introduced by the trigger is significant,
> I'd rather rely on the front-end checking (although this
> is not the right thing to do...).
>
> Any advice?
>
> Thanks, Alex

If you are already on the limit for performance then a trigger could in theory blow it. You have to ask yourself how many times a second is the INSERT statement (along with its possible trigger) going to be executed. I would guess in the majority of cases the trigger will not have any meaningful impact the performance. However, you can always trace the insert statement with timings turned on and run the output through tkprof. You will then be able to see timings for the INSERT plus timings for the SQL statments issued by the trigger. (I assuming here you would put all the action_types in a lookup table with a column to say whether the destination column can be null or not.)

If you are currently in the middle of developing an application then I would err on maintaining referential integrity rather than worrying about theortical performance impacts. Once your development gets to the testing stage you can always focus on any statements which are actually giving you performance problems.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 15 1999 - 11:46:33 CDT