Solved

cascaded delete  causing delete query to time out

Posted on 2011-03-04
6
367 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 40

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 40

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now