db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Barnet Wagman <b.wag...@comcast.net>
Subject Suggestions for improving performance?
Date Tue, 14 Dec 2004 22:41:40 GMT
I'm in the process of converting an app to Derby (from MySQL).  Running 
some simple tests, I'm getting surprisingly slow times.  Of course I 
don't expect Derby to be as fast as MySQL, but the differences I'm 
getting are > 2 orders of magnitude.  So perhaps I'm doing something 
wrong.  I've pretty much tried everything relevant in "Tuning Derby" and 
I've tried varying all possibly relevant Derby properties.  (I set 
properties progarmmatically using the 'Properties info' parameter of  
DriverManager.getConnection()). Nothing has made much of difference.

I've been working with two simple tests, an INSERT and a SELECT

(i) The INSERT test:  1000 jdbc calls of
    "INSERT INTO " + tableName +
                             " ( int0, int1, int2, blob0, " +
                             "   short0, blob1 ) " +
                             " VALUES ( ?, ?, ?, ?, ?, ? ) "

int0 has a unique index; int1 and int2 have non-unique indexes.
Each INSERT has different values.
The 'blob0's are ~600 bytes each, the 'blob1's are zero length byte[].
The blobs are loaded using ByteArrayInputStreams and ps.setBinaryStream(). 
The same PreparedStatement is used in each iteration; 
ps.clearParameters() is called after each INSERT.
This takes ~ 134 seconds on derby and ~ 5 seconds on mysql.

(ii) The SELECT test:  1000 jdbc calls, reading back the data entered in 
the INSERT test, using

    "SELECT blob0, int2, blob1 " +                 
                            " FROM " + tableName +
                            " int1 = ? AND int2 = ? "

As in the INSERT test, the same PreparedStatemnt is used for each 
iteration; ps.clearParameters() is called after
each select.  Note that the two variables that define the select 
criteria are indexed.
This takes ~ 105 seconds on derby and ~ 1 second on mysql.

FYI I ran these tests with Cloudscape 10.0 (under SuSE 9.0) (haven't had 
time to re-run them under Derby

A couple questions/issues:

Re logging: Something I read in the Derby documentation (or perhaps in 
the mailing list archive) indicated that logging may be expensive. Is 
there any way to disable logging completely?

Re record vs table locking:  "Tuning Derby" indicates that record level 
locking can add a lot of overhead and implied that there's a way to 
force table locking, but it wasn't clear to me how to do this.

Any comments/suggestions re tweaking performance would be appreciated.


View raw message