For all entries in XXXX works very slowly

[Former Member]

Hello

I use below construction in update routine SAP BW 3.5

Table /bic/azmmo03bf00 is an active table of ods and contains 10mln records

table /bic/azmmo03bf00 is indexed by material and val_type

the size of internal table is 250K records

The entire contruction works very slowly and often hungs. Any suggestions how to improve it?

select material val_type /bic/zmmicpqua

into corresponding fields of table itab_odslookup3

from /bic/azmmo03bf00

for all entries in lt_pckge

where material eq lt_pckge-material

and val_type eq lt_pckge-val_type.

[former_member156446]

if not  lt_pckge[] is initial.
sort  lt_pckge by material val_type.
delete adjacent duplicates comparing material val_type.

select material val_type /bic/zmmicpqua
into corresponding fields of table itab_odslookup3
from /bic/azmmo03bf00
for all entries in lt_pckge
where material eq lt_pckge-material
and val_type eq lt_pckge-val_type.

endif.

[Former Member]

Hi,

1. LT_PCKGE should be sorted and duplicate records should be deleted

Eg. SORT LT_PCKGE BY material val_type.

DELETE ADJACENT DUPLICATES FROM lt_pckge

COMPARING material val_type.

IF NOT lt_pckge[] IS INITIAL.

select material val_type /bic/zmmicpqua

into table itab_odslookup3

from /bic/azmmo03bf00

for all entries in lt_pckge

where material eq lt_pckge-material

and val_type eq lt_pckge-val_type.

ENDIF.

2. Avoid using INTO CORRESPONDING FIELDS OF TABLE and use "INTO TABLE" in the SELECT statement

3. If you believe there will be a huge volume of data will be retrieved from the Active table. Use CURSOR based SELECT

Regards,

Chathia.

[former_member192616]

Hi,

>

> The entire contruction works very slowly and often hungs. Any suggestions how to improve it?

what does "very slowly and often hungs" mean? Could you post some execution details from ST05?

Execution plan, records per exectuion, time per execution, avg. time per record, .... .

As already posted by others: remove duplicates if there are some. Some fine tuning might be possible

with the blocking factors (depends on comlexity of the sql statement).

Kind regards,

Hermann