db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jianhui Jin" <ced1...@leeds.ac.uk>
Subject RE: Derby vs. HSQL, auto-commit
Date Thu, 09 Sep 2004 16:12:14 GMT
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 < 10000; 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


Mime
View raw message