db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Shlomo Hershkop <sh...@cs.columbia.edu>
Subject Re: Suggestions for improving performance?
Date Tue, 14 Dec 2004 22:20:32 GMT
I ran into similar problems. If you turn off autocommit before many 
inserts and turn it on afterward, you can speed things up considerably.

here is a short method for doing this.

public void setAutocommit(boolean flag) throws SQLException {
		this.connection.setAutoCommit(flag);
		if(flag){
			this.connection.commit();
		}
	}


so setAutocommit(false) before and setAutocommit(true) after.

~Shlomo


On Tue, 14 Dec 2004, Barnet Wagman wrote:

> 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 10.0.2.1).
> 
> 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.
> 
> Thanks
> 

-- 
~Shlomo Hershkop
*  GRA - Columbia University
*  http://www.cs.columbia.edu/~sh553



Mime
View raw message