Re: Package performance.

From: Keith Boulton <boulke_at_globalnetnospam.co.uk>
Date: Thu, 24 Sep 1998 13:43:02 GMT
Message-ID: <360a4c62.12679692@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:

I'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:

I changed your currency_trans table so that the currency date is stored as a four digit character field which is sensible given that for your calculation to work there should be only one exchange rate per month and currency.

• create the table create table currency_trans ( currency_code varchar2(3) , currency_date varchar2(4) , accounting_rate number(12,4) );
• populate with test exchange rates insert into currency_trans values( 'ABC', to_date('9701', 12.45 ); insert into currency_trans values( 'ABD', '9701', 12.45 ); insert into currency_trans values( 'ABE', '9701', 12.45 ); insert into currency_trans values( 'ABF', '9701', 12.45 ); insert into currency_trans values( 'ABG', '9701', 12.45 ); insert into currency_trans values( 'ABH', '9701', 12.45 ); insert into currency_trans values( 'ABI', '9701', 12.45 ); insert into currency_trans values( 'ABJ', '9701', 12.45 ); insert into currency_trans values( 'ABK', '9701', 12.45 );

insert into currency_trans select currency_code, '9702', 13.4 from currency_trans where currency_date='9701'; insert into currency_trans select currency_code, '9703', 13.4 from currency_trans where currency_date='9701'; insert into currency_trans select currency_code, '9704', 13.4 from currency_trans where currency_date='9701'; insert into currency_trans select currency_code, '9705', 13.4 from currency_trans where currency_date='9701'; insert into currency_trans select currency_code, '9706', 13.4 from currency_trans where currency_date='9701'; insert into currency_trans select currency_code, '9707', 13.4 from currency_trans where currency_date='9701'; insert into currency_trans select currency_code, '9708', 13.4 from currency_trans where currency_date='9701'; insert into currency_trans select currency_code, '9709', 13.4 from currency_trans where currency_date='9701'; insert into currency_trans select currency_code, '9710', 13.4 from currency_trans where currency_date='9701'; commit;

Currency trans table now has 80 records

Now we generate a test table containing 2048 records each of which is at the midpoint of the currency_trans table. This is approximately equivalent to repeated random seraches through the currency_trans table.

create table test ( currency_code varchar2(3), currency_date varchar2(4), USDollar number(12,2) );
insert into test values( 'ABF', '9706', 12344.6 );

• repeat until 1024 records inserted to get 2048 records insert into test select 'ABF', currency_date, 2345.7 from test;
• test statement set timing on select avg( t.convert( currency_code, USDollar, currency_date ) ) from test;
• package header create or replace package t as function convert(cCurrencyType in varchar2, nUSDollar in number,dYYMM in varchar2 ) return number; pragma restrict_references( convert, WNDS ); end t; /
• version 1 search currency_trans table each time
• to_char around date column no longer required create or replace package body t as function convert(cCurrencyType in varchar2, nUSDollar in number,dYYMM in varchar2 ) return number is nExchange number default 0; begin if (cCurrencyType = 'UD') then return nUSDollar; end if;

      begin
         select accounting_rate rate
         into   nExchange
         from   currency_trans
         where  currency_code = cCurrencyType
         and    currency_date = dYYMM;
      exception
         when no_data_found then
            return -1;
         when others then
            return -1;
      end;
     
      return round(nExchange * nUSDollar,2);

   end convert;
end t;
/

• time taken for 2000 records = 850 ms
• time taken for 16000 records = 4.7s

alter table currency_trans

   add constraint currency_trans_pk primary key( currency_code, currency_date );

• time taken for 2000 records = 580 ms interestingly, analyzing the table take the figure back to 850 ms (bad table scan decision as ever by the optimiser)
  • version 2, iterate through a pl/sql table in memory create or replace package body t as

   type tctr is table of currency_trans%rowtype index by binary_integer;

   ctr tctr;
   nRowCount binary_integer := 0;

   function convert(cCurrencyType in varchar2, nUSDollar in number,dYYMM in varchar2 )

      return number
   is

      nExchange number default -1;  -- set default to zero
      cursor c is
         select currency_trans.*
         from   currency_trans;
   begin
      if (cCurrencyType = 'UD') then
         return nUSDollar;
      end if;
      
      if nRowCount = 0 then
         for r in c loop
            nRowCount := nRowCount + 1;
            ctr(nRowCount) := r;
         end loop;
      end if;
   
      for i in 1..nRowCount loop
         if ctr(i).currency_code = cCurrencyType
         and ctr(i).currency_date = dYYMM then
            nExchange := ctr(i).accounting_rate;
            exit;
         end if;
      end loop;
 
      return round(nExchange * nUSDollar,2);

   end convert;
end t;
/

time taken 2000 = 661ms
time taken 16000 = 5.1s (interesting that this is slower than version 1)

• version 3, hash table create or replace package body t as

   type tctr is table of currency_trans%rowtype index by binary_integer;

   ctr tctr;
   nRowCount binary_integer := 0;

   function convert(cCurrencyType in varchar2, nUSDollar in number,dYYMM in varchar2 )

      return number
   is

      nExchange number default -1;  -- set default to zero
      nIndex binary_integer;
      cursor c is
         select currency_trans.*
         from   currency_trans;
   begin
      if (cCurrencyType = 'UD') then
         return nUSDollar;
      end if;
      
      -- get hash value accepts numbers up to 2^30
      if nRowCount = 0 then
         for r in c loop
            nIndex := dbms_utility.get_hash_value( r.currency_date ||
r.currency_code, 0 , 1073741824 );
            while ctr.exists(nIndex) loop
               nIndex := nIndex + 1;
            end loop;
            ctr(nIndex) := r;
         end loop;
         nRowCount := 1;
      end if;
   
      nIndex := dbms_utility.get_hash_value( dYYMM || cCurrencyType, 0
, 1073741824 );
      while ctr.exists(nIndex) loop
         if ctr(nIndex).currency_code = cCurrencyType
         and ctr(nIndex).currency_date = dYYMM then
            nExchange := ctr(nIndex).accounting_rate;
            exit;
         end if;
      end loop;
 
      return round(nExchange * nUSDollar,2);

   end convert;
end t;
/
time taken 2000 = 400 ms
time taken 16000 = 2.9s
This is the fastest approach, but I've experienced much better improvements (up to x6) in other similar situations.

My feeling was that all these choices provided acceptable performance, and I remembered that some years ago, I had found date conversion functions expensive, so I created a new table with a date column.

create table currency_trans2(currency_code, currency_date, accounting_rate )
as select currency_code, to_date( currency_date, 'YYMM' ), accounting_rate from currency_trans;

create or replace package body t as

   function convert(cCurrencyType in varchar2, nUSDollar in number,dYYMM in varchar2 )

      return number
   is

      nExchange number default 0;
   begin

      if (cCurrencyType = 'UD') then
         return nUSDollar;
      end if;
   
      begin
         select accounting_rate rate
         into   nExchange
         from   currency_trans2
         where  currency_code = cCurrencyType
         and    to_char(currency_date,'YYMM') = dYYMM;
      exception
         when no_data_found then
            return -1;
         when others then
            return -1;
      end;
     
      return round(nExchange * nUSDollar,2);

   end convert;
end t;
/
time 16000 = 22 seconds

An alternative is to create a view where the converted value is required (just realised the USDollar column should be called foreign currency value ):
create view test_x ( currency_code, currency_date, test_value ) as
select test.currency_code,

    test.currency_date,
   currency_trans.accounting_rate * test.USDollar from test, currency_trans
where test.currency_code(+) = currency_trans.currency_code and test.currency_date(+) = currency_trans.currency_date;

select avg(test_value) from test_x;
16000 records = 300 ms Received on Thu Sep 24 1998 - 08:43:02 CDT