Re: Trigger performance not good

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