Home » SQL & PL/SQL » SQL & PL/SQL » How to tune/reduce time for this simple procedure ? (9i,9.2.0.4, redhat linux)
How to tune/reduce time for this simple procedure ? [message #416227] Fri, 31 July 2009 05:04 Go to next message
sajiv
Messages: 2
Registered: July 2009
Location: INDIA
Junior Member
Hi,

The following procedure takes around 20-25 minutes to calculate a months value. How can i speed up this procedure ?


Tables
---------
act_master - 1213 rows [ac_no]
test1 - 43012199 rows [ac_no,amount,trn_dt,value_dt]
valhist - [ac_no,amount,value_dt]


Procedure
----------

SQL> DECLARE
2 d2 date;
3 v_amt long;
4 cursor rac is select ac_no from act_master order by ac_no;
5 v_ac act_master.ac_no%TYPE;
6 cursor c1 is select sum(amount) FROM test1 where value_dt<=d2 and ac_no in(v_ac);
7
8 BEGIN
9 d2:='01-may-2009';
10 LOOP
11 open rac;
12 loop
13 fetch rac into v_ac;
14 exit when rac%NOTFOUND;
15 open c1;
16 fetch c1 into v_amt;
17 insert into valhist values (v_ac,d2,v_amt);
18 close c1;
19 end loop;
20 commit;
21 close rac;
22 d2:=d2+1;
23 EXIT WHEN d2='01-jun-2009';
24 END LOOP;
25 END;
26 /

PL/SQL procedure successfully completed.

Elapsed: 00:23:54.09


any suggestions will be helpful...
Re: How to tune/reduce time for this simple procedure ? [message #416233 is a reply to message #416227] Fri, 31 July 2009 05:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well. Cursor loops ARE the slowest way to do things. And here you even have nested two cursor loops, so it's "the slowest way to do things" squared.

The fastest way to do it would probably be a straight SQL.

But please read the forum guide first. ("The big, blinking thing at the top of the forum where it says "Read this before posting")

It even has a "Performance Tuning" section with the standard thing that you have to look at/for when you want to speed up a query / procedure.


Re: How to tune/reduce time for this simple procedure ? [message #416238 is a reply to message #416227] Fri, 31 July 2009 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As it is written it is expected that it takes long.
As already said, you can do it in a single SQL statement.

Regards
Michel
Re: How to tune/reduce time for this simple procedure ? [message #416241 is a reply to message #416227] Fri, 31 July 2009 05:30 Go to previous message
sajiv
Messages: 2
Registered: July 2009
Location: INDIA
Junior Member
Ok...I''ll try with sql.
Previous Topic: Compile UTL_FILE PACKAGE BODY
Next Topic: Pivot Query using connect by in 10g
Goto Forum:
  


Current Time: Sun Dec 04 00:35:24 CST 2016

Total time taken to generate the page: 0.12814 seconds