Derby vs. HSQL/HXSQL (in-memory use)

Szilard just reminded me that JChem now supports Derby as well. Any reason to go with it rather HSQLDB for in-process use? I tried it a couple of years ago, when it was still the IBM Cloudscape product and gave up: I dimly remember a 10-page installation booklet and being unable to get going in a reasonable amount of time (i.e., a couple hours). I also know that it is now included in the Sun JDK 1.6 under the JavaDB name but I don't see how that makes a difference to me.





Thanks in advance.

We use Derby for the local database in Instant JChem. Originally we used HSQL, but found some limitations that were solved by Derby. I think both are great databases, and which you choose depends on your exact needs.





Derby aims to be a fully functional relational database, and as such could be considered as an alternative to "grown up" databases like Oracle and MySQL in some cases. HSQL is more like a "toy database", providing a subset of the features of a fully functional databases. But that subset of functionality may be all you need.





Benefits of HSQL over Derby.


- light weight (smaller libraries, less complex)


- supports in memory databases





Benefits of Derby over HSQL


- much larger development community


- secured future (is part of JDK, is sponsored by IBM and Sun)


- More complete feature set (e.g. proper handling of transactions, encryption...)








In addition, when using HSQL we came across a number of big performance bottlenecks. These were the prime reasons why we switched to Derby:


1. adding a column was v. slow for big tables (could take a minute!)


2. select count(*) from aTable was very slow for large tables (did a full table scan to count the rows!)


3. SELECT DISTINCT ... did a full table scan, even though the column was indexed, making it a very slow operation on large tables


4. shutdown was slow causing big delays when you quit the application.


5. startup was slow if DB was not shutdown correctly.





The commercial version, HXSQL, addresses some of these problems, but is not free, and the files are not compatible with HSQLDB.








Tim

We use HXSQL and the data set is rather small; so far performance has not been an issue. My main concern is whether the db can be packaged within the application so as to require zero configuration on-site. It is also a bonus if it can be used as all-in-memory db, although not necessarily in-process (i.e., as long as using it in client-server mode does not create configuration hassles). Would Derby meet these requirements?

Yes, Derby can meet all of those needs, except for a totally in-memory database.





Tim