Philippe Hamelin <[EMAIL PROTECTED]> 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
