H2 MUCH SLOWER than HSQLDB in my Java application? (both inmem)
I am sure there's something stupid from my side going on here.
I have a Java application where I need to query a collection of 2,5 Million objects repeatedly all the time, therefore I put them into an in memory db.
For this purpose I tried out hsqldb v2.4.1 and h2 v1.4.198
For both I use exactly the same create table:
String createRateTable = "CREATE MEMORY TABLE INTEREST_RATES " + "(EFFECTIVE_DATE DATE not NULL, " + "INTEREST_RATE DOUBLE, " + "INTEREST_RATE_CD BIGINT, " + "INTEREST_RATE_TERM BIGINT, " + "INTEREST_RATE_TERM_MULT VARCHAR(50)," + "PRIMARY KEY (EFFECTIVE_DATE, INTEREST_RATE_CD, INTEREST_RATE_TERM, INTEREST_RATE_TERM_MULT))";
The only difference is the Connection, either I take
con = DriverManager.getConnection("jdbc:h2:mem:ftp", "SA", "");
con = DriverManager.getConnection("jdbc:hsql:mem:ftp", "SA", "");
An often fired query for example is this one:
SELECT * from INTEREST_RATES where INTEREST_RATE_CD = ? and EFFECTIVE_DATE = (SELECT MIN(EFFECTIVE_DATE) from INTEREST_RATES where INTEREST_RATE_CD = ?)
as for Hsql the application is finished within about 2 minutes.
as for H2 it's still not done after > 8 minutes.
What's wrong with my H2 setup? Seems like there are no indices created there, as Hsql does with help of the PRIMARY KEY ()? What else can be the problem?
1 Answers H2 MUCH SLOWER than HSQLDB in my Java application? (both inmem)
Each DB engine has it's own implementation and in some scenarios can behave different. I would try the following query, I think the index could be more efficiently used and returns the same result:
SELECT * from INTEREST_RATES where INTEREST_RATE_CD = ? order by INTEREST_RATE_CD, EFFECTIVE_DATE limit 1;
In the "order by" the first column is not really required as is already filtered but as explained here is needed so the PRIMARY KEY is correctly used by the engine in the execution plan.