1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Cursor select query taking long time

Discussion in 'SQL PL/SQL' started by bhola, Sep 22, 2014.

  1. bhola

    bhola Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    Dear All,

    I have written a procedure inside which I have declared 2 cursors. Cursor 1 is calling Cursor 2 on the flow.

    Cursor 1 - It fetches a list of applications along with all details from the database as per the given date. Below is the cursor query.


    Cursor 2 - I have another table which contains verification messages related to each application. One application will have multiple verification messages. I just want to fetch the latest message from the table by passing the application id.

    While fetching the data for 1 month period its takes approximately about 30 minutes about 500-1000 records. Without the cursor 2 the same data fetching takes only 1 minute to fetch. Can anyone please help me with optimizing the queries ?



    Thanks,
    Bhola
     
  2. bhola

    bhola Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    Queries used are below -

    Cursor 2 -
    CURSOR C2 IS
    select APPID, MESSAGE, MESSAGEDATE
    from (select T1.HORIGINATIONS$APPLICATIONID APPID, T2.MESSAGE MESSAGE, T2.OCCURENCETIMESTAMP MESSAGEDATE, row_number()
    over (partition by T1.HORIGINATIONS$APPLICATIONID order by T2.OCCURENCETIMESTAMP desc) ROW_NUMBER
    FROM HORG_ODS.HORIGINA$VERIFICATION_APPLICAT T1,
    HORG_ODS.HORIGINATION$APPLICATIONMESSAG T2
    WHERE T2.ID = T1.HORIGINATION$APPLICATIONMESSAG
    AND T2.CODE = 'OAM:0152'
    and T1.HORIGINATIONS$APPLICATIONID = VARC1.APPID) r
    where r.ROW_NUMBER=1;
    VARC10 C2%ROWTYPE;
     
  3. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Wellll....I only see one query and it looks to be the second cursor

    There's no information about the tables in question.

    How many rows are returned in the first cursor?

    How are you accessing the cursor...where's that code?

    You are assuming the problem is the second cursor...not necessarily true.


    CJ
     
    bhola likes this.
  4. bhola

    bhola Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    Hi ,

    I have attached the complete procedure code. Please have a look into it .

    My first cursor is fetching approximately 1000 - 2000 records. For each record I am calling other cursors by passing the record id.

    e.g : For applicationid 101 , I am calling 3 or 4 other cursors similar to the cursor2 code I have pasted before. You can find that in attachment with the name 'C9, C10, C11'. Without these 3 cursors the whole procedure works fine.

    Can you please help me if there is any optimized way to include the logic of C9, C10, C11 within C1 itself so that I have to avoid necessary cursors.

    Much appreciate for your help. Thanks again.

    Bhola
     

    Attached Files:

  5. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Just one observation for the moment...you are not using BULK_COLLECT and probably should be. The only other thing that really bothers me about this is the single Procedure is doing far too much. Have you thought about breaking it up into smaller procedures and throwing them all into a package?
     
    bhola likes this.
  6. bhola

    bhola Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    Thanks for the help. Will try to do it through bulk_collect. Hope it gives a better performance.
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation