Apache's Derby database has graduated from incubation to become a full project. According to some sources, Derby is going to be supplied by Sun with some of its Java products.

Derby is a version of IBM's Cloudscape database. IBM says that the difference between Derby and Cloudscape is purely based on support, according to "Why IBM is open sourcing Cloudscape as Derby."

Apparently Derby does not seem to be the fastest database engine around:
http://polepos.sourceforge.net/results/html/bahrain_query_indexed_int.html

Did they use the current version? Did they use embedded Derby, or networked derby? Some of the results make me question the validity of the test - or what it's testing.

Note that I'm not saying Derby is *fast* or not - that's not my role. Nor can I say offhand whether that test is right or not, but I can only say that they don't say enough about how they test in the test results for me to be able to say "wow" or not.

Did they use the current version? Did they use embedded Derby, or networked derby?

Well, the source *is* available, so it's not rocket science to find out. jdbc.properties currently says:
derby.driverclass=org.apache.derby.jdbc.EmbeddedDriver
derby.connecturl=jdbc:derby:data/derby/dbbench;create=true

which would imply that the embedded version is used.

Thank you, Rickard. That answers that - now Apache gets to figure out why Derby does so poorly, perhaps?

Hi,

When people compare apples with oranges...

I tried HDQLDB last year but got Out of memory error when the database had more than a few hundred thousand records. I tried all the possible configurations properties with no succes (I do not remember all I did, but I tried everything that the developers advised me to do); HSQLDB starts slowly and cannot handle big databases. I am not saying this is a bad database; this simply has a very different design and not suitable for big databases when memory is limited. I guess it handles everything in memory...

I have been working with derby for a few months and I am very happy with it. It does not take too much heap and is reasonably fast. I compared it to a postgres database 8.0.3 (feeling comparison only) and it seems similar. For embedded databaes, this a a real killer because very simple to integrate and has functions to backup and restore with or without rollover log. I am not worried at all when comes the time to roll out my application.

Patrice Drolet
President
Logiciels INFO-DATA inc.

I took a quick look at the benchmark at polepos, specially the bahrain test. The test uses Statements instead of PreparedStatement for the select queries. ( see BahrainJdbc.java where select queries are like "select * from bahrain where LicenseID=" + i )

One important performance tip is to use PreparedStatement with dynamic markers instead of Statements. This can show considerable improvements, I have seen 6 times improvements in some cases.

If you use Statement with literal values as in the case above, that will involve derby having to compile a query plan for each of the statements which affects performance. Using PreparedStatement with dynamic markers ('?') avoids unnecessary compilation cost , and thus faster.

So for example - if you are executing the query above(in 1st para) with different LicenseID values, using a Statement will involve compilation cost for each statement executed. But in case of PreparedStatement, the query would be ' select * from bahrain where LicenseID=?" and the statement is compiled only the first time or when the optimizer thinks the plan is stale.

Check out the tuning manual for more details :- http://db.apache.org/derby/docs/dev/tuning/ctunperf18705.html
http://db.apache.org/derby/docs/dev/tuning/ctundepth29804.html#ctundepth29804

---------
Also I think, sometimes default behaviors of databases are different and this can lead to different performance out of the box - some key factors such as the default isolation level, I/O syncs on a commit or not. While Derby forces a I/O sync call on a commit by default... but when I looked at the docs for HSQLDB and Pointbase last, I saw that by default they dont force a sync call to the disk on a commit.

It might be good to take note of these points when benchmarking to ensure that we are not comparing apples to oranges.

Sunitha.

How does Derby compare with other open source databases written in Java?
Now we just need an open source OODBMS written in Java or does such allready exist?

Have a look at Db4o. It seems a great product expecially for embedded use.

I created a document that is a Java benchmark to compare "small" database engines http://sites.google.com/site/olivabianco/java-blog/comparisonbetweenderby-sqlite-hslqdb-h2databases Tell me what you think about it, and how could I improve it. Note: You can also check http://www.polepos.org/ to see a deeper DB comparisson

Could it be that there is a slight difference between how HSQLDB and Derby handle transation isolation?

"HSQLDB support only read uncommitted isolation level when queries are made from your Java program." in http://www.hsqldb.org/web/hsqlFAQ.html

Derby is read COMMITED by default: "If a connection does not specify its isolation level, it inherits the default isolation level for the Derby system, The default value is CS. When set to CS, the connection inherits the TRANSACTION_READ_COMMITTED isolation level." in http://db.apache.org/derby/docs/devguide/cdevconcepts22300.html

Also, Derby supports all (four) isolation levels. From: http://db.apache.org/derby/docs/devguide/cdevconcepts15366.html

Regards,

Nicolas

Having a great open source database is great news for the Java community.

Now the task is to get Derby to run with Hibernate to have a great couple for ones database needs.

Unfortunately there is only a Derby dialect for Hibernate 3, for the lot of Hibernate 2 using systems out there is no support yet, is it?

Having a great open source database is great news for the Java community.

Now the task is to get Derby to run with Hibernate to have a great couple for ones database needs.

Derby is not alone to be open source and there are greater data bases ......
Daffodil's one dollar DB, Mckoi DB etc. are also great java databases that run with hibernate as well as major JDO implementations. Have you seen any of them ?

Derby is not alone to be open source and there are greater data bases ......Daffodil's one dollar DB, Mckoi DB etc. are also great java databases that run with hibernate as well as major JDO implementations. Have you seen any of them ?

There's some important differences that make Derby special (originally developed by IBM, it's an Apache project, technical support from IBM as Cloudscape, etc).

Derby has a lot going for it - you can read more here:

http://www.codefutures.com/weblog/corporate/archives/2005/04/apache_derby_a.html

This was written on the (correct) assumption that Derby would graduate to a full project status.

PJ Murray
CodeFutures Software
Code Generation for Java Persistence

Unfortunately there is only a Derby dialect for Hibernate 3, for the lot of Hibernate 2 using systems out there is no support yet, is it?

See http://opensource.atlassian.com/projects/hibernate/browse/HB-1224 for the actual dialect class and a couple of other required classes. If you have those on your classpath, you can use the DerbyDialect. I use Hibernate3 now, but used this dialect fine with Hibernate2 for months.

hth...