?
Solved

Deleting rows from a database table having foreign key constraints using BMP with Jboss as server

Posted on 2006-04-07
13
Medium Priority
?
663 Views
Last Modified: 2008-01-09
Hi,
    i have two tables TabA and TabB. TabA is the parent table and TabB is the child table. A column in the TabB references Primary key in the TabA.

TabA contains 3000 rows
TabB contains 10 rows for each row in TabA sp total of 30,000 rows

   I am using BMP EJB to delete the rows from the two tables, first i have deleted the rows from the TabB..
   it took 600 milli seconds
 
  Now when i delete the rows from TabA (parent) it is consuming 4 minutes..... which is very high...
 
 later i tried to do the same thing by droping the foreign - key constraint form the database.
i.e i have dropped the foreign-key constraint TabB refers TabA.

 Now the delete is happening in 1 second..

 i think there might be some settings in the JBoss which is causing this delay... in the case of  foreign-key constraints.
 
0
Comment
Question by:sandeep63
  • 6
  • 4
11 Comments
 
LVL 6

Expert Comment

by:mightyone
ID: 16402217
how are you deleting?
hybernate or sql? show your query
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16404242
It would probably be a lot simpler just to specify a cascading delete on the parent table
0
 

Author Comment

by:sandeep63
ID: 16406108
Hi mightyone

I am deleting using SQL... Oracle Query...
and i am using CMP to delete them..

Hi CHEJ
Can you please help me how to use cascading delete on parent table...
i have added a clause for the foreign of the child table saying..... "cascade on delete"
but this did not improve the performamance it is still taking 4 minutes.. to execute.



0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 86

Expert Comment

by:CEHJ
ID: 16406383
>>Can you please help me how to use cascading delete on parent table...

It appears you've done what's necessary. If you're getting the same effect, we need to investigate further. Please post

a. full ddl definition of both tables
b. any sql involved in the delete
0
 

Author Comment

by:sandeep63
ID: 16424114
HI CHEJ

  here are the full DLL defination of both the tables...

 Parent Table
 
Table Name:  TestCaseDetails

    Id --- Unique -- number
    TestCaseName -- String

Child Table

Table Name: Test Case Data
 Id ---- references Id of TestCaseDetails


the query is as simple as it....

delete from TestCaseDetails where Id in(1,2,3,4,5....)

this query is talking much time using BMP...
if i remove the foreign key constraint from the child table then.. its very fast......

hope i gave you the sufficient information... so that you can investigate further....

thanks
Sandeep...

 


   
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16424174
>>here are the full DLL defination of both the tables...

No - the full DDL definition would be CREATE TABLE statements.

TestCaseData should have something like

CONSTRAINT DATA_DETAILS FOREIGN KEY(ID) REFERENCES TESTCASEDETAILS(ID) ON DELETE CASCADE

but see your Oracle SQL syntax guide
0
 

Author Comment

by:sandeep63
ID: 16459112
Hi CHEJ......

First of all thanks for your reply....

My TestCaseData table contains that constraint .... even though the time to delete from the parent table is not reduced...

but if i remove the DATA_DETAILS constrainsts the deleting process is very much faster.....

Thanks
Sandeep.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16459126
>>but if i remove the DATA_DETAILS constrainsts the deleting process is very much faster

That it's faster is quite normal. You could drop the constraint of course then reinstate it later
0
 

Author Comment

by:sandeep63
ID: 16505522
>>That it's faster is quite normal. You could drop the constraint of course then reinstate it later

Can you explain the process of dropping the constraint and reinstating later.....

Thanks & Regards
Sandeep
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16505544
Here's the syntax for dropping and adding constraints
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 200 total points
ID: 16505547
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

864 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