Re: Trigger performance not good

Hi Chuck,

Thank you for your detailed response.

I'm not sure if the following would create a distinct isolation-level so
I'll ask ...

If I re-write the trigger so it does less work and write to an intermediate
table, and have a trigger on the intermediate table to do all the work of
calling the UDFs and write to the interface table, will that free up the
application making the initial updates/inserts? Or is the entire process
still considered all-in?

Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144


On 28 April 2015 at 13:17, CRPence <crpbottle@xxxxxxxxx> wrote:

On 28-Apr-2015 11:10 -0500, Glenn Gundermann wrote:

<<SNIP>>
I don't completely understand why the application is caused to wait
if this is an "after" trigger.

The /time/ of the trigger work, specified as before or after the I\O
event, merely defines where within a timeline, the work of the trigger is
performed. That specification of the timing does not also tell the
database whether to perform asynchronously the work of the trigger. Trigger
work is inherently part of the isolation and therefore incompatible with
the notion of operating asynchronous to the I\O event; the use of *NC
viewed as being an effect of autocommit rather than as an actual distinct
isolation-level.

If the trigger should perform work asynchronous to the I\O event, then
the trigger has to perform what is necessary to make that happen [e.g.
enqueue the data from the buffer [or transition table] to be processed
elsewhere], and of course deal with the potential issues of concurrency and
loss\lack of isolation.

If there is a trigger, I can understand the DBMS has to take time to

fill a buffer of data. Once the buffer is filled, why can't the
application continue, even if the trigger had a DLYJOB for an hour?

Effectively, that /buffer/ is tied to the thread [and stack-frame] of
the trigger program that has been given visibility to the data. To effect
separation from that thread, the trigger must send that data elsewhere to
be processed asynchronously.

If such a capability is desirable to be requested of the database SQL,
then perhaps ask for a new clause on the CREATE TRIGGER to inform that the
trigger program should be invoked in a separate thread; also of the non-SQL
database, something similar via the Add Physical File Trigger (ADDPFTRG).
Presumably the effect would be restricted to the I\O operations performed
WITH NC, so probably best to describe [in such an request for enhancement]
what should be the effect for all affected events [e.g. the SQL statements]
that would run in isolation [i.e. not *NC] against a file with such a
trigger; perhaps all statements running with isolation should fail similar
to how when the file is not journaled, with -7008?

--
Regards, Chuck