Deleting data in a sql server table takes much time

I have a table which has more than 380 million records. I have a stored procedure which:

1. Deletes some records.
2. Insert something.

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

Delete is a simple statement, 开发者_开发技巧something along these lines:

Delete a where condition_1 AND condition_2 AND condition_3

Can anybody help me?

---

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).

---

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..

---

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

---

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.