Deleting a sql server table takes much time

Question

Hi,

I have a table which have more than 380 million records.... I have a stored procedure which 1. Delete some records in that 2. Insert something into it.

The total procedure takes around 30 minutes to execute. Out of this DELETE takes 28 minutes.

Delete is a simple statement -> Delete a where condition_1 AND condition_2 AND condition_3

Can anybody help mme please...

Answer 1

It might help to index the fields in the condition. If you create a view of the rows you want deleted, how long does it take? If you can speed up a view, you can speed up the delete.

Answer 2

Are the conditions too large ? Maybe using index could help to delete faster.

Or, you may use truncate instead of delete.

CREATE [UNIQUE] INDEX indexName
ON table
(fieldName [ASC/DESC], ...)

The option ASC/DESC can define an order

Answer 3

How is your table organized? what clustered index you have and what non-clustered indexes you have? And what exactly are the 3 conditions?

A DELETE behaves much like a SELECT in that it needs to find the rows that qualify for deletion. To do so, it will use the same techniques a SELECT would, and if your condition_1, condition_2 and condition_3 don't have a covering index, they will trigger a table scan which is going to be timed by the size of data (380M).

Answer 4

Firstly have a look at your indexing and query..it should really in the first place not take 28 mins ?

It maybe worth taking a look at database tuning and query optimization...maybe you can also try to delete records incrementally..something suggested here..