db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Richard <richard.st...@fsa.gov.uk>
Subject Large multi record inserts into a large database.
Date Wed, 20 Jan 2010 09:15:49 GMT
Sorry, I am new to this gmane thing so I have posted this message in the 
developers section aswell by mistake.

I am writing a test database application to see if derby is suitable for the 
job. I have a very large dataset (>100m records in the main table) which grows 
at more that 1m records per day. I want to insert these records quickly each 
day and then perform various queries on the data.

I have tested the insert performance with no indexes using prepared statements 
and only committing after each 1000 records and I get excellent insert rates 
of about 3000 records/second. When I add a simple index based on two 10 
character columns (varchar(10)) the peformance of the inserts dives to about 
30 records/second which is a much greater degradation than I expected. Can 
anyone tell me where I might be going wrong?

I am running the embedded version of Java DB. My record (row) length is about 
80 bytes made of 8 strings (varchar(10)). The index I created is on two of 
these strings. I have tried altering the pageSize and the pageCacheSize to no 
avail.

Any help will be geatly appreciated.



Mime
View raw message