Re: Improving performance of calculations in PL/SQL

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 Received on Tue Jan 27 2004 - 04:06:37 CST