Re: Using a Trigger to partition your processing

On Mon, 12 Feb 2001 23:24:27 -0800, GHouck <hksys_at_teleport.com> wrote:

There are always inefficiencies when comparing PL/SQL code to PRO*C code: obviously, for the same functionality, C code will always be faster than PL/SQL, all other factors being the same.

However, there are a few things you can do to improve the PL/SQL code.

Make sure the trigger code is a procedure in a package: these are cached in a 'pre-compiled' form that is slightly better than a simple piece of code when called repeatedly.

Make sure the SQL that does the update is up to scratch: ie, using indexes appropriately, minimal access paths, not changing columns that are themselves indexed, etc. Or else even with PRO*C you won't get better results.

>During a large invoicing run, updates are made to
>an invoicing table within a Pro*C program; however,
>the invoicing table has an insert trigger on it
>which causes updates to be made to a separate table.
>This program takes several hours to execute, so we
>are looking at ways to optimize it.
>
>My question is: is there an advantage to 'encapsulating'
>some of the update processing (to the second table) within
>the trigger as described above, or are there inefficiencies
>or drawbacks to doing it this way? The updates to the
>2nd table could have just as easily been written into the
>Pro*C module in the appropriate place.

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Tue Feb 13 2001 - 03:29:39 CST