H2 in-memory performance issue

[GoogleCodeExporter]

While running a complex query the im-memory performance is even slower than the 
normal postgres database.

Also for the middle category query not getting any significant performance
as expected n times say. only few milliseconds  of difference can't  solve the 
fast database purpose.

So kindly tell me how to improve.

Thanks in advance

Original issue reported on code.google.com by ppramani...@gmail.com on 20 Feb 2014 at 12:58

[GoogleCodeExporter]

Hi,

I think you forgot to attach the test case?

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 20 Feb 2014 at 2:06

[GoogleCodeExporter]

Hi Thomas,

Thanks for the immediate response...
Please find the test case given bellow :

*H2 version* :
Version 1.3.174
*URL* :
("jdbc:h2:mem:test123;OPTIMIZE_REUSE_RESULTS=0", "test", "password")
*Test Machine : *
OS - Ubuntu , RAM - 4GB , Max Heap Size - 2048 mb, Java JDK 1.6

*Database :*
 3 dimension table of 100 records maximum
 1 fact table with 60,00000 records of  size 400 mb
 Created primary key hash index for each dimension, and created one hash
index on fact table.

*Query :*
String query = "SELECT * FROM (SELECT \"division\", \"billbasis\", \"mth\",
SUM(\"billunits\") " +
 "AS \"billunits\" FROM (SELECT \"master_location\".\"division\",
\"master_billbasis\".\"billbasis\"," +
 " \"master_month\".\"mth\", \""+getFactTableName()+"\".\"billunits\" FROM
\""+getFactTableName()+"\"  " +
 "INNER JOIN \"master_location\" ON \""+getFactTableName()+"\".\"loc_id\" =
\"master_location\".\"loc_id\" " +
 "INNER JOIN \"master_billbasis\" ON
\""+getFactTableName()+"\".\"billbasis_id\=\"master_billbasis\".\"bb_id\"+
 "INNER JOIN \"master_month\" ON \""+getFactTableName()+"\".\"mth_id\"
\"master_month\".\"mth_id\"\"INNER_QUERY\"" +
 "GROUP BY \"division\", \"billbasis\",\"mth\") \"INNER_QUERY\"";

*Result : *
H2 database : 12548 milliseconds
Postgres      : 11223 milliseconds

*Code details : *

*Create Table : *

//*** fact_consumer ***
String sql =  "CREATE TABLE \""+getFactTableName()+"\" (\"cons_id\"
DOUBLE," +
 "\"billunits\" DOUBLE, \"billamt\" DOUBLE," +
"\"billbasis_id\" DOUBLE,\"loc_id\" DOUBLE,\"mth_id\" DOUBLE)" ;
 stmt.executeUpdate(sql);
//*** master_billbasis ***
sql = "CREATE TABLE \"master_billbasis\" (\"bb_code\" VARCHAR,\"billbasis\"
VARCHAR," +
 "\"bb_id\" DOUBLE,CONSTRAINT \"master_billbasis_bb_id_pKey\" PRIMARY KEY
HASH(\"bb_id\"))" ;
stmt.executeUpdate(sql);
 //*** master_location ***
sql = "CREATE TABLE \"master_location\" (\"division\" VARCHAR,\"sub_div\"
VARCHAR," +
 "\"section\" VARCHAR,\"sdobinder\" VARCHAR,\"sdo_cd\" VARCHAR,\"binder\"
VARCHAR," +
"\"loc_id\" DOUBLE,CONSTRAINT \"master_location_loc_id_pKey\" PRIMARY KEY
HASH(\"loc_id\"))" ;
 stmt.executeUpdate(sql);
//*** master_month ***
sql = "CREATE TABLE \"master_month\" (\"bill_mth\" DOUBLE,\"mth\" VARCHAR,"
+
 "\"year\" DOUBLE,\"mth_no\" DOUBLE,\"quater\" VARCHAR," +
"\"mth_id\" DOUBLE,CONSTRAINT \"master_month_mth_id_pKey\" PRIMARY KEY
HASH(\"mth_id\"))" ;

*Insert Table :*
Each table is inserted in JdbcPreparedStatement with batch

Please let me know if I am missing anything , provide you complete scenario
Appreciate your help !

Original comment by ppramani...@gmail.com on 21 Feb 2014 at 5:42

[GoogleCodeExporter]

Hi,

I'm sorry, but this doesn't quality as a bug. Please read the performance 
section first: http://h2database.com/html/performance.html if this does not 
solve the problem, please use the Google Group instead. We would need to have a 
complete, simple (as simple as possible) and reproducible test case.

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 21 Feb 2014 at 4:24

• Changed state: Invalid