Please see attached.
At my notebook the test takes 0.9s without compression and 50s with
compression
On Thursday, January 19, 2017 at 5:51:36 PM UTC, Noel Grandin wrote:
>
> Can you post your performance test code?
> On Thu, 19 Jan 2017 at 18:36, Anatolii K <a.ka...@gmail.com <javascript:>>
> wrote:
>
>> Sorry to say so, but performance dropped dramatically.
>> Previously compressed storage was just a bit slower than uncompressed.
>> But now it become almost 100 times slower!
>>
>>
>>
>> On Thursday, January 19, 2017 at 1:32:49 PM UTC, Noel Grandin wrote:
>>>
>>> I have pushed a fix for this, it now survives your test.
>>>
>>> I had to simplify the current code and just synchronize the methods,
>>> which means it may lose some performance. If this
>>> is a problem for you, I'm sorry, but you'll have to come up with some
>>> patches yourself.
>>>
>> --
>> 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...@googlegroups.com <javascript:>.
>> To post to this group, send email to h2-da...@googlegroups.com
>> <javascript:>.
>> Visit this group at https://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
--
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.ArrayList;
import java.util.Collection;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
public class Bug_H2_5 {
static String compressedDbUrl = "jdbc:h2:nioMemLZF:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1;LOCK_TIMEOUT=10000;WRITE_DELAY=500";
static String uncompressedDbUrl = "jdbc:h2:nioMemFS:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1;LOCK_TIMEOUT=10000;WRITE_DELAY=500";
static String user = "sa", pwd = "";
static int threadCount = 300;
public static void main(String args[]) throws InterruptedException, SQLException, ClassNotFoundException {
Class.forName("org.h2.Driver");
run(uncompressedDbUrl);
run(compressedDbUrl);
}
private static void run(String dbUrl) throws SQLException, InterruptedException {
System.out.println("\nURL: " + dbUrl);
Connection dbConnect = DriverManager.getConnection(dbUrl, user, pwd);
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");
Collection<Arte> artes = new ArrayList<>();
for (int i = 0; i < threadCount; i++) {
artes.add(new Arte(DriverManager.getConnection(dbUrl, user, pwd), i));
}
ExecutorService threadPool = Executors.newFixedThreadPool(threadCount);
start = System.currentTimeMillis();
threadPool.invokeAll(artes);
System.out.println("Test duration " + (System.currentTimeMillis() - start) + "ms\n");
threadPool.shutdown();
}
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"
+ " BALANCE NUMBER null,\n"
+ " CONTRACTID NUMBER(18,0) 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();
}
}
static class Arte implements Callable<Integer> {
Connection db;
PreparedStatement readAcctStmt;
public Arte(Connection db_, int number) throws SQLException {
db = db_;
db.setAutoCommit(false);
readAcctStmt = db.prepareStatement("select * from account where contractId = ?");
}
@Override
public Integer call() {
try {
for (int i = 0; i < 200; i++) {
readAcctStmt.setLong(1, (int) (Math.random() * DbPreparator.OBJ_CNT));
try (ResultSet rs = readAcctStmt.executeQuery()) {
rs.next();
}
}
} catch (SQLException e) {
System.out.println("DB write error: ");
e.printStackTrace();
System.exit(0);
}
return null;
}
}
}
