Re: Oracle triggers = poor performance ??

The impact of triggers depends on many things.

1. How big is your database? Size does matter.
2. What is the frequencies of transactions that will fire a trigger?
3. How many users will be connected at once?
4. What type of hardware will host the database?

Here are some incomplete rules of thumb.

• Queries against big tables within triggers will reduce performance.
• Small look up tables that will simply end up in the cache will have very little impact *IF* there is enough memory to support your application and the number of users connecting,
• A large number of users firing multiple triggers against the same tables will reduce performance.
• Bigger is better when it comes to hardware and Oracle.
• Don't use a complicated trigger when a constraint will do (in general).

The best option is to load test your database. Build it once with triggers and test, try again with constraints and test again. Compare and pick the best option. Unless you need the flexibility of a trigger, constraints are much easier to maintain and to debug.

Christopher Weiss
Professional Serivices
Compuware Corporation

Edmund Landgraf <elandgraf_at_nospam.1rent.com> wrote in message news:rq1ike$0$37nspbj$3e_at_corp.supernews.com...
> I've heard Oracle triggers really impact performance. I'm using ErWin and
> am trying to decide if I should use triggers to enforce RI in addition to
my
> foreign key constraints. With triggers, for example, I have the ability
to
> cascade, restrict or orphan child nodes when a parent is either inserted,
> updated, or deleted. Can I get that flexibility with keywords off of the
> constraints (e.g. cascade delete)???
>
> Here's a typical cascade delete trigger: (table address has two children,
> useraddress and propertyaddress)
>
> Thoughts????
>
> thx in adv.
> -Ed
>
>
>
>
>
> declare numrows INTEGER;
> begin
> /* ERwin Builtin Thu Jul 29 13:47:02 1999 */
> /* Address R/268 UserAddress ON PARENT DELETE CASCADE */
> delete from UserAddress
> where
> /* %JoinFKPK(UserAddress,:%Old," = "," and") */
> UserAddress.AddressID = :old.AddressID;
>
> /* ERwin Builtin Thu Jul 29 13:47:02 1999 */
> /* Address R/266 PropertyAddress ON PARENT DELETE CASCADE */
> delete from PropertyAddress
> where
> /* %JoinFKPK(PropertyAddress,:%Old," = "," and") */
> PropertyAddress.AddressID = :old.AddressID;
>
>
> -- ERwin Builtin Thu Jul 29 13:47:02 1999
> end;
>
>
Received on Thu Jul 29 1999 - 17:04:25 CDT