Which is better H2 or HSQLDB?

Question

HSQLDB 2.0 is soon to be released. I wonder if it will outperform H2 since, as far as I know, most users prefer H2 than HSQLDB. I am interested in the MVCC support of HSQLDB 2.0. I have learned that MVCC on H2 is still experimental. With regards to support/documentation, concurrency, performance, which is better between the two?

Solution 1

Please note I had provided this answer in 2011. It may be outdated

My company develops a database abstraction library (jOOQ), which supports both databases. Our integration tests cover a lot of functionality, including the calling of stored procedures and functions, arrays, nested selects, etc. I experience HSQLDB 2.1 to be slightly faster than H2 1.3 for small databases as far as DML is concerned.

However, HSQLDB highly outperforms H2 in DDL operations as well as when starting/shutting down the instance, even for a small database (due to H2's compiling stored functions afresh with javac at every database startup!). This depends on the way you store stored functions. Apparently, that's a rather jOOQ-specific "issue", see also Thomas Mueller's comment.

On the other hand, I agree with user trashgod, you should test performance against a reasonable schema and use-case for yourself.

Solution 2

Both HyperSQL and H2 Database are fairly transparent, so testing may be the best approach to determine which is more suitable for a particular use. Comparisons involving one and the other are available. They share a common heritage, and both are open source.

Comments

[Thomas Mueller]

Stored functions are only re-compiled if they are stored "as source code", which is a feature that isn't available in HSQLDB (as far as I know). If you use pre-compiled stored functions, then there is no such overhead.

[Lukas Eder]

@ThomasMueller: Thanks, I didn't know that. That'll help speed up my integration tests, massively. I'll adapt my answer accordingly. HSQLDB has a PL/SQL-like procedural language, which is more powerful IMO. I'm not sure though, if that language is pre-compiled or interpreted...

[trashgod]

"the trash can is an important design tool"—loc. cit.

[specialk1st]

Thanks for that "heritage" link. They summarize the history pretty wonderfully. First came HSQLDB (Hypersonic SQL) then came H2, but HSQLDB and H2 do not share any of the same code and are separate projects as of today.

[trashgod]

@specialk1st: Right, the heritage in this case is a common author, not common code.