db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Iwud H8u <iwud...@hotmail.com>
Subject RE: memory usage for row delete cascade
Date Mon, 18 Aug 2008 15:52:57 GMT


Michael Segel wrote:
> 
> Since you're a bit cryptic..
> 
>  Hmm sorry I wasn't intending to be cryptic ... was trying not to be
> verbose! 
> 
> You are right about the column IDs ... lemme post the actual SQL
> statements for creating the tables themselves... nothing confidential in
> them I guess.... :)
> 
> ==================== tables  ==========================
>  CREATE TABLE Child_info (child_id INTEGER NOT NULL PRIMARY KEY GENERATED
> ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) , 
>                         first_name VARCHAR(32) NOT NULL, middle_name
> VARCHAR(32) NOT NULL, 
>                         last_name VARCHAR(32) NOT NULL)
> 
> CREATE TABLE parent_info (parent_id INTEGER NOT NULL PRIMARY KEY GENERATED
> ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) , " +
>                         "first_name VARCHAR(32) NOT NULL, middle_name
> VARCHAR(32) DEFAULT ' ', " +
>                         "last_name VARCHAR(32) NOT NULL,  child_id INTEGER
> NOT NULL ,
> FOREIGN KEY(child_id) REFERENCES Child_info (child_id) ON DELETE CASCADE)
> 
> CREATE TABLE attendance_info (attendance_id INTEGER NOT NULL PRIMARY KEY
> GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) , 
>                         monday VARCHAR(16),
>                         tuesday VARCHAR(16), wednesday VARCHAR(16) ,
> thursday VARCHAR(16) ,
>                         friday VARCHAR(16) ,
> UNIQUE (child_id) , 
> FOREIGN KEY(monday) REFERENCES sessions (session_name)  ON DELETE CASCADE, 
>                         FOREIGN KEY(tuesday) REFERENCES sessions
> (session_name) ON DELETE CASCADE, FOREIGN KEY(wednesday) REFERENCES
> sessions (session_name) ON DELETE CASCADE, 
>                         FOREIGN KEY(thursday) REFERENCES sessions
> (session_name) ON DELETE CASCADE, 
>                         FOREIGN KEY(friday) REFERENCES sessions
> (session_name) ON DELETE CASCADE, 
>                         FOREIGN KEY(room_name) REFERENCES rooms
> (room_name) ON DELETE CASCADE, 
>                         FOREIGN KEY(child_id) REFERENCES Child_info
> (child_id)  ON DELETE CASCADE )
> 
> As you can see the lines in bold declare the primary key and foreign keys
> (with on delete cascade). 
> 
> I am guessing child_id does not need a separate index to be generated
> because it is the primary key in Child_info table and atttendance_info
> tables. However child_id cant be either unique or primary in parent_info
> table because there will be atleast two parents with the same child_id.
> 
> I however created and index on this table using 
> 
> CREATE INDEX ChildIdIndex ON PARENT_INFO(CHILD_ID)
> 
> My query times for something like 
> 
> DELETE FROM CHILD_INFO WHERE CHILD_ID = 1
> 
> is around 6 seconds with only two row entries CHILD_INFO, 6 row entries in
> PARENT_INFO and just one entry in ATTENDANCE_INFO!
> 
> Hope this makes sense... 
> 
> 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
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/memory-usage-for-row-delete-cascade-tp19028179p19034510.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message