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 <[email protected] <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 [email protected] <javascript:>.
>> To post to this group, send email to [email protected]
>> <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 [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.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;
}
}
}
