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.
