Fastest way to delete large data from sql server

The time required for deleting increase with this technique of deleting records in lower volumes / groups.

When i delete 25,000 records at one go it takes 17 secs
When i delete 25,000 in group of 10,000 it takes 33 secs
When i delete 25,000 in group of 500 it takes 1:39 secs

With this the total processing time increases many folds, which will degrade the performance.

kindly suggest some other way.

There a lot of utilities for insert bulk data, isnt there any thing for bulk deletion of data.

P.S.
Database is stored in sql server.

step 1 drop all indexes oin the table
step 2 truncate the table : truncate table MyTable
step 3 Import data
step 4 Re create indexes

anneshattingal,
My concern is more because the volume of data inside this table is going to increase many fold, in the future the table will carry millions of records
Considering this the time for deletion will be substiantial, on the other hand, to use a temp table, i will have to use select into to throw out records which are or use, then truncate, then again fill the records back from temp table. I had a benchmark here also, the last part of insert rows back into the table is taking a lot of time.

regbes,
as i have explained that the deletion is conditional, therefore when i truncate i will have to store the required in some other table and get it back. This is time consuming again.


---More findings--------------

I found that when i was executing the deletion by -
connection.execute (delete from table where companyid=1) - this took 4:07 mins
Then on the same data, i created a stored procedure for deleting the data, and surprisingly the time was half of the existing 2:37 mins
sql query analyzer took - 2:07 mins to finish the same job

What are your suggestions on the above findings.

-- Or --
Rename table to oldTable
CREATE table AS Select * From oldTable.
DROP oldTable

When i delete 25,000 records at one go it takes 17 secs
When i delete 25,000 in group of 10,000 it takes 33 secs
When i delete 25,000 in group of 500 it takes 1:39 secs

these are small amouts of data for sql ou will start seeing performance gains using this sort of strategy when the total amount of records you need to delete are in tke millions

25,000 records is just a small benchmark, the last finding that i have put up, has 200,000 records and out of which the number of records which qualify for deletion is 175,000. Again this ratio of total record to deletion records can not be the same always, it may be the other way also.

Therefore i am trying to find an optimal solution in this context.

why dont take the opposite way around ?
query from the table only the records you need and overwrite the table ...

DeepDan
as already stated above, writing records back into the table is a very expensive task

I assume you have indexes on the deletion key?

yes the is an index on companyid field

>25,000 records is just a small benchmark, the last finding that i have put up, has 200,000 records and out of which the number of records which qualify for >deletion is 175,000. Again this ratio of total record to deletion records can not be the same always, it may be the other way also.

>Therefore i am trying to find an optimal solution in this context.

these sorts of tests do not scale. try delete 2 mil records in one go then delete the records in batches of 250 000 these are the sort of levels where you will start to see improvements

the very large transactions (implicit or ecplict) will speed up when broken into smaller groups better improvements can be seen when the breaks are along clusterd index values
 

If you are doing this through ADO then you can try setting the connection to asynchronous.

Then when you issue the delete, the command will return immediately, then raise a completion event when it is complete.

This might resolve your timeout issue anyway.