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
H2: SET LOCK_MODE 0

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.

4 comments:

invincible01 said...

Amazing Article

internship in chennai
internship in chennai for cse
internship for mba in chennai
internship in chennai for hr
internship in chennai for mba
companies for internship in chennai
internship in chennai for ece
paid internship in chennai
internship in chennai for biotechnology
internship in chennai for b.com students

Kaylee Brown said...

I wonder how you became so capable to write such interesting and informative articles, any way you are one of the best bloggers in my eyes, Keep it up. computer network assignment help

Thomas More said...

Hmm, my younger brother is a computer science student. I think he will find this post helpful. I am sharing the link with him and then I have to look for dissertation presentation writing services for my marketing dissertation presentation. Ah, I wish I had taken computer science as well. This post looks interesting but sadly, I can’t understand it.

Dim4ksan said...

I spent a long time looking for a woman who would be supportive and helpful to me. But, sadly, I could not locate such a girl in my nation. It was a period when I truly needed a woman's help. Then I came upon an article regarding ukrainian women for marriage. I was interested in the prospect, so I started looking online. I didn't get very far, but with these guys, you may find a woman who will love and support you. I wish to suggest these gentlemen to everyone; they will not disappoint you and may perhaps alter your life.