Derby aka JavaDb is the slowest

I am into an interesting experiment on using multiple open source dbs for my current assignment. The requirement is to come up with a data mashup simillar to Apatar (but not as sophisticated as that). Though Apatar is an open source and code is available for public, the need of the hour is to complete the data mashup within a week of time and that too customized and another key thing was Apatar uses Derby db which is very slow.

Now, back to the point, this required me to test the code with various open source databases to show the client about the performance of each database.

I initially tested it with mysql. But then I thought we need an embedded database so that the deployment of this application would be easier. I investigated few sites and got through handful of new open source dbs like SmallSQL, H2, HSQL, Derby, PostGreSQL, axiom etc.

Due to time constraint, I chose to test the app's performance with few databases like Derby, H2, HSQL, mysql (though mysql is not an option for me).

Here is the matrix (showing the execution time in seconds) after I test. My test was
1. To creating 3 tables
2. Inserting 20000 records into them
3. To create indexes
4. Joining them and building expressions and produce the output.

With 2 tables with 20K records
MySQL - 34
Derby - 183
HSQL - 7.67
H2 - 9.45

With 3 tables with 20K records
MySQL - 53
Derby - 604
HSQL - 13.47
H2 - 14.36

To my surprise, HSQL is better than even H2.

JDBC Drivers for Open source DBs

Though I am a .Net guy working on Java always facinates me with some compromise and little complaints. One of the tough thing is to get the drivers of different databases (non-conventional one's) when we need especially on Open source databases. It is not available on the readme files when you download the database jars. Ofcourse, we can figure it out from google, but I thought of putting it in this blog some of the JDBC Drivers with URL so that you need not have to google for each open source drivers and url.

The following are some of the open source database connectivity parameters which I came across when I worked with java recently.

#FOR MYSQL

Url=jdbc:mysql://localhost:3306/
Driver=com.mysql.jdbc.Driver

Please note the mysql can have a server mode and hence the url might differ. In general, when you install mysql in your desktop and use it then the above holds good. The default port is 3306 and if you talented enough to change it then I believe you are equally talented enough to figure out the url :).

#FOR DERBY

URL=jdbc:derby:DBNAME;create=true
Driver=org.apache.derby.jdbc.EmbeddedDriver

The DB is created with the DBName you specify in Derby (aka Javadb) when you say create=true.

#FOR SMALLSQL

URL=jdbc:smallsql:DBNAME
Driver=smallsql.database.SSDriver

#FOR HSQL

URL=jdbc:hsqldb:DBNAME
Driver=org.hsqldb.jdbcDriver

#FOR H2

URL=jdbc:h2:~/test
Driver=org.h2.Driver

Hope this helps to few atleast.