db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sunitha Kambhampati <ksunitha...@gmail.com>
Subject Re: Performance problem
Date Mon, 12 Sep 2005 01:18:13 GMT
Knut Anders Hatlen wrote:

>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.
>
>  
>
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.

Mime
View raw message