Performant way to filter itab against another itab values with 7.40+ syntax?

Question

Suppose I have 2 internal tables:

TYPES:BEGIN OF LTY_FILE,
       SOKEY TYPE CHAR5,
       SOPONO TYPE CHAR15,
       SOCONO TYPE CHAR15,
       FLAG TYPE C,
      END OF LTY_FILE.
data:IT_ARCHIVE TYPE TABLE OF LTY_FILE,
     IT_SAP     TYPE TABLE OF LTY_FILE.
it_archive = VALUE #(
    ( sokey = 'Key1' sopono = 'PO#12' socono = 'Three' flag = 'A' )
    ( sokey = 'Key2' sopono = 'PO#34' socono = 'Four'  flag = 'B' ) ).
it_sap = VALUE #(
    ( sokey = 'Key1' sopono = 'PO#12' socono = 'Three' flag = 'A' ) ).

IT_ARCHIVE:

SOKEY	SOPONO	SOCONO	FLAG
Key1	PO#12	Three	A
Key2	PO#34	Four	B

IT_SAP:

SOKEY	SOPONO	SOCONO	FLAG
Key1	PO#12	Three	A

Now I want the lines from IT_ARCHIVE which don't exist in IT_SAP, based on keys SOKEY, SOPONO and SOCONO, to be added to IT_SAP with FLAG = D:

SOKEY	SOPONO	SOCONO	FLAG
Key2	PO#34	Four	B

So, new IT_SAP should look like:

SOKEY	SOPONO	SOCONO	FLAG
Key1	PO#12	Three	A
Key2	PO#34	Four	D

Until now, I have tried following ways,but failed in ABAP 7.4, but as the data is quite huge (more than 400K in each run), I am looking for performance centric code to achieve this.

DATA(ls_wa) = it_sap[ 1 ].  " Temporary workarea for insertion.
CLEAR:ls_wa  
LOOP AT IT_ARCHIVE ASSIGNING FIELD SYMBOL(<lfs_archive>).
   IF NOT LINE_EXISTS( it_sap[  sokey = <lfs_Archive>-sokey
                                sopono = <lfs_Archive>-sopono
                                socono = <lfs_archive>-socono ] ).

      ls_wa = <lfs_archive>.
      ls_wa-flag = 'D'.
      APPEND ls_wa to IT_SAP.
      CLEAR:ls_wa.
   ENDIF.
ENDLOOP.

Second approach I tried:

  data(LT_FINAL) = IT_SAP[].
  REFRESH:LT_FINAL[].
  lt_final = VALUE #( FOR ls_ar IN IT_ARCHIVE
                          FOR ls_sap IN IT_SAP WHERE ( sokey <> <lfs_Archive>-sokey
                                                       sopono <> <lfs_Archive>-sopono
                                                       socono <> <lfs_archive>-socono )
                        ( 
                          "Explicitly mentioning all fields as have to change FLAG value
                            sokey = ls_Ar-sokey
                            sopono = ls_ar-sopono
                            socono = ls_ar-socono
                            flag = 'D'
                         ) ).

The first approach, takes too much of time as have huge number of data. The second approach, to my surprise is not giving me the same result as the first one.

Is there any better way in ABAP 7.4+ to achieve this?

