SPs from Triggers in V8.2 - Performance?

Paul Reddin

Hi,

Currently, in V8.1 we have a particular trigger which causes a
significant performance impact on an inserting query (around
10seconds) which appears because the optimiser is having to recompile
the insert with the trigger body everytime a new value is inserted
(i.e not using parameter markers) - the app. status shows 'compiling'
for 10secs+

In V8.2 with the ability to call SPs from triggers - if I move the
trigger body to an SP, would it be precompiled and hence overcome this
problem completely?
or would a compile still be necessary ?

Many thanks

Paul.

Nov 12 '05 #1

Serge Rielau

Paul,

Everything you pushed into the preocedure would be precompiled.
However the trigger stub "CREATE TRIGGER ... CALL trigger_body(...);"
Still needs to be picked up. So there will still be "some" overhead.
You benefit directly relates to teh complexity/length of the trigger-body.

The obvious question of course is why no parameter markers are used.

Cheers
Serge

Nov 12 '05 #2