Re: Improving performance of calculations in PL/SQL

charlie3101_at_hotmail.com (Charlie Edwards) wrote in message news:<217ac5a8.0401270206.4df0d5fd_at_posting.google.com>...
> Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0401260646.23bbc411_at_posting.google.com>...
> > charlie3101_at_hotmail.com (Charlie Edwards) wrote in message news:<217ac5a8.0401260221.6b9f1d60_at_posting.google.com>...
> _snip_
> > CE, your PC does not have a concurrent user load to contend with so
> > the difference in performance may not be as great as it appears.
> >
> > First, if you code is computation heavy and SQL light then you would
> > be better off performing it in Java or Pro*C rather than in pl/sql.
> >
> > To improve the pl/sql itself if changing from pl/sql is undesired or
> > not practical then:
> >
> > 1- substitute pls_integer for any numbers of the form (n,0) as this
> > will give you native machine binary operations rather than library
> > math.
> >
> > 2- If you are using mod() to time commit loops repace mod with
> > counters and IF tests. (Thomas Kyte has demostrated that this is
> > faster than mod division)
> >
> > 3- Make sure you release any pl/sql tables as soon as you are done
> > with them so that the memeory is available for reuse.
> >
> > 4- If you reuse any pl/sql tables make sure you "trucate" them rather
> > than deleting each entry before reuse [just as with work heap (normal)
> > tables].
> >
> > 5- review the loops to make sure any statement in them can not be
> > moved up to the outer loop.
> >
> > 6- look for common statements on both paths of an IF statement and
> > move it up
> >
> > 7- look for any large chunks of identical code that could be replaced
> > with a procedure and a call to it
> >
> > 8- if you have captive procedures (procedures codes into a bigger
> > pl/sql block and called only from that block) repace the procedure
> > lists with global variables to reduce call overhead
> >
> > HTH -- Mark D Powell --
>
> Mark,
>
> Thanks for those suggestions. I've already done a lot of that, but I
> didn't know about the performance issue with using pls_integer for
> integer variable.
>
> CE

I forgot one of the most beneficial changes. If you have loops where you select and then insert a large row set without performing any pl/sql processing on the individual rows before inserting them and the inserts are done as a single transaction replace the logic with a bulk select/insert. This can be very beneficial.

HTH -- Mark D Powell -- Received on Tue Jan 27 2004 - 08:43:27 CST