Solved

cascaded delete  causing delete query to time out

Posted on 2011-03-04
6
371 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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…

756 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