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.

Vinayaka Chaturthi @ Home ...

The day started off with as usual lovely scolding from my mummy and my wife to get up from the bed. It was too soon. Only 8 am. My eyes wished more sleeping, but no other go, I need to get up as my 1 yr son is awake and he expects someone to start off the day with.

After a dull and slow proceedings of the day, I mean, the definite activities of ...... and brushing, I was made brisk enough to start the day with the help of a tea offered by my wife (never tastes different...surprising!!!).
It is almost 9 am when I got an alarm call from my mummy (actually she was shouting...) to buy vinayaka for today's pooja as the Rahukalam (Non-auspicious time) for the day is between 9 and 10.30 am. I was going through the Hindu paper seriously reading the reason for Rahul dravid's resignation as captain and looking at the Property Plus advertisement (not for buying a flat for sure...).

Then when I started from home, i thought of taking my kid with me as the place to purchase vinyaka idols (made up of clay) is just 100 meters away from home. I took my son and reached the place but I could see only heads, I mean, heads of the people as it is too crowded. Then I decided to go to the main road and get a Vinayaka (before 9 am :( ). At last, I did.....for Rs.15.

Again I started looking at the Hindu paper.....and the time passed. Parallely, I could hear some confusion statements and sounds from the kitchen and I could understand that my wife is trying something to test Vinayaka (Oh God!). Later I found, I was right. She tried preparing kozhukattai, a food item made up of rice flour and coconut jaggery, considered to be favourite for Vinayaka.

Every year I was given different book for vinayaka pooja and what I look at first is the number of pages I need to go through for Pooja. With my experience, I thought this time its going to be the same small book with 18 pages. But surprisingly, my wife took a new book for vinayaka pooja with 86 pages (oh No...When am I going to get my breakfast?...its already 11.00 am to start the pooja). Pooja started. My wife could understand my eagerness and reluctancy towards breakfast and pooja. So she could help me in dubbing....and Pooja got over.

Now towards the end, When I was going through the tamil channels, having asusual advertisements than the programme, my thought pricked a question, Is it a day of festival or day for entertainment? Media has the same logic for all days....whether its deepavali, vinayaka chaturthi or Gandhi jayanthi.

Thanks to Vinayaka....I could unplug the cable and TV wires today so early due to lighitning and thunder showers...otherwise I would have been watching the newly launched Kalaigner TV with same non-sense shows and faces and I wouldn't have written this blog, My first Blog.