Home » SQL & PL/SQL » SQL & PL/SQL » How to optimize this code?
How to optimize this code? [message #356010] Wed, 29 October 2008 00:51 Go to next message
newToPlsql
Messages: 4
Registered: October 2008
Junior Member
Hi All

I have a procedure which runs fine,but since the data in table XYZ,ABC runs into millions it becomes too slow..Can any one guide me in optimising the code.I have already created indexes on these columns

CREATE OR REPLACE PROCEDURE test AS
    CURSOR c1 IS
        SELECT
            t.cutGrp,
            t.cutGrpCode,
            t.modelDesc,
            t.startdate,
            t.enddate,
            avg(t.price) price,
            avg(t.netprice) netprice, 
            sum(t.awdQTY) awardqty,
            sum(t.planQTY) revplanqty,
            
        FROM
            ABC t
        GROUP BY 
            t.cutGrp,
            t.cutGrpCode,
            t.modelDesc,
            t.startdate,
            t.enddate;
 
counter NUMBER := 0;
l_aggregate NUMBER;
BEGIN
     FOR rec IN c1
     LOOP
        counter := counter + 1;
 
        SELECT
            sum(t.anotherQTY) INTO l_aggregate
        FROM
             XYZ t
        WHERE
            t.cutstomer_Grp = rec.cutGrp
            AND t.cutGrp_Code = rec.cutGrpCode
            AND t.product_model = rec.modelDesc;
            
        UPDATE XYZ t 
        SET
            t.testQTY = l_aggregate,
            t.testprice = rec.price,
            t.testnetprice = rec.netprice,
            t.testawardQTY = rec.awardqty,
            t.testplanQTY = rec.revplanqty,          
            t.fcstartdate = rec.startdate,
            t.fcenddate = rec.enddate
        WHERE
            t.cutstomer_Grp = rec.cutGrp
            AND t.cutGrp_Code = rec.cutGrpCode
            AND t.cutGrp_Code = rec.modelDesc;
           
        
 
    END LOOP;
 
    COMMIT WORK;
 
END test;
/ 
SHOW ERRORS;

Re: How to optimize this code? [message #356013 is a reply to message #356010] Wed, 29 October 2008 01:12 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Why to use cursor when you can achieve this via SQL
Statements.

Cursor approach gonna be slow.You can use Bulk Collect
feature but that will not help you much.

Try to modify your code and use some analytical functions.

Regards,
Rajat Ratewal
Re: How to optimize this code? [message #356017 is a reply to message #356010] Wed, 29 October 2008 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the whole PL/SQL code and do it in a single SQL statement.

Regards
Michel
Re: How to optimize this code? [message #356021 is a reply to message #356010] Wed, 29 October 2008 01:31 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

CREATE OR REPLACE PROCEDURE test AS
CURSOR c1 IS
SELECT
t.cutGrp,
t.cutGrpCode,
t.modelDesc,
t.startdate,
t.enddate,
avg(t.price) price,
avg(t.netprice) netprice,
sum(t.awdQTY) awardqty,
sum(t.planQTY) revplanqty,
FROM
ABC t
GROUP BY
t.cutGrp,
t.cutGrpCode,
t.modelDesc,
t.startdate,
t.enddate;

counter NUMBER := 0;
l_aggregate NUMBER;
Rec c1%Rowtype;
BEGIN
Open cl;
LOOP
Fetch c1 into Rec;
Exit when c1%notfound;
counter := counter + 1;

SELECT
sum(t.anotherQTY) INTO l_aggregate
FROM
XYZ t
WHERE
t.cutstomer_Grp = rec.cutGrp
AND t.cutGrp_Code = rec.cutGrpCode
AND t.product_model = rec.modelDesc;

UPDATE XYZ t
SET
t.testQTY = l_aggregate,
t.testprice = rec.price,
t.testnetprice = rec.netprice,
t.testawardQTY = rec.awardqty,
t.testplanQTY = rec.revplanqty,
t.fcstartdate = rec.startdate,
t.fcenddate = rec.enddate
WHERE
t.cutstomer_Grp = rec.cutGrp
AND t.cutGrp_Code = rec.cutGrpCode
AND t.cutGrp_Code = rec.modelDesc;



END LOOP;
Close C1;
COMMIT WORK;

END test;

-------------Try It.------------------
Re: How to optimize this code? [message #356024 is a reply to message #356021] Wed, 29 October 2008 01:33 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

In addition, we already said that PL/SQL is BAD in this case, and you code will not help in any case.
By the way, what is the difference with the original one?

Regards
Michel

[Updated on: Wed, 29 October 2008 01:35]

Report message to a moderator

Previous Topic: Using variable
Next Topic: order by relation 'father' , 'mother'
Goto Forum:
  


Current Time: Tue Dec 06 02:39:18 CST 2016

Total time taken to generate the page: 0.14588 seconds