ABAP Performance for DELETE on ITAB

DELETE on ITAB is widely used when you need to filter out the entries. Lets see the performance for different Delete statements.

DELETE on ITAB

Filtering entries from an internal table would be done using the DELETE itab WHERE condition.

There would be huge difference in performance is you don’t use proper type for the ITAB on which delete is being performed. For comparison, I have used different type of tables with DELETE.

This example contains the two internal table – Header and Item. For every 3rd record on header, all items would be deleted. This code lines is for setting up the data:

 
DATA: lv_flag TYPE flag,
      lv_sta_time TYPE timestampl,
      lv_end_time TYPE timestampl,
      lv_diff_w   TYPE p DECIMALS 5,
      lv_diff_f   LIKE lv_diff_w,
      lv_save     LIKE lv_diff_w.
 
TYPES:
  BEGIN OF ty_header,
    vbeln   TYPE char10,
    field1  TYPE char50,
  END   OF ty_header.
TYPES: tt_header TYPE STANDARD TABLE OF ty_header.
 
TYPES:
  BEGIN OF ty_item,
    vbeln   TYPE char10,
    posnr   TYPE char6,
    field2  TYPE char50,
  END   OF ty_item.
TYPES: tt_item TYPE STANDARD TABLE OF ty_item.
 
DATA: t_header TYPE tt_header,
      t_item   TYPE tt_item.
 
PARAMETERS:  p_num TYPE i DEFAULT 1000.
 
START-OF-SELECTION.
  DATA: ls_header LIKE LINE OF t_header,
        ls_item   LIKE LINE OF t_item.
 
  DO p_num TIMES.
    ls_header-vbeln = sy-INDEX.
    ls_header-field1 = sy-abcde.
    APPEND ls_header TO t_header.
    DO 10 TIMES.
      ls_item-vbeln = ls_header-vbeln.
      ls_item-posnr = sy-INDEX.
      APPEND ls_item TO t_item.
    ENDDO.
 
  ENDDO.
 

Using DELETE on standard table using WHERE

Now, lets delete the items using the “classic” DELETE using WHERE.

  DATA: lv_mod_3 TYPE i.
  GET TIME STAMP FIELD lv_sta_time.
  SORT t_item BY vbeln.
  LOOP AT t_header INTO ls_header.
    lv_mod_3 = sy-tabix MOD 3.
    IF lv_mod_3 IS INITIAL.
      DELETE t_item WHERE vbeln = ls_header-vbeln.
    ENDIF.
  ENDLOOP.
  GET TIME STAMP FIELD lv_end_time.
  lv_diff_w = lv_end_time - lv_sta_time.
  WRITE: /(15) 'DELETE', lv_diff_w.

Processing time for DELETE

I ran the same code for multiple times, and timings are like these:

Using Parallel Cursor

Now, lets use some parallel cursor on this DELETE. This is similar to Parallel Cursor for the Nested Loop. First read the index of the required record, DELETE from that index on wards, till it finds a different key.

  DATA: lv_mod_3 TYPE i.
  GET TIME STAMP FIELD lv_sta_time.
  SORT t_item BY vbeln.
  LOOP AT t_header INTO ls_header.
    lv_mod_3 = sy-tabix MOD 3.
    IF lv_mod_3 IS INITIAL.
 
      READ TABLE t_item TRANSPORTING NO FIELDS
        BINARY SEARCH
        WITH KEY vbeln = ls_header-vbeln.
      IF sy-subrc EQ 0.
        LOOP AT t_item INTO ls_item FROM sy-tabix.
          IF ls_item-vbeln NE ls_header-vbeln.
            EXIT.
          ENDIF.
          DELETE t_item INDEX sy-tabix.
        ENDLOOP.
      ENDIF.
 
    ENDIF.
  ENDLOOP.
  GET TIME STAMP FIELD lv_end_time.
  lv_diff_w = lv_end_time - lv_sta_time.
  WRITE: /(15) 'DELETE', lv_diff_w.
 

Processing time for DELETE

Obviously, the performance has to improve as parallel cursor is in play. The saving is surprisingly in range of ~95%.

Using SORTED table

Next test is using the SORTED table with Unique Key. Since this item table has unique key fields, I can easily define.

 
* Change type to sorted
"types: tt_item type STANDARD TABLE OF ty_item.
TYPES: tt_item TYPE SORTED TABLE OF ty_item WITH UNIQUE KEY vbeln posnr.
 
*
  DATA: lv_mod_3 TYPE i.
  GET TIME STAMP FIELD lv_sta_time.
  SORT t_item BY vbeln.
  LOOP AT t_header INTO ls_header.
    lv_mod_3 = sy-tabix MOD 3.
    IF lv_mod_3 IS INITIAL.
      DELETE t_item WHERE vbeln = ls_header-vbeln.
    ENDIF.
  ENDLOOP.
  GET TIME STAMP FIELD lv_end_time.
  lv_diff_w = lv_end_time - lv_sta_time.
  WRITE: /(15) 'DELETE', lv_diff_w.
 

Processing time for DELETE on SORTED Unique Key

There is more performance improvement when using the SORTED table with unique key. Sorted tables are much more performance efficient as what we have seen in ABAP Internal Table Performance for STANDARD, SORTED and HASHED Table.

Using Secondary Key non-unique key

Since ABAP 731, Secondary key on the ITAB can be declared. This would be also helpful and increase the performance if you don’t have clear key.

 
"types: tt_item type STANDARD TABLE OF ty_item.
"types: tt_item type SORTED TABLE OF ty_item with UNIQUE key vbeln posnr.
TYPES: tt_item TYPE SORTED TABLE OF ty_item
                     WITH UNIQUE KEY vbeln posnr
                     WITH NON-UNIQUE SORTED KEY key2nd COMPONENTS field2.
 
  DATA: lv_mod_3 TYPE i.
  GET TIME STAMP FIELD lv_sta_time.
  LOOP AT t_header INTO ls_header.
    lv_mod_3 = sy-tabix MOD 3.
    IF lv_mod_3 IS INITIAL.
 
      DELETE t_item USING KEY key2nd WHERE field2 = ls_header-vbeln.
 
    ENDIF.
  ENDLOOP.
  GET TIME STAMP FIELD lv_end_time.
  lv_diff_w = lv_end_time - lv_sta_time.
  WRITE: /(15) 'DELETE', lv_diff_w.
 

Processing time for DELETE with Secondary Key

The numbers are almost same as Parallel cursor approach.

On Graph

Putting all together. Needed to change the scale to log10 in order to display the difference.

If DELETE with standard table is considered 100%, the other two options finishes the work in less than 0.15% time of that 100%.