Re: Trigger performance not good

comments in-line

On Tue, Apr 28, 2015 at 12:10 PM, Glenn Gundermann <
glenn.gundermann@xxxxxxxxx> wrote:

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.

​If both tables are journaled, then you'll want to make sure that you are
either using commitment control or have installed the optional license
program HA Journal Performance​ (5770-ss1 option 42).

Read the following redbook to understand why
http://www.redbooks.ibm.com/abstracts/sg246286.html

(note that HA Journal Performance was originally a PPRQ known as Journal
caching (5799-BJC))

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.

So, 107 description lookups...each one to a different table I assume?

​Yeah, that's going to hurt. Instead of the UDFs, consider having the
interface table be just the columns from the trigger buffer + whatever
other event data you need (ie. timestamp?) Then have BizTalk read a view
over the interface table that joins back to the description tables.

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.

​Understanding the effects of journaling (per the above redbook) would help
improve this.

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?

​Because that not the​ way triggers work. After triggers can throw
exceptions that are then passed back to the application.