What is your table types? If `it`sap` is a table with primary key, you may just [`insert`](https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-us/abapinsert_itab.htm) into it, which would insert a row if no primary key violation or ignore the row if the same PK value already exists in the table. For performance you may use hashed table as target — astentx, Jun 19 '21 at 21:53
And as you used `hana` tag: do your tables come from the database or produced by some code? — astentx, Jun 19 '21 at 21:58
@astentx ..Table type is same of both woth 4 columns...I just need abap 7.4+ approach if its feasible as the first ine using loop and line exists is taking too much of time...Is the same using For loop possible? — CoderW, Jun 20 '21 at 05:49
@astentx It comes from standard table whose data is stored in both above given internal table — CoderW, Jun 20 '21 at 05:50
@astentx Could you help me to understand using hash approach you mentioned using code? — CoderW, Jun 20 '21 at 05:52
The types and values are missing in your code, please provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). — Sandra Rossi, Jun 20 '21 at 06:56
@SandraRossi Have changed the question to contain types.Please see and advice — CoderW, Jun 20 '21 at 08:10
Consider also my old question https://stackoverflow.com/questions/48825937/most-efficient-itab-filtering-with-abap-7-40-syntax — Suncatcher, Jun 22 '21 at 11:05

score 7 · Accepted Answer · answered Jun 20 '21 at 10:35

Your main question is about performance. For information, there are other alike questions/answers in Stack Overflow concerning performance and internal tables.

In your case, you use "standard" table types (DATA ... TYPE TABLE OF ...), so each lookup (line_exists) will look at all lines of the internal table until one is found. Instead of looking at all the lines again and again, it's best to sort once the internal table, and each lookup will be much faster; that's easy to understand why: if you, human, look at a word in a dictionary (book), hopefully the words are sorted and you will quickly find this word (providing that you know the order of letters A, B, C, etc.; it's the same for a computer.

There are two ways to sort the internal table:

Either implicitly, by declare it as a "sorted" or "hashed" internal table and any initialization or change of the internal table will automatically update the index.
Or explicitly, by the statement SORT. This option is simpler to understand but highly not recommended whenever possible.

To choose between sorted and hashed (i.e. for implicit sorting), you must simply know if it's possible to have two same lines with the exact same key.

If yes, you must choose a sorted internal table with a non-unique key.
If no, you may choose a hashed internal table, which by definition uses a unique key. For more information, read the ABAP documentation - itab - Selection of the Table Category.

In your case, just doing that declaration below would dramatically improve the performance of your current (working) algorithm:

TYPES:BEGIN OF lty_file,
        sokey  TYPE char5,
        sopono TYPE char15,
        socono TYPE char15,
        flag   TYPE c,
      END OF lty_file.
TYPES:lty_file_tab TYPE SORTED TABLE OF lty_file WITH NON-UNIQUE KEY sokey sopono socono.
DATA:it_archive TYPE lty_file_tab,
     it_sap     TYPE lty_file_tab.

You may also improve a little bit your algorithm by using the built-in operator FILTER which I think runs more in the kernel (more information about FILTER in the answer of this other question).

For instance:

it_sap = VALUE #(
    BASE it_sap
    ( LINES OF VALUE #( FOR <line> IN
                            FILTER #( it_archive EXCEPT IN it_sap
                                    WHERE sokey  = sokey
                                      AND sopono = sopono
                                      AND socono = socono )
                        ( VALUE #( BASE <line> flag = 'D' ) ) ) ) ).

ASSERT it_sap = VALUE lty_file_tab(
    ( sokey = 'Key1' sopono = 'PO#12' socono = 'Three' flag = 'A' )
    ( sokey = 'Key2' sopono = 'PO#34' socono = 'Four'  flag = 'D' ) ).

(NB: I use ASSERT here just to prove that the internal table has the indicated value; otherwise that would stop the program/raise a runtime error)

I've tested `filter` approach over my suggested hashtable with simple `insert` and check of `sy-subrc`, it runs 4x faster on table of 10k lines. So it definitely uses kernel execution. — astentx, Jun 21 '21 at 00:12
Thank you very much Sandra.... Your algo snippet have made me understand my mistake and it works fine.. However, have one quick question, the LINES OF and BASE isn't the same as both inserts/ holds more than one line item?Do we have something kike LINE_EXISTS functionality inside FOR/BASE/FILTER? — CoderW, Jun 21 '21 at 10:51
I don't understand well your question. `BASE` simply copies the lines of `it_sap`, to which we add the lines of `it_archive` which are not in `it_sap` yet. You can use `LINE_EXISTS` inside the constructor expression, for instance `... = COND #( WHEN line_exists( it_sap[ ... ] ) THEN ... )`... — Sandra Rossi, Jun 21 '21 at 12:18
@SandraRossi :Thanks for this clear explanation.......What I meant was is ther e — CoderW, Jun 21 '21 at 13:15

Performant way to filter itab against another itab values with 7.40+ syntax?

1 Answers1