Following a comment from Alexandre on a previous post, I went a little bit further with my performance test of database engines running under Java. This evening, I tested a profiling tool and a variable number of insertions/retrievals (I didn’t tested transaction).

Taking the code from the previous time, I simply changed the number of elements to be inserted/retrieved. As expected, the durations of object initialization (except for 2 points for Derby and H2) and database creation did not change with the number of elements to be inserted, Derby being still the slowest engine to create a simple database (1 table only). The durations of the insertion step increased slowly with all the database engine, except for SQLite+JDBC: you can see a much steeper initial angle in the increase of the duration in the graph below (be careful: x-axis shows logarithmic values).

For the retrieval, all the engines increased their time spent in this step in the same way (approximately). All the graphs can be seen here.

Performance analysis was completed using a free profiling tool, jRat (list of tools available here and here). There is a big difference here since jRat measures the time spent in each function. These functions approximately match the previous “steps” but not exactly. And I had memory problems using jRat with a number of elements inserted > 100 (hence the limit here).

Derby and SQLite+JDBC always performed worst than other engines (except for the showData() function). Usually, H2 and HSQLDB had more stable results (smaller standard deviations). And SQLite+JDBC was still the worst engine regarding data insertion (see graph below).

It was also very strange to see that H2 and HSQLDB took approximately the same time to insert 100 or 1000 elements (note that for HSQLDB, I did not take into account the fact one needs to explicitly close the connection, allowing HSQLDB to temporarily store data in memory before committing them to the file — but closing the connection didn’t take so much time). All the graphs can be seen here.

One conclusion of all this: if you write a Java application and need a fast Java database engine, use HSQLDB (BSD-like license) or H2 (modified MPL). Next time, I’ll test transactions (but I don’t know when it’ll be).

Following a comment from Alexandre on a previous post, I included SQLite in my performance test of database engines running under Java.

What prevented me from using SQLite in the previous test is that it’s not a pure Java database and one have to use third-party JDBC driver and implementation classes in order to manage this database engine. IMHO, I also dislike another fact: SQLite does not enforce data type constraints (and it’s a feature, not a bug) so everything is stored as ASCII string, even if you have very few other “artificial” data types.

In order to include SQLite in my test, I have to rely on a third-party libraries: David Crawshaw’s JDBC driver for SQLite. Some minor adaptations also had to be done to the code (see the SqliteTest class in the source code below). Brandon T. provides a good tutorial on how to use this driver on MS-Windows here.

The result? SQLite performances are worst than Derby! In this case, the slowest step is the data insertion: more than 11s for 100 insertions (see graph below). The database creation step is also slower than with H2 or HSQL (but much faster than Derby). If you compare the whole process (initialisation + creation + insertion + retrieval), SQLite (and its JDBC driver) is the worst database engine. The only good point is that it only creates one file (the other engines creates at least 2 files) and this file is only 5kb.

Click to show the normal size graph.
A graph with a log y-axis is available here

Protocol. Same as in the previous post except that now I have 4 engines to rotate. Source code is here (4kb) and the jar file + libraries is here (5Mb).