Solved

cascaded delete  causing delete query to time out

Posted on 2011-03-04
6
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 334 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 334 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
Industry Leaders: 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 166 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 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