question

jctronicsshiva avatar image
jctronicsshiva 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.
sql-server-2008sql-server-2008-r2stored-proceduresdatabasedelete
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tanglesoft avatar image
tanglesoft answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.