Re: Trigger performance not good

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?