SQL Zone is brought to you in partnership with:

Mitch Pronschinske is the Lead Research Analyst at DZone. Researching and compiling content for DZone's research guides is his primary job. He likes to make his own ringtones, watches cartoons/anime, enjoys card and board games, and plays the accordion. Mitch is a DZone Zone Leader and has posted 2578 posts at DZone. You can read more from them at their website. View Full User Profile

HyperSQL - The Sequel

06.10.2010
| 13315 views |
  • submit to reddit
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:

  • 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.

Comments

Nicolas Doye replied on Thu, 2010/06/10 - 2:54pm

No comparison with H2?

Ashwin Jayaprakash replied on Thu, 2010/06/10 - 11:04pm

Nice! Full concurrency (MVCC) is going to be interesting. So, now H2 and HSQL are going to look similar I suppose.

Mitch Pronschinske replied on Fri, 2010/06/11 - 9:45am in response to: Ashwin Jayaprakash

It would be interesting to see if someone does a benchmark comparison of H2 and HSQLDB 2.0.  That graph uses HyperSQL 1.8 in the tests.

Thomas Mueller replied on Fri, 2010/06/11 - 11:17am

I just ran the H2 benchmarks, and HSQLDB gets very good numbers: 45 thousand statements per second in embedded mode, and 9 thousand statements per second in server mode (over TCP/IP). H2 may be still be faster (59 thousand and 10 thousand), but performance is no longer a deciding factor when having to choose between the two. Not so with Apache Derby: with 7 thousand in embedded mode, it's actually slower than both HSQLDB and H2 in server mode. (Yes I know just one number doesn't say a lot, and I know performance depends on the use case, but the H2 benchmarks are similar to the TPC benchmarks, so the numbers do have some significance; plus the benchmark source code is open source).

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: Thomas Mueller

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.

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.

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:

 

Hi,

Sorry for the delay. There are various problems: 1) H2 doesn't
currently optimize queries with OR very well. 2) You should probably
add more indexes 3) You should run ANALYZE to help the query
optimizer.


http://groups.google.com/group/h2-database/browse_thread/thread/a6f2db8dffe64e88/23d37b0d6535610f

 

Hi,

> I just wanted to reiterate, optimizing the OR case is very important.

You are right of course. It is already on the roadmap, but still
priority 2. You could try to implement it yourself of course, it is in
ConditionAndOr.java, method optimize. I know it would be better if I
can implement it... Just I don't have time at the moment, sorry.
Regards,
Thomas

 

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

Just as an FYI HyperSQL 2.1 has been Released:

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

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.