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: 

Performance issue fetching huge number of record with "FOR ALL ENTRIES"

Former Member
0 Kudos

Hello,

We need to extract an huge amount of data (about 1.000.000 records) from VBEP table, which overall dimension is about 120 milions records.

We actually use this statements:

CHECK NOT ( it_massive_vbep[] IS INITIAL ) .

SELECT (list of fields) FROM vbep JOIN vbap

ON vbepvbeln = vbapvbeln AND

vbepposnr = vbapposnr

INTO CORRESPONDING FIELDS OF w_sched

FOR ALL ENTRIES IN it_massive_vbep

WHERE vbep~vbeln = it_massive_vbep-tabkey-vbeln

AND vbep~posnr = it_massive_vbep-tabkey-posnr

AND vbep~etenr = it_massive_vbep-tabkey-etenr.

notice that internal table it_massive_vbep contains always records with fully specified key.

Do you think this query could be further optimized?

many thanks,

-Enrico

1 ACCEPTED SOLUTION

former_member194613
Active Contributor
0 Kudos

the are 2 option to improve performance:

+ you should work in blocks of 10.000 to 50.000

+ you should check archiving options, does this really make sense

> VBEP table, which overall dimension is about 120 milions records.


it_massive_vbep  into it_vbep_notsomassive (it_vbep_2)

CHECK NOT ( it_vbep_2[] IS INITIAL ) .

  get runtime field start.
SELECT (+list of fields+) 
              INTO CORRESPONDING FIELDS OF TABLE w_sched
              FROM vbep JOIN vbap
              ON vbep~vbeln = vbap~vbeln AND
                   vbep~posnr = vbap~posnr
              FOR ALL ENTRIES IN it_vbep_2
              WHERE vbep~vbeln = it_vbep_2-vbeln
              AND      vbep~posnr = it_vbep_2-posnr
              AND      vbep~etenr  = it_vbep_2-etenr.

  get runtime field stop.

t = stop - start.
write: / t.

Be aware that even 10.000 will take some time.

Other question, how did you get the 1.000.000 records in it_massive_vbep. They are not typed in, but somehow select.

Change the FAE into a JOIN and it will be much faster.

Siegfried

9 REPLIES 9

Former Member
0 Kudos

I would suggest you use for all entries in packages of say 5000 and use APPENDING TABLE in the select.

Refer to Nekenti's reply in this thread:

https://forums.sdn.sap.com/click.jspa?searchID=12538774&messageID=840359

Former Member
0 Kudos

Hi Enrico,

MOVE CORRESPONDING can cause trouble depending on how many fields are in your list of fields. Another time consuming issue is the structure of your it_massive_vbep structure. This table contains a substructure tabkey.

Hope this helps,

Heinz

Former Member
0 Kudos

Hi,

Are you use select..endselect??? looks like that since you are using INTO CORRESPONDING FIELDS OF w_sched...

Use into CORRESPONIDNG FIELD OF TABLE...and remove the select..endselect statement. If possible, remove the corresponding fields and try to use INTO TABLE directly.

SELECT (list of fields) FROM vbep JOIN vbap

ON vbepvbeln = vbapvbeln AND

vbepposnr = vbapposnr

INTO CORRESPONDING FIELDS OF TABLE w_sched

FOR ALL ENTRIES IN it_massive_vbep

WHERE vbep~vbeln = it_massive_vbep-tabkey-vbeln

AND vbep~posnr = it_massive_vbep-tabkey-posnr

AND vbep~etenr = it_massive_vbep-tabkey-etenr.

Regards,

Shruthi R

0 Kudos

Hi Shruthi,

Sorry. You are not correct with your comment regarding into table. Please take a look at the below thread (I dont have an URL). There is a long discussion about this issue!

Performance "SELECT INTO TABLE" vs. "SELECT IN LOOP"

Regards,

Heinz

former_member194613
Active Contributor
0 Kudos

I would be interested in this one:

Performance "SELECT INTO TABLE" vs. "SELECT IN LOOP"

where can I find it?

Siegfried

0 Kudos

Hello Siegfried,

This thread is to find under ABAP Performance and Tuning. You posted also a message on April 4th. I am sorry not being able to provide you with the proper URL. It doesn't come up on my screen. I don't know why!?

Tschuess,

Heinz

0 Kudos

Hallo Siegfried,

I got it: []

Have fun,

Heinz

former_member194613
Active Contributor
0 Kudos

the are 2 option to improve performance:

+ you should work in blocks of 10.000 to 50.000

+ you should check archiving options, does this really make sense

> VBEP table, which overall dimension is about 120 milions records.


it_massive_vbep  into it_vbep_notsomassive (it_vbep_2)

CHECK NOT ( it_vbep_2[] IS INITIAL ) .

  get runtime field start.
SELECT (+list of fields+) 
              INTO CORRESPONDING FIELDS OF TABLE w_sched
              FROM vbep JOIN vbap
              ON vbep~vbeln = vbap~vbeln AND
                   vbep~posnr = vbap~posnr
              FOR ALL ENTRIES IN it_vbep_2
              WHERE vbep~vbeln = it_vbep_2-vbeln
              AND      vbep~posnr = it_vbep_2-posnr
              AND      vbep~etenr  = it_vbep_2-etenr.

  get runtime field stop.

t = stop - start.
write: / t.

Be aware that even 10.000 will take some time.

Other question, how did you get the 1.000.000 records in it_massive_vbep. They are not typed in, but somehow select.

Change the FAE into a JOIN and it will be much faster.

Siegfried

Former Member
0 Kudos

nope ur working on massive table never use for all entry..

try with joins

read this http://www.sapbrainsonline.com/ARTICLES/TECHNICAL/optimization/optimization.html