Deleting many records takes a long time 1

[dragonwell]

Hi,

I need to delete about 86,000 records based on the value of a non-key column. After running for about 30 minutes without completing, I cancelled the query. I ran the index tuning wizard on the delete query and it recommended creating a non-clustered index on that column. So I did that and tried the delete again. Still not completing after minutes, so I cancelled it again. Should it be taking this long? What should I do?

Thanks

ps - this is sql 2005, if that helps.

[blue]_______________________________________[/blue]
Business Logic:"AND when tweetle beetles battle with paddles in a puddle, they call it a tweetle beetle puddle paddle battle AND..." - Dr. Suess

 

[dragonwell]

Oops, I meant to say "Still not completing after 20 minutes" the second time.

 

[manmaria]

How big is the table?

 

[SQLSister]

What else has to happen when the records delete? Are there triggers or lots of referential integrity checks it has to make. Our main table has 99 foreign key tables (and they are delberately set to not do a cascading delte as we don't want to delete from the main table if any other records exist) so it would take us hours (maybe even days)to delete this number of records. And every table has an audit log which records the deltes, so the deltes are an inherently slow process. Sometimes that's the price of doing business, expecially if you do not often run large deletes.

My suggestions to you are:

First, pull all the ids for the records you want to delete into a holding table of some kind. Then join to this table and delete in batches. You might want to start with a very small batch just to see how long it takes for a few records.

Run the process during non-business hours if at all possible. You may be getting blocking issues during the day that is slowing the delete.

Check your query with a select to make sure it will only be deleting the records you intended to delte. If a delete takes too long, sometimes it is deleting more records that you expected.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[dragonwell]

Thanks. The table only has 92,013 total records and no foreign keys at all. I'll try the holding-table technique.

[blue]_______________________________________[/blue]
Business Logic:"AND when tweetle beetles battle with paddles in a puddle, they call it a tweetle beetle puddle paddle battle AND..." - Dr. Suess

 

[dragonwell]

cool, this seems to be working

declare @ids table(ErrorID uniqueidentifier)
insert into @ids 
select top 1000 ErrorID from Error_Log where statuscode = 404 order by TimeUtc
delete from Error_Log where errorid in (select ErrorID from @ids)

takes 15 seconds per 1000, so at least I know how long to expect.



[blue]_______________________________________[/blue]
Business Logic:"AND when tweetle beetles battle with paddles in a puddle, they call it a tweetle beetle puddle paddle battle AND..." - Dr. Suess