topic Re: Delete custom table records without performance issue in Application Development and Automation Discussions

Delete custom table records without performance issue

former_member671693 — Sun, 26 Apr 2020 08:23:32 GMT

Hi,

In my custom table having more than 10 lakhs records.Need to delete the records without performance issue.

Could you please suggest best solution for this.

I have found some below solutions...

1. Using package or up to rows, can split the records and deletion can be performed.

Re: Delete custom table records without performance issue

Sandra_Rossi — Sun, 26 Apr 2020 09:19:30 GMT

The SQL statement DELETE, in itself, should not lead to performance issues.

So, I suggest that you first learn how to identify the cause of performance issues. There are already many blog posts/questions about this topic in the forum.

The addition "package size" concerns only SELECT and does not, in itself, improve the performance, but the use of packages allows making several ABAP processes run in parallel, so you have a gain in performance at ABAP side. Using packages may also avoid memory/network issues.

I don't see how "up to rows" can improve the performance (it's only to allow working with packages).

Now, the question is, do you have a performance issue, and what does it concern?

Re: Delete custom table records without performance issue

teshanappadoo — Sun, 26 Apr 2020 10:01:42 GMT

Hello,

Do you need to do this by code ? If not, you can use SE14(database Utility) to erase all the records.

Regards,

Teshan

Re: Delete custom table records without performance issue

former_member671693 — Sun, 26 Apr 2020 11:06:52 GMT

@ sandra.rossi

For delete entries in database table i use instruction:

Delete from <table> where <zone> = 'X'.

The delete take seven hours (the table is very big and <zone> isn't an index)

How can i optimize for reduce the delete time.

I have tried this one......now...

do100times.

select*fromintotable itab.whereupto10.000 records.

if( itab isinitial)

exit.

endif.

delete...fromtable itab.

commitwork.

Re: Delete custom table records without performance issue

Sandra_Rossi — Sun, 26 Apr 2020 12:31:56 GMT

Please use the COMMENT button for comments, questions, adding details, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area.

Re: Delete custom table records without performance issue

Sandra_Rossi — Sun, 26 Apr 2020 12:40:40 GMT

In English, I think you mean 1 million records in the table. It's somewhat a low volume nowadays. If you delete these lines using:

DELETE FROM (tablename) WHERE zone = 'X'.

and the table doesn't have an index with column ZONE, then probably the database optimizer does a full scan (but only the Execution Plan of the database can confirm).

But you say that it takes seven hours to run, I can't imagine that this single statement can take so much time. So, before changing any ABAP code without even know the reason of that, first analyze the situation: do an ABAP + SQL trace, make sure what takes time, get the execution plan. If you don't know how to interpret them, paste the traces here.