Large multi-record insert performance

[Richard <ri...@fsa.gov.uk>]

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.