RE: Trigger performance not good

One method I've used regarding caching in a couple of service programs was to build the cache as you go.  Try the cache first, if not found, retrieve the data and add it to the cache.   I have one reporting app that needs a LOT of lookups and there is a lot of repetition.  This method has helped.

Roger Harman
COMMON Certified Application Developer – ILE RPG on IBM i on Power
OCEAN User Group
<http://www.ocean400.org>






----------------------------------------

Date: Wed, 29 Apr 2015 11:45:02 -0400
Subject: Re: Trigger performance not good
From: glenn.gundermann@xxxxxxxxx
To: midrange-l@xxxxxxxxxxxx

Hi Chuck,

The primary key is CCLR and I include FETCH FIRST because I recall this was
advice from Birgitta Hauser. I may be wrong or things may have changed
since then but my understanding was that providing such a clause helped the
SQE be more efficient.

I think you're right about replacing the UDFs.

I can't recall why I used DISALLOW PARALLEL other than probably seeing
examples using it. For example, Michael Sansoterra's example here:
http://www.itjungle.com/fhg/fhg030514-printer01.html. Changing my UDF to
ALLOW PARALLEL makes sense.

Your suggestion of caching results is intriguing. I'm not sure it's best
in this case since I have some large tables, e.g. get_vendor_name()
retrieves a vendor name from over 5000 vendor codes.

Thank you again. :-)

Yours truly,

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



On 28 April 2015 at 18:01, CRPence <crpbottle@xxxxxxxxx> wrote:

On 28-Apr-2015 11:44 -0500, Glenn Gundermann wrote:

On 28-Apr-2015 11:10 -0500, Glenn Gundermann wrote:

<<SNIP>>

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

On 27-Apr-2015 20:36 -0500, Glenn Gundermann wrote:
<<SNIP>>
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. <<SNIP>>

Here's a typical UDF. Feel free to comment. Thanks!

CREATE OR REPLACE FUNCTION get_colour_desc
( colour_code NUMERIC(3, 0)
) RETURNS CHARACTER(25)
LANGUAGE SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
DISALLOW PARALLEL
NOT FENCED
RETURN
( SELECT clrn
FROM ipcolor
WHERE cclr = colour_code
FETCH FIRST 1 ROW ONLY
)

I would think there would be a primary key and RI relationship for the
CCLR data of IPCOLOR for which the FETCH FIRST clause should be
unnecessary.? If the limit to the fetch is required because the data is
not protected by a unique constraint [and thus duplicates are possible],
would that not indicate a problem with the encoding of the data and thus
possibly unpredictable and thus inherently non-deterministic results.? With
a uniquely keyed access path on the two columns CCLR and CLRN, the subquery
should be eligible for implementation as an index-only query for which no
access is required from the dataspace.

In the given scenario, there may be value in testing the effects from
having moved the scalar subselect [which BTW prevents inlining the function
invocations] from the UDF invocation directly into the statement that is
generating the row for the "interface table". For example, instead of
coding "... , get_colour_desc(colour_code_fld) ..." code "... , (SELECT
clrn FROM ipcolor WHERE cclr = colour_code_fld) ..." with an effective
attempt at explicitly inlining. The effect from the subqueries maintained
directly for\within a reusable statement may be more directly noticeable,
and thus beneficial in effect for the run-time. Again, having the unique
key [defined on both columns] has the greatest potential for improvement
due to index-only access.

If the result of the scalar UDF are indeed DETERMINISTIC, then there
should be little reason to DISALLOW PARALLEL; if the UDF is run against
multiple columns and\or rows [and for a statement-trigger vs a row trigger,
the latter would be more likely], the cost of the multiple threads to
implement parallel invocations could be easily surpassed by gains in
obtaining more values concurrently vs successively.

Again if the results are deterministic, then possibly caching the
results in a manner that streamlines the providing the matching value could
assist; stored in a form other than as rows of a table [for which a scalar
subselect prohibits the inlined function invocation] either in a manner
that could be beneficial for enabling the function to be created as capable
of the INLINE effect at run-time or just as a simple addressing [e.g. value
as an element of an array] per use of an External routine. For example, if
the color codes and character representations are defined by
values(1,'red'),(2,'blue'),(3,'yellow') then the RETURN expression of an
SQL function could be coded as SUBSTR('red blue
yellow', ((cclr-1)*25)+1, 25) and that function could be _generated code_
from the data within the IPCOLOR file; regenerated after each time those
codes change [likely only when new values are added].

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.