November 8, 2007 at 12:48 pm
Contemplating whether to use cascade delete or not.
- This is for a SQL 2000 DB.
- As part of weekly purge, data older than 6 months are purged off all transaction tables of db
- have a Package (master table) in which possible 200,000 to 300,000 records will be deleted off the table each week
- have about 6 to 7 other child tables linked with package id with 1-many relationship with master table (no instances of many-to-many)
- child table could potentially have 10-20 references per packageid in child table
- currently we do not have foreign keys set up
- the current purge job is handled thru sp which takes care of deleting all child rows before the master records are deleted
As we now have more growth in the # of child tables, one of the suggestions put forth was to use CASCADE DELETE
Our current purge job is running quite smoothly for many years without major issues. I like the idea of the simplicity of CASCADE DELETE...but was wondering if there is any major risks involved on transaction log, locking of the tables and such given that it is a bulk-purge rather than a specific record deletion???
Gurus...This is such a great forum that I reference ever so often when in doubt...Please advise. Thanks
November 8, 2007 at 1:29 pm
I can't really speak on any performance issues that may cause, but one thing to consider is what other affects this may have.
You'll need to make sure that any applications that are doing inserts into those tables are doing things in the correct order so they don't blow up wehn you set up the foreign keys. I used to manage a database with no foreign keys, but I couldn't set them up just because an application that was adding data to the tables didn't insert them in the correct order, and the inserts would fail when I set the foreign key constaints up.
Just something to keep in mind.
The Redneck DBA
November 10, 2007 at 8:47 pm
I'm assuming you're going to get the same performance hit as executing the deletes individually and wrapping them in a transaction. It should have the same effect on the transaction log and on locking. It wouldn't be hard to run a test, though, to verify and see the behavior of each approach. But I wouldn't think they would be very different from each other.
K. Brian Kelley
@kbriankelley
November 11, 2007 at 6:30 am
just to add..
Make sure you have indexes that exactly match your FK-definition in the dependant table(s). (column order, column sorting order (asc/desc))
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply