Panasonic Youth rob sanheim writes about software, business, ruby, music, stuff and things

Searching for the perfect in memory database

I’ve been using HSQLDB for my in memory database testing, but I think its time to find something else. Things have been getting pretty painful lately and I’m wondering what kind of experience others have with the alternatives. My criteria are:

• fast
• easy to embed in a Java test suite - so basically just drop in the database, a property file and the jdbc driver and go
• excellant support for the sql standard - including all the outer joins, some core set of conversion functions, etc
• support full outer join
• did I mention fast?

My latest query is doing full outer joins and HSQLDB doesn’t like it. Just checked out Sqllite, but it has the same problem as HSQLDB - left outer joins supported, but nothing else.

Derby looks like an option, as they claim support for right outers and left outers, but no full outer. I may take a look at Firebird next, but I can’t find much on doing things embedded in an app without too much pain.

Did you try the H2 DBMS ?
It looks great.
http://www.h2database.com

Apache Derby meets all your requirements and more. It offers a tremendous amount of features inside a very small memory footprint. Derby is also already used inside many well known projects including ActiveMQ and Geronimo. See the website for more details:

http://db.apache.org/derby/

I’m the maintainer of H2, and currently think about adding full outer join support. It is not so easy to do that efficiently (it would probably require the implementation of a special merge join, but I’m not sure yet if I want to do that).

But, if so many databases don’t support full outer join, why don’t you try to avoid full outer join? It’s possible to emulate full outer join with UNION:

SELECT A.X, B.Y FROM A
FULL OUTER JOIN B ON A.X = B.Y

can be written without full outer join:

SELECT A.X, B.Y FROM A
LEFT OUTER JOIN B ON A.X = B.Y
UNION ALL
SELECT A.X, B.Y FROM B
LEFT OUTER JOIN A ON A.X = B.Y
WHERE A.X IS NULL

You have then a much wider set of candidates. Another question: why do you need full outer join?

i was looking at embedded databases for my current project. i am fronting the database with hibernate, so i had/have different concerns than you, but here are my findings as they relate to my requirements

1) hsqldb - must be memory-resident, so cannot scale
2) ten times (http://www.oracle.com/timesten/index.html) - also memory-only
3) derby - cannot do “chunky” queries, i.e. requesting rows 20001-20050 of a query that returns 50000 records [what is this feature really called? “paging queries”]
4) one$db / daffodil (http://www.daffodildb.com/) - cannot handle binary keys

i didn’t try sqlite. i had worked a project before where it was used (not by me), and the folks i worked with found the jdbc driver to be very poor, performance-wise, so they ended up going JNI into it. this i was not interested in…

btw, i ended up using a non-embedded, non-free (all the above are free in some form) small-footprint database (SQL anywhere)

so, how was it with firebird? do tell…