patternsqlMajor
Methods of speeding up a huge DELETE FROM <table> with no clauses
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:
The t-log is already on a separate disk.
Any suggestions welcome!
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:
Where xxx is, say, 50000
A modification of this, if you want to remove a very high percentage of rows...
SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
DELETE TOP (xxx) MyTableWhere 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 #HoldingtableCode Snippets
SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
DELETE TOP (xxx) MyTableSELECT 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 #HoldingtableContext
StackExchange Database Administrators Q#1750, answer score: 42
Revisions (0)
No revisions yet.