Hi,
I've changed my tests as you've proposed (the code at the bottom):
- I use the newest version: 1.0.78
- I use PreparedStatements
- I tried using h2.optimizeInJoin
- I set autocommit to false
My results:
- version change (1.0.76 -> 1.0.78) didn't change much
- prepared statements slightly decreased the performance
- autocommit set to false helped MySQL and didn't affect H2
- h2.optimizeIn didn't change the result
- h2.optimizeInJoin reduced the performance of the test case "B"
dramatically
Here is a result with h2.optimizeInJoin off:
Testing jdbc:h2:mem:test
Initialization [296ms]
A. SELECT pk FROM t WHERE pk = ? [31ms]
B. SELECT pk FROM t WHERE pk IN (?, ?) [997ms]
C. SELECT pk FROM t WHERE pk = ? OR pk = ? [3614ms]
D. SELECT pk FROM t WHERE pk = ? AND pk = ? [0ms]
E. SELECT pk FROM t WHERE pk = ? AND pk > ? [467ms]
Total [5405ms]
Testing jdbc:mysql://localhost:3306/test?user=test&password=test
Initialization [1495ms]
A. SELECT pk FROM t WHERE pk = ? [125ms]
B. SELECT pk FROM t WHERE pk IN (?, ?) [156ms]
C. SELECT pk FROM t WHERE pk = ? OR pk = ? [140ms]
D. SELECT pk FROM t WHERE pk = ? AND pk = ? [109ms]
E. SELECT pk FROM t WHERE pk = ? AND pk > ? [109ms]
Total [2134ms]
... and here with on:
Testing jdbc:h2:mem:test
Initialization [281ms]
A. SELECT pk FROM t WHERE pk = ? [46ms]
B. SELECT pk FROM t WHERE pk IN (?, ?) [40078ms]
C. SELECT pk FROM t WHERE pk = ? OR pk = ? [3631ms]
D. SELECT pk FROM t WHERE pk = ? AND pk = ? [0ms]
E. SELECT pk FROM t WHERE pk = ? AND pk > ? [467ms]
Total [44503ms]
package test;
import static java.lang.System.currentTimeMillis;
import static java.lang.System.out;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Random;
/**
* @author [EMAIL PROTECTED] on 29 Aug 2008
*/
public class SearchByPKPerformance {
public static void main(String[] args) throws SQLException {
// System.setProperty("h2.optimizeIn", "true");
// System.setProperty("h2.optimizeInJoin", "true");
DriverManager.registerDriver(new org.h2.Driver());
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
String h2url = "jdbc:h2:mem:test";
String mysqlUrl = "jdbc:mysql://localhost:3306/test?
user=test&password=test";
for (String url : Arrays.asList(h2url, mysqlUrl)) {
Random rnd = new Random(1234);
out.println("Testing " + url);
long start = currentTimeMillis();
Connection conn = DriverManager.getConnection(url);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.execute("DROP TABLE IF EXISTS t");
stmt.execute("CREATE TABLE t (pk INT, PRIMARY KEY
(pk))");
PreparedStatement pstmt = conn.prepareStatement("INSERT
INTO t (pk)
VALUES (?)");
for (int i = 0; i < 10000; i++) {
pstmt.setInt(1, rnd.nextInt());
pstmt.executeUpdate();
}
out.println("Initialization [" + (currentTimeMillis() -
start) +
"ms]");
pstmt.close();
run(rnd, conn, "SELECT pk FROM t WHERE pk = ?", "A", 1);
run(rnd, conn, "SELECT pk FROM t WHERE pk IN (?, ?)",
"B", 2);
run(rnd, conn, "SELECT pk FROM t WHERE pk = ? OR pk =
?", "C", 2);
run(rnd, conn, "SELECT pk FROM t WHERE pk = ? AND pk =
?", "D", 2);
run(rnd, conn, "SELECT pk FROM t WHERE pk = ? AND pk >
?", "E", 2);
out.println("Total [" + (currentTimeMillis() - start) +
"ms]");
}
}
private static void run(Random rnd, Connection conn, String sql,
String tst, int params)
throws SQLException {
long start = currentTimeMillis();
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int i = 0; i < 1000; i++) {
for (int j = 1; j <= params; j++)
pstmt.setInt(j, rnd.nextInt());
pstmt.executeQuery();
}
out.println(tst + ". " + sql + " [" + (currentTimeMillis() -
start)
+ "ms]");
pstmt.close();
}
}
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
