How to Optimize code?

[sreenath205]

Hi All

I have this Procedure which works fine for a smaller dataset.But for larger dataset its going on and on.Please let me know if there can be some optimization done to this code?

The issue is if the first cursor returns less records ie 100 or so it excutres in no time but when the resulkt set is larger say in thousands the script is a killer.

CREATE OR REPLACE procedure test AS

s1 NUMBER(6);
s2 NUMBER(6);
s3 NUMBER(6);
s4 float;
scount NUMBER(6);

CURSOR c1 IS
SELECT
ship_to_customer_number ,
product_number,
quarter,
month,
year
FROM
testtable
GROUP BY
ship_to_customer_number ,
product_number,
quarter,
month,
year;

CURSOR c2(ship_to_customer_number1 VARCHAR2,
product_number1 VARCHAR2,
quarter1 VARCHAR2,
year1 VARCHAR2) IS
SELECT
ship_to_customer_number ,
product_number,
quarter,
month,
sum(vquantity) s1
FROM
testtable
WHERE
ship_to_customer_number = ship_to_customer_number1
AND product_number = product_number1
AND quarter=quarter1
AND year = year1
GROUP BY
ship_to_customer_number,
product_number,
quarter,
month,
year
ORDER BY
month;


counter NUMBER := 0;
BEGIN

FOR rec in c1
LOOP
counter := counter + 1;

scount:=0;
FOR rec2 IN c2(rec.ship_to_customer_number,rec.product_number,rec.quarter,rec.year)
LOOP

scount := scount + 1;

IF scount = 1
THEN
s1 := rec2.s1;
END IF;

IF scount = 2
THEN
s2 := rec2.s1;
s4 := ((s1 + s2) / 2) / 1;
END IF;

IF scount = 3
THEN
s3:= rec2.s1;
s4:= ((s1 + s2) / 2) / s3;

END IF;

END LOOP;

IF scount =3
THEN
UPDATE testtable t
SET t.linearity = s4
WHERE
t. ship_to_customer_number = rec.Ship_to_Customer_Number
AND t.product_number = rec.product_number
AND t.quarter = rec.quarter
AND t.month = rec.month
AND t.year = rec.year;



END IF;

IF (counter = 10)
THEN
COMMIT WORK;

counter := 0;
END IF;


END LOOP;

COMMIT WORK;
DBMS_OUTPUT.PUT_LINE('-- commmit: ' || counter );

EXCEPTION
WHEN OTHERS
THEN
raise_application_error(-20001, 'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);

END test;
/
SHOW ERRORS;

Thanks

 

[Thargy]

It is impossible to optimise your code without further information. However, at first glance, it appears to be doing some simple processing between two tables.

I therefore suspect that this is a classic example of "never do in code what can be done in sql". Can you post the relevant create table statements, insert statements to populate them with sample data (anonymised if required) and an English language statement of what is supposed to be done with the data, and a sample of the desired output.

That way it is possible for us all to see what you're after, and perhaps suggest possible sql alternatives, which are almost invariably faster. It may be that pl/sql is the only way to achieve what you want, but SQL should at least be considered.

Regards

T

 

[LKBrwnDBA]

Also include an approximate number of rows for each table.

PS: Try using the 'code' '/code' tags (enclosed in square braackets) to format your code.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Dagon]

There are two likely areas for the performance problem:

a) The inner cursor c2, which takes parameters from c1, is not optimized. Check there is an index on some or all of the columns ship_to_customer_number, product_number, quarter and year, and that the cardinality of these columns is high.

b) The update is slow, again because of no indexes on the above columns.

Also, your logic doesn't seem to make much sense. The outer cursor c1 contains month but then month is eliminated in the parameters passed to cursor c2. This means you must be calling c2 multiple times with identical parameters. I suggest removing month from c1 and see if that changes things.

 

[LKBrwnDBA]

Assuming you have made the month changes suggested by Dagon, here is a (not tested) starter code as template to optimize your procedure given you also have created the suggested indexes:

CREATE OR REPLACE procedure MyTest AS
-- Etc --
Cursor C1 Is
Select Ship_To_Customer_Number, Product_Number
     , Year, Quarter
     , ((Max(Decode(Mth,1,Sumq,0))+Max(Decode(Mth,2,Sumq,0)))/2)/Max(Decode(Mth,3,Sumq,0))) S4
     , Max(Mth) Num_Mths
  From (
Select Ship_To_Customer_Number, Product_Number
     , Year, Quarter, Month
     , Sum(Vquantity) Sumq
     , Row_Number() Over (Order By Month) Mth
  From Testtable
 Group By Ship_To_Customer_Number, Product_Number, Year, Quarter, Month
 Having Sum(Vquantity) > 0)
  Where Mth <= 3;
Type C1_Row Is Table Of C1%Rowtype;
Rec C1_Row;
I Pls_Integer;
-- Etc --
Begin
  Open C1;
  Loop
    Fetch C1 Bulk Collect Into Rec Limit 10000;
    Exit When Rec.Count = 0;
    Forall I In Rec.First..Rec.Last
      Update Testtable U
         Set Linearity = Rec(I).S4
       Where U.Ship_To_Customer_Number = Rec(I).Ship_To_Customer_Number
         And U.Product_Number = Rec(I).Product_Number
         And U.Year = Rec(I).Year;
         And U.Quarter = Rec(I).Quarter;
  End Loop;
End;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Somwhat more simple:

-- Etc --
Cursor C1 Is
Select Ship_To_Customer_Number, Product_Number, Year, Quarter
     , Sum(decode(mod(month,3),1,Vquantity,0)) S1
     , Sum(decode(mod(month,3),2,Vquantity,0)) S2
     , Sum(decode(mod(month,3),0,Vquantity,0)) S3
  From Testtable
 Group By Ship_To_Customer_Number, Product_Number, Year, Quarter;
Type C1_Row Is Table Of C1%Rowtype;
Rec C1_Row;
I Pls_Integer;
-- Etc --
Begin
  Open C1;
  Loop
    Fetch C1 Bulk Collect Into Rec Limit 10000;
    Exit When Rec.Count = 0;
    Forall I In Rec.First..Rec.Last
      Update Testtable U
         Set Linearity = ((Rec(I).S1+Rec(I).S2)/2)/Rec(I).S4
       Where U.Ship_To_Customer_Number = Rec(I).Ship_To_Customer_Number
         And U.Product_Number = Rec(I).Product_Number
         And U.Year = Rec(I).Year;
         And U.Quarter = Rec(I).Quarter
         and Rec(I).S4 != 0;
  End Loop;
End;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb