SQL Server Performance Forum – Threads Archive
cascading deletes
does cascading deletes give better performance vs manually deleting all FK records?Cascadin deletes give you oportunity to control integrity.
In manual yoy has to begin with botton table tree and going up, so I suppose is better cascading delete.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
I don’t think that there is much difference in terms of performance… I’ve never had any performance issues with declared RI… One thing to watch for though is transactions getting too big depending on how complex your cascade is (especially true for self-referencing constraints) Cheers
Twan
we have a data import that inserts/updates/deletes based on existing data. ALOT of calls going to getting rid of child records. ALOT of SP calls. Just wondering if someone has a real performance cost analysis for this.
The net performance difference between the two methods of deleting dependant data will be zero if there is a useful index on the foreign key. Another factor to consider is the concurrent activity on the target parent and child tables (read locking and deadlocking). Irrespective of any measurable performance differences, the second method introduces the question of a lag phase in data integrity because if the time difference between deleting ‘parent’ records and ‘child’ records – and this will be amplified by the depth of dependencies and the complexity of the relationships between your tables. IMHO, the in-built cascading referential integrity will save you the headache of writing and maintaining code (stored procs,triggers etc.) and will ensure almost real-time data integrity. In the end your relational architecture (hopefully based on validated requirements) should be the primary determinant of which method you elect to implement. This way, performance will be a beneficiary rather than a driver of the DELETE statements that you send to the SQL server engine. Nathan H.O.
Moderator
SQL-Server-Performance.com
> Irrespective of any measurable performance differences, the second method introduces the question of a lag phase
> in data integrity because if the time difference between deleting ‘parent’ records and ‘child’ records – and
> this will be amplified by the depth of dependencies and the complexity of the relationships between your tables. This shouldnt be an issue inside a transaction. Also if the user chooses not use use cascade delete, I would assume he does still choose to have constraints to enforce the relationships integrity, therefore deleting a parent before the child would not be possible. Deleting the children first, and then working back up the hierarchy deleting each parent is how I do it. I also prefer not to use cascade delete, for the simple reason that it is <i>translucent</i>. You can sometimes get into real difficulty with complex relationships when relying on cascades. Theyre fine for a simple two or 3 level hierarchy though IMO.
]]>