87

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?

dexter
  • 1,789
  • 2
  • 13
  • 13
  • 6
    What is your use case? What queries will you perform against your data? How large is your data? Is your application read or write heavy? – Matt Apr 20 '11 at 15:06
  • 29
    Another perfectly good question closed. – rtcarlson Apr 02 '14 at 00:18
  • I find it fascinating that focus on answers appears to be performance, despite question only mentioning it as one of the aspects. I would be much more interested in other areas from support/doc, usability (ease-of-use, intuitiveness, feature set), reliability and such. – StaxMan May 13 '15 at 18:17
  • Take a look at http://www.jpab.org/Hibernate/H2/embedded/Hibernate/HSQLDB/embedded.html – Beto Neto Oct 04 '17 at 11:16

2 Answers2

62

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.

Lukas Eder
  • 181,694
  • 112
  • 597
  • 1,319
  • 7
    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. – Thomas Mueller Oct 20 '11 at 09:01
  • 2
    @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... – Lukas Eder Oct 20 '11 at 09:19
45

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.

trashgod
  • 196,350
  • 25
  • 213
  • 918
  • 10
    "the trash can is an important design tool"—[_loc. cit._](http://stackoverflow.com/a/22259102/230513) – trashgod Sep 21 '14 at 22:00
  • 3
    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. – specialk1st Mar 18 '16 at 10:16
  • 1
    @specialk1st: Right, the _heritage_ in this case is a common author, not common code. – trashgod Mar 18 '16 at 17:15