-----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:[EMAIL PROTECTED] > 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-----
