02-12-2009 12:29 PM
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
02-12-2009 12:45 PM
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
02-12-2009 12:45 PM
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
02-12-2009 1:09 PM
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
02-12-2009 3:24 PM
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