topic DELETE FROM database table takes more time... in Application Development and Automation Discussions

DELETE FROM database table takes more time...

Former Member — Tue, 15 Sep 2009 05:46:42 GMT

Hi Friends,

The below statement takes more time.

LOOP AT i_final.
  DELETE FROM zcisconec WHERE werks = i_final-werks
                          AND aufnr = i_final-aufnr
                          AND vornr = i_final-vornr.
ENDLOOP.

Internal table I_FINAL will have more than 80,000 records.

DB Table zcisconec have 4 primary key fields out of 10 fields.

Below 4 fields are primary key fields

WERKS

AUFNR

VORNR

MATNR

Please guide me..How to optimize it?

Regards,

Viji

Re: DELETE FROM database table takes more time...

Former Member — Tue, 15 Sep 2009 05:57:36 GMT

Hi,

Instead of looping the internal table and deleting the Db table use the below syntax,


DELETE zcisconec from i_final.

Make sure i_final has the same structure as that of zcisconec

Regards,

Vikranth

Re: DELETE FROM database table takes more time...

Former Member — Tue, 15 Sep 2009 06:00:46 GMT

Hello Viji,

it depends on the data you have. Is the matnr filled? Or do you want do delete every entry with a combination of werks/aufnr/vornr? Then you can first sort the table, loop and do a at new vornr. So you have to do less deletes.

Another possiblity, but I'm not sure if it works, is too collect the entries to delete in a special itab with fields werks/aufnr/vornr and do a delete zcisconec from table itab_special. I'm not sure if it works, because you don't have the complete key specified.

Regards,

Bastian

Re: DELETE FROM database table takes more time...

Former Member — Tue, 15 Sep 2009 06:02:11 GMT

try sorting i_final .

sort i_final by werks aufnr vornr.

LOOP AT i_final.

DELETE FROM zcisconec WHERE werks = i_final-werks

AND aufnr = i_final-aufnr

AND vornr = i_final-vornr.

ENDLOOP.

Re: DELETE FROM database table takes more time...

Former Member — Tue, 15 Sep 2009 06:05:36 GMT

why u using LOOP for deletion .if u have some lines from ifnal needed to be deleted.then its better to first move those line to another internal table and then just user DELETE Itab

means. if u have table ifinal which have 80,000 records.

in this table u found suppose 1000 record needed to be deleted..so u move those records to another internal table like i_Deleted

then when u required just delete complete with DELETE I_delete so its performance will be good.

Re: DELETE FROM database table takes more time...

Former Member — Tue, 15 Sep 2009 06:06:17 GMT

hii,

add some more conditions to your where clause,if possible .

Re: DELETE FROM database table takes more time...

Former Member — Tue, 15 Sep 2009 06:11:48 GMT

HI,

Check this one ,

put a break point on that delete statement and add another line of code after, like .... CHECK SY-SUBRC = 0. Now got to debug and stop at the DELETE statement, check the number of records in your DB table, now hit F5 to step to the next statement, now go back to SE16 and refresh, do you see the number change? It should.........if you are selecting the data correctly, make sure that you are getting data into the IT_ table.

DELETE zcisconec from i_final.

CALL FUNCTION 'DB_COMMIT'.

Regards,

Ansari.

Edited by: Ansari Samsudeen on Sep 15, 2009 8:14 AM