bulk delete - cascading delete performance killer?

My SQL Server 2008R2 database has a lot of large, related tables:

• master
• detail-1
• detail-2
• detail-2-A

all foreign keys are cascading:

• master -> detai开发者_Python百科l1
• master -> detail2
• detail2- > detail-2-A

So when I delete a record from master, all the related details follow. Deleting takes a very long time. E.g. one master can have 1.2 million records in detail2 and 60 million records in detail2A.

Would it a more efficient to create a stored procedure that deletes first the detail2A, then detail2 etc. Or is there a better approach.

DB is not partioned.

We don't allow delete cascade on our systems because you have no control over locking table when a large delete is done. If you have large numbers of records to delete frequently, there are several choices. First, do you really need to delete or should you just make them as inactive? This is probably the most efficient way to handle.

Or you can process in batches. Move all the id values you want to delte to a staging table and then delete them 1000 (or 20000 or 200 the actual number can only be determined by the system for performance reasons.) at a time and loop through until all the records are deleted. You still might want to experiement with deleting the records one table at a time, rather than deleting in a cascade.

Or you can mark the records for deletion and do the actual deletes overnight during the low usage periods of the database to avoid blocking other users.

One thing to consider with cascade delete is do you really want to delete the record if child records exist? Foreign keys exist, in part, to stop you from doing that and cascade delete overrides that behavior. So if your child records are something like orders, under no circumstances do you want to delete them automatically in a cascade delete or you will lose financial information you need even if the customer is no longer active.