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

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.
 
sandeep63Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mightyoneCommented:
how are you deleting?
hybernate or sql? show your query
0
CEHJCommented:
It would probably be a lot simpler just to specify a cascading delete on the parent table
0
sandeep63Author Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

CEHJCommented:
>>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
sandeep63Author Commented:
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
CEHJCommented:
>>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
sandeep63Author Commented:
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
CEHJCommented:
>>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
sandeep63Author Commented:
>>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
CEHJCommented:
Here's the syntax for dropping and adding constraints
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.