Sql-server – Methods of speeding up a huge DELETE FROM with no clauses

deleteperformancesql server

Using SQL Server 2005.

I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement – except I'm not allowed to use TRUNCATE. The problem is the table is huge – 10 million rows, and it takes over an hour to complete. Is there any way of making it faster without:

Using Truncate
Disabling or dropping indexes?

The t-log is already on a separate disk.

Any suggestions welcome!

Best Answer

What you can do is batch deletes like this:

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable

Where xxx is, say, 50000

A modification of this, if you want to remove a very high percentage of rows...

SELECT col1, col2, ... INTO #Holdingtable
           FROM MyTable WHERE ..some condition..

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable WHERE ...

INSERT MyTable (col1, col2, ...)
           SELECT col1, col2, ... FROM #Holdingtable

Related Solutions

Sql-server – Should I lock the table during delete

Changing the locking to table locks will just make the deletes run even slower as the delete won't be able to run until the lock can be taken on the table which means that all other threads need to be finished or blocked. If you have foreign keys with delete cascade enabled that will probably take a lot of the time.

You might want to change it to a SQL Agent job so instead of running your app which connects and disconnects, you just run a loop deleting data until you are done.

SELECT NULL --Makes the WHILE loop work.
WHILE @@ROWCOUNT <> 0
BEGIN
    DELETE TOP (1000) FROM YourTable
    WHERE Column < getdate()-31
END

If this doesn't work you could look into table partitioning which would allow you to switch the data to another table very quickly, then truncate the data from the new table. This however does require Enterprise Edition.

Postgresql – Disk file effects of delete and vacuum

You can save on the delete and vacuum if you will use smart partitioning! Make sure your design is compatible with dropping partitions instead of actual DELETE.