HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Methods of speeding up a huge DELETE FROM <table> with no clauses

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
deletewithmethodshugespeedingfromtableclauses

Problem

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!

Solution

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

Code Snippets

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable
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

Context

StackExchange Database Administrators Q#1750, answer score: 42

Revisions (0)

No revisions yet.