topic Re: Performance issue while deleting entries from internal table in Application Development and Automation Discussions

Performance issue while deleting entries from internal table

Former Member — Mon, 07 Mar 2016 11:16:01 GMT

Hello,

I have two internal tables. Both internal tables have more than 50,000 rows.

The scenario is: I want to delete data in one internal table based on other internal table entries.

I have tried both below scenarios:

1. Looping one internal table and deleting entries in other internal table based on where condition

Loop at itab1.

Delete itab2 where field = itab1-field.

Endloop.

2. Converting the values in itab1 into ranges and then deleting from ranges.

Delete itab2 where field in r_range1.

But both syntaxes are taking lot of time. I understand that since there are more than 50,000 records, it could take some time.

But the user is running the report online. So, waiting for 5 minutes is becoming an issue.

Is there any better syntax than the two described above?

I have tried searching forums, but most of the thread talk about Database deletes and not internal table deletes.

Re: Performance issue while deleting entries from internal table

davis_raja — Mon, 07 Mar 2016 11:19:08 GMT

Insert a new field in the internal table which you want to delete.

Check for your condition using a loop and read statement.

If your condition matches, make an entry or set the flag in the field and modify it inside the loop.

Then use DELETE statement outside the LOOP.

DELETE itab WHERE flag EQ 'X'.

Re: Performance issue while deleting entries from internal table

SimoneMilesi — Mon, 07 Mar 2016 11:26:21 GMT

And you'll get almost the same result.


1. Looping one internal table and deleting entries in other internal table based on where condition

Loop at itab1.
Delete itab2 where field = itab1-field.
Endloop.

1- ITAB1-FIELD is unique or can exist multiple times?

2- ITAB2 is sorted by the deletion field?

SORT ITAB1 by FIELD

SORT ITAB2 by field

LOOP at ITAB1

at end of FIELD

delete itab2 where fields = itab1-field

endloop.


2. Converting the values in itab1 into ranges and then deleting from ranges.

Delete itab2 where field in r_range1.

same as before: check R_RANGE1 is contains not duplicates and sort ITAB2.

Another option more "nice&clean" is to create a secondary index on ITAB2 and use it in deleteion

Re: Performance issue while deleting entries from internal table

Former Member — Mon, 07 Mar 2016 12:39:34 GMT

Thanks of answering.

Yes the fields are sorted and are unique.

I had tried using secondary index, but I am getting below syntax error regarding declaration.

Can you please help me with syntax error? I am not able to declare a key that is not sorted.

Re: Performance issue while deleting entries from internal table

SimoneMilesi — Mon, 07 Mar 2016 12:47:10 GMT

Try "WITH NON-UNIQUE KEY key COMPONENTS vbeln"

Re: Performance issue while deleting entries from internal table

matt — Mon, 07 Mar 2016 13:12:14 GMT

I've written a blog about the use secondary keys. Maybe if you search for it, it'll be helpful.

Re: Performance issue while deleting entries from internal table

Former Member — Mon, 07 Mar 2016 14:47:55 GMT

I ended up declaring it as a hashed table with unique primary key.

But there is barely any improvement in performance.

Re: Performance issue while deleting entries from internal table

matt — Mon, 07 Mar 2016 20:45:41 GMT

What does sorange contain? That's kind of important.

I'd have written it something like this:

data lt_rcsos type hashed table of Solist with unique key vbeln.

insert lines of rcsos into table lt_rcsos.

delete lt_rcsos where vbeln in sorange.

Re: Performance issue while deleting entries from internal table

Former Member — Tue, 08 Mar 2016 12:07:36 GMT

It is a range with VBELN as low and high.

Re: Performance issue while deleting entries from internal table

matt — Tue, 08 Mar 2016 12:55:24 GMT

Yeah - I know what a range or a select-option is. What values does it contain?

Re: Performance issue while deleting entries from internal table

ThomasKruegl — Tue, 08 Mar 2016 14:58:33 GMT

If you are on 7.40, SP08 or higher you can try Filter:

I don't know about its performance though.

regards,

Thomas

Re: Performance issue while deleting entries from internal table

Former Member — Tue, 08 Mar 2016 15:27:57 GMT

It's a good hint to use the FILTER operator, if possible due to your ABAP release. I used already the FILTER operator to create intersections of tables. It's short and easy. In addition you should use key definitions for your internal table; maybe secondary keys. Using keys should speed up your runtime performance.

Regards

Armin

Re: Performance issue while deleting entries from internal table

former_member210008 — Tue, 08 Mar 2016 15:50:54 GMT

After you changed table contents, key must be updated. On large tables it takes some time. So that is why you dont have improvement IMO.

You can try two ways:

1) add new field flag to use it for filter on output, i.e. flag = 'X' - show it, flag = ' ' - dont show. Pros: you dont need to delete records - save some time.

2) try to use filter or binary search (depends on your abap version).

sort itab2 by field.

loop at itab1 assigning <itab1>.

clear sy-subrc.

while sy-subrc = 0. "if your field is not unique

read table itab2 with key field = <itab1>-field binary search assigning <itab2>.

if sy-subrc = 0.

delete itab2 index sy-tabix.

"OR

"copy row <itab2> to new table itab3. Test both variants.

endif.

endwhile.

endloop.

Re: Performance issue while deleting entries from internal table

Former Member — Tue, 08 Mar 2016 19:52:07 GMT

Declare itab2 as a sorted table with the appropriate key.

loop at itab1

read table itab2 with your key.

if the return code is 0

delete itab2 index sy-tabix

If there are multiple records in itab2 with the same key, you will have to add extra logic to handle that.

Rob

Re: Performance issue while deleting entries from internal table

Former Member — Wed, 09 Mar 2016 08:20:20 GMT

Another idea: Is it possible to prevent the filling of the second table, where you want to delete lines by now?

Re: Performance issue while deleting entries from internal table

RicardoRomero_1 — Wed, 09 Mar 2016 09:04:49 GMT

Hi,

My grain of sand; I don't know if it'll improve the performance, try and tell us;


SORT itab2 BY field. "Or declare it as SORTED 

  LOOP AT itab1.
   
    READ TABLE itab2
     WITH KEY field = itab1-field
     BINARY SEARCH.


    IF sy-subrc EQ 0.


      LOOP AT itab2
        FROM sy-tabix
        WHERE field = itab1-field.
       
        DELETE itab2 INDEX sy-tabix.
       
      ENDLOOP.


    ENDIF.

ENDLOOP.

Re: Performance issue while deleting entries from internal table

Former Member — Thu, 10 Mar 2016 09:46:50 GMT

Hi,

The test in this ABAP Keyword Documentation maybe useful for you, take a look.

Regards

Bill

Re: Performance issue while deleting entries from internal table

Former Member — Thu, 10 Mar 2016 10:19:23 GMT

This message was moderated.

Re: Performance issue while deleting entries from internal table

Former Member — Thu, 10 Mar 2016 13:22:13 GMT

Read within a loop will take more time I guess.

Re: Performance issue while deleting entries from internal table

Former Member — Thu, 10 Mar 2016 13:23:29 GMT

Sorry, we are on lower release.