April 14, 2015 at 6:06 am
Hi,
I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.
What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.
If anybody know the best approach, please do let me know.
Thanks,
Naveen
April 14, 2015 at 7:53 am
Naveen J V (4/14/2015)
Hi,I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.
What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.
If anybody know the best approach, please do let me know.
Thanks,
Naveen
Since you didn't post enough information for a detailed answer (we can't see your tables) about all we can say is the approach you are doing should work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2015 at 8:08 am
ON DELETE CASCADE really places a lot of locks , log is growing badly and it's rather slow. But when trying do it 'by hand' with a number of smaller transactions in concurrent environment a risk of inconsistent DB state can not be ignored. So the procedure cann't be simple.
May be it's reasonable to have CASCADE and delete rows from parent by small batches.
Just a general point as no specific specs have been provided yet.
April 14, 2015 at 8:28 am
Your approach of deleting the grandchildren, then children then parent is the same one I use. If you have foreign keys defined, then the DELETE statements will benefit from an index on the column. The foreign key is there to provide referential integrity, but the performance gain comes from the index.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply