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:[EMAIL PROTECTED] > Sent: Monday, August 18, 2008 4:16 AM > To: [email protected] > 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.
