Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

cascaded delete  causing delete query to time out

Posted on 2011-03-04
6
Medium Priority
?
377 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:TheCommunicator
  • 3
  • 2
6 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 1336 total points
ID: 35041018
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
 

Author Comment

by:TheCommunicator
ID: 35041073
exactky: what trigger is going to do is doing the same thing manually what Cascaded delete was doing automatically....
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1336 total points
ID: 35041093
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 664 total points
ID: 35043576
@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
 

Author Comment

by:TheCommunicator
ID: 35112549
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
 

Author Comment

by:TheCommunicator
ID: 35492988
Thank you so much for all the help guys :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question