Sqlite in Java. Performance.

I’ve prepared comparison and gathered some statistical information about sqlite performance in Java. Performance overhead in auto-commit enabled mode is huge.

First of all let’s take a look at database model which is used in our analysis.

DbStructure

There is main table syslog and several depentent tables. Each record in main table has timestamp field, text attribute and several additional attributes (session, category, type and sender). In our test we consider record length is 128 bytes, attribute length is 64 bytes. So for each record we generate:

  1. Random text attribute (128 bytes length string)
  2. Random category attribute (64 bytes length string)
  3. Random type and sender attributes (both 64 bytes length strings)
  4. Timestamp attribute is set to the current system time
  5. Session attribute is the same for all of the records

Test is run three times. First test run uses 25 iterations and on each one of them it perform insertion of 10 records into main database table. Second run uses 25 iterations and 100 records. And finally (third run) 25 interations and 1000 records are used. Auto-commit is set to true, so sqlitejdbc driver commits results to database after each record’s insertion.

Before each iteration and exactly after it system time is collected (as well as main table and all dependent ones are truncated). Here are the results:

Sqlite insertion time

Sqlite insertion time

As you can see when insert 25 records one by one, it takes average 14386 ms to perform overall insertion (or average 575 ms per one insert). If we insert more records, average per record time increases:

Plot2d

BarPlot

So, we have average 575-615 ms per one insertion (actually 4 records are inserted – into syslog, syslog_categories, syslog_types and syslog_senders tables).

Now let’s turn off autocommit mode.

One Response to “Sqlite in Java. Performance.”

  1. hibernated Says:

    there are many java libraries for SQLite, so would you please tell us which one used for this test?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: