Very slow itab loop with nested SELECTs

Question

I have declare an internal table like:

DATA: wa_collectoraction TYPE zcollectoraction,
  it_collectoraction LIKE STANDARD TABLE OF zcollectoraction.

Then I fill the table with:

SELECT bukrs kunnr yearmonth MAX( dat ) AS dat
FROM zcollectoraction
  INTO CORRESPONDING FIELDS OF TABLE it_collectoraction
WHERE bukrs IN so_bukrs AND
      kunnr IN so_kunnr AND
      dat   IN so_date
GROUP BY bukrs kunnr yearmonth.

and finally I have the following loop

LOOP AT it_collectoraction INTO wa_collectoraction.
PERFORM progress_bar USING 'Retrieving data...'(035)
                           sy-tabix
                           i_tab_lines.
"Get the MAX TIME for all lines in order to cover the case we have more than 1 line."
SELECT SINGLE * FROM zcollectoraction
    INTO CORRESPONDING FIELDS OF wa_collectoraction
  WHERE bukrs = wa_collectoraction-bukrs AND
        kunnr = wa_collectoraction-kunnr AND
        dat   = wa_collectoraction-dat   AND
        time  = ( SELECT MAX( time ) AS time
                    FROM zcollectoraction
                    WHERE bukrs = wa_collectoraction-bukrs AND
                          kunnr = wa_collectoraction-kunnr AND
                          dat   = wa_collectoraction-dat ).

MODIFY it_collectoraction FROM wa_collectoraction.
ENDLOOP.

This loop is doing 5 minutes for 3000 records. Can someone tell me what to do in order to be faster?

Thanks in advance

Component Version: SAP ECC 6.0. DATABASE SYSTEM: DB6 sap versions: 700, 710, 701, 702, 711, 720, 730 — ekekakos
The loop itself is fine. But what kind of grudge do you hold against your database that you're trying to kill it so cruelly? ;-) — vwegert
vwegert, what I am trying to do, I explain it below. Well, let me tell you. In the select I am taking the specific fields of the max date of the month. Then I select all the fields of the record with the max time because there is a big possibility to have more than 1 record as a max date. You can see an example below. Thanks — ekekakos

András András · Accepted Answer · 2017-10-03T12:19:30

The best tool to analyze a standalone report's performance is ST12, so if you have the chance, trace it.
Without a trace, we have to guess, the biggest problem is either the SELECT with the subSELECT, or the MODIFY.

1) SELECTs in a LOOP are always slow

Here you actually make two for every line in it_collectoraction.

Try reducing the number of SELECTs

Depending on the number of lines with the same dat, it might be much faster to replace the SELECT in the LOOP with a SELECT with FOR ALL ENTRIES from zcollectoraction outside the LOOP, and find the MAX(time) on ABAP side.

Index coverage

Seems to be fine.

2) MODIFY is slow on STANDARD tables

You have to sieve through the whole table just to find the relevant line. If you define it_collectoraction as SORTED, this will be much faster. If you use a field symbol in the LOOP, it can be avoided altogether.

Coding

Replace your LOOP with this:

TYPES: BEGIN OF tty_coll_act,
        bukrs TYPE burks,
        kunnr TYPE kunnr,
        dat   TYPE dat,
        time  TYPE time,
      END OF tty_coll_act.

DATA: lt_coll_act TYPE TABLE OF tty_coll_act,
      ls_coll_act LIKE LINE OF lt_coll_act.

FIELD-SYMBOLS: <fs_collectoraction> LIKE LINE OF it_collectoraction.

SELECT bukrs kunnr dat time
    INTO TABLE lt_coll_act
    FROM zcollectoraction
    FOR ALL ENTRIES IN it_collectoraction
    WHERE bukrs = wa_collectoraction-bukrs AND
          kunnr = wa_collectoraction-kunnr AND
          dat   = wa_collectoraction-dat.

SORT lt_coll_act BY bukrs kunnr dat time DESCENDING.

LOOP AT it_collectoraction ASSIGNING <fs_collectoraction>.
" the READ TABLE finds the first matching row,
" it will be MAX(TIME) as TIME is sorted descending       
  READ TABLE lt_coll_act INTO ls_coll_act
      WITH KEY  bukrs = <fs_collectoraction>-bukrs
                kunnr = <fs_collectoraction>-kunnr
                dat   = <fs_collectoraction>-dat BINARY SEARCH.
  <fs_collectoraction> = ls_coll_act.
ENDLOOP.