11-04-2009 11:15 AM
Hello Gurus,
What is the difference if we use PACKAGE SIZE with SELECT ... ENDSELECT & when using OPEN CURSOR?
Also does OPEN CURSOR offer any added advantages over SELECT ?
BR,
Suhas
PS: Pardon me if this has been discussed in SDN, i have searched without success !!
11-04-2009 11:25 AM
Hi Suhas,
Package size could be used with cursor also.
data s_cursor type cursor.
DATA:it_mara TYPE TABLE OF mara.
OPEN CURSOR WITH HOLD s_cursor FOR
SELECT * FROM mara.
DO.
FETCH NEXT CURSOR s_cursor APPENDING TABLE it_mara PACKAGE SIZE 1000.
IF sy-subrc <> 0.
EXIT.
ENDIF.
ENDDO.
Its the difference b/w select..Endselect and Cursors
Read this link:[http://www.sapfans.com/forums/viewtopic.php?t=231963]
link:[Performance analysis select..endselect & Cursors|http://wiki.sdn.sap.com/wiki/display/ABAP/SELECTStatementsandCURSORstatement-Performance+Analysis.]
Edited by: Keshu Thekkillam on Nov 4, 2009 5:17 PM
11-04-2009 11:25 AM
Hi Suhas,
Package size could be used with cursor also.
data s_cursor type cursor.
DATA:it_mara TYPE TABLE OF mara.
OPEN CURSOR WITH HOLD s_cursor FOR
SELECT * FROM mara.
DO.
FETCH NEXT CURSOR s_cursor APPENDING TABLE it_mara PACKAGE SIZE 1000.
IF sy-subrc <> 0.
EXIT.
ENDIF.
ENDDO.
Its the difference b/w select..Endselect and Cursors
Read this link:[http://www.sapfans.com/forums/viewtopic.php?t=231963]
link:[Performance analysis select..endselect & Cursors|http://wiki.sdn.sap.com/wiki/display/ABAP/SELECTStatementsandCURSORstatement-Performance+Analysis.]
Edited by: Keshu Thekkillam on Nov 4, 2009 5:17 PM
11-04-2009 11:55 AM
I have used OPEN CURSOR in many scenarios and greatly increases the performance.
07-28-2015 8:06 AM
hi kesavadas,
i seen your so many answers in SCN.SAP . would please give me a conclutionmto my doubt?
if we didnt mention size of the package in fetch statement, how it will work? is it get all records in the database table. my database table have nearly 2 lakh records.please clear my doubts on package.
i writing some programme in bw, so i am looking at performance point of view.
11-04-2009 12:53 PM
Hi,
What I think the difference is that with PACKAGE SIZE , you can restrict the number of records that are fetched from the database with a single connection to the database. Here the db connection and the data transfer take place at the same time.
This is also true with normal SELECT into or SELECT ...ENDSELECT.
On the other hand OPEN cursor only establishes a db connection, but the data transfer only takes place with the subsequent fetch statement. Thus you can fetch the data only when required in the program.
This allows you to decouple the process of opening the connection and fetching the data which is not possible in normal select/select end select.
Hope this helps.
Br,
Advait
07-28-2015 8:38 AM
i seen your so many answers in SCN.SAP . would please give me a conclutionmto my doubt?
if we didnt mention size of the package in fetch statement, how it will work? is it get all records in the database table. my database table have nearly 2 lakh records.please clear my doubts on package.
i writing some programme in bw, so i am looking at performance point of view.
11-04-2009 1:09 PM
Hello Keshu,
Apologies first, i did not search the WIKI section
I read the wiki & i understand that PACKAGE SIZE in SELECT ... ENDSELECT will result in repeititive hits to the DB which is expensive.
But with OPEN CURSOR ... FETCH ... CLOSE CURSOR the DB hit is restricted to once. And for every FETCH the data gets populated into the target area.
Correct me if i am wrong.
BR,
Suhas
11-04-2009 1:38 PM
Hi Suhas,
<li>If you use PACKAGE SIZE n(lets say n = 100) between SELECT-ENDSELECT, 100 records are brought into table it_tab internal table for every loop.
<li>Here PACKAGE SIZE must be used with only SELECT-ENDSELECT.
<li>If you use PACKAGE SIZE n(lets say n = 100) with FETCH statement like below statement, 100 records are brought into table it_tab at once.
SELECT carrid connid
FROM spfli
INTO CORRESPONDING FIELDS OF TABLE itab
PACKAGE SIZE 3.
LOOP AT itab INTO wa.
WRITE: / wa-carrid, wa-connid.
ENDLOOP.
ENDSELECT.
Thanks
Venkat.OFETCH NEXT CURSOR c1 APPENDING TABLE it_nfal PACKAGE SIZE no_of_lines.
11-04-2009 1:45 PM
>
>If you use PACKAGE SIZE n(lets say n = 100) with FETCH statement like below statement, 100 records are brought into table it_tab at once.
>FETCH NEXT CURSOR c1 APPENDING TABLE it_nfal PACKAGE SIZE no_of_lines
>
I dont think you can use package size with FETCH statement.
Suhas,
Yes you are right when you said that the with PACKAGE or even without package in the select statment, the data is directly populated in the target area.
But with cursor , the data is populated only when the fetch statement is encountered.
Br,
Advait
P.S : Read the first line in the help documentation [here|http://help.sap.com/saphelp_47x200/helpdata/EN/fc/eb3b23358411d1829f0000e829fbfe/frameset.htm]
Edited by: Advait Gode on Nov 4, 2009 2:47 PM
11-04-2009 1:54 PM
Advait Gode,
<li>You should not think and come with facts when you raise doubt on others reply.
<li> Please press F1 on FETCH statement.
Thanks
Venkat.O
I dont think you can use package size with FETCH statement.
11-05-2009 4:38 AM
11-04-2009 2:21 PM
11-04-2009 2:39 PM
You won't find much because they are different things and not really performance related.
You can use the PACKAGE SIZE addition to manage memory.
You can use a CURSOR to manage position while SELECTing from a table. (You can have two CURSORs open while SELECTing different data from the same table.)
They have probably not been discussed together much, but each has been discussed individually many times. Sometimes, that's the way you have to look.
Rob
07-28-2015 8:39 AM
i seen your so many answers in SCN.SAP . would please give me a conclutionmto my doubt?
if we didnt mention size of the package in fetch statement, how it will work? is it get all records in the database table. my database table have nearly 2 lakh records.please clear my doubts on package.
i writing some programme in bw, so i am looking at performance point of view.