FOR ALL ENTRIES versus a Loop

[dean_hinson2]

Hello,

I am trying to tune-up some code that executes 7 selects using various data from an internal table using FOR ALL ENTIES. I was wondering if loopng through the table and selecting indiviually would be faster.

For example:

t_EKKO and t_EKPO contains alot of data...

Then this code is executed....

SELECT (l_t_marc_flds) FROM marc

INTO TABLE g_t_marc

FOR ALL ENTRIES IN t_ekpo

WHERE matnr = t_ekpo-matnr

AND werks = t_ekpo-werks.

• Read LFA1 for country

SELECT lifnr land1 telfx telbx adrnr

FROM lfa1 INTO TABLE g_t_lfa1

FOR ALL ENTRIES IN t_ekko

WHERE lifnr = t_ekko-lifnr.

• Read MAKT for Material Description in SY-LANGU

SELECT * FROM makt

INTO TABLE g_t_makt

FOR ALL ENTRIES IN t_ekpo

WHERE matnr = t_ekpo-matnr

AND spras = sy-langu.

• Read MBEW for valuation class

SELECT matnr bwkey bklas FROM mbew

INTO TABLE g_t_mbew

FOR ALL ENTRIES IN t_ekpo

WHERE matnr = t_ekpo-matnr

AND bwtar = space.

• Read T001 to obtain general data for the company

SELECT bukrs waers FROM t001

INTO TABLE g_t_t001

FOR ALL ENTRIES IN t_ekko

WHERE bukrs = t_ekko-bukrs.

• Determine the valuation area

SELECT SINGLE bwkrs_cus FROM tcurm

INTO tcurm-bwkrs_cus.

IF tcurm-bwkrs_cus = '3'. "Valuation area = Company code

SELECT bwkey bukrs FROM t001k

INTO TABLE g_t_t001k.

ENDIF.

• Read Purchasing Info: Country, Cert. Category, Prior Vendor

SELECT infnr urzla urztp kolif umren umrez lmein

FROM eina INTO TABLE g_t_eina

FOR ALL ENTRIES IN t_ekpo

WHERE matnr = t_ekpo-matnr.

SELECT msehi zaehl nennr isocode FROM t006

INTO TABLE g_t_t006

FOR ALL ENTRIES IN t_ekpo

WHERE msehi = t_ekpo-meins.

I was thinking of looping through t_EKKO & t_EKPO, executing individual selects with APPENDING TABLE options.

Would this be the best solution or is the tight as is?

Thanks and point will be awarded accordingly.

Dean.

[Former Member]

No - I wouldn't put it in a loop. One thing you could do: instead of:

*READ t001 to obtain general data for the company
SELECT bukrs waers FROM t001
INTO TABLE g_t_t001
FOR ALL ENTRIES IN t_ekko
WHERE bukrs = t_ekko-bukrs.

Read all the entries into an internal table before this portion of the code and then read the table witha binary search.

Rob

[RaymondGiuseppi]

Keep the FOR ALL ENTRIES as they are, the program will execute sequential access to database, if you replace with select single, you will execute many direct access to database, much much longer.

Regards

[dean_hinson2]

Raymond,

But if t_EKPO is not sorted by the key of the table of the SELECT wouldn't that cause extra "reads" thus more overhead and I/O?

[RaymondGiuseppi]

FOR ALL ENTRIES implicitly build an internal table and sort it before accessing database. (hopefully)

Regards

[ThomasZloch]

well, I'm a fan of joins, admittedly, so why not select as much as possible in one go as the database allows before it cracks down?

not sure how you fill T_EKKO and T_EKPO, but at the same time as selecting from EKKO and EKPO, you could join MARC on EKPO-MATNR and EKPO-WERKS, LFA1 on EKKO-LIFNR, MAKT on EKPO-MATNR and SY-LANGU, MBEW on EKPO-MATNR and BWTAR = space and EINA on EKPO-INFNR. The three T-Tables I would buffer beforehand then READ TABLE ... BINARY SEARCH inside a loop.

I'm sure some people will not recommend so many joins in one statement, but this has worked for me over and over in the past, and the boxes are alive and well.

Happy selecting

Thomas

[Former Member]

> FOR ALL ENTRIES implicitly build an internal table and sort it before accessing database

no!

It only takes care that no duplicates come back.

[RaymondGiuseppi]

The way it remove duplicates is disputable too. it delete duplicate results and not duplicate records.

If you extract only some of the field (thus allowing actual duplicates results but without duplicate records selected) only one is transfered.

Which can be false.

Sample :

Select some purchase orders,

Then with a for all entries select material, quantity and price

If two or more order lines have the same values, then only one will be extracted.

To avoid the trap, always read the whole primary key. (so you will get each and only once each record)

Regards