Re: Package performance.

From: Keith Boulton <boulke_at_globalnetnospam.co.uk>
Date: Thu, 24 Sep 1998 13:42:33 GMT
Message-ID: <360a4c2b.12624533@news.globalnet.co.uk>

On Wed, 23 Sep 1998 14:21:19 -0700, Michael Rothwell <michael_rothwell_at_non-hp-usa-om46.om.hp.com> wrote:

>On a 7.2.2 version of Oracle, HPUX 9.x I have a package that
>contains a function to convert currency based on values
>stored in a table. The currency_trans table consists of a
>currency_code, date and exchange_rate.
>

've experimented with this. The main problem is the to_char on a date in your search. Your code implies that there should be only one exchange rate for a given month. It would be better to replace the date column in your currency_trans table with a varchar2(4) field. This will result in fivefold performance improvements. Other options include using pl/sql table to store the translation table. The fastest approach is to build views which join your base tables to the exchange rate table this gave 70-fold performance improvement in my experiments.

There follows a series of experiments I carried out: Received on Thu Sep 24 1998 - 08:42:33 CDT