How to improve BSIS reading inside dynamic itab loop?

Question

I have a code as below, which takes more than 200 minutes to execute, could you help me to improve the performance.

This code is triggered at User Command. Say users select all records (about 300K records) at first level output for next level drill down report:

The dynamic internal table <OI_TABLE> has around 300K records with a field component called BOX marked as 'X' for those selected lines by the user.
The program reads the selected lines from the dynamic internal table (<OI_TABLE>), compare them to another standard internal table (GT_BSIS) which has around 300K records, whose clearing key value should be the same for both internal tables (1:N cardinality).
It then inserts those common records to the third standard internal table (GT_L2_DISP) for further processing/display.

Code:

LOOP AT <oi_table> ASSIGNING <oi_line>.
  ASSIGN COMPONENT 'BOX' OF STRUCTURE <oi_line> TO <oi_field>.
  IF <oi_field> = 'X'.
    ASSIGN COMPONENT 'CLEARING_KEY' OF STRUCTURE <oi_line> TO <oi_field>.
    LOOP AT gt_bsis WHERE clearing_key = <oi_field>.
      MOVE-CORRESPONDING gt_bsis TO gt_l2_disp.
      APPEND gt_l2_disp.
    ENDLOOP.
  ENDIF.
ENDLOOP.

Here, <oi_table> contains data for first level ALV output and GT_BSIS would contain data for 2nd level ALV output.

My understanding :

If we can fill up standard internal table GT_BSIS and marking/passing 'X' to a column (say FLAG) in GT_BSIS, while user is selecting rows from ALV first level output, it would help in performance as ONE LOOP .. ENDLOOP could be avoided.

An indexed internal table may also be an option. Please suggest a way to improve the performance.

NB: our SAP System is ECC, ABAP 7.31, so please do not propose inline code/declaration.

Gt_bsis has to be TYPE SORTED table with the field clearing_key — József Szikszai
I have edited your question, could you verify that I didn't introduce errors, especially 1) "12 million mili seconds" -> "200 minutes" (3 hours and 20 minutes) and 2) "would NOT require" -> "could be avoided" (by the way, I don't understand what this sentence exactly means). — Sandra Rossi
If you know that the "indexed internal tables" exist and are related to performance issues (that's right), why don't you try to use them? — Sandra Rossi
Use BSIS db hints here 1, 2, 3 and do not use itab implicit header lines which is strongly prohibited and discouraged now — Suncatcher
And thinks twice if you need dynamics in your report (<oi_table>), because it slows down a little too — Suncatcher

Zero Zero · Accepted Answer · 2020-02-13T11:50:55

I might be wrong, but reversing the loops might help you somewhat. In theory this should reduce exponential growth of iterations when the 2 tables grow.

I think reversal might help because you don't appear need <OI_TABLE> for anything beyond filtering, but you are looping over it anyways. That means both looping over more rows AND you sometimes might invoke a full scan of GT_BSIS if no no corresponding key exists (I'm not entirely sure if loops make use indexing).

sort <oi_table> by ('CLEARING_KEY'). "Only if table isn't already a sorted table
Loop at GT_BSIS assigning <ls_bsis> where "Assigning! Don't make copies of each row on each iteration, if you can help it
    read table <oi_table> ASSIGNING <oi_line> with key ('CLEARING_KEY') = <ls_bsis>-clearing_key binary search. 
    check sy-subrc = 0. 
    assign component 'BOX' of structure <oi_line> to <oi_field>
    if <oi_field> = 'X'.
      MOVE-CORRESPONDING <ls_bsis> TO gt_l2_disp.
      append ls_l2_disp to gt_l2_disp.
    endif.
endloop.

How to improve BSIS reading inside dynamic itab loop?

2 Answers