db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Insert/Update Performance
Date Fri, 18 Jul 2008 13:27:00 GMT
Paul Henry wrote:
> Hi All,
> We have been using an embedded derby data base within our application for
> some time now. However Recently we have added a new customer that has a
> significantly larger amount of data within the application than existing
> customers. The customer has been complaining about performance. We have
> tracked the issue to our persistance layer.
> We are using Hibernate layered over Derby. The database is relatively
> simple, just six or seven tables, and we have added indexes where ever
> required. So the Query speed doesnt seem to be a problem. However when
> saving a new item into the database (An item will create 1 or 2 entries in
> most of the tables and around 30 entries in 2 of the tables) we have been
> able to measure a decay in the insert time as the db grows in size.
> 1) With an empty db we had measured delays of 31-140 ms to write a new item
> into the DB
> 2) When the DB contained 1 million items (so a couple of the tables have ~30
> million rows) we measured delays of 1000-1500 ms to store a new item
> 3) When the DB contained 2 million items we measured delays of 1600-2000 ms
> to store a new item
> Similar delays were recorded when performing an update of existing data.
> Has anyone had any experience tuning the combination of hibernate over Derby
> for databases with a similar table size, or does anyone have any advice how
> to improve performance, whilst retaining both hibernate and derby?

Hello Paul,

A few questions first to better understand your environment.

1) What's your Derby version? JVM? Platform?
2) Is this using a single connection to the database?
3) Is auto commit enabled (default) or disabled?
4) Are you sure indexes are being used when you insert/update data?
5) Can you try to compress the tables to make sure the statistics are up 
to date and see if that helps on a database where the insert/update 
performance is poor?
6) Since you are describing this as a performance problem over time, 
with growing database size, I don't think disk IO itself is the source 
problem but can you describe what kind of IO activity you see when 
inserting/updating data?

You might also want to post a bit of derby.log with statement logging 
enabled, if the table structure and/or data can be made public.


View raw message