April 14, 2010 at 2:24 pm
Hello,
I am trying to delete duplicates.
The table has about 100 million duplicates!
I am deleting in batches of 500,000.
I have indexes created on WHERE clause (most of them).
However, since the table is huge, its taking time.
I want to know if anyone has used a better efficient way to delete the records.
Thank you.
April 14, 2010 at 3:20 pm
I think you need to provide more info before anybody here can help you.
What does the table schema look like?
How are you detecting the duplicates? Is THAT the process that is taking a long time, or is the delete itself taking a long time?
What do your indexes actually look like?
You'll get better performance if you can take the file offline while you're deleting and set the recovery mode to bulk logged. Every DELETE is adding an entry into the transaction log if you are using the 'Full' recovery model.
Rob Schripsema
Propack, Inc.
April 14, 2010 at 3:27 pm
If you are deleting most of the rows and you have enough space, it might be faster to insert the rows you are keeping into a new table, and renaming both tables when you are done.
April 14, 2010 at 3:31 pm
Depending on your requirements, you may also want to read this article: http://www.sqlservercentral.com/articles/T-SQL/67898/.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply