Improving Delete Performance

Question

Improving Delete Performance

Jack Corbett

SSC Guru

Points: 184394
More actions
August 18, 2011 at 1:16 pm

#150457

My current project is to delete data related to a widget if the widget has been inactive for more than 24 months. I have about 33000 widgets that meet the criteria for deleting. Due to FK's I have to delete data from 50+ tables to delete the Widgets. Because the business want the deletion of each Widget and related data to be all or nothing I have to loop through the widgets and do the deletes in a transaction per widget. The problem is that it takes about 4 minutes per Widget because, even though all related data is deleted before hand, the FK's have to be verified on each delete. The majority of the look ups are index seeks with a very few scans (index & table) so adding indexes isn't likely to help, and I don't know how those would affect other processes. I wanted to do the delete in a set, which I think would still take about 4 minutes, but I was overruled on that one.
I have checked for triggers and there are none. I'm thinking that the most likely way to speed this up is to disable the FK's, but, the application is live during this time (although it will be done overnight with limited activity), so I don't think that is an option.
Any other suggestions?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 1

I don't normally recommend it but have you considered changing the FK's to "Cascade on Delete"? It sounds like that would be the easiest and fastest way to do this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Jeff Moden (8/18/2011)
I don't normally recommend it but have you considered changing the FK's to "Cascade on Delete"? It sounds like that would be the easiest and fastest way to do this.

Hadn't really considered that Jeff. For the same reason I don't really want to disable the FK's, although changing it to Cascade on Delete is better than disabling the FK's. I'll have to look into that.

Thanks

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 3

Gosh... I just thought of this... if all these things are properly FK'd (heh... if that doesn't sound like an HR violation, I don't know what does), then what would actually cause a delete to fail on any of this? My guess is... nothing. Start a transaction, lock the tables, do your deletes, commit the transaction if no errors, rollback if errors.

If that causes too much interference with users, then I'd say you're stuck with either the RBAR method you suggested or the cascading FK's.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Jeff Moden (8/18/2011)
Gosh... I just thought of this... if all these things are properly FK'd (heh... if that doesn't sound like an HR violation, I don't know what does), then what would actually cause a delete to fail on any of this? My guess is... nothing. Start a transaction, lock the tables, do your deletes, commit the transaction if no errors, rollback if errors.

That was my original recommendation. They thought that would take too long and cause too many issues. I actually don't think it would take much longer than doing the single row deletes (down to 3 minutes after a minor change). There are 4 parts that are 90% of the time and they are all due to enforcing the FK's which wouldn't be much more, I don't think, with larger batches. Even if we did it in 1000 "widget" batches over 30 nights, it would be better. The boss doesn't really understand SQL and I can't convince her that the set-based method will be fast and unlikely to fail.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 5

Gosh... if it's taking 4 minutes per row to delete because of foreign key updates, there's bound to be some blocking or even deadlocking if people try to access any of those rows while the delete is in progress. The cascaded FK's may reduce that time but 4 minutes per record a high price to pay. Perhaps one overnight experiment for a half hour to check for blocking is in order.

As a side bar, I'd likely also be using table and query hints out the ears to try to prevent lock escalation from the row level.

You have, however, peaked an interest in such a widely keyed table deletion. I've got too much on mmy plate to get to it soon but I'm just going to have to set up a 50+ FK scenario like that of which you speak and see if the cascaded FK's work and what else may work. This isn't all that uncommon a problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

I'm not sure that that table and query hints would help much. The majority of the access patterns are seeks which I assume would be at most Page Locks. I'll have to add locking events to my trace for my next test run.

I'll also have to setup some queries to run during the run to see if I get any deadlocks or major blocking. We are getting out of my experience at this point.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Well, still working on it. Added an index and that took 15% off.

The real winner is disabling and then re-enabling the FK's. There are 48 FK's on this table and disabling them, deleting, and then enabling them reduced it to sub-second.

I'm not sure I like that, but if it stays sub-second for large amounts of data I may stick with it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 8

"Sub-second" is a purdy word, Jack. Thanks for the feedback on what you ended up doing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nadrek SSC-Insane Points: 20039 More actions · Answer 9

Jack Corbett (8/24/2011)
Well, still working on it. Added an index and that took 15% off.
The real winner is disabling and then re-enabling the FK's. There are 48 FK's on this table and disabling them, deleting, and then enabling them reduced it to sub-second.
I'm not sure I like that, but if it stays sub-second for large amounts of data I may stick with it.

Did you check to see if your FK's are trusted after this? At a guess, you didn't use WITH CHECK CHECK in the re-enabling, which means they're no longer trusted by SQL Server, which has optimizer implications as well.

Untrusted constraints article; detection SQL is at the end.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

Nadrek (8/25/2011)
Jack Corbett (8/24/2011)
Well, still working on it. Added an index and that took 15% off.
The real winner is disabling and then re-enabling the FK's. There are 48 FK's on this table and disabling them, deleting, and then enabling them reduced it to sub-second.
I'm not sure I like that, but if it stays sub-second for large amounts of data I may stick with it.
Did you check to see if your FK's are trusted after this? At a guess, you didn't use WITH CHECK CHECK in the re-enabling, which means they're no longer trusted by SQL Server, which has optimizer implications as well.
Untrusted constraints article; detection SQL is at the end.

Nope I did use WITH CHECK CHECK to make sure the constraints were trusted after. I haven't deployed with this yet because I don't really like disabling FK's even for a very limited time, but it may be the only way the boss finds performance acceptable.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question