db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Segel" <mse...@segel.com>
Subject RE: memory usage for row delete cascade
Date Mon, 18 Aug 2008 12:07:43 GMT
Since you're a bit cryptic..

 

On Table A, you show Col_x_ID, but in your foreign key, you show Col_x. I'm
going to assume that you meant Col_x_ID.

 

On Table A, do you have an index on Col_x_ID? Is Col_x_ID a unique ID? If
so, is this the primary key for the table?

 

That would be the first thing to look at. If no index exists, my guess would
be that you're doing a sequential table scan.

 

HTH

 

-Mike

 

 

  _____  

From: jay _ [mailto:iwudh8u@hotmail.com] 
Sent: Monday, August 18, 2008 4:16 AM
To: derby-user@db.apache.org
Subject: memory usage for row delete cascade

 

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) ON DELETE CASCADE)
Table B : Col_m_ID, Col_n_Name, .... Col_x ... FOREIGN KEY(Col_x) REFERENCES
Table A (Col_x) ON DELETE CASCADE)

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 10.4.1.3 on MacOSX with JRE 1.5 version 6.

Thanks,
Jay

  _____  

Get thousands of games on your PC, your mobile phone, and the web with
WindowsR. Game with <http://clk.atdmt.com/MRT/go/108588800/direct/01/>
Windows


Mime
View raw message