cascaded delete causing delete query to time out

hello guys,

I have cascaded delete option turned on on my database design . As a result of that, it is going to try to delete all the dependent rows when the delete on the parent table is going to happen.

because of this, ,it causes the delete query to time out.

is there any quicker way to do this?
TheCommunicatorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
Drop the FK constraint and try with a trigger. Create a trigger and ON DELETE operation, delete the rows from othe table.
But I am not sure if this improves the performance as I have not tested.
0
 
TheCommunicatorAuthor Commented:
exactky: what trigger is going to do is doing the same thing manually what Cascaded delete was doing automatically....
0
 
SharathConnect With a Mentor Data EngineerCommented:
Trigger is not a manual process. If you have a 'ON DELETE' trigger, it would be invoked and the statement in your trigger would be executed when a record is deleted from the table.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
LowfatspreadConnect With a Mentor Commented:
@sharath
 i think the communicator means that you will have to manually write the trigger code ... whereas with the cascade delete its all processed automatically in the background...

i'm not clear that a trigger will be any quicker, however you may know things about the structure of the data which will allow you to perform the deletes more efficiently....

@TheCommunicator
 how long is your timeout set for? in what environment are you experiencing the problem?

can you post the delete code you have?

how often is this processing being performed?

why do you allow physical deletes in your business processing (rather than logical deletes)..

how many tables/rows are involved in the deletes which are cause a timeout?



 
0
 
TheCommunicatorAuthor Commented:
HIi LowFatSpread,
 We were actually hosting some website for our client and it was multi-tenancy architecture. Because of its multi tenancy nature, a lot of data from other clients was also there.

Now that the client wants to host the website itself, we are on a task to give them their data( without exposing other client's data)

In this case,  we have to go through manual deletes.

The database has to delete around 10k records but because of cascade, it  has to go through all the dependent tables for each row for 10k times.

It is timing out after 30 minutes and apparently it is taking more than 30 minutes to perform the task.
0
 
TheCommunicatorAuthor Commented:
Thank you so much for all the help guys :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.