views:

122

answers:

3

I have a database in which there is a parent "Account" row that then has a 1-Many relationship with another table, and that table has a 1-Many relationship with another table. This goes on about 6 levels deep (with Account at the top). At the very bottom there could possibly be thousands (can even go beyond 100k) of rows. On each table there is a foreign key set to cascade on delete.

The issue is, that if I try to delete the very top row (an "Account"), it can take minutes, sometimes well over 10 minutes. Is there a faster way to delete all the rows (such as maybe going from the bottom up in individual delete statements) or is cascading pretty much it?

I am using MSSQL 2005 & MSSQL 2008 for the server, ans L2S to perform the delete, although i can use a T-SQL statement if it is faster.

Ive tried doing the delete from the SQL Management Studio too, and that takes just as long.

edit: we have tried re-indexing the database, with negligible difference, maybe a minute or two difference. I appreciate all your answers, it looks like i am going to have to start writing some code to do soft deletes!

+2  A: 

A delete is a delete, and if you want to delete massive amounts of rows (100k), it will take a while.

If you do a soft delete (set a status to "D" for example) you can then run a job to actually delete the rows in batches of say 1,000 or so over time it may work better for you. The soft delete should update only the header row and would be very fast. You'd need to code your application to ignore these "D" status rows and their children though.

EDIT To further @Kane's comment. you could only do a soft delete, or you could do a soft delete followed by a batch process to do the actual deletes if you really want to. I'd just stick with the soft deletes if drive space is not an issue.

KM
further to @KM I'd strongly suggest that you read an excellent article from Udi Dahan on deleting data: http://www.udidahan.com/2009/09/01/dont-delete-just-dont/
Kane
@Kane, nice link, I'm a big fan of the soft delete
KM
also, soft deletes are real nice when a user calls up and says that they deleted something in error...
KM
I'd agree on the soft deletes as well. The only addition I would make to it is that, depending on the environment, I will often also move the "deleted" records off to a separate "archive" table identical to the original just to keep things clean and keep table and index sizes minimized. If there's not a lot of deletion, I don't, but if a table has frequent "deletes" then I do.
BBlake
@BBlake, that does depend on the environment. if you have many foreign keys on the soft deleted data, then it becomes very tricky to move or actually delete the data.
KM
If you use soft deletes, do you not need to work in some way to actually delete the data? If not, won't the inevitable table growth impact performance and storage space? I've written jobs that run weekly on Sunday PM to delete old data, and others to "move" it to archive tables. Plan now, or pay later!
Philip Kelley
A: 

It sounds like you might have indexing issues.

Assume a parent-to-child relationship on column ParentId. By definition, column ParentId in the Parent table must have a primary or unique constraint, and thus be indexed. The child table, however, need not be indexed on ParentId. When you delete a parent entry, SQL has to delete all rows in the child table that have been assigned that foreign key... and if that column is not indexed, the work will have to be done with table scans. This could occur once for each table in your "deletion chain".

Of course, it might just be volume. Deleting a few k rows from 100k+ databases with multiple indexes, even if the "delete lookup" field is indexed, could take significant time -- and dont' forget locking and blocking if you've got users accessing your system during the delete!

Deferring the delete until a schedule maintenance window, as KM suggests, would definitely be an option--though it might require a serious modification to your code base.

Philip Kelley
+1  A: 

Have you indexed all the foreign keys? That's a common issue.

erikkallen