Knut Anders Hatlen wrote:
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.
If you make the suggested changes, you will see considerable
improvements.
For reasons as to why this improves performance- also check out the
following performance tip; faq at
http://db.apache.org/derby/faq.html#derby_faster
Sunitha.
