Re: Trigger performance not good

Hi Charles,

Thank you for your note.

Both tables are being journaled. The application table is because the ERP
package requires it. The interface table is because the Microsoft BizTalk
Server, which is reading the table, requires it to be.

I didn't have an isolation level specified on the INSERT statement in the
trigger. I added "WITH NC" and the results were no different. When
testing from the green screen, I have RUNSQLSTM ... COMMIT(*NC). When
testing from System i Navigator Run SQL Scrips, I have Isolation level *NC.

The record length of the master file is 2303 bytes.
The record length of the interface table is 3944 bytes.

The UDFs retrieve a description from a support table for a specified code.
Eg. get_colour_desc(10) returns 'WHITE', which is stored in the IPCOLOR
table. Similar for get_vendor_name, get_tax_code, etc.

I changed the trigger and replaced the 107 references to a UDF to ' ' and
drastically reduced the times to an average of 2.6 seconds / thousand.
While this is still 5x slower than without a trigger, this is by far a big
improvement.

I don't completely understand why the application is caused to wait if this
is an "after" trigger. 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?


Yours truly,

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



On 27 April 2015 at 22:27, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

​Are either or both tables journaled?

Are you using commitment control?​

You mention, "The table that has the triggers active has 422 columns. The
interface
table will receive all 422 columns plus additional information to make a
total of 541 columns. Of the 541 columns, I'm calling a UDF for 107 of
them. My next task is to see if the UDFs can be improved.
​"

What's the total # of bytes in each record?

What kind of UDFs?​ Are they doing DB access?

I'd try a run where you putting dummy values into the interface table in
place of the UDF calculated ones just so you can see the overhead of the
trigger vs. the UDFs.

Honestly, off the top of my head, I'd bet you'er trying to do way to much
work in the trigger.

If this is for some sort of replication, reading the journal would be a
much better idea.

Charles

On Mon, Apr 27, 2015 at 9:36 PM, Glenn Gundermann <
glenn.gundermann@xxxxxxxxx> wrote:

Hi all,

I have one SQL AFTER INSERT trigger program and one SQL AFTER UPDATE
trigger program and I'm not happy with the performance.

Does anyone know of any published numbers for what is considered

acceptable

performance statistics?

I've tried inserting and updating anywhere from 500 to 12,000 rows at a
time.
I averaged 12 insert test times and 12 update test times, excluding the
first test in each batch, which is always higher, to produce the

following

results:

When inserting rows, the avg elapsed times are:
without trigger: 1.193 secs / thousand
with trigger: 36.151 secs / thousand
A factor of 30x slowdown.

When updating rows, the avg elapsed times are:
without trigger: .482 secs / thousand
with trigger 32.804 secs / thousand
A factor of 68x slowdown.

I find the above factors of slowdown appalling.

The purpose of the triggers is to write the incoming information to an
interface table.
The table that has the triggers active has 422 columns. The interface
table will receive all 422 columns plus additional information to make a
total of 541 columns.
Of the 541 columns, I'm calling a UDF for 107 of them. My next task is

to

see if the UDFs can be improved.

I'm not sure what else could be slowing the process down.
The interface table has a generated identity column. NO CACHE or CACHE

200

makes no difference. I haven't tried it without the identity column

since

I really want this but it would be interesting to see the difference.

I'm using this method:
REFERENCING OLD TABLE AS OLD_ITEM
NEW TABLE AS NEW_ITEM
FOR EACH STATEMENT
MODE DB2SQL

I tried this method, which was much slower (avg 74 secs/thousand):
REFERENCING OLD ROW AS OLD_ITEM
NEW ROW AS NEW_ITEM
FOR EACH ROW
MODE DB2ROW

I'm wondering what can be done to accomplish the same end result and have
the least impact on the application doing the update/insert of the master
table.

If I first write the information to an intermediate table first and have

a

trigger on this table to execute the UDFs and write to the interface

table,

would the application still be waiting for everything to be complete?

Is there any performance monitoring that can be done for triggers?

Any suggestions would be most welcome.

Thank you.

Yours truly,

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