db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kathey Marsden <kmars...@Sourcery.Org>
Subject Re: Derby vs. HSQL, auto-commit
Date Thu, 09 Sep 2004 17:02:01 GMT
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jianhui Jin wrote:

> Even I set the auto-commit false, and reuse Statement.
>
> It is still slower than hsql.
>
> Jin
>
>
> -----Original Message-----
> From: Steen Jansdal [mailto:steen@jansdal.dk]
> Sent: 09 September 2004 17:01
> To: Derby Discussion
> Subject: Re: Derby vs. HSQL
>
>
>
>>Sure, it's below.  Definitely not the greatest code in the world, but
>>it is the exact same code that I'm using to test HSQL.
>>
>>Thanks!!!
>>
>>----------------------------------------------------------------------
>>-----------------------------------------------
>>
>>import java.sql.*;
>>
>>public class Cloudscape {
>>
>>  Connection conn;
>>
>>  public Cloudscape() throws Exception {
>>    Class.forName("com.ihost.cs.jdbc.CloudscapeDriver");
>>    conn =
>>DriverManager.getConnection("jdbc:cloudscape:test;create=true");
>>  }
>>
>>  public void shutdown() throws SQLException {
>>    conn.close();
>>  }
>>
>>  public synchronized void query(String expression) throws
>
> SQLException {
>
>>    Statement st = null;
>>    ResultSet rs = null;
>>
>>    st = conn.createStatement();
>>    rs = st.executeQuery(expression);
>>
>>    st.close();
>>  }
>>
>>  public synchronized void update(String expression) throws
>
> SQLException {
>
>>    Statement st = null;
>>
>>    st = conn.createStatement();
>>    st.executeUpdate(expression);
>>
>>    st.close();
>>  }
>>
>>  public static void main(String[] args) {
>>    Cloudscape cloudscape = null;
>>
>>    try {
>>      cloudscape = new Cloudscape();
>>    } catch (Exception e) {
>>      e.printStackTrace();
>>
>>      return;
>>    }
>>
>>    try {
>>      cloudscape.update("CREATE TABLE mytable(id int not null
>>generated always as identity, col1 varchar(256) not null, col2 int not
>
> null)");
>
>>    } catch (SQLException e) {
>>      e.printStackTrace();
>>    }
>>
>>    long start = System.currentTimeMillis();
>>
>>    for (int i = 0; i < 10000; i++) {
>>      try {
>>        cloudscape.update(
>>            "INSERT INTO mytable(col1, col2) VALUES('blah', 100)");
>>        cloudscape.update(
>>            "INSERT INTO mytable(col1, col2) VALUES('blah', 100)");
>>        cloudscape.update(
>>            "INSERT INTO mytable(col1, col2) VALUES('blah', 100)");
>>        cloudscape.update(
>>            "INSERT INTO mytable(col1, col2) VALUES('blah', 100)");
>>
>>        cloudscape.query("SELECT * FROM mytable");
>>
>>      } catch (SQLException e) {
>>        e.printStackTrace();
>>      }
>>    }
>>
>>    System.out.println("time: " + (System.currentTimeMillis() - start)
>
>
>>/ 1000);
>>
>>    try {
>>      cloudscape.shutdown();
>>    } catch (SQLException e) {
>>      e.printStackTrace();
>>    }
>>  }
>>}
>
>
>
> First of all, you are using Cloudscape and not Derby. Not that I think
> it makes any significant difference in the results.
>
> One of the reasons hsqldb is so much faster than Derby is that hsqldb
> isn't fail-safe. Is does not flush data to the disk after each
> transaction. So in your case Derby makes 40000 disk writes whereas
> hsqldb only makes one. Try to wrap your insertions in a transaction
> like:
>
>
> try {
>   conn.setAutoCommit(false);
>   for (int i = 0; i < 1000; i++) {
>     cloudscape.update(
>       "INSERT INTO mytable(col1, col2) VALUES('blah', 100)");
>     cloudscape.update(
>       "INSERT INTO mytable(col1, col2) VALUES('blah', 100)");
>     cloudscape.update(
>       "INSERT INTO mytable(col1, col2) VALUES('blah', 100)");
>     cloudscape.update(
>       "INSERT INTO mytable(col1, col2) VALUES('blah', 100)");
>     cloudscape.query("SELECT * FROM mytable");
>   }
>   conn.commit();
> } catch (SQLException e) {
>    e.printStackTrace();
> }
> }
>
> This way Derby doesn't need to write to disk until the transaction is
> committed.
>
> Steen
>
>
One thing that should make a big difference is to just use a couple of
prepared  statements for the insert and select.  You would need to
prepare just once at the beginning of your program and then can execute
over and over again.
e.g.

PreparedStatement insertPS = conn.prepareStatement("INSERT INTO
mytable(col1, col2) VALUES('blah', 100)");

PreparedStatement selPs = conn.prepareStatement("SELECT * from mytable");

You can use parameter markers if you want to insert something else
besides 'blah' and 100.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBQIyJG0h36bFmkocRArvqAJ4tYgXQHa2+f+xzIpAXC86fbU0EHgCgrxsA
cqDT+roKL2Dnfx8CdWnjouQ=
=kFaN
-----END PGP SIGNATURE-----

Mime
View raw message