3 Replies Latest reply: Sep 10, 2013 11:43 AM by user10857924 RSS

    Procedure running slow Need inputs for Performance Improovments

    yadala

      we have 3 tables and we need to update the date in one table based on criteria. I had created a procedure and it is taking hours to complete. can any one help me how I can improve the performance.

       

      Here is the procedure

      ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

      DECLARE

         CURSOR c1 is

            select distinct  TO_DATE(po.INTRO_DATE, 'MM/DD/YYYY'), po.article_num,  po.season from  R_INTRO_DATE po where po.band='26' ; 

         introDate Date;

         num1 VARCHAR2(20);

         code VARCHAR2(20);

         sqlquery VARCHAR2(2000);

      BEGIN

         OPEN c1;

           FETCH c1 INTO  introDate, num1 , code  ;

          WHILE c1%FOUND loop

           sqlquery := 'update spl  set date2=TO_DATE(''' ||introDate||''', ''DD-MON-YYYY'') where oid=(select distinct spl.oid from spl,seas, sku where seas.att18=''';

          sqlquery := sqlquery ||code ||'''  and sku.att23=''';

          sqlquery := sqlquery ||num1 ||'''  and sku.master=lspl.skuid and seas.master=spl.a3b5)';

            EXECUTE IMMEDIATE (sqlquery);

              FETCH c1 INTO  introDate, num1 , code  ;

         END LOOP;

         CLOSE c1;

      END;

      Regards

      Yadala