db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rami Ojares / PDF-Comics Oy <r...@absinth.fi>
Subject Re: delete from table
Date Fri, 15 Jan 2010 13:47:09 GMT
One observation about what makes "DELETE FROM TBL" slow.

I have two tables

CATEGORY that holds 10 rows.

ABSTRACT_PROGRAM that holds 20000 rows each pointing with a foreign key 
to category table where it is defined that ON DELETE SET NULL

If I delete CATEGORY table first and then ABSTRACT_PROGRAM deleting the 
CATEGORY table (=10 rows) takes over 30 minutes.
This is caused somehow because the ABSTRACT_PROGRAMs category references 
have to be set to null.

If I do it the other way around deleting those two tables takes 
something like 30 SECONDS!!!

It is beyond me why it takes so long to set the CATEGORY attributes to 
null in ABSTRACT_PROGRAM table.

It becomes even more mysterious because there is another table called 
IMAGE that contains images.
ABSTRACT_PROGRAM table also refers to that table with a foreign key 
constraint.
But deleting IMAGE table does not take that long at all.

The only difference between these relations is that Many 
ABSTRACT_PROGRAMs point to ONE CATEGORY
whereas ONE ABSTRACT_PROGRAM points to ONE IMAGE.
this might explain it.

- rami

Mime
View raw message