fastest way to run DELETE command

Question

Post reply

fastest way to run DELETE command

mgraulich

Ten Centuries

Points: 1294
More actions
March 13, 2007 at 2:03 pm

#63051

I have to purge selected data from my db (around 70 million rows) using the delete command unfortunately. Whats the most efficient way of doing this?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 1

a Move data you are keeping into a temp table

truncate original

restore temp into original table

b break the data down into a reasonable count (10k? 100k? ) and loop through the deletes with a moving delete clause

while yada

begin

delete from target where (your criteria) and id between @low and @high

set low and high

end

those would be my starting location.

Daryl

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

http://www.sqlservercentral.com/columnists/sjones/batching.asp

Deadwood Mr or Mrs. 500 Points: 522 More actions · Answer 3

How many of the 70 million rows are you deleting?

Thanks

Leo Leong SSCommitted Points: 1615 More actions · Answer 4

I have similar problem in my database.

I need to purge 10 mil record every month. My table size is 233 mil.

I can move the data to a temp table first. But, I can't truncate my original table (due to foreign key constraint). Unless I drops all my constrains/indices and rebuild it again (I am not sure how much time it will take).

Eventhough I can delete batch by batch, it will still take me 2 - 3 hours to complete.

Is there any other suggestion?

Thanks.

Leo Leong

mgraulich Ten Centuries Points: 1294 More actions · Answer 5

I followed Steve's instructions up top and I was getting about 2 million deletes an hour (8 way 12 GBs ram on a SAN). Looking back, I probably should have just took the data I needed and dumped it to a temp table and truncated.

Thanks.

Camilo Torres SSCrazy Points: 2034 More actions · Answer 6

I have seen INSERT working faster than DELETE so, I will:

1) copy monster_table to temp_table

2) script the table to CREATE first then DROP and build a script that will do this:

DROP TABLE [monster_table]

CREATE TABLE [monster_table] (with all its constraints and indexes)

insert into [monster_table]

select <fields>

from [temp_table]

where <just the records you need>

DROP TABLE [temp_table]

Try this scenario and compare what takes longer.

Deadwood Mr or Mrs. 500 Points: 522 More actions · Answer 7

You could always, create the temp table, transfer the records you want to the temp table, drop the orignal table and then rename the temp table to the orignal table name...

Thanks