asked
Deleting DB entries
For ex: Lets say there are 100000 entries which i got after a date filter on a DB. now i want to delete those entries . How do i do that? The problem here would be that there will be linked tables through FKs, i want to delete those entries also from DB. Whats the best way to implement this? How can performance be improved. i want to implement this by normal scripting, without using partitioning and other concepts.
You'll need to do a few things to make this work well. First, you will have to know the structure so that you can delete all the related data prior to deleting the data in the principal table. That means also deleting data related to the related data. Second, you're not going to want to do all the deletes in a single transaction. This will lead to a huge transaction log and lots and lots of blocking. Instead, you should plan on breaking down the deletes into smaller chunks, say 1,000 rows at a time. To support this you'll need to load the data from your SELECT criteria as described into a temporary storage (temp table or table variable, since you'll be searching on it, probably a temp table is better). Then you can use this data to step through the delete process. It's a lot of work, but it's the safest and most efficient mechanism I know.
If you want to delete a parent row and all other child rows referenced by foreign keys you could use cascading deletes. Alternatively you would have to work you way up from the bottom of the tree deleting child, parent, parents parent etc.
14 People are following this question.
