asked
Delete Cascade
We have a table which has 18 children tables, and two of them have foreign key set using Cascade Delete condition. In stored proc when deleting data from parent table, it is exclusively removing data from all children including these two. So not using this condition really.. Now in this case still the inbuilt trigger is invoked I believe, does it effect the performance. Our DB is over 1.5 TB and thie stpred proc timeout for even 100 records delete, as this is one of key table. I am looking to improve the performance here and wondering whether I should remove the (cascade delete). Experts can you provide some advice on where else to look for, or what conditions to use. Will Read Committed help? Thanks, veena
You're still calling delete even though cascade is in place? Yes, it will affect performance, a little bit. It's going to try to delete data that has already been deleted. So it's an extra read to the table. But, if you're also getting scans to delete this data (and I don't know how your queries are performing based on what you've told us) you may even see large performance hits (a scan for missing data is not really any faster than a scan for data that's there). To understand the performance of the queries, you should look to the execution plans to understand how the data is being accessed. Without that, you're just guessing. READ COMMITTED is the default isolation level. I can't tell if it's going to help in a given situation without knowing what isolation level you have. But, since you're deleting data, your isolation level is less important because SQL Server will take exclusive locks while it deletes data, even if you're using READ UNCOMITTED.
15 People are following this question.
