Forum Stats

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

Discussions

Procedure taking too much time to complete

2»

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Sudhir_Meru wrote:
    
    Hi Karthic,
    
      Thanks for your reply. Step 2 Procedure worked Perfect how do I do Step 3 Procedure Please suggest
    
    Thanks
    Sudhir
    

    Your requirement can be done in single INSERT statement as already mentioned by me. Why do you want to split it and make it slow?

  • Sudhir_Meru
    Sudhir_Meru Member Posts: 327

    Hi Karthic,

      Step 3 is to update CONTRACT_NUMBER which is given in Step 3 In the insert statement you are inserting CONTRACT_NUMBER directly only the SERIAL_NUMBER, MAX(SERVICE_END_DATE) CONTRACT_NUMBER must be inserted. Please suggest.

    Thanks

    Sudhir

  • Sudhir_Meru
    Sudhir_Meru Member Posts: 327

    Thanks Moazzam Tried your method after applying INDEXES as you suggested its fast takes less than a minute to complete the Update Process.

    Thanks

    Sudhir

  • Four suggestions:

    1. Write modular code - don't use one procedure (proc 1) to do TWO separate things. Write a CONTROL procedure to manage the work to be done.

    That control procedure can then do any appropriate logging/timing to track what happens.

    RENEWAL_ASSET_ALL()
        RENEWAL_ASSET_ONE();
        RENEWAL_ASSET_TWO();
        RENEWAL_ASSET_THREE();
    

    2. Add exception handlers and logging to ALL procedures - create/use a custom logging package and insert calls to that package in the procedures.

    3. Modify that first procedure to

       a. remove the calls to the other procedures so that it does ONE thing.

       b. use a simple SQL query to populate the work table - you don't need PL/SQL or collections for that at all

    4. Create an actual Global Temporary Table instead of whatever you are using now.

This discussion has been closed.