Home » SQL & PL/SQL » SQL & PL/SQL » Slow cursor loop
Slow cursor loop [message #222270] Fri, 02 March 2007 10:35 Go to next message
blitz_g
Messages: 3
Registered: March 2007
Junior Member
I have an Oracle 9i db with a proc that is running too slow for my taste. I was wondering what the best practice is to speed up the following:

FOR CUSTGRADES_rec IN CUSTGRADES LOOP
FOR x IN V_START_PER..V_END_PER LOOP

IF x < V_FORECAST_START_PERIOD_NBR THEN
v_voltype := 'a';
ELSE
v_voltype := 'e';
END IF;

INSERT INTO VOLUME
(VOL_LINE_NBR, FORECAST_NBR, PERIOD_NBR, CUSTGRADE_NBR, VOLUME_TYPE, VOLUME)
VALUES
(SEQ_VOL_LINE_NBR.NEXTVAL, V_NEW_FORECAST_NBR, x, CUSTGRADES_rec.CUSTGRADE_NBR, v_voltype, 0);

END LOOP;
END LOOP;
Re: Slow cursor loop [message #222289 is a reply to message #222270] Fri, 02 March 2007 12:20 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
the structure looks fine. You need to determine if the select of CUSTGRADES is taking the time - or the inserts. You should be able to test that easily by commenting out the actual insert.
Re: Slow cursor loop [message #222307 is a reply to message #222270] Fri, 02 March 2007 14:41 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
I'd say that best practise would be to rewrite this as simple
INSERT INTO ... SELECT FROM ...
It would definitely be faster.
Of course there are times when logic of SELECT is probably toooo complicated and cursor loop is required, but on the other hand these cases definitely are less than people are thinking Smile

Gints Plivna
http://www.gplivna.eu
Re: Slow cursor loop [message #222317 is a reply to message #222307] Fri, 02 March 2007 15:48 Go to previous messageGo to next message
blitz_g
Messages: 3
Registered: March 2007
Junior Member
Thx for the tips. I rewrote the loops to the following which cut the time to 5 min. instead of 15+ min. I'm going to try to speed this up some more which is just a portion of the overall proc., but I think it was the main problem.

INSERT INTO VOLUME (VOL_LINE_NBR, FORECAST_NBR, PERIOD_NBR, CUSTGRADE_NBR, VOLUME_TYPE, VOLUME)
(SELECT SEQ_VOL_LINE_NBR.NEXTVAL, V_NEW_FORECAST_NBR, p.PERIOD_NBR, c.CUSTGRADE_NBR,
CASE WHEN p.FISCAL_PERIOD < V_FORECAST_START_PERIOD_NBR THEN 'a' ELSE 'e' END, 0
FROM CUSTOMERGRADE c, PERIOD p WHERE FISCAL_YEAR=V_SETUP_FY);
Re: Slow cursor loop [message #222537 is a reply to message #222317] Mon, 05 March 2007 05:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
FROM CUSTOMERGRADE c, PERIOD p WHERE FISCAL_YEAR=V_SETUP_FY

It looks like you are doing a cartesian join between these two tables - ie if CustomerGrade has N rows, and Period has M, then this query will return NxM rows.

Do you need a join condition between the two tables?
Re: Slow cursor loop [message #222577 is a reply to message #222537] Mon, 05 March 2007 09:01 Go to previous message
blitz_g
Messages: 3
Registered: March 2007
Junior Member
Yes unfortunately I do have to do that. Whomever originally designed the DB schema didn't really think this all through in setting up the customer records for this sales forecast system.
Previous Topic: How to use UPDATE command in TABLE FUNCTION with PIPELINED
Next Topic: first_ value and last_value errors
Goto Forum:
  


Current Time: Sat Dec 10 22:21:11 CST 2016

Total time taken to generate the page: 0.05361 seconds