Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Package performance.
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.
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 );
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;
alter table currency_trans
add constraint currency_trans_pk primary key( currency_code, currency_date );
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;
time taken 2000 = 661ms
time taken 16000 = 5.1s (interesting that this is slower than version
1)
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;
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;
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:42:29 CDT