06-04-2008 9:06 PM
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
06-09-2008 8:21 AM
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
06-04-2008 9:17 PM
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
06-05-2008 1:58 AM
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
06-05-2008 4:36 AM
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
06-05-2008 5:21 PM
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
06-06-2008 8:22 AM
I would be interested in this one:
Performance "SELECT INTO TABLE" vs. "SELECT IN LOOP"
where can I find it?
Siegfried
06-06-2008 9:09 PM
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
06-07-2008 2:49 AM
06-09-2008 8:21 AM
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
06-10-2008 5:51 AM
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