VGTech is a blog where the developers and devops of Norways most visited website share code and tricks of the trade… Read more



Are you brilliant? We're hiring. Read more

Speeding up SQLite insert operations

Mobile

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:

Show code
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:

Show code
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

Developer at VG with a passion for Node.js, React, PHP and the web platform as a whole. espen.codes - @rexxars


13 comments

  • Richard

    Hi, thanks for this very helpful post. I'm look at a very similar issue and was wondering how you did the setting of the 'PRAGMA synchronous' parameter.


  • Douglas Costa

    Hello I am Brazilian, I would get one of your information, I am android developer, would like to learn more as you get better detales the speed of INSERT, I have a problem to insert 2000 rows and takes about 5-6 minutes. Because you used "PRAGMA synchronous = OFF". Could you give more details of what you changed to improve performance. If you can pass your email address to communicate better. Thank you =]


  • Bojan Kseneman

    Thanks, I was about to give up on databases and move to simple txt files, since writing 1k rows took a little less than 17 seconds, now with your help it takes 170ms.


  • Suraj Shinde

    Thanks , It's to good post for me,because I lot of wast time on similar problem..


  • greenDao 40 seconds to insert 600 records - android - Developers Q & A

    [...] http://tech.vg.no/2011/04/04/speeding-up-sqlite-insert-operations/ [...]


  • Roberto

    OMG you saved me with this article just when i was almost giving up of databases. thank you


  • Bruno Voqui

    Hi,
    I am working with SQLite on a legacy board with only 32Mo SDRAM, and fear that your transaction approach may fail because it means that the queries inside it are stored somewhere before the COMMIT ; gains in SQLite may be lost because the parameters will be swapped in and out.
    In my context, we have sometimes 1 or 200000 rows times tens of columns (yes I know it is not a well formed table but have to do with it) giving a total SQL script in the order of 50 to 70000 kO
    I am now thinking about a FUSE filesystem with a far faster fsync , may it be the proper solution ?


  • Ramesh

    Hi,
    your article was very helpful to me as i was trying to insert 40000+ records into database.. Thank You so much


  • Mr Bug

    Hi, Thanks very much. Your post very help full with current project... 13 seconds for 764 records down to 142ms... :)) (y)


  • jailson

    IF SO inverter for (int i = 100; i> 0; i -) I had been STILL Faster


  • DIOGO RODRIGUES GONCALVES

    Ty so much. Words are powerless to express my gratitude.


  • Simon

    this is amazing. thanks so much


  • Sagar

    Superb .. thanks


Leave your comment