DELETING rows of the internal table within the LOOP. Is it a Taboo? A big NO NO?

Share on Facebook43Share on LinkedIn37Tweet about this on TwitterShare on Google+0
Please Share!!

All these years, I was told that DELETE statement within a LOOP is always prohibited. If you delete any rows inside a LOOP, it is guaranteed that the Quality Reviewer would give you a point and ask you to modify your code to delete the entries outside the LOOP.

After going through Naimesh’s post on performance on DELETE, I wondered how would deleting the entries of the table on which we are looping would behave in two scenarios. This is a very practical scenario where we need to DELETE entries of the LOOPING table based on some checks (For simplicity, I am checking a field of the same table, but in real projects, you might check another table fields and then delete the driver LOOPing table).

Since most of the developers use STANDARD INTERNAL tables, I am analysing only STANDARD internal tables (SORTED/HASHED Tables will have to wait for some other day).. 😛

The two scenario DELETEs we are talking today are:
1) Parallel Cursor Technique with DELETE w.r.t INDEX within the LOOP.
2) Parallel Cursor Technique with marking the row to be DELETED and then DELETING all at one shot outside the LOOP.

Interested in SAP ABAP for HANA? Check our popular series ABAP on SAP HANA Tutorials.

What does your logical mind say?

Let me be honest. Since my training days, almost a decade ago, we were told that DELETE within LOOP is never acceptable. So, I always took the second route. Mark by Parallel Cursor technique and DELETE outside the LOOP.

Later on, as I started understanding the concept of Index and Table scan, I started believing that, DELETING within LOOP in parallel cursor with the target INDEX known, would be better in the view of Performance. Also, I thought that if we mark and DELETE outside loop with WHERE clause, the whole table needs to be scanned for the item to be deleted and it would affect the performance negatively. So I began to believe, DELETING within LOOP should be acceptable too.

But, never before did I try to do the quantitative analysis. Finally yesterday, after months of procrastination, I thought of wearing the statistician’s hat. I spent almost a day playing with the numbers. I tried to increase my sample size as much as I could. I ran the data multiple times in multiple hours of the day (as system can have variable load in different hours of the day) to get the best average figure for the analysis.

Here is the code snippet which I used for doing this comparison. I used the same set of data for both DELETE statements so that I could compare the two in neutral venue. 🙂

Have questions in SAP HANA? Check this: SAP HANA from Space Level.

1) Parallel Cursor Technique with DELETE w.r.t INDEX within the LOOP.

2) Parallel Cursor Technique with marking the row to be DELETED and then DELETING outside the LOOP.

The time taken is for the complete activity set i.e. complete LOOP and DELETE in both scenarios.

Scared of programming in OOPs ABAP? Check this: Just 4 Versions of the same program to understand OOPs ABAP

Below is the output data which I collected after numerous runs.

DELETE Internal Table Performance

Table 1. Multiple DELETE within LOOP comparison with respect to DELETE outside LOOP

2

Graph 1. Chart showing tables with rows 13K to 30K has better performance while DELETE within LOOP

The first glance of the data shows; for table rows with entries less than 10,000; DELETE outside LOOP works better. Look at column 6 (% change). This column is the percentage change between DELETE outside LOOP and DELETE within LOOP. Reference is DELETE outside LOOP.

Are you an ABAPer? A must read for you. SAP ABAP on HANA. Part I: Create your first program in HANA Studio.

How to interpret column 6.
Negative number means, DELETE outside LOOP is better.

If you go through the above table 1 or graph 1, it clearly depicts that DELETE inside LOOP using INDEX and PARALLEL Cursor is clear winner from 13,000 to 30,000 records in internal table. But again after 35,000 rows in internal table, DELETE outside LOOP is the winner.

So, it made me conclude (wrongly) that:
i) For small internal tables, less than 10,000 rows, one DELETE outside LOOP was good.
ii) For medium tables 10K to 30K row entries, DELETING using index within LOOP in PARALLEL cursor was good.
iii) For large tables 35K and greater, one DELETE statement outside LOOP has better performance.

Please note, I have mentioned I wrongly concluded. The above three statements are NOT completely correct.

I was still not convinced with above result. I was wondering, if there is any other factor which is influencing the result other than the number of entries.

Then I decided to run the program in a different client. I ran for the same numbers of records starting from 1000 till 40,000.

Performance Tuning

Table 2. Multiple DELETE within LOOP showing negative improvement over DELETE outside LOOP

Table 2 shows the result of running the same program in different client. The output does not tally with Table 1 results at all. Here one DELETE statement outside the LOOP is clear winner. The age old funda passed on by seniors/mentors holds good here. DELETE inside LOOP is never advisable.

SAP ABAP internal table Performance Analysis

Graph 2. Charts showing % improvement of DELETE within LOOP even after following Parallel Cursor Technique is NEGATIVE

Look at the graph, the performance of DELETE within LOOP is below zero (i.e not better than DELETE outside LOOP)

[adToAppearHereLink]

Also Read: Lazy and Smart ABAPers.

But, I wanted to know the reason. WHY? Why is that in the other client, there was different pattern and in this client DELETE outside LOOP is better?

Then, I happened to notice the ‘No of Rows Deleted’ column 2. I began to develop my theory and started doubting, is this one of the several factors which is influencing the overall performance.

Referring to Table 1
Out of 1000 enteries in table, 430 were deleted. So by the time the last entry was deleted, i.e. 430th entry, there were 1000-429 = 571 rows in the table. So, when 430th was deleted, indexes were regenerated for 570 rows in the internal table.

Which method impacts the performance more?

a) Regenerating the indexes every time the internal table row is deleted in LOOP; or
b) Regenerating the indexes when it encounters the DELETE statement only once outside the LOOP.

Depending on the cost of the regeneration of indexes, probably the performance depends.

SAP Help document says: When deleting rows in an internal table, costs are incurred for updating all existing table keys and table indexes.

Food for thought.
In Table 2, line with 30,000 records in internal table. Which method would have better performance.
a) Deleting 1931 rows in LOOP and regenerating the index in every loop for maximum of 29999 rows (first delete) and minimum of 28069 records (last delete)? 

When the first row is deleted, there are 29,999 rows in internal table to be re-indexed. When the last row i.e. 1931th is deleted, there are 28,069 rows in internal table to be re-indexed. In total, the internal table is re-indexed 1931 times in LOOP.

Also Read: Google Map API in SAP.

[ad3ToAppearHere]

You might also like: SAP Adobe Forms for Beginners.

b) Deleting 1931 rows at one shot outside the LOOP and re-indexing?

DELETING outside LOOP seems superior even though in the PARALLEL cursor we are DELETING using INDEX. But, may be the cost of updating the existing table keys and table indexes downgrades the advantage of PARALLEL CURSOR DELETE using INDEX.

I am not sure, how correct the above analysis is. But, one thing is clear, internal table re-indexing cost should be considered before deciding which route to go.

We can safely conclude that performance of the two scenarios is dependent on:
i) The number of entries in the table
ii) The number of entries to be deleted in the table
iii) The total number of entries left after delete statement
iv) Total number of times the index of the internal table need to be regenerated

And for now, DELETE outside LOOP seems to have an Upper Hand.. (I am stating something which might attract lots of criticism/debate from Parallel Cursor Technique lovers).

I would like to request experts and mentors to throw more light on this topic. If you have better explanation to this topic, please let us know. Also please point us to the right source/link which are authentic and where we can get more answers and better understanding.

Please share your experience and thoughts so that we might learn from your experience. Did you every try to do a similar analysis? What were your results?

 

Image source: kognitio.com.

Share on Facebook43Share on LinkedIn37Tweet about this on TwitterShare on Google+0
Please Share!!

About the Author

SAP Yard
SAP Yard

SAPYard is one stop page for all Technical Folks in SAP. You would find un-conventional explanations, tutorials, and tricks.

Please like our Facebook Page and also join our LinkedIn Group.

38 Comments on "DELETING rows of the internal table within the LOOP. Is it a Taboo? A big NO NO?"

  1. Kripa Rangachari | May 24, 2016 at 8:42 pm | Reply

    Interesting analysis 🙂 Came for Vistex and got this interesting insight!!!

    Thanks for posting

    Regards,
    Kripa Rangachari.

    • Dear Kripa – We are glad that you found more than you searched for.. 🙂 Thank you so much for leaving us your feedback.

      Please keep visiting.

      Regards,
      Team SAPYard.

  2. Mr. Abhishek jain | March 7, 2016 at 5:40 am | Reply

    Nicely explained!!! Great work Raju sir… I go with outside delete.

    Can you provide your views on “Is binary search good for every scenarios?? Or it also depends on number of data in internal table and number of time read statement used”

  3. Dear Raghu

    Thanks for your examples, are very demostratives.

    My way to delete records in a internal table is using Field Symbol.

    First you Loop your table and mark the records that you want to delete.

    After that, outside the loop you can using delete statement with where condition.

    Is the way that Learned and I Think is faster and easy to maintained.

    Please, contact me for any example by skype camejia24.

    Thanks
    Cesar Mejia

    • Dear Cesar – Thank you so much for going through the post and leaving your feedback.
      We too found the Field Symbol method most used in real practical projects.
      Thank you so much for sharing your contact. We would reach you if we need any help.

      Please keep visiting.
      Regards,
      Raju

  4. Good read. Such articles helps to reinforce our understandings.
    Thanks
    Raghu

  5. Really great job. You put a lot effort in this experiment. I must to say thank you.

  6. Hi SAP Yard!

    Thanks for the detailed explanation! I really like it, it’s very impressive!
    I vote for “DELETING outside the LOOP”, I always do deleting like this.

    Regards,
    @abapmentor

  7. Siddhartha Datta | September 1, 2015 at 10:14 am | Reply

    Hi Raju,

    Thanks for explaining in details different scenarios for deletion within loop.

    Apart from these two proven methods, we can accomplish the same by appending the rows we want to retain to another local internal table from within the loop…and just after the loop, we can refresh the original internal table, move data from the local internal table to the original internal table and then free the local internal table.

    Best wishes.
    -Siddhartha

    • Hi Sid – Thank you for your comment. Yes we can do in the way described by you. May be, I will try to write something on comparison of the method with parallel cursor.

      Regards,
      Raju.

  8. Nice work!..

  9. Mohamed Haarish | August 27, 2015 at 12:52 am | Reply

    Hi Raju,

    Your work is great! I never wanted to write a code like this and try! May be you inspire me to do in future!

    Regards,
    Haarish

  10. Good effort. Informative and very interesting cases.
    This is another proof what is good for handling high volume might not be good for low volume.
    In a SAP environment, I am not sure of who would really care about a few seconds performance difference over inside loop or outside loop deletion. When volume is low, either solution is ok. It is the volume that makes difference.. So it is generally a safe-approach to use mass operation instead of individual operation in a loop.

    • Dear Eric – Really honoured that an expert like you writing your thoughts here. Thank you so much. I follow your posts diligently. Thank you so much.

      True, only for high volume we need to be extra careful.

      Regards,
      Raju

  11. Prasanta Maiti | August 7, 2015 at 7:48 pm | Reply

    Superb analysis bosss!!! Hats off!! It is really very interesting statistics and made the topics very interesting to all of us. Really thanks for all your effort.

  12. Sumanta Hati Sumanta Hati | August 7, 2015 at 3:55 am | Reply

    This is good and informatic analysis.

  13. Mutero william | August 6, 2015 at 2:17 pm | Reply

    Thank You so much…!!!

  14. very good analysis

  15. Rajeev Kumar | August 6, 2015 at 8:56 am | Reply

    Hi Raju da,

    Appreciate your efforts to analyse this !!!..I am also of the same opinion to use delete outside the loop until data is manageable. But many times with huge data it would be better to chose delete inside loop for a block of data. Parallel cursor is always a plus. So I vote for “Parallel Cursor Technique to mark the row to be DELETED and then DELETING outside the LOOP”.

    Also in your analysis you can put one more graph by keeping the number of entries same but changing the number of rows to be deleted.

    Best Regards,
    Rajeev

    • Hi Rajeev – True. Both the methods can be handy in different scenarios. If we are sure of the pattern of rows to be deleted in advance, then definitely we can plan better on which method to use.
      Thank you for your suggestion. Will try to incorporate.

      Regards,
      Raju.

  16. Angshuman Pal | August 6, 2015 at 8:32 am | Reply

    Nice analysis.

  17. krishnamraju | August 6, 2015 at 7:54 am | Reply

    excellent analysis dude……u put a lot of hard work..

  18. Good work…Your effort is appreciated!!

  19. Sandip Barnwal | August 6, 2015 at 6:26 am | Reply

    Very Informative even though we use it everyday we use it because we were told so / because everyone uses it. Please keep up the good work.

Leave a Reply to Daniel Rios Cancel reply

Your email address will not be published.


*