ABAP Performance for DELETE on ITAB

By Naimesh Patel | July 29, 2015 | Performance | 108,782 | 18

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%.

Like It? Share!!

Don't miss an Update

fb/zevolving @zevolving

Naimesh Patel{274 articles}

I'm SAP ABAP Consultant for more than a decade. I like to experiment with ABAP especially OO. I have been SDN Top Contributor.

Follow :

Explore all of his 274 articles.

Load comments

18 Comments

Raju Shrestha

# July 30th, 2015 at 4:15 pm

Nice Analysis Naimesh!! The graphs makes the analysis better..

I was wondering if you could add one more in the list of comparison; in parallel cursor technique where we mark the items to be deleted and delete them outside the loop.

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 ASSIGN FROM sy-tabix.
IF -vbeln NE ls_header-vbeln.
EXIT.
ENDIF.
* Mark the Item data for deletion
CLEAR: -vbeln.
ENDLOOP.
ENDIF.

ENDIF.
ENDLOOP.

DELETE t_item[ ] WHERE vbeln is INITIAL.
Naimesh Patel

# July 30th, 2015 at 4:32 pm

Raju – I think it would not be very good from performance. Two main reasons:

Parallel Cursor technique using the INDEX to delete
DELETE with WHERE VBELN is INITIAL would have to scan the entire table to delete the items

I would try to add the code and its performance measurement.

Regards,
Naimesh Patel
Raju Shrestha

# July 30th, 2015 at 4:40 pm

Yes Naimesh.. I also think so.. Since you already have the numbers for others, just wanted to have the quantitative analysis as to where it falls in the graph.. 🙂
T R Chandra

# August 2nd, 2015 at 8:13 am

Hi Naimesh,

I want to delete the data from one internal table based on company code. Let say we have one table called RESULT_PACKAGE, In that table i have different company code data. Now i want to have only one company code data.
I want to use the parallel cursor technique. Now my question is can we write loop inside the another loop with the same internal table. Can we have any performance impact. Please provide your valuable input.

Thanks & Regards,
Chandra.

Naimesh Patel

# August 3rd, 2015 at 8:48 am

Chandra – You should be able to use parallel cursor technique to delete by using logic similar to this:

 
data: lv_bukrs type bkpf-bukrs.
lv_bukrs = 'CC01'.
 
SORT RESULT_PACKAGE by BUKRS.
 
      READ TABLE RESULT_PACKAGE TRANSPORTING NO FIELDS
        BINARY SEARCH
        WITH KEY bukrs = lv_bukrs.
      IF sy-subrc EQ 0.
        LOOP AT RESULT_PACKAGE  INTO ls_result FROM sy-tabix.
          IF ls_result-bukrs NE lv_bukrs.
            EXIT.
          ENDIF.
          DELETE RESULT_PACKAGE INDEX sy-tabix.
        ENDLOOP.
      ENDIF.
 
    ENDIF.

I haven’t wrote this in the system so there might be some syntax errors…

Regards,
Naimesh Patel

T R Chandra

# August 3rd, 2015 at 1:35 pm

Hi Naimesh,

Thanks for the input. Now i got it.
One more query :
How to delete the records which has blank records.
Like
DELETE RESULT_PACKAGE WHERE divison NE ‘ ‘.

Can we achieve this through parallel cursor ?

Regards,
Chandra.
Naimesh Patel

# August 3rd, 2015 at 2:54 pm

You can do that using the same technique as you can do for single company code. Just pass the blank value for the required field and you would be set ..

Regards,
Naimesh Patel
T R Chandra

# August 4th, 2015 at 7:35 am

Thank you so much Naimesh…..

Regards,
Chandra.
Raju Shrestha

# August 6th, 2015 at 1:03 am

Hi Naimesh,

I did some analysis for DELETE within LOOP using Parallel cursor and DELETE outside LOOP after marking in LOOP.

Strangely, one DELETE outside the LOOP gave better performance. Seems the cost of re-indexing in LOOP takes away the advantage of Parallel Cursor Technique and index wise DELETE. I tried to mention the details in this post. Please check it at your convenience.

http://www.sapyard.com/deleting-rows-of-the-internal-table-within-the-loop-is-it-a-taboo-a-big-no-no/

Can you please confirm if you get the same result if you delete the driver table within the LOOP.

Regards,
Raju.
Naimesh Patel

# August 6th, 2015 at 11:27 am

Hello Raju,

Humm. There would be added cost for regenerating the index. I would have to run that scenario with my logic. I would update the post with the numbers.

Thanks,
Naimesh Patel
Raju Shrestha

# August 6th, 2015 at 11:31 am

Thanks Naimesh..

If we delete some other internal table in LOOP using Parallel Cursor technique, it might be better. But if the driver internal table is being deleted, it might have some cost.

Waiting for your updated post.

Regards,
Raju.
DELETING rows of the internal table within the LOOP. Is it a Taboo? A big NO NO? | SAP Yard

# September 3rd, 2015 at 11:55 pm

[…] going through Naimesh’s post on performance on DELETE, I wondered how would deleting the entries of the table on which we […]
T R Chandra

# September 18th, 2015 at 1:33 pm

Hi Naimesh,

Need info regarding the internal table sorting.
Let’s say i have one internal table with structure fields like
calmonth,site,customer,salesman,date0,routeid

Now i have created internal table let’s say itab and i have sorted the internal table like
```
 
sort itab by calmonth ascending
                   site         ascending
                   customer ascending
                  salesman ascending
                  routeid     ascending
                  date0       descending.
 
delete adjacent duplicates comparing calmonth site customer salesman routeid.
 
```
In this case how the internal table sorting and delete duplicates will work as the sequence of the field is changed in the sorting and deleting.

Need ur valueable input..

Many thanks,
Regards,
RaviChandra.
Naimesh Patel

# September 21st, 2015 at 4:46 pm

@Ravi Chandra,

The DELETE ADJACENT will not produce expected result as the sorting and the delete has different fields. For DELETE ADJACENT to work, the table should be sorted by the same fields. If they are not sorted using the same fields, rows of the table would be in different order.

From SAP Help:

The use of ADJACENT DUPLICATES usually requires a suitable sorting by the components compared in the statement.

http://help.sap.com/abapdocu_740/en/abapdelete_duplicates.htm

Thanks,
Naimesh Patel
Raju Shrestha

# September 21st, 2015 at 5:40 pm

Hi Naimesh – I think for the example of Ravi, the SORT and DELETE ADJACENT DUPLICATE would still work. The DELETE ADJACENT is still in the same sequence as his SORT. Only one field of the SORT which is in descending order is not used there in DELETE.

So if there are two entries with the same calmonth site customer salesman routeid but different date0, then the first entry with highest date0 (as sorted in descending order) would remain and the other would delete.

Your thought please.

@ Ravi, did you debug and check?

Regards,
Raju.
Naimesh Patel

# September 22nd, 2015 at 10:02 am

oops.. Yes you are right Raju that the sequence is correct.

In this case the biggest value for the DATE0 would be left in the table after the DELETE adjacent using all other fields.

Fields for delete adjacent doesn’t need to be in the sequence of how they were defined in the type or the structure. As long as the fields after DELETE adjacent has SORT with same fields (or more), you can get the explainable results.

Regards,
Naimesh Patel
T R Chandra

# September 22nd, 2015 at 12:51 pm

Hi Naimesh & Raju,

Thank for the reply.
The sequence of sorting and deleting is same. But my concern is the sequence in the internal table structure is different to the sorting sequence.
Will it have any impact ?

Many thanks,
Regards,
RaviChandra.
T R Chandra

# September 22nd, 2015 at 1:12 pm

Hi Naimesh & Raju,

Can you pls elaborate more on this. As i think there might be some performance issue, since the fields in the structure is different to the sorting and deleting statement.

Comments on this Post are now closed. If you have something important to share, you can always contact me.

‹

CALL TRANSFORMATION to download Excel

›

SALV Table 22 - Get Data Directly after SUBMIT

You seem to be new here. Subscribe to stay connected.

Keep in touch. We would do great working together.

Current Poll

Loading ...

Welcome !

Write Green Programs

Display ALV Grid on List Generated by WRITE

Why NOT to have a wrapper around Exception (RAISE) ?