I found one more scenario where compressed database is 30 times slower -
see attachment
But now it's not related to synchronization because the test is single
threaded
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class Bug_H2_6 {
static String compressedDbUrl = "jdbc:h2:nioMemLZF:db1";
static String uncompressedDbUrl = "jdbc:h2:nioMemFS:db2";
static String dbParams = ";DB_CLOSE_DELAY=-1;MULTI_THREADED=1;LOCK_TIMEOUT=10000;WRITE_DELAY=500";
static String user = "sa", pwd = "";
public static final int ITERS = 100_000;
public static void main(String args[]) {
try {
Class.forName("org.h2.Driver");
run(uncompressedDbUrl + dbParams);
run(compressedDbUrl + dbParams);
} catch (ClassNotFoundException | SQLException | InterruptedException ex) {
Logger.getLogger(Bug_H2_6.class.getName()).log(Level.SEVERE, null, ex);
}
}
private static void run(String dbUrl) throws SQLException, InterruptedException {
System.out.println("\nURL: " + dbUrl);
Connection dbConnect = DriverManager.getConnection(dbUrl, user, pwd);
dbConnect.setAutoCommit(false);
DbPreparator.prepareScheme(dbConnect);
System.out.println("DB scheme prepared");
long start = System.currentTimeMillis();
DbPreparator.populate(dbConnect);
System.out.println("DB populated in " + (System.currentTimeMillis() - start) + "ms");
PreparedStatement readAcctStmt = dbConnect.prepareStatement("select * from account where contractId = ?");
start = System.currentTimeMillis();
for (int i = 0; i < ITERS; i++) {
readAcctStmt.setLong(1, (int) (Math.random() * DbPreparator.OBJ_CNT));
try (ResultSet rs = readAcctStmt.executeQuery()) {
rs.next();
}
}
System.out.println("Test duration " + (System.currentTimeMillis() - start) + "ms\n");
}
static class DbPreparator {
public static final int OBJ_CNT = 50000;
private static final String[] SQLs = new String[]{
"CREATE TABLE IF NOT EXISTS "
+ "ACCOUNT (\n"
+ " ID NUMBER(18,0) not null PRIMARY KEY,\n"
+ " CONTRACTID NUMBER(18,0) null,\n"
+ " BALANCE NUMBER null,\n"
+ " NOTES VARCHAR2(4000 char) null)",
"create index IF NOT EXISTS IDX_ACCOUNT_CONTRACT on ACCOUNT (CONTRACTID asc)",};
public static void prepareScheme(Connection db) throws SQLException {
for (String sql : SQLs) {
db.createStatement().execute(sql);
db.commit();
}
}
public static void populate(Connection db) throws SQLException {
PreparedStatement mergeAcctStmt = db.prepareStatement("MERGE INTO Account(id, balance, contractId) key (id) VALUES (?, ?, ?)");
for (int i = 0; i < OBJ_CNT; i++) {
mergeAcctStmt.setLong(1, i);
mergeAcctStmt.setBigDecimal(2, BigDecimal.ZERO);
mergeAcctStmt.setLong(3, i);
mergeAcctStmt.addBatch();
}
mergeAcctStmt.executeBatch();
db.commit();
}
}
}
