Re: Triggers, performance, and distributed processing

From: David G. (david_nospam_at_nospam.com)
Date: 09/09/04 

Date: Thu, 9 Sep 2004 15:41:05 -0400

JoelB wrote:
> David (et al),
>
> Yes, we really like the concept of triggers, and are reluctant to use
> them less.
>
> It seems to me that the gist of your comments about SP optimization
> boil down to the question, "where is the bottleneck, in the triggers
> or the SPs?" If so, in a test DB couldn't you also test that by
> running a lengthy process and timing it, then dropping the triggers
> and doing the same thing again? Our triggers do not make much use of
> calls to SPs, so it would seem to be an easy way to answer the
> question. Then the only question would be, would the SPs or a COM
> object (or perhaps even IO triggers) be any quicker at doing checks
> and maintenance than the conventional triggers were!
>
> We do often resort to using cursors in our triggers, but only if
> necessary. For some operations, we have not been able to find
> alternatives to cursors.
>
> Any comments on the use of COM? To date, we have put as much
> business logic as possible into SQL objects, and only put in the
> interface what code absolutely must occur there. COM seems to add
> protection of intellectual property as well as the flexibility of
> n-tier, whereas not using COM is simpler and more closely protects
> data. Any other comments?
>
> Thanks for your input,
> Joel

Obviously if you perform all validation outside the database,
performance will increase. This is achieved at the expense of possible
data integrity issues for those users that access data from outside the
COM objects / business layer. It's a risk, but one that may companies
accept given that much integrity is coded using referential integrity
constraints (so you have those in place?). If your databases are secure
and users are not given any way to get at data without using the
application, using triggers for data validation can be eliminated.

It's not a simple matter of timing the calls with and without triggers.
That's like test driving a car with a transmission stuck in first gear
first on a straight road and then on a downward slope. Sure, the car
going downhill will accerate faster and attain a high velocity, but
neither test will give you an accurate picture of the car's performance
until the transmission is fixed.

I wasn't clear from your post whether you are using stored procedures
or dynamic SQL from the application. If you are not using stored
procedures for all data access, you should start doing so immediately.
It's better for security and better for performance.

In either case, if you haven't performance tuned the queries and
triggers, then any testing is somewhat pointless. You might find that
with performance tuning, you can achieve a high transaction workload
with the triggers, making a redesign of the application unnecessary.
Until you do, you'll never know.

Once you've tuned your queries, you can then go ahead and do a "with and
without" trigger test. From your test database, you need to make sure
the data is highly representative of what you have in production.
Preferably, the data is an exact copy of production. If you use a
smaller set, the type of data must be representative of production. That
means that customer tables that all have the same last name of "Smith"
won't suffice, since you'll throw off the statistics in indexes.

Any cursors should be eliminated, if possible, if they appear in
triggers or procedures that are executed often. Cursors are very slow
and since the trigger is executed while inside the transaction, it just
adds a measurable amount of processing duration for the locks that are
already held by the SPID. The extended locks mean blocking problems,
long wait times, and deadlocks. 

-- 
David G.

Relevant Pages

  • Indirect use of cursors
    ... 9iR1 database has been caused by the recent introduction of some PL/SQL ... The triggers typically fire on insert and/or update, ... but there's zero use of cursors as I understand ...
    (comp.databases.oracle.misc)
  • Re: Triggers, performance, and distributed processing
    ... > Yes, we really like the concept of triggers, and are reluctant to use ... > alternatives to cursors. ... the data is an exact copy of production. ... David G. ...
    (microsoft.public.sqlserver.programming)
  • Re: Triggers, performance, and distributed processing
    ... David, ... down to the question, "where is the bottleneck, in the triggers or the SPs?" ... We do often resort to using cursors in our triggers, ... > you'll have a difficult time getting bad data into a database that uses ...
    (microsoft.public.sqlserver.programming)
  • Re: Triggers, performance, and distributed processing
    ... David, ... down to the question, "where is the bottleneck, in the triggers or the SPs?" ... We do often resort to using cursors in our triggers, ... > you'll have a difficult time getting bad data into a database that uses ...
    (microsoft.public.sqlserver.server)
  • Re: triggers
    ... >> is it ok to code triggers? ... overhead like cursors do? ... donw by the host program, ... Avoid triggers when you can in favor of DRI actions. ...
    (microsoft.public.sqlserver.programming)
Quantcast