Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Regarding usage of PACKAGE SIZE using SELECT ... ENDSELECT & OPEN CURSOR

SuhaSaha
Advisor
Advisor
0 Kudos

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 !!

1 ACCEPTED SOLUTION

kesavadas_thekkillath
Active Contributor
0 Kudos

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

13 REPLIES 13

kesavadas_thekkillath
Active Contributor
0 Kudos

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

0 Kudos

I have used OPEN CURSOR in many scenarios and greatly increases the performance.

0 Kudos

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.

Former Member
0 Kudos

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

0 Kudos

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.

SuhaSaha
Advisor
Advisor
0 Kudos

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

venkat_o
Active Contributor
0 Kudos

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.

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.
<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.
FETCH NEXT CURSOR c1 APPENDING TABLE it_nfal PACKAGE SIZE no_of_lines.
Thanks Venkat.O

Former Member
0 Kudos

>

>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

venkat_o
Active Contributor
0 Kudos

Advait Gode,


 I dont think you can use package size with FETCH statement.
<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

0 Kudos

Hi Advaith Its possible.

Check my code.

raymond_giuseppi
Active Contributor
0 Kudos

Look at SAP documentation on General Performance Advice for Open SQL

.

Regards,

Raymond

Former Member
0 Kudos

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

0 Kudos

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.