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.
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:
- Random text attribute (128 bytes length string)
- Random category attribute (64 bytes length string)
- Random type and sender attributes (both 64 bytes length strings)
- Timestamp attribute is set to the current system time
- 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:
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:
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.