3 Replies Latest reply: Feb 12, 2009 4:25 PM by Thomas Zloch RSS

Will OPEN CURSOR improve performance

Mudit Batra
Currently Being Moderated

Moved to correct forum by moderator.  Subject changed.  Please use a more informative subject in future.

 

Hi All,

 

I have got a requirement to improve the performance of a report . While debugging i came to know that the program got struck at the FETCH CURSOR  command.

 

 

OPEN CURSOR WITH HOLD cur1 FOR

  • Select data from the purchase order header table.

  SELECT runit rprctr sprctr                                "CR0777

         racct rfarea hsl msl kostl aufnr ps_psp_pnr

          FROM glpca

         WHERE docnr  IN s_docnr

           AND rbukrs =  p_rbukrs

           AND rprctr IN s_rprctr

           AND sprctr IN s_sprctr                           "CR0777

           AND rfarea IN s_rfarea

           AND budat  IN s_budat

           AND kostl  IN s_kostl

           AND aufnr  IN s_aufnr.

 

 

  DO.

  • fetch rows from cursor depending on package size w.kuszelewicz

    FETCH NEXT CURSOR cur1 INTO TABLE t_glpca

       PACKAGE SIZE p_size.

    IF sy-subrc NE 0.

      CLOSE CURSOR cur1.

      EXIT.

    ENDIF.

 

What is the use of PACKAGE SIZE command with FETCH CURSOR? It is in the requirement.

 

 

Any suggestions on this...

 

Regards

Mudit

 

Edited by: Matt on Feb 12, 2009 2:10 PM

  • Re: Performance improvement
    Jayapradha Neeli
    Currently Being Moderated

    Hi,

    Package size is used to process limited amount of data because of  lack of memory.

     

    suppose if more records are there then 1000 records we select, and the next 1000 records can be read.

    Regards,

    jaya

  • Re: Performance improvement
    Matthew Billingham
    Currently Being Moderated

    Please use a more informative subject in future.

     

    If you use SELECT INTO TABLE, then all the data is retrieved from the database in one go.  This can result in running out of memory and other system resources for very large datasets.

     

    Use CURSOR and FETCH, you limit the amount of data selected at a time by the package site.

     

    BW/BI extractors often use this method to extract data a chunk at a time.  Read the ABAP Help of OPEN CURSOR, etc.

     

    It can improve performance, but it depends on how exactly you are handling the data. 

     

    matt

     

    Edited by: Matt on Feb 12, 2009 2:11 PM

  • Re: Will OPEN CURSOR improve performance
    Thomas Zloch
    Currently Being Moderated

    PACKAGE SIZE is also available with SELECT. The combination with OPEN CURSOR ... WITH HOLD allows package processing of tasks that require a database commit inside the loop. A normal SELECT loop without holding the cursor would be interrupted by a commit -> short dump.

    Other than that I see not much reason to use OPEN CURSOR and FETCH in Open SQL.

    Thomas

Actions