Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
karthik_padbanaban
Constraint Violating Yak Guru
263 Posts |
 Posted - 2012-04-23 : 06:36:36
|
| hi, I have a table 'Test' of million rows in which, I want to delete some 30,000 records. here the table 'Test' has foreign key reference in 100 tables and in that 100 tables it has "on delete cascade" in 60 tables. so whenever i try to delete a row in 'Test' its taking 30 secs to delete row.. here i need to delete for 30,000 rows. please give me suggestion how should i proceed with this.Karthikhttp://karthik4identity.blogspot.com/ |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-23 : 09:27:51
|
Delete in small batches, say 100 rows at a time:DELETE TOP (100) FROM myTable You should also consider NOT deleting rows, but instead add a Status column and update it to a value like "deleted" or "inactive". If that's not an option, consider turning off the cascade delete, and create a stored procedure that performs the necessary deletes from the related tables in the proper sequence. Cascading deletes is not a best practice, and not just for performance reasons. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-23 : 11:34:56
|
| I'm also against cascadinf deletes as its really a pain to maintain the cascading logic code and quite often we wont realize whats happening when we do some deletions in main table as it might affect lot of dependent tables which we're not aware of due to presence of cascading deletes. Keeping all delete logic inside procedure will make it clear for anyone analysing the code at a later point of exact sequence of events happening and code will be more accountable.You can take a look at below logic to get dependency of tables and apply sequential delete logic to them.http://visakhm.blogspot.com/2011/11/recursive-delete-from-parent-child.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Delete takes too much time..