Re: Performance hit with trigger program

[Jon Paris <jon.paris@xxxxxxxxxxxxxx>]

You don’t say what AG you are using but that would also potentially have an impact.  It is the constant close/open of the file that is killing you when setting LR on. 

No need to set on LR each time but without it you need to decide how you are going to close the file. 


Jon Paris

www.partner400.com
www.SystemiDeveloper.com

> On Apr 7, 2016, at 11:25 AM, Dan <dan27649@xxxxxxxxx> wrote:
>
> I'm testing the trigger app I developed with help from some of you here
> these past few days.
>
> I ran an iteration of performance tests.  A simple program that reads (by
> key) the OWNER table, updates a field, and updates the OWNER record, inside
> a FOR X = 1 TO 100000 loop.  I ran this program 20 times; odd-numbered runs
> without a trigger, even-numbered runs with a trigger.  The difference was
> significant.  Without a trigger, the 10 runs averaged 3.4 seconds.  With a
> trigger, the 10 runs averaged 198.9 seconds.  The 100,000 records is
> representative of the batch job volumes they have here.  The OWNER table is
> updated in interactive programs as well.
>
> Reviewing several examples of trigger programs online, I noticed that all
> of them turned on the LR indicator.  For testing, I no-op'd the EVAL
> *INLR=*ON so the program would compile, but never have LR turned on.
> Duplicating the previous test cycle, without a trigger, the 10 runs
> averaged 3.6 seconds.  With a trigger, the 10 runs averaged 11.8 seconds.
> Yeah, about 16 times faster than when turning LR on.
>
> As you can tell by the program source I've included below, this is a simple
> app that basically acts like a journal, so there's no need for
> initialization each time it's called.  I was concerned that each of the
> 100,000 calls would put 100,000 instances of the trigger program in the
> job's call stack, but that was not the case.  The output file in the
> trigger program remained open after my test run completed.  I'm not sure
> whether this a concern, because there are about 200 interactive users who
> will be doing something throughout their day that updates the OWNER table.
> CAN ANYONE ADVISE WHETHER THERE ARE ANY PROBLEMS WITH THIS APPROACH?
>
> Oh, and for those who will inevitably ask, I was told that we cannot turn
> on journaling.  This is my second week here, and I don't have enough clout
> to delve into the why.
>
>     fTRG001H   o    e             disk
>     d                 pi                  ExtPgm( 'TRG001R' )
>     d  eTrgBuffer                         LikeDS( TrgBuffer )
>     d  eTrgBufferLen                      Like( TrgBufferLen )
>      /copy TRGBUFCOPY
>     d TriggerPtr      s               *
>     d TriggerRecord e ds                  ExtName( OWNER     )
>     d                                       Based( TriggerPtr )
>     d                                       Prefix( T_ )
>     d OutputRecord  e ds                  ExtName( OWNER     )
>
>          Select;
>          When eTrgBuffer.tbEvent = '1';  // Insert
>            TriggerPtr = %addr( eTrgBuffer ) + eTrgBuffer.tbNewOffset ;
>            TrgType = 'I';
>          When eTrgBuffer.tbEvent = '2';  // Delete
>            TriggerPtr = %addr( eTrgBuffer ) + eTrgBuffer.tbOldOffset ;
>            TrgType = 'D';
>          When eTrgBuffer.tbEvent = '3';  // Update
>            TriggerPtr = %addr( eTrgBuffer ) + eTrgBuffer.tbNewOffset ;
>            TrgType = 'C';
>          Endsl;
>
>          TrgTmStamp = %timestamp;
>
>          OutputRecord = TriggerRecord;
>          Write TRG001Hr;
>
>          If *inLR <> *inLR;  // Test for performance by not turning LR on
>            *inLR = *on;
>          Endif;
>          Return;
>
> copy member TRGBUFCOPY:
>     d TrgBuffer       ds
>     d  tbFileName                   10
>     d  tbLibraryName                10
>     d  tbMemberName                 10
>     d  tbEvent                       1
>     d  tbTime                        1
>     d  tbCommitLock                  1
>     d  tbFill01                      3
>     d  tbCCSID                      10i 0
>     d  tbRRN                        10i 0
>     d  tbFill02                     10i 0
>     d  tbOldOffset                  10i 0
>     d  tbOldLength                  10i 0
>     d  tbOldNullOff                 10i 0
>     d  tbOldNullLen                 10i 0
>     d  tbNewOffset                  10i 0
>     d  tbNewLength                  10i 0
>     d  tbNewNullOff                 10i 0
>     d  tbNewNullLen                 10i 0
>     d  tbBufChar              1  32767
>     d   tbBufArry                    1    Overlay( tbBufChar )
>     d                                     Dim( %size( tbBufChar ))
>
>     d TrgBufferLen    s             10i 0
> -- 
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related questions.