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!