a test for duplicate values in FOR ALL ENTRIES condition

[raffinkira]

SELECT *

FROM vbap

INTO TABLE gt_ab "gt_ab is like vbap

FOR ALL ENTIRES IN gt_xy

WHERE xxx = gt_xy-xxx.

Assume that when gt_xy-xxx = '001', 500 records will be fetched.

Then if you do this to fill in gt_xy:

DO 500000 TIMES.

gs_xy = '001'.

APPEND gs_xy TO gt_xy.

ENDDO.

It will cause a session restarted because of memory insufficient.

So I think that both SAP server & DB server do not eliminate duplicates in value range,

and for every '001', 500 records will be fetched and appended.

After certain amounts of repeatation, memory is exhausted.

Furthermore, I have tried to calculate the max number allowed of duplicates and found it's

not just max_memory/stucture lenth/hit number, for example, let's assume:

the max memory allowed for one dialog process is 4,000,000,000Bytes

the structure lenth of vbap is 4,000Bytes

hit number for single value is 500

I thought the max duplicate number is 4,000,000,000/4,000 /500, about 2000.

However, when I tried to fill in gt_xy with 2000 times, it will not cause the memory problem.

Acutually, the number of possible duplicates is far more above 2000.

So I guess may the system does the fetch and delete duplicate in the result set at

the same time. Is there anyone also interested in this?

[former_member184455]

Hi Ming,

the result of a  SELECT ... FOR ALL ENTRIES is always DISTINCT (unique) with respect to the fields in the FAE driver table that are used in the WHERE-clause. But in the FAE, this uniqueness is only established after the result is returned from the database. Since data from the database is fetched in packages and not as one big chunk, it would be clever of the database interface on the ABAP side to throw out identical records immediately after each package to not run into memory issues.

Since identical entries in the FAE driver table (that is identical wrt to the selection in the WHERE-clause) are not eliminated before statement execution, it is the task of the developer to care for uniqueness (DELETE ADJACENT DUPLICATES ... COMPARING ...). Otherwise unneccessary database load is generated.

Best Regards, Randolf

[raffinkira]

As tested, it does go into memory issue when result set is big enough.

[Former Member]

gt_xy is bseg subset I guess ?

What do You need from bseg ? Maybe you can rewrite query and use proper join on index tables.

Other way around be to chop gt_xy into chunks and fetch for all entries of subset of gt_xy.

Hope it helps.

[Former Member]

gt_xy is a whatever internal table.

The purpose of the test is to dig out further of FAE.

It seems that FAE does influence the performance when the driver internal table is big and contains

many duplicate records.

[Former Member]

Ok, got it.

I don know about duplicate records effect, but I've seen FAE queries who's exec time has grown in Production from few minutes in background, to few hours after a year or so (more data in internal table, but not as much more) and finally to memory exhaustion.

Didn't test it thoroughly, but exponential growth of execution time comes to mind .

I have one case that would be perfect for test, when I find time I'll test it and post some data.

Till then,  enjoy coding !