Re: optimizing order by queries...

[Thomas Mueller]

Hi,

> I am getting results of 0.13 seconds for Mysql and 1.879 seconds for H2.

I can't reproduce this. According to my test, H2 is slightly faster
than MySQL. I guess it is related to caching (MySQL and PostgreSQL use
quite a lot of heap memory by default, while H2 uses very little).
With my test case:

http://h2database.com/p.html#fe04ff3aea1103ba2c90c21f62a269d1

I get the following results (timing data for the second run):

jdbc:h2:~/temp/test
  select: 472
jdbc:h2:~/temp/test;cache_size=65536
  select: 217
jdbc:h2:~/temp/test;max_memory_rows=60000
  select: 34
jdbc:hsqldb:/tmp/db/test
  select: 1118
jdbc:mysql://localhost:3306/test
  select: 431
jdbc:postgresql:test
  select: 41

Regards,
Thomas

----------------

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;

public class TestMultiDb {

    public static void main(String[] a) throws Exception {
        for (int i=0; i<3; i++) {
            DeleteDbFiles.execute("~/temp", "test", true);
            test("org.h2.Driver", "jdbc:h2:~/temp/test", "sa", "sa");
            test("org.h2.Driver",
"jdbc:h2:~/temp/test;cache_size=65536", "sa", "sa");
            test("org.h2.Driver",
"jdbc:h2:~/temp/test;max_memory_rows=60000", "sa", "sa");
            test("org.hsqldb.jdbcDriver", "jdbc:hsqldb:/tmp/db/test",
"sa", "sa");
            // test("org.apache.derby.jdbc.EmbeddedDriver",
"jdbc:derby:/tmp/db/test;create=true", "sa", "sa");
            test("com.mysql.jdbc.Driver",
"jdbc:mysql://localhost:3306/test", "sa", "sa");
            // test("oracle.jdbc.driver.OracleDriver",
            // "jdbc:oracle:thin:@192.168.0.100:1521:XE", "sa", "sa");
            test("org.postgresql.Driver", "jdbc:postgresql:test", "sa", "sa");
        }
    }

    static void test(String driver, String url, String user, String
password) throws Exception {
        System.out.println(url);
        Class.forName(driver);

        Connection conn = DriverManager.getConnection(url, user, password);
        final Statement stat = conn.createStatement();
        try {
            stat.execute("drop table test");
        } catch (SQLException e) {
            // ignore
        }
        stat.execute("create table test(id int primary key, column1
varchar(255), column2 varchar(255))");
        conn.setAutoCommit(false);
        PreparedStatement prep = conn.prepareStatement("insert into
test values(?, ?, ?)");
        String dummy = new String(new char[200]).replace((char) 0, 'x');
        for (int i=0; i<50000; i++) {
            prep.setInt(1, i);
            prep.setString(2, dummy);
            prep.setString(3, "" + i);
            prep.execute();
            if (i % 1000 == 0) {
                conn.commit();
            }
        }
        conn.commit();
        long t = System.currentTimeMillis();
        ResultSet rs = stat.executeQuery("select * from test order by
column2 limit 100");
        while (rs.next()) {
            rs.getInt(1);
            rs.getString(2);
            rs.getString(3);
        }
        System.out.println("  select: " + (System.currentTimeMillis() - t));
        conn.close();
    }

}

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.