db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Steen Jansdal" <st...@jansdal.dk>
Subject Re: Derby vs. HSQL
Date Thu, 09 Sep 2004 16:01:15 GMT
> 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