DELETE records within the loop

[former_member197475]

Hi Experts,

I just read some of the threads in SCN and all have prescribed to avoid DELETE statement inside the loop.

See me case below. I have two local tables lt_beleg and lt_rot.

LOOP AT lt_beleg into lw_beleg.

DELETE lt_rot where BELNR = lw_beleg-BELNR.

END LOOP.

Am deleting records from lt_rot based on the BELNR from lt_beleg. But my lt_rot contains possible records where BELNR = ' '. So I dont have any other option to delete the records within loop, such that i'll will have records with other than BELEG as well as empty BELNR in my lt_rot.

I don't understand how it will affect the performance, as am deleting the records with exact condition within the loop.

Please help me to clear it, else is there any other way to handle the code.

Waiting for your valuable ideas

BR,

RAM.

[karun_prabhu]

Hello Ramakrishnan.

     I don't where you read about it, but using DELETE itab looping the same itab is only not advisable.

     For instance,

          loop at itab where field = value.

               delete itab.

          endloop.

     Instead delete itab where field = value is the best way.

Regards.

[matt]

K.Arun Prabhu wrote:

.., but using DELETE itab looping the same itab is only not advisable.

Not correct. While DELETE ... WHERE is preferred, there is absolutely nothing wrong with

LOOP AT itab INTO wa.

  IF some complicated condition is met.

   DELETE itab.

  ENDIF.

ENDLOOP.

You might find

LOOP AT itab ASSIGNING <record>.

IF some complicated condition is met.

   DELETE <record>.

ENDIF.

ENDLOOP.

more problematic, as <record> becomes unassigned. HASHED tables need more care with the delete, but the principle holds: generally there is no issue.

[karun_prabhu]

Matthew,

     You are right. I should have phrased it better.

     I meant for the case wherein there is no check involved inside loop but only just plain DELETE statement.

Regards.

[matt]

K.Arun Prabhu wrote:

Matthew,
     You are right. I should have phrased it better.
     I meant for the case wherein there is no check involved inside loop but only just plain DELETE statement.
Regards.

Still doesn't make sense to me. What do you mean "no check involved"? If there's no check, you can't use WHERE in the first place? Anyway I've done things like this quite happily.

LOOP AT itab INTO wa.

<logic>.

DELETE itab.

<more logic>.

ENDLOOP.

[karun_prabhu]

Matthew,

     You again got me wrong.

     I meant a case exactly like

     loop at itab where field = value.

          delete itab.

     endloop.

     Naive programmers usually code as above.    
     Comparatively, delete itab where field = value is better in terms of performance.

[former_member193512]

Dear RamaKrishnan

you can try this way ...

with in the loop , if the condition satisfies set the flag variable and then delete the records after the loop if the flag is set.

LOOP AT lt_beleg into lw_beleg.

IF <CONDITION>.

move 'x' to flag.

ENDIF.

END LOOP.

DELETE lt_rot where flag eq 'x'.

hope it helps.

[matt]

That's nonsense. This program simply will not work.

If you wanted to do something similar, you'd have to update a delete flag field in each record of the table, But what would be the point? You might as well put the delete in the loop. You're just making the program more inefficient.

[former_member193512]

Dear Matthew Billingham

As Ramakrishnan wrote

" is there any other way to handle the code"

i am trying to answer that , as you told in case of field symbols we cannot delete the record

with in the loop as it unassigns the workarea, i think in that case we can use it ...

if any thing is wrong or if i understood wrongly, could you please correct me ,thankyou.

Thanks

Shravan

[matt]

You can delete records within the loop with ASSIGNING: You just need to be aware that you can't address the record afterwards.

[former_member197475]

Thanks for your idea Shraven.

BR,

RAM.

[srikanthv2]

Hello Ramakrishnan,

   Please filter BELNR records while fetching in select query using 'for all entries' without deleting in loop.

For Ex:

if the BELEG and ROT are database tables.

select * from BELEG into table lt_beleg where condition.

if lt_beleg is not initial.

select * from ROT into table lt_rot for all entries in lt_beleg where ( belnr = lt_beleg-belnr and belnr <> space ).

endif.

Regards

Srikanth

[matt]

Oh come on. Don't use FOR ALL ENTRIES. Use a JOIN.

[former_member197475]

Dear all,

Thanks for your response.

So I can understand that, I can go ahead with the same coding as am not deleting the it_table with the same looped it_table.

If not, please correct me.

BR,

RAM.

[matt]

If you are solely deleting records that meet a condition, use DELETE...WHERE...

Looping through a table and deleting from that table is not a problem. Depending what you are doing, it might be good programming.

[former_member197475]

Hi Matthew,

Thanks a lot for your well explanation.

Closing this thread.

BR,

RAM.

[kesavadas_thekkillath]

Hello,

Just would like to add another suggestion.

If there are lot( LOTS 🙂 ) of records in an internal table which is looped and deleted, then it will affect the performance( like rearrangement of internal record index etc ). I have faced this issue, the program was getting executed for a day because of delete in loop. I solved it by appending the required records to a new internal table if the condition was met. I am not sure if the below helps you. You can do a comparison based on start-end runtime and check it.

data lt_beleg type sorted table of ty_beleg with non-unique key belnr.

sort lt_rot by belnr.

LOOP AT lt_rot into lw_rot.

at new belnr.

  flag = 'X'.

  read table lt_beleg with key benlr = lw_rot-belnr transporting no fields.

  if sy-subrc = 0.

    flag = ' '.

  endif

endat.

if flag = 'X'.

  append lw_rot to lt_t_rot.

endif.

endif.

endloop.

[Former Member]

hi,

another way is , create a range for type BELNR.

loop.

fill range r_belnr.

endloop.
after looping , write delete statement.

DELETE lt_rot where BELNR in r_belnr.

regards,

Satyen T.

[Former Member]

Hi,

You can aslo try in this way,

Yes, generally it is not advisable to use Delete Statements under the Loop.

But when you want to compare records of another internal table then there is no other go we have to use delete statement under the loop.

I have slightly modified the code, try this off.

SORT lt_beleg by BELNR.

SORT lt_rot      by BELNR.

LOOP AT lt_beleg into lw_beleg.

Read table lt_rot into LS_ROT with key BELNR = lw_beleg-BELNR

                                                                    BINARY SEARCH.

if sy-subrc eq 0.

DELETE lt_rot where BELNR = LS_ROT-BELNR.

endif.

clear: LS_ROT, LW_BELEG.

END LOOP.

Regards,

Saravanan Sambandam

[matt]

This is what I disagree with. There is no general principle of not using DELETE in a loop. In many cases it is exactly the thing to do.

Oh, and don't use SORT and BINARY SEARCH. Use SORTED tables - or even better, HASHED tables from the start! They've only been part of the ABAP language for over fifteen years!

[Former Member]

bt we can delete the record outside the loop.

DELETE lt_rot where BELNR in r_belnr.

No need to write delete statement within the loop.

[matt]

The problem is that loose statements like "Don't use delete within a loop of the same table" lead to a myth that if you ever see LOOP AT .... DELETE ... ENDLOOP, that this must be bad programming. It is not.

Let me be very clear.

In the ONE case that you are simply wishing to delete records that do not meet some simple criteria, then you MUST use DELETE WHERE, rather than LOOP AT... DELETE... ENDLOOP.

In other cases, deleting within the loop is perfectly acceptable and will not lead to problems.

I'll allow one caveat. It might be slightly better performing to remove records which don't meet some criteria before looping through them.  But I doubt very much it will be noticeable, and worth the (slight) extra complexity.

Is that clear enough?

[Former Member]

Please see my above post carefully and then write any comment to me.

Here , I am just giving the  idea to solve the issue. i am not telling who is right and wrong.

[matt]

Yes, it might be appropriate to fill a range and use that to do a delete where. How you construct the where clause if fairly moot.

[former_member184551]

Hi Mathew, interesting caveat actually, only one concern 'wont deleting records inside the loop change the indexing and mess up any tabix based operations' ?

Frankly I dont think this is an issue for which I would lose my sleep. but for people more interested in under the hood stuff, refer to this magnum opus on delete inside a loop

http://scn.sap.com/thread/2074310

peace !

[matt]

Yes, it will affect tabix... If you rely on it. But really there's no reason to. HASHED tables don't even have an index.