Performance of Delete Statement

[Former Member]

Hello Everybody,

could you please tell me, which is better Delete statement and why from below statements.

1. DELETE FROM (lv_lead_table) WHERE pcrnr IN lt_pcrnr_temp.

2. DELETE (lv_lead_table) FROM TABLE <lead_backup_table> and

3. DELETE FROM (lv_lead_table) WHERE date_field LT target_field.

I also interested to know that how system will generate internal select statement for above statement.

Thanks in advance.

Regards,

Anil

[Former Member]

Hi Anil,

The better statment is the one which spend less time and fullfill your deletion requiriments. All mentioned SQL can be used with performance, it depends not for the statement it self but of indexes.

1. pcrnr is first column of a index on table ?

one native sql depends of the number of rows on lt_pcrnr_temp:

DELETE FROM table WHERE pcrnr IN (111,222,333,444);

2. In this case the kernel will send to database statments to delete the tables using primary key of table.

many native sql will be generated using the primary key (in group of 5 records each).

DELETE FROM table WHERE primarykey1 EQ lt_pcrnr_temp1-primarykey

OR primarykey2 EQ lt_pcrnr_temp2-primarykey OR primarykey3 EQ lt_pcrnr_temp3-primarykey OR primarykey4 EQ lt_pcrnr_temp4-primarykey OR primarykey5 EQ lt_pcrnr_temp5-primarykey;

3. date_field is first column of an index on table ?

one native sql is short and fixed as:

DELETE FROM table WHERE date_file < 20092403;

Regards,

Fernando Da Ró

[Former Member]

Hi Fernando,

Thanks for your reply. offcource PCRNR is part of primary key.

take the first case, suppose table is having 1000 records and we have below statement

DELETE FROM table WHERE pcrnr IN (111,222,333,444);

so, will system search whole table(1000 records) for each value from i.e (111,222,333,444) ?

I am deleting the table in Block of 500 records and table from which i am deleting records is having millions of entries. only for 500 deletion its seems it taking roughly i would say 7 - 8 min or some time more than that. Is there any way to improve the performance of this statement or can you suggest some better statement?

Regards,

Anil

[Former Member]

> Thanks for your reply. offcource PCRNR is part of primary key

any part is not sufficient, it must be the first key field, otherwise the primary key will not help, maybe

a secondary key.

Number 2

2. DELETE (lv_lead_table) FROM TABLE <lead_backup_table> and

should be the best in most cases, because it has the full records, i.e. the primary key.

But any of the three can become the winner.

For 1 and 3 you must take care, than an index supports the WHERE condition. It is the same as with the SELECT, no difference.

Siegfried

[Former Member]

Thanks Sie,

I got it.

Regards,

Anil