db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nurullah Akkaya <nurullah_akk...@yahoo.com>
Subject keeping the table ordered
Date Mon, 05 Feb 2007 22:51:20 GMT
i am using derby in embedded mode.
i have a table of 100 million records when i do a select i get 600k  
to 1million records table structure is as follows

		stmt.executeUpdate("CREATE TABLE POSTINGLIST ("
				   +"WORDID INTEGER NOT NULL,"
				   +"DOCID INTEGER NOT NULL,"
				   +"ANCHORID INTEGER NOT NULL,"
				   +"DOCPOSITION SMALLINT NOT NULL,"
				   +"FLAG SMALLINT NOT NULL)");
		
		stmt.executeUpdate("CREATE INDEX WORDID ON POSTINGLIST(WORDID)");
		stmt.executeUpdate("CREATE INDEX DOCID ON POSTINGLIST(DOCID)");
		stmt.executeUpdate("CREATE INDEX ANCHORID ON POSTINGLIST(ANCHORID)");

select docId , docPosition , anchorId, flag from postingList  where  
wordId = ?

99 percent of the time i select ( above query ) based on the wordId i  
have implemented all the tuning tips in the manual but it stil takes  
so much time( more than a minute ) disk i/o seems to be the bottleneck 
( no swapping occurs  cpu is idle during select derby uses index)  
what i want to do is keep the tables sorted by wordId so that i can  
avoid random reads and do a sequential read. rigth now insert  
performance is faster than i expected so i can trade some write for  
read is this possible? if this is not possible out of the box can you  
give me some tips as to how can i implement this in to the source code?

Thanks for your time...


Nurullah Akkaya
nurullah_akkaya@yahoo.com

Blooby.com
Tel: +1 (256) 270 4091




Mime
View raw message