Re: Performance hit with trigger program

If you want to avoid any issues with file open and maybe get another boost
in performance:
Use dataQ in the trigger program instead of writing to the log file.
The trigger program writes the relevant info to a dataq (tikme stamp,
field changes ...) and quit.

You then have a single batch program that listen to the dataQ and comit
the info to the file. This solution is not always possible but, in the case
presented to us, it would work.


Denis Robitaille
Chef de service TI – Solution Entreprise
Infrastructure et Opérations
Cascades Centre des technologies,
412 Marie Victorin
Kingsey falls(Québec) Canada J0A 1B0
T : 819 363 6130



-----Message d'origine-----
De : RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Jon
Paris
Envoyé : 7 avril 2016 12:59
À : Rpg400 Rpg400-L <rpg400-l@xxxxxxxxxxxx>
Objet : Re: Performance hit with trigger program

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.

--
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.
--
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.