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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
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();
}
}
}
