SPEEDING UP SQLITE INSERT OPERATIONS

tangbinde

I’ve been working on an Android application recently which uses an SQLite database to store and search about 6000 rows of data, and the data is updated from a webservice periodically. It would seem like a fair assumption to think the retrieval of data from the webservice would be the slowest part of the operation, however – this turned out not to be the case.

On the emulator, the actual retrieval and parsing of the data from a CSV-format took about 20 seconds, while inserting the data to the database took 71 seconds.

Since this was an operation that only ran about once a week, I felt a minute and a half was an acceptable amount of time since it would run in the background anyway. When I launched the application to my Android device and ran the initial synchronization, the 6000 inserts took a shocking 478 seconds – almost 8 minutes!

I found this strange – most operations usually run a little slower on the emulator than on an actual device, and this was on the Samsung Galaxy S – one of the faster Android devices. This being my first real use of SQLite, I figure the next step was to try and optimize my inserts – compiled statements was next in line:


String sql = "INSERT INTO table (number, nick) VALUES (?, ?)";
SQLiteStatement stmt = db.compileStatement(sql);
for (int i = 0; i < values.size(); i++) {
    stmt.bindString(1, values.get(i).number);
    stmt.bindString(2, values.get(i).nick);
    stmt.execute();
    stmt.clearBindings();
}

This brought the total time of the insertion on the emulator down from 71 to 56 seconds. Testing the same operation on the device resulted in a total insert time of 487 seconds, 9 seconds slower than before. I ran the test again and ended up with a similar result – I was clearly not on the right track here.

Digging further, I found a documentation page for SQLite telling me that “PRAGMA synchronous = OFF” would tell SQLite to continue without syncing as soon as it has handed data off to the operating system. This resulted in an insertion time of 50 seconds, compared to the 71 seconds we started with. Testing this on the device resulted in encouraging results; 361 seconds – almost two minutes faster than what we started with.

Feeling I was on to the right track and suspecting the filesystem was slowing down the inserts, I found various references around the net mentioning problems with the performance of the I/O on the Samsung Galaxy S – all related to the RFS file system. What this means for our SQLite database is that it will perform an fsync at every insert to make sure it’s been written to disk…

Reading up on SQLite, I discovered that with transactions I could keep the data in memory and commit the changes to filesystem with one operation instead of 6000:

String sql = "INSERT INTO table (number, nick) VALUES (?, ?)";
db.beginTransaction();

SQLiteStatement stmt = db.compileStatement(sql);
for (int i = 0; i < values.size(); i++) {
    stmt.bindString(1, values.get(i).number);
    stmt.bindString(2, values.get(i).nick);
    stmt.execute();
    stmt.clearBindings();
}

db.setTransactionSuccessful();
db.endTransaction();

Combining transactions with compiled statements yielded a tremendous performance boost: From the original 71 seconds on the emulator down to an incredible 5 seconds!
The performance on the Galaxy was even more shocking: from the original 478 seconds down to 1.5 seconds!

I had to rerun the benchmarks and verify that all the data were actually being inserted – but luckily I was able to reproduce the results.

Transactions are useful for multiple reasons:

When performing inserts to multiple tables and you want to make sure that either everything or nothing gets inserted
When doing a batch insert of multiple rows (like the example above shows)
Conclusions:

Unless you’re doing a single insert or it is critical that rows gets written to disk right away, use transactions.
Make sure to test your application on an actual device, preferably more than one.
The performance numbers above apply to the Samsung Galaxy S – a different brand or model might give a smaller performance gain