Monday, November 17, 2008

Comparison between SQLite and H2 Databases

I found some article that says SQLite JDBC is slower than other DBMS. According to this source, SQLite took more than 11s for 100 insertions. This result is quite strange for me, and I guess no tuning option for SQLite is used in that experiment.

So, I wrote a simple test code to confirm the performance between SQLite and H2 databases. To the best of my knowledge, H2 is the fastest DBMS in the ones purely written in Java. Here is my test code.  In short, this code is optimized for insertions; using no transactions (a set of insertions is wrapped in a single transaction) and no locks. 

Tuning options for disabling lock acquisition:
SQLiteJDBC: pragma synchronous=off

For 1,000,000 insertions of simple records, I got the following results:
SQLite JDBC: 198.7 sec.
H2:    365.6 sec. 

If the above tuning options are not set in SQLite, each insertion acquires a file lock on the database file and creates an journal (log) file, so the performance will be significantly slower than the above result.

Although I can confirm SQLite is not slow, I have to admit SQLite JDBC driver has some overhead due to the interaction between native C codes and Java through JNI. When I used the command-line client of SQLite, it only took almost 100 sec for the same number of insertions through the import command of SQLite.

Ah, well, ... it's the nature of Java, which cannot beat sophisticated C programs in terms of performance.