performence tuning

[Former Member]

hi

can any body tell me how it will work for all entries statement in select stmnt

[former_member404244]

Hi,

check the belwo code..

SELECT ekko~ebeln

ebelp

ekgrp

lifnr

ekpo~aedat

matkl

matnr

mtart

mfrpn

netwr

menge

netpr

FROM ekko JOIN ekpo

ON ekpo_{ebeln = ekko}ebeln

INTO TABLE I_PURCHASE

WHERE ekgrp IN s_ekgrp

AND lifnr IN s_lifnr

AND ekko~aedat IN s_aedat

AND matkl IN s_matkl

AND matnr IN s_matnr

AND mtart IN s_mtart

AND bsart NE c_sto

AND ekko~ebeln IN s_ebeln.

IF NOT i_purchase[] IS INITIAL.

SELECT ebeln

ebelp

zekkn

vgabe

gjahr

belnr

buzei

dmbtr

menge

shkzg

FROM ekbe

INTO TABLE i_ekbe

FOR ALL ENTRIES IN i_purchase

WHERE ebeln = i_purchase-ebeln

AND ebelp = i_purchase-ebelp

AND vgabe IN (c_1,c_2).

IF sy-subrc EQ 0.

SORT i_ekbe BY ebeln ebelp .

ENDIF.

Regards,

Nagaraj

[Former Member]

See basically for all entries works when u r fetching data in response to another internal table then u can use for all entries.

eg.

select maktx from makt into table it_makt

for all entries in it_mara

where mara eq it_mara-mara.

Reward points if useful

Regards,

Vimal

[Former Member]

Hi,

If you are inner joining two tables and the data records to be fetched are large in number then the performance is hampered.

In that case you first select the data from 1 database table say 'mara' into an internal table say itab1 based on some conditions.

Now in order to fetch other data from a different database table,only for these selected materials in itab1 then we write

select * from 'XYZ' into table itab2 for all entries in itab1 where matnr = itab1-matnr.

Let me know in case you need any more information on the same.

And if your query is answered please close the post.

Thank you,

taher

[Former Member]

Hi,

You can only use FOR ALL ENTRIES IN ...WHERE ...in a SELECT statement.

SELECT ... FOR ALL ENTRIES IN itab WHERE cond returns the union of the solution sets of all SELECT statements that would result if you wrote a separate statement for each line of the internal table replacing the symbol itab-f with the corresponding value of component f in the WHERE condition.Duplicates are discarded from the result set. If the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records (in the current client).

For example:

SELECT * FROM sflight INTO wa_sflight

FOR ALL ENTRIES IN ftab

WHERE CARRID = ftab-carrid AND

CONNID = ftab-connid AND

fldate = '20010228'.

This condition, return all entries of the sflight.

When using FOR ALL ENTRIES the number of matching records is restricted to the number of records in the internal table. If the number of records in the database tables is too large then join would cause overheads in performance. Additionally a JOIN bypasses the table buffering.

follow these links for knowing the difference between For all entries and joins.

http://www.erpgenie.com/abap/performance.htm#For%20all%20entries

http://blogs.ittoolbox.com/sap/db2/archives/for-all-entries-vs-db2-join-8912

Regards,

Priyanka.

[Former Member]

The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

merits:----

Large amount of data

Mixing processing and reading of data

Fast internal reprocessing of data

Fast

demerits:------

Difficult to program/understand

Memory could be critical (use FREE or PACKAGE size)

Some steps that might make FOR ALL ENTRIES more efficient:

Removing duplicates from the driver table

Sorting the driver table

If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:

FOR ALL ENTRIES IN i_tab

WHERE mykey >= i_tab-low and

mykey <= i_tab-high.