Mitch Pronschinske 
HyperSQL - The Sequel
Email
The BSD licensed HyperSQL database (HSQLDB) reached version 2.0 this week. The "100% Java Database" now supports a wider array of SQL standard features (wider than any open source database engine available, they say). Version 2.0 is also fully multi-threaded and supports 2PL and MVCC transaction models. It's been 5 years since HSQLDB's last major release (1.8). HSQLDB 1.8 is still distributed with many commercial and open source products including OpenOffice.org 3.2.
HyperSQL 2.0 has nearly achieved full conformance with SQL-1992 Advanced Level and SQL:2008 core language features. It also includes a large amount of SQL:2008 optional features and extensions. The main categories of new features in HyperSQL 2.0 are the new core, higher scalability, query optimization, new data types, and stored procedures.
In HyperSQL 2.0, the new core is fully multi-threaded with two phase locking and multi-version concurrency control. There's also new support for greater BLOB (Binary Large Object) capacity. Now HSQLDB can store up to 64Tb of data. The large result sets from queries are able to be stored on disk while they are being generated and accessed.
The new SQL features let HyperSQL handle procedural SQL stored in procedures, including WHILE, IF, WHEN, CASE, and exception handling. HyperSQL also has new expression types, support for all standard JOIN types and TRIGGER definitions, and multi-row inserts and updates. BIT, BIT VARYING, CLOB, BLOB and INTERVAL data types have also been implemented in compliance with the SQL standards.
The HSQLDB developers did their own performance test with the PolePosition package comparing the previous version of HyperSQL to other database engines. You can see the results here:
The HSQLDB JDBC driver supports nearly full ANSI-92 SQL (BNF format). HyperSQL 2.0 also has several new JDBC-related features:
For the full list of new features, see the list of changes.
HyperSQL 2.0 has nearly achieved full conformance with SQL-1992 Advanced Level and SQL:2008 core language features. It also includes a large amount of SQL:2008 optional features and extensions. The main categories of new features in HyperSQL 2.0 are the new core, higher scalability, query optimization, new data types, and stored procedures.In HyperSQL 2.0, the new core is fully multi-threaded with two phase locking and multi-version concurrency control. There's also new support for greater BLOB (Binary Large Object) capacity. Now HSQLDB can store up to 64Tb of data. The large result sets from queries are able to be stored on disk while they are being generated and accessed.
The new SQL features let HyperSQL handle procedural SQL stored in procedures, including WHILE, IF, WHEN, CASE, and exception handling. HyperSQL also has new expression types, support for all standard JOIN types and TRIGGER definitions, and multi-row inserts and updates. BIT, BIT VARYING, CLOB, BLOB and INTERVAL data types have also been implemented in compliance with the SQL standards.
The HSQLDB developers did their own performance test with the PolePosition package comparing the previous version of HyperSQL to other database engines. You can see the results here:
The HSQLDB JDBC driver supports nearly full ANSI-92 SQL (BNF format). HyperSQL 2.0 also has several new JDBC-related features:
- Supports getGeneratedKeys() calls in Statement and PreparedStatement.
- Supports POSIX functions TIMESTAMPDIFF and TIMESTAMPADD.
- Supports Java 1.6 and JDBC4. All applicable new methods are supported.
For the full list of new features, see the list of changes.
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)
Comments
Nicolas Doye replied on Thu, 2010/06/10 - 2:54pm
Ashwin Jayaprakash replied on Thu, 2010/06/10 - 11:04pm
Mitch Pronschinske replied on Fri, 2010/06/11 - 9:45am
in response to: AshwinJay
Thomas Mueller replied on Fri, 2010/06/11 - 11:17am
Features, stability, ease-of-use, and documentation are also very important. Those areas are harder to measure.
Jeff Brooks replied on Sun, 2010/06/13 - 9:00pm
in response to: thomasmueller
Thomas, did you fix the H2 benchmark yet?
A few years ago I pointed out numerous problems with the benchmarks but you ignored them at the time (I'm Brish on the h2 forum).
Examples included Derby using a 4 meg cache vs a 16 meg cache for H2. Derby does durable operations but none of the other database do (I tried turning on durability for H2 about a year ago and ran the H2 benchmark and Derby beat H2 on it's own benchmark!). The HSQLDB configuration in the benchmark slows HSQLDB down (in my tests HSQLDB beat H2 on the H2 benchmark if the crippling configuration in the h2 benchmark was removed).
I also noticed that anything that H2 has a performance problem with isn't in the H2 benchmark. An example is handling ORs is slow on H2 so the benchmark doesn't include tests the show how bad H2 OR performance is.
Until you fix the benchmark, or at least allow someone else to add fixes to the benchmark, I can't take the H2 benchmark seriously.
Thomas Mueller replied on Tue, 2010/06/15 - 1:57pm
> still haven't been fixed.
As I wrote, there is nothing to fix.
> It's the configuration that matters not the heap size.
Because?
> FileChannel.force() method is called
Actually you are right. I will change the documentation: Derby calls FileChannel.force(false), but only twice per log file (not on each commit). Commenting out this call improves performance for Derby by about 2%. Unlike H2, Derby does not call FileDescriptor.sync() on each checkpoint.
> I tried the current H2 benchmark
I also ran some more tests:
Persistent database, regular tables:
H2 1.2.137: 56974 statements per second
HSQLDB 2.0: 53884 statements per second
Persistent database, in-memory tables:
H2 1.2.137: 64124 statements per second
HSQLDB 2.0: 38614 statements per second (why slower?)
In-Memory database:
H2 1.2.137: 151609 statements per second
HSQLDB 2.0: 129275 statements per second
> You removed that part where I said H2 was slow for ORs.
You mean, I didn't _add_ your specially constructed test case.
Jeff Brooks replied on Wed, 2010/06/16 - 10:33pm
> As I wrote, there is nothing to fix.
Please refer to your own comments stating that H2 doesn't handle ORs well when responding to problems people were having with ORs in queries with H2:I don't doubt that you believe that but it's not true.
> I also ran some more tests: ... snip ...
Don't care. Every test you run involves configuration that cripples other database performance.
> You mean, I didn't _add_ your specially constructed test case.
http://groups.google.com/group/h2-database/browse_thread/thread/a6f2db8dffe64e88/23d37b0d6535610f
http://groups.google.com/group/h2-database/browse_thread/thread/a2620dcbea306414/de840365caa1f899
Thomas, the H2 performance page is out of date. HSQLDB has had significant performance improvements and it should be reflected on the page. Somehow I doubt it will be updated.
From the h2 performance webpage:
- H2 28,117 statements per second
- HSQLDB 4,317 statements per second
H2 1.2.137 version of the benchmark (using your crippling configuration):
- H2 1.2.137 - 51,144 statements per second
- HSQLDB 1.8.1_2 - 41,401 statements per second
- HSQLDB 2.0.0 - 41,813 statements per second
Thomas Mueller replied on Wed, 2010/07/21 - 10:25am
I don't think there is anything that needs to be fixed in the H2 benchmark. It is based on the TPC benchmarks, which are used by many database vendors (including Oracle, IBM, and Microsoft).
But you are right about FileChannel.force(). I changed the documentation: "Derby calls FileChannel.force(false), but only twice per log file (not on each commit). Commenting out this call improves performance for Derby by about 2%. Unlike H2, Derby does not call FileDescriptor.sync() on each checkpoint." The documentation is now updated (you may have to click 'refresh').
By the way, I also ran some more tests:
Persistent database, regular tables:
H2 1.2.137: 56974 statements per second
HSQLDB 2.0: 53884 statements per second
Persistent database, in-memory tables:
H2 1.2.137: 64124 statements per second
HSQLDB 2.0: 38614 statements per second (why slower?)
In-Memory database:
H2 1.2.137: 151609 statements per second
HSQLDB 2.0: 129275 statements per second
Aaron Kocourek replied on Sun, 2011/05/01 - 2:21am
The latest version 2.1 introduces enhancements in all areas. It supports the widest range of SQL Standard features seen in any open source database engine. We have reached SQL Standard conformance to almost full SQL-1992 Advanced Level and SQL:2008 core language features plus an extensive list of SQL:2008 optional features and many extensions.
Sewing Supplies