> 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
