Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
2  
If you will be doing this a lot, consider partitioning the table –  Gaius Mar 16 '11 at 20:41
    
Can you not use TRUNCATE because there are FK constraints referencing the table? –  Nick Chammas Aug 10 '11 at 15:56
add comment

4 Answers

up vote 10 down vote accepted

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
share|improve this answer
    
Thanks, I'll give that a try and update the thread. But just out of interest - why is that faster? –  tuseau Mar 15 '11 at 14:01
2  
@tuseau: each delete requires some log space in case of error, to rollback. A 50k row delete takes less resources/space than 10m row delete. Of course, log backups still run etc and take space but it's easier on the server to lots of small batches than mucking big one. –  gbn Mar 15 '11 at 14:29
    
Thanks, the batch delete helps a bit, I guess it's the best option. –  tuseau Mar 16 '11 at 9:36
    
@gbn I thought the batched operations were to prevent excessive blocking and preserving disk space. We had a process that was inserting rather than deleting and the batching did not help the speed. Only getting the indexes out of the way helped. I know I'm leaving a lot of details out. I'm more curious as to the results you've seen on your servers as I may very well be doing something else wrong (autogrow, etc.). @tuseau If you use the 2nd set of code from gbn, you can also use the OUTPUT clause instead of that final insert to save the need for an extra DML statement. –  Phil Helmer Apr 21 '11 at 7:31
1  
@Phil Helmer: if the batch delete is in a transaction then there is no gain is using it. Otherwise each log write is smaller which is, simply, an easier load –  gbn Apr 21 '11 at 7:36
show 1 more comment

I agree with the suggestions to batch your deletes into managable chunks if you aren't able to use TRUNCATE, and I like the drop/create suggestion for it's originality, but I'm curious about the following comment in your question:

It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE

I'm guessing the reason for this restriction has to do with the security that needs to be granted to directly truncate a table and the fact that it would allow you to truncate tables other than the one you are concerned with.

Assuming that is the case, I'm wondering if having a stored procedure created that uses TRUNCATE TABLE and uses "EXECUTE AS" would be considered a viable alternative to giving security rights necessary to truncate the table directly.

Hopefully, this would give you the speed you need while also addressing the security concerns that your company may have with adding your account to the db_ddladmin role.

Another advantage of using a stored procedure this way is that the stored procedure itself could be locked down so that only specific account(s) are allowed to use it.

If for some reason this is not an acceptable solution and your need to have the data in this table removed is something that needs to be done once a day/hour/etc, I would request that a SQL Agent job was created to truncate the table at a scheduled time each day.

Hope this helps!

share|improve this answer
add comment

Except truncate.. only delete in batches can help you.

You could drop the table and recreate it, with all constraints and indexes, off course. In Management Studio you have the option to script a table to drop and create, so it should be a trivial option. But this only if you're allowed to do DDL actions, which I see it's not really an option.

share|improve this answer
    
Because the application is designed for concurrent operations, altering the structure (DDL) and using truncate are not options... I guess batch delete is the best available. Thanks though. –  tuseau Mar 16 '11 at 9:35
add comment

You could use the TOP clause to get this done easily:

WHILE (1=1)
BEGIN
    DELETE TOP(1000) FROM table
    IF @@ROWCOUNT < 1 BREAK
END
share|improve this answer
    
The curly brackets format your code –  gbn Mar 15 '11 at 15:10
    
@gbn That is on SO. here it is still 101 010. –  bernd_k Mar 15 '11 at 19:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.