Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of gcaddu-derby-user@m.gmane.org
 designates 80.91.229.12 as permitted sender)
To: derby-user@db.apache.org
From: Richard <richard.stout@fsa.gov.uk>
Subject: Large multi record inserts into a large database.
Date: Wed, 20 Jan 2010 09:15:49 +0000 (UTC)
Lines: 22
Message-ID: <loom.20100120T101510-852@post.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: Loom/3.14 (http://gmane.org/)
Sender: news <news@ger.gmane.org>

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.