topic Re: Will OPEN CURSOR improve performance in Application Development and Automation Discussions

Will OPEN CURSOR improve performance

Former Member — Thu, 12 Feb 2009 12:29:06 GMT

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: Will OPEN CURSOR improve performance

Former Member — Thu, 12 Feb 2009 12:45:38 GMT

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: Will OPEN CURSOR improve performance

matt — Thu, 12 Feb 2009 13:09:19 GMT

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

ThomasZloch — Thu, 12 Feb 2009 15:24:22 GMT

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