Is cascade delete Slow

Question

Post reply

Is cascade delete Slow

yuvipoy

SSChampion

Points: 10469
More actions
March 4, 2014 at 11:23 pm

#396369

Hi,

Is cascade delete is slow in sql server ?

Having fk reference of 15 tables.

If the record is deleted in main table(pk table) the following child tables (fk tables (15tables) ) record is also deleted.

My main table contains some 10 million records.

If i delete some 5 million records the corresponding child tables record is also deleted, child table will have some 50k records each, here it is taking more time(nearly 1 hour :crying:) to delete the records, the tables are designed with cascade delete , is this a problem?

Can i remove the cascade delete?

What can be the alternate way to delete the records with reference?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

Cascade delete is pretty dangerous. I don't know of many people that use it. It tends to create very large transactions that lead to lots and lots of blocking. You're going to have more control by deleting the child records yourself and then deleting the records from the main table, but, that's a heck of a lot more work than setting up cascade delete. But, you're unlikely to see performance improvements. Deleting 5 million records and x number of child records for each of those records is going to be a long process. What you want to do is prevent that process from blocking all other operations on the database, and that's best achieved by tossing the cascade deletes and manually deleting up the chain.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

yuvipoy SSChampion Points: 10469 More actions · Answer 2

Grant Fritchey (3/5/2014)

Cascade delete is pretty dangerous. I don't know of many people that use it. It tends to create very large transactions that lead to lots and lots of blocking. You're going to have more control by deleting the child records yourself and then deleting the records from the main table, but, that's a heck of a lot more work than setting up cascade delete. But, you're unlikely to see performance improvements. Deleting 5 million records and x number of child records for each of those records is going to be a long process. What you want to do is prevent that process from blocking all other operations on the database, and that's best achieved by tossing the cascade deletes and manually deleting up the chain.

So how can we delete manually for parent child relationship.

can we pass delete statment across all the tables (in this case)?

what is the best approach for delete statement in the case of parent (1 table)- child (20 tables )relationship.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

SSISDB incorporates cascaded deletes and MS release it without including indexes for the fk's in the child tables. As a result, heaps of people (we don't have clustered indexes) saw their SSIS maintenance jobs taking days after deleting packages. Bingle it, there's tons of information.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

yuvipoy (3/5/2014)

Grant Fritchey (3/5/2014)

Cascade delete is pretty dangerous. I don't know of many people that use it. It tends to create very large transactions that lead to lots and lots of blocking. You're going to have more control by deleting the child records yourself and then deleting the records from the main table, but, that's a heck of a lot more work than setting up cascade delete. But, you're unlikely to see performance improvements. Deleting 5 million records and x number of child records for each of those records is going to be a long process. What you want to do is prevent that process from blocking all other operations on the database, and that's best achieved by tossing the cascade deletes and manually deleting up the chain.

So how can we delete manually for parent child relationship.

can we pass delete statment across all the tables (in this case)?

what is the best approach for delete statement in the case of parent (1 table)- child (20 tables )relationship.

It's not a single delete statement. It's one for each table. Like I said, a bunch of work, but more direct control.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

yuvipoy SSChampion Points: 10469 More actions · Answer 5

yuvipoy

SSChampion

Points: 10469

March 5, 2014 at 9:20 am

#1694369

So u mean to say

Delete from main_table where id=some_input;

Delete from Child_table1 where id=some_input;

Delete from Child_table2 where id=some_input;

Delete from Child_table3 where id=some_input;

Commit tran;

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 6

yuvipoy (3/5/2014)

So u mean to say

Delete from main_table where id=some_input;

Delete from Child_table1 where id=some_input;

Delete from Child_table2 where id=some_input;

Delete from Child_table3 where id=some_input;

Commit tran;

Nope, you have to do it the other way around. Delete from each of the child tables, then the main table.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

ScottPletcher SSC Guru Points: 100262 More actions · Answer 7

Yes, the logging especially will be take a lot of time.

But, it will be much more bearable if:

1) the child tables are clustered properly (as usual!), i.e., starting with parent key (rather than a ~~(dopey)~~ identity).

2) the child tables don't have any nonclus index(es), or at least no more than 1 (perhaps 2)

SQL should defer the actual page deallocations if they are more than 128 extents, so if all rows are within a single, clus key range, it shouldn't be that awful for the CASCADE DELETE itself (the first person that reads through the table may get slammed though).

Personally, I'd use CASCADE, but do the DELETEs from the main table in smaller batches. It's extremely difficult to keep all the tables in sync when trying to do the DELETEs yourself. Likewise, I'd recommend against trying to do your own data integrity in the code and to use FK references instead.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!