Package performance.

From: Michael Rothwell <michael_rothwell_at_non-hp-usa-om46.om.hp.com>
Date: Wed, 23 Sep 1998 14:21:19 -0700
Message-ID: <3609664F.5468C948@non-hp-usa-om46.om.hp.com>

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.

The function is as follows:

  function convert( cCurrencyType in varchar2, nUSDollar in number,

                    dYYMM in varchar2 )

    return number IS
    nExchange number default 0; -- set default to zero   Begin
   if (cCurrencyType = 'UD') then

      Return nUSDollar;
   end if;

   begin
--

• try getting the exchange rate from the currency trans table
  --
  select accounting_rate rate into nExchange from currency_trans where currency_code = cCurrencyType and to_char( currency_date, 'YYMM' ) = dYYMM;
  --
  
• if exchange rate not found return -1
  --
  exception when no_data_found then return -1; when others then return -1; end; Return round(nExchange * nUSDollar,2); End;

This is fine for 1 or two records, but when I run a query that either returns 1000's of records, or rolls up 1000's of records, the speed of the system slows to a crawl and CPU usage gets pegged. If I pass 'UD' as my currency code, it is very fast again. The currency_trans table only has 81 records in it.

Any suggestions would be greatly appreciated.

Thanks.

Michael Received on Wed Sep 23 1998 - 16:21:19 CDT