delete from table with internal table or delete in loop

[Former Member]

HI ,

I have in program table with entries which i need to delete on DB tables.

The question is what is the difference in performance between doing loop and delete one entry at a time or using the commend : delete dbtable from itab .

The problem in the delete from itab (give all table at a time ) that u don't know which record is not deleted u just have sy-dbcnt but i don't see any other indication so when u doing loop i know which record is exactly not deleted and send some message .

p.s. i guess that the delete from itab doing loop inside too .

Regards

Nina

[Former Member]

You are right Nina. Asking SAP to delete all the corresponding entries from the database table that exist in an internal table is faster however you will then have to ascertain if all the records were deleted or not by adding extra code checking for their existance after the deletion. If the table is widely used it is quite likely that the records you are deleting could be locked by some other process. In this mass deletion the locked records (locked by other processes) will not be deleted and the unlocked records will.

Deleting records within a loop could be time consuming but you will not have to write additional code to determine if the deletion was successful or not.

The other constraint with mass deletion is that you will have to lock the entire table (there is a limit on the number of records you can lock so creating a lock for each record is not advisable). When you delete in a loop you can lock, delete and release lock so you will be creating just one lock effectively.

One thing you need to keep in mind is that as far as possible do not delete data from SAP provided tables programatically. Use a BAPI if available of a BDC to delete a record. For example if you want to delete a sales order deleting records from VBAK and VBAP is not advisable. Use a DBC or BAPI to do the same.

[Former Member]

Hi Nina,

Delete single record within loop will always access database and the no. of database hits are going to be increased, thats why it is not the efficient one.

You can also use an internal table to delete several lines:

DELETE <target> FROM TABLE itab .

This deletes all lines from the database that have the same primary key as a line in the internal table <itab>. The same rules apply to the line type of <itab> as to the work area <wa> described above.

If the system cannot delete a line because no line with the specified key exists, it does not terminate the entire operation, but continues processing the next line of the internal table.

If all lines from the internal table have been processed, SY-SUBRC is set to 0. Otherwise, it is set to 4. If not all lines are used, you can calculate the number of unused lines by subtracting the number of deleted lines in SY-DBCNT from the total number of lines in the internal table. If the internal table is empty, SY-SUBRC and SY-DBCNT are set to 0.

Whenever you want to delete more than one line from a database table, it is more efficient to work with an internal table than to insert the lines one by one.

Check this link for further info.

[http://help.sap.com/saphelp_46c/helpdata/EN/fc/eb3aef358411d1829f0000e829fbfe/frameset.htm]

[Former Member]

it is as you said,

the

DELETE dbtab FROM TABLE itab.

is faster, because it is an array-operation.

However, if you need to know for every single line, whether the line was deleted or not, then processing in a loop is probably better:

LOOP AT itab INTO wa.
  DELETE dbtab FROM wa.
  IF ( sy-subrc <> 0 ).
...
  ENDIF.
ENDLOOP.

If there are really a lot of records, then a preselect with a FOR ALL ENTRIES might be better. You check what of itab is

on the database, they can be deleted with the array operation abd what is not on the database needs you additional logic.

Siegfried

[kesavadas_thekkillath]

Just an Suggestion,

Instead of deleting ( which results in rebuilding of index )

isn't it better to move the satisfied entries to a new internal table ?

[Former Member]

isn't it better to move the satisfied entries to a new internal table ?

it is about db tables !!!