db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jay _ <iwud...@hotmail.com>
Subject memory usage for row delete cascade
Date Mon, 18 Aug 2008 09:16:19 GMT

Hi all,

I have a database with three tables (Table_A, Table_B and Table_C). There is one identity
column in Table_A which acts as a foreign key in Tables B and C with an ON DELETE CASCADE.

Here is the table structure .... 

Table A : Col_x_ID , Coly_Name, .... 
Table B : Col_p_ID, Col_q_Name, .... Col_x ... FOREIGN KEY(Col_x) REFERENCES Table A (Col_x)
Table B : Col_m_ID, Col_n_Name, .... Col_x ... FOREIGN KEY(Col_x) REFERENCES Table A (Col_x)

When I now try to delete a row in Table_A, I experience a spike in CPU usage to almost 100%
and also the memory  usage bumps from 40MB to 100MB. This remains sustained for a few seconds
(like 5 seconds) causing the UI to almost freeze in my application. Even worse, it causes
an Out of Memory Exception!

I am pretty sure that something isn't quite right with what I am experiencing. I know row
inserts and deletes are computationally expensive, but surely it has to be less expensive
than what I am experiencing. 

I'd appreciate if anyone can tell me how to optimise this or is there a more efficient method
to delete entries with a cascade effect?

I am running Derby version on MacOSX with JRE 1.5 version 6.


Get thousands of games on your PC, your mobile phone, and the web with Windows®.
View raw message