Re: PL/SQL loop performance

you were very correct.

In article <3A6FB447.7777A559_at_exesolutions.com>,   "Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote:
> > I am writing a procedure that loops through symbols and their phrases and
> > prints them to an output file. One symbol and many phrases on a line. The
> > cursors run quickly but the entire procedure takes 40 seconds. Is their a way
> > to improve the speed. The procedure can be seen below.
> >
> > CREATE OR REPLACE PROCEDURE symbols_gram(login2 varchar2)
> > AS
> > --Variables to input into the file
> > v_sym_num lists.symbol.sym_num%TYPE;
> > v_ticker lists.symbol.ticker%TYPE;
> > v_class lists.symbol.class%TYPE;
> > v_exchange lists.symbol.sym_exchange%TYPE;
> > v_sym_num2 lists.symbol_phrase.sym_num%TYPE;
> > v_symbol_phrase lists.symbol_phrase.phrase%TYPE;
> > v_sym_num_bind lists.symbol.sym_num%TYPE;
> > --File Handle
> > v_filehandle UTL_FILE.FILE_TYPE;
> > --Cursors
> > CURSOR c_symbol IS
> > select distinct s.sym_num, s.ticker, s.class, s.sym_exchange
> > from
> > lists.symbol s,
> > lists.symbol_phrase sp,
> > lists.phrase_user_xref xref,
> > lists.umevoice_user u
> > where
> > u.login = login2
> > and u.user_num = xref.user_num
> > and xref.sym_phrase_num = sp.sym_phrase_num
> > and sp.sym_num = s.sym_num;
> >
> > CURSOR c_phrase IS select sp.sym_num, sp.phrase from
> > lists.symbol_phrase sp, lists.phrase_user_xref xref,
> > lists.umevoice_user u where u.login = login2 and sp.sym_num =
> > v_sym_num_bind and u.user_num = xref.user_num and
> > xref.sym_phrase_num = sp.sym_phrase_num; BEGIN --Open file to write
> > v_filehandle := UTL_FILE.FOPEN('c:\grammars','symbols.gram','w'); --Static
> > Data before first loop UTL_FILE.PUT_LINE(v_filehandle,'grammar
> > com.umevoice.uvautex.'||login2||'.symbols;');
> > UTL_FILE.PUT_LINE(v_filehandle,'<uv_new_symbols>=( ('); --Loop through
> > c_symbol cursor OPEN c_symbol; LOOP
> > FETCH c_symbol INTO v_sym_num, v_ticker, v_class, v_exchange;
> > EXIT WHEN c_symbol%NOTFOUND; IF c_symbol%ROWCOUNT =
> > 1 THEN
> > UTL_FILE.PUT(v_filehandle,'<'||lower(v_ticker)||v_class||v_exchange||'>');
> > ELSIF c_symbol%FOUND THEN
> > UTL_FILE.PUT(v_filehandle,' |
> > <'||lower(v_ticker)||v_class||v_exchange||'>');
> > ELSE EXIT; END IF;
> > UTL_FILE.PUT_LINE(v_filehandle,null); END LOOP;
> > CLOSE c_symbol; UTL_FILE.PUT_LINE(v_filehandle,') {$TICKER})');
> > --static data before second loop UTL_FILE.PUT_LINE(v_filehandle,'{');
> > UTL_FILE.PUT_LINE(v_filehandle,'symbolAction=setsymbol;');
> > UTL_FILE.PUT_LINE(v_filehandle,'symbolValue =$TICKER.SYM;');
> > UTL_FILE.PUT_LINE(v_filehandle,'};'); --Loop through table and input into
> > file OPEN c_symbol; LOOP FETCH c_symbol INTO
> > v_sym_num, v_ticker, v_class, v_exchange; EXIT WHEN
> > c_symbol%NOTFOUND;
> > UTL_FILE.PUT(v_filehandle,'<'||lower(v_ticker)||v_class||v_exchange||'>=(');
> > v_sym_num_bind := v_sym_num; OPEN
> > c_phrase; LOOP FETCH c_phrase
> > INTO v_sym_num2, v_symbol_phrase; EXIT WHEN
> > c_phrase%NOTFOUND; IF c_phrase%ROWCOUNT = 1 THEN
> > UTL_FILE.PUT(v_filehandle,v_symbol_phrase);
> > ELSE UTL_FILE.PUT(v_filehandle,' | '||v_symbol_phrase);
> > END IF; END LOOP;
> > CLOSE c_phrase;
> > UTL_FILE.PUT_LINE(v_filehandle,')');
> > UTL_FILE.PUT_LINE(v_filehandle,'{');
> >
> > UTL_FILE.PUT_LINE(v_filehandle,'SYM='||v_ticker||':'||v_class||':'||v_exchange||';');
> > UTL_FILE.PUT_LINE(v_filehandle,'};');
> > END LOOP;
> >
> > CLOSE c_symbol;
> > --Close file
> > UTL_FILE.FCLOSE(v_filehandle);
> > --Error Messages
> > EXCEPTION
> > WHEN OTHERS THEN
> > DBMS_OUTPUT.PUT_LINE('ERROR '||to_char(SQLCODE)||SQLERRM);
> > NULL;
> > END;
> > /
> >

>
> My first thought would be ... do you have indexes that correspond with the cursors WHERE
> clauses?
>
> Daniel A. Morgan
>
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 25 2001 - 19:28:50 CET