Forum Stats

  • 3,815,708 Users
  • 2,259,070 Discussions
  • 7,893,213 Comments

Discussions

Procedure taking too much time to complete

Sudhir_Meru
Sudhir_Meru Member Posts: 327
edited Apr 2, 2014 1:00PM in SQL & PL/SQL

Hi, 

I am doing a DML action on a table. below are the steps used to perform the action. 

There are two tables IB_SUPPORT_CONTRACTS_MV which has 174435 records and RENEWAL_ASSETS_TEMPS is a Temporary table  

Step 1: Created a Procedure ( I am inserting all the row from IB_SUPPORT_CONTRACTS_MV and Inserting NULL records into below columns )

create or replace PROCEDURE renewal_asset_one AS

  CURSOR s_cur

  IS

SELECT    

    SERIAL_NUMBER,

    NULL SERVICE_START_DATE,

    NULL SERVICE_END_DATE,

    NULL IB_CREATION_DATE,

    NULL AS MIN_SERVICE_START_DATE,

    NULL AS MAX_SERVICE_END_DATE,

    NULL   AS MIN_IB_CREATION_DATE,

    NULL CONTRACT_NUMBER,

    ITEM_DESCRIPTION PRODUCT_ID

    FROM IB_SUPPORT_CONTRACTS_MV

WHERE

LENGTH(SERIAL_NUMBER) > 10 ;

   

TYPE fetch_array

IS

  TABLE OF s_cur%ROWTYPE INDEX BY PLS_INTEGER;

  s_array fetch_array;

BEGIN

 

DELETE FROM RENEWAL_ASSETS_TEMPS;

COMMIT;

  OPEN s_cur;

  

    FETCH s_cur BULK COLLECT INTO s_array;

    FORALL i IN 1..s_array.COUNT

    INSERT INTO RENEWAL_ASSETS_TEMPS VALUES s_array

      (i

      );

   

  CLOSE s_cur;

  COMMIT; 

RENEWAL_ASSET_TWO();

RENEWAL_ASSET_THREE();

 

END renewal_asset_one;



Step 2:  I am updating table RENEWAL_ASSETS_TEMPS using BULK collect (This procedure is taking lot of time to update)


create or replace PROCEDURE RENEWAL_ASSET_TWO AS

  CURSOR s_cur

  IS

SELECT

    SERIAL_NUMBER,

    MIN(SERVICE_START_DATE) AS MIN_SERVICE_START_DATE,

      MAX(SERVICE_END_DATE) AS MAX_SERVICE_END_DATE,

    MIN(IB_CREATION_DATE)   AS MIN_IB_CREATION_DATE

    FROM IB_SUPPORT_CONTRACTS_MV

    WHERE

    LENGTH(SERIAL_NUMBER) > 10 

    GROUP BY SERIAL_NUMBER ;

   

TYPE fetch_array

IS

  TABLE OF s_cur%ROWTYPE INDEX BY PLS_INTEGER;

  s_array fetch_array;

 

BEGIN

  OPEN s_cur;

  

    FETCH s_cur BULK COLLECT INTO s_array;

    FORALL i IN 1..s_array.COUNT

    

     UPDATE RENEWAL_ASSETS_TEMPS

     SET

     MIN_SERVICE_START_DATE = s_array(i).MIN_SERVICE_START_DATE,

     MAX_SERVICE_END_DATE   = s_array(i).MAX_SERVICE_END_DATE,

     MIN_IB_CREATION_DATE   = s_array(i).MIN_IB_CREATION_DATE

     WHERE

     SERIAL_NUMBER = s_array(i).SERIAL_NUMBER;

   

  CLOSE s_cur;

  COMMIT; 

 

END;



Step 3: I am doing a final update on same table RENEWAL_ASSETS_TEMPS ( Even this Procedure is taking lot of time to update)


create or replace PROCEDURE RENEWAL_ASSET_THREE AS

CURSOR u_crr

  IS

SELECT

IBV.SERIAL_NUMBER,

RA.MAX_SERVICE_END_DATE,

IBV.CONTRACT_NUMBER

FROM

RENEWAL_ASSETS_TEMPS RA,

IB_SUPPORT_CONTRACTS_MV IBV

WHERE

LENGTH(IBV.SERIAL_NUMBER) > 10 AND

RA.SERIAL_NUMBER = IBV.SERIAL_NUMBER AND

RA.MAX_SERVICE_END_DATE = IBV.SERVICE_END_DATE;

TYPE s_curr

IS

  TABLE OF u_crr%ROWTYPE INDEX BY PLS_INTEGER;

  up_crr s_curr;

BEGIN

  OPEN u_crr;

    FETCH u_crr BULK COLLECT INTO up_crr ;

    FORALL i IN 1..up_crr.count

    UPDATE RENEWAL_ASSETS_TEMPS

    SET CONTRACT_NUMBER        = up_crr(i).CONTRACT_NUMBER

    WHERE SERIAL_NUMBER        = up_crr(i).SERIAL_NUMBER

    AND MAX_SERVICE_END_DATE = up_crr(i).MAX_SERVICE_END_DATE;

  CLOSE u_crr;

  COMMIT;

END;



Please suggest me how to improve performance to update the procedure.


Thanks

Sudhir

PabloleeSudhir_Meruswapnil kambli
«1

Answers

  • Sujoy
    Sujoy Member Posts: 305 Bronze Badge

    Hi Sudhir,

    For a start, you can generate the explain plan for the individual SQL statements - SELECT, UPDATE etc - and modify them for improvements.

    Regards,

    Sujoy

  • Pablolee
    Pablolee Member Posts: 405 Bronze Badge

    Well, without looking at it in too much depth, you are deleting all rows in RENEWAL_ASSETS_TEMPS so instead of doing a delete, do a truncate on that table.

    Instead of using an explicit cursor, just do the insert in SQL:

    insert into renewal_asset_temps 
    SELECT     
        SERIAL_NUMBER,
        NULL SERVICE_START_DATE,
        NULL SERVICE_END_DATE,
        NULL IB_CREATION_DATE,
        NULL AS MIN_SERVICE_START_DATE,
        NULL AS MAX_SERVICE_END_DATE,
        NULL   AS MIN_IB_CREATION_DATE,
        NULL CONTRACT_NUMBER,
        ITEM_DESCRIPTION PRODUCT_ID
        FROM IB_SUPPORT_CONTRACTS_MV
    WHERE
    LENGTH(SERIAL_NUMBER) > 10
    

    Use similar techniques in the rest of your code i.e. use plain SQL instead of looping through cursors.

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    All the 3 procedure can be converted into one INSERT statement like this.

    insert 
      into renewal_assets_temps
           (
               serial_number
             , service_start_date
             , service_end_date
             , ib_creation_date
             , min_service_start_date
             , max_service_end_date
             , min_ib_creation_date
             , contract_number
             , product_id
           )
    select serial_number
         , service_start_date
         , service_end_date
         , ib_creation_date
         , min(service_start_date) over (partition by serial_number)
         , max(service_end_date)   over (partition by serial_number)
         , min(ib_creation_date)   over (partition by serial_number)
         , contract_number
         , item_description
      from ib_support_contracts_mv
     where length(serial_number) > 10;
    
    PabloleeSudhir_Meruswapnil kambli
  • Sudhir_Meru
    Sudhir_Meru Member Posts: 327

    Hi Karthic,

      Thanks for your reply. Step 2 Procedure worked Perfect how do I do Step 3 Procedure Please suggest

    Thanks

    Sudhir

  •     UPDATE RENEWAL_ASSETS_TEMPS

        SET CONTRACT_NUMBER        = (SELECT IBV.CONTRACT_NUMBER

                                    FROM

      RENEWAL_ASSETS_TEMPS RA,

      IB_SUPPORT_CONTRACTS_MV IBV

      WHERE

      LENGTH(IBV.SERIAL_NUMBER) > 10 AND

      RA.SERIAL_NUMBER = IBV.SERIAL_NUMBER AND

      RA.MAX_SERVICE_END_DATE = IBV.SERVICE_END_DATE)

      WHERE SERIAL_NUMBER        = (SELECT IBV.SERIAL_NUMBER

                                    FROM

      RENEWAL_ASSETS_TEMPS RA,

      IB_SUPPORT_CONTRACTS_MV IBV

      WHERE

      LENGTH(IBV.SERIAL_NUMBER) > 10 AND

      RA.SERIAL_NUMBER = IBV.SERIAL_NUMBER AND

      RA.MAX_SERVICE_END_DATE = IBV.SERVICE_END_DATE)

      AND MAX_SERVICE_END_DATE = (SELECT RA.MAX_SERVICE_END_DATE

                                    FROM

      RENEWAL_ASSETS_TEMPS RA,

      IB_SUPPORT_CONTRACTS_MV IBV

      WHERE

      LENGTH(IBV.SERIAL_NUMBER) > 10 AND

      RA.SERIAL_NUMBER = IBV.SERIAL_NUMBER AND

      RA.MAX_SERVICE_END_DATE = IBV.SERVICE_END_DATE);

      COMMIT;

    If I'm not wrong this should be the same thing, but i'm not sure this one is faster tham the original one, try it.

    Cyrille

    http://www.dba-scripts.com

  • Sudhir_Meru
    Sudhir_Meru Member Posts: 327

    Hi Cyrillie,

      Thanks for your reply your Update Statement is returning error with more than one value to SET. Please suggest me how to modify.,

    Thanks
    Sudhir

  • Hi Sudhir,

    sorry my first query is completly wrong, I,m not an expert is SQL or PL/SQL but I think this is the equivalent :

        UPDATE RENEWAL_ASSETS_TEMPS RA

        SET CONTRACT_NUMBER        = (SELECT IBV.CONTRACT_NUMBER

      FROM

      IB_SUPPORT_CONTRACTS_MV IBV

      WHERE

      LENGTH(IBV.SERIAL_NUMBER) > 10 AND

      RA.SERIAL_NUMBER = IBV.SERIAL_NUMBER AND

      RA.MAX_SERVICE_END_DATE = IBV.SERVICE_END_DATE);

    Cyrille

  • Sudhir_Meru
    Sudhir_Meru Member Posts: 327

    Thank Cyrille for your reply. This query you pinged me is taking too much time to update. Need some experts to suggest how to tune the query

    Thanks

    Sudhir

  • Sudhir_Meru
    Sudhir_Meru Member Posts: 327

    Hi Karthic,

      Can we add Procedure 3 update in you insert statement. Please suggest

    Thanks

    Sudhir

  • Moazzam
    Moazzam Member Posts: 1,356

    Try the below one:

    update RENEWAL_ASSETS_TEMPS RA
    set RA.CONTRACT_NUMBER in ( select distinct CONTRACT_NUMBER
                            from IB_SUPPORT_CONTRACTS_MV IBV
      where LENGTH(IBV.SERIAL_NUMBER) > 10 
      and RA.SERIAL_NUMBER = IBV.SERIAL_NUMBER
      and RA.MAX_SERVICE_END_DATE = IBV.MAX_SERVICE_END_DATE)
    


    Also ensure that an index exists in IB_SUPPORT_CONTRACTS_MV table on SERIAL_NUMBER column. Also there should be index on RENEWAL_ASSETS_TEMPS for (SERIAL_NUMBER, MAX_SERVICE_END_DATE) columns

    Sudhir_Meru
This discussion has been closed.