db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Performance problem
Date Sun, 11 Sep 2005 14:11:41 GMT
Philippe Hamelin <philippe.hamelin.1@ens.etsmtl.ca> writes:

> Hi,
>
> I just did a test program to verify performance of derby. I tooks 23 seconds to insert
500 rows in a table.
>
> Here is the create table statement :
>
> String sqlCreateTable = "CREATE TABLE test (oid INT, " +
>
>                         "userName VARCHAR(100), " +
>
>                         "password VARCHAR(100), " +
>
>                         "firstName VARCHAR(100), " +
>
>                         "lastName VARCHAR(100))";
>
> Then I just called :
>
> for(int i=0; i<500; i++) {
>
> String userName = "user"+i;
>
>       String password = "pass"+i;
>
>       String firstName = "firstname";
>
>       String lastName = "lastname";
>
>       String sqlInsert = "INSERT INTO test VALUES("+i+", '"+userName+"', '"+password+"',
'"+firstName+"',
> '"+lastName+"')";
>
>       Broker.executeUpdate(sqlInsert);
>
> }
>
> The broker is just a wrapper on the executeUpdate of Derby. Note that the 23 secondes
only include this <for loop>.
>
> I’m running on windows XP and my computer is a AMD Sempron 2600 with 768Mb DDR400.
Anything wrong ?

The performance will probably increase enormously if you rewrite your
code to something like this (not tested):

String sqlInsert = "INSERT INTO test VALUES (?, ?, ?, ?, ?)";
PreparedStatement ps = connection.prepareStatement(sqlInsert);
connection.setAutoCommit(false);
for(int i = 0; i < 500; i++) {
    String password = "pass" + i;
    String firstName = "firstname";
    String lastName = "lastname";
    ps.setInt(1, i);
    ps.setString(2, userName);
    ps.setString(3, password);
    ps.setString(4, firstName);
    ps.setString(5, lastName);
    ps.executeUpdate();
}
connection.commit();

Using one PreparedStatement object 500 times is much faster than
creating 500 Statement objects. Performing all the inserts in one
transaction (connection.setAutoCommit(false) ... connection.commit())
will also increase the performance.

-- 
Knut Anders

Mime
View raw message