Search this site:

2010-02-07

Batch insert to SQLite database on Android


I am using LIME for Cangjie(倉頡) input method on my Nexus one. The app is really nice and handy. However, there are quite a lot of users asking to improve the speed for loading the mappings.

After knowing the app was released as open source software, I immediately checked out the source and had a look on the loading function.

Oh, data was stored in SQLite. Below is the simplified version of pseudo code for the importing:

ArrayList list = new ArrayList();
count = 0;
while (reading a line from input file succeed) {
list.add(line);
if (count = 100) {
insertList(list);
reset(list);
count = 0;
} else {
count++;
}
}
insertList(list);


In order words, the author was trying to do a batch insert of 100 records (indeed 101...☺). This is a reasonable trial as batch insert can normally save some overheads. However, when I further went into the insertList() function, I saw something like the below pseudo code:
for each record in the list {
do_some_processing();
if (line represent a valid entry) {
db.insert(SOME_TABLE, null, SOME_VALUE);
}
some_other_processing();
}


Thanks to my previous projects involving SQLite, I believed I had found out the potential issue of the slow loading of mappings.

As described in my previous notes, SQLite, by default, creates a journal file for each transaction, and delete the file after the transaction completes. The above code does not explicitly create any transactions, so each insert call will be treated as an independent one. As a result, expansive file operations will be called frequently and slow down the process...

Therefore, I modified the code to make all the insert calls in the same batch in a single transaction:
try{
db.beginTransaction();
for each record in the list {
do_some_processing();
if (line represent a valid entry) {
db.insert(SOME_TABLE, null, SOME_VALUE);
}
some_other_processing();
}
db.setTransactionSuccessful();
} catch (SQLException e) {
} finally {
db.endTranscation();
}


I then tested the code in the emulator running on my notebook. The original code inserts at a rate of around 10 records per second, while the version with transaction inserts at a rate of 30+ records per seconds. WOW, that's a sound improvement!


I then attempted to increase the batch size from 100 (ok, 101...☺) to 500 (501...☺) and hoping for a further improvement. However,the insert rate drop to around 28 records per seconds. Still sounds good, but why not just keep the old batch size for a better result? The drop may due to limited resources in emulator, so in other words, the app will have a different optimal batch size on different handset. I rather keep that part untouched.

Reported the findings to the author, and hoping for a new release soon~ Anyway, just another story of optimizing the insert speed of large amount of data to SQLite. Enjoy! :)


22 comments:

  1. this was extremely helpful! thank you!

    ReplyDelete
  2. Good information! Many thanks!

    ReplyDelete
  3. Wow! I just implemented this and cut a 15 minute operation to 6 minutes. Thank you very much.

    ReplyDelete
  4. This tip totally made my day, and it is even more effective on a real device than in emulator. Together with some other minor changes, a process which used to take about 10-15 minutes now takes 9 seconds!

    ReplyDelete
  5. Glad to know that the article helps. Looking forward to see your great app on market!

    ReplyDelete
  6. That nice trick speeded up my App so much! Now 1000 inserts take 100ms instead of 20000ms!

    ReplyDelete
  7. Thank you!!! Thank you!!! You've made my day. Insertion times are down from 10s per 1500 rows to under 250 ms.

    ReplyDelete
  8. Damn was that helpful! My update process took 8 seconds and now it takes only 1 second! :-D

    Thank you!

    ReplyDelete
  9. Thank you it helped a lot.

    ReplyDelete
  10. What about using the http://developer.android.com/reference/android/database/DatabaseUtils.InsertHelper.html#prepareForInsert()

    This class is made for your use case.

    ReplyDelete
  11. @dirk, yes you are probably right! it proved the importance of using the correct API :p

    ReplyDelete
  12. Thanks for the post.
    Importing a complete database in my app was taking some 2 minutes. Now it takes all of 3 seconds.

    ReplyDelete
  13. Lovely article. Cut down the database execution time by 39 seconds... (from 40 seconds to 0.5 seconds ^^)

    Bookmarked and spread around trough social media :)

    ReplyDelete
  14. Thanks for the post, I used begin and end transactions for syncing data, every thing is working fine for the first time app launch and if I restart the app and run sync process the log says Failure 5 (database is locked) on 0x547380 when executing 'BEGIN EXCLUSIVE;'
    java.lang.IllegalStateException: no transaction pending
    at android.database.sqlite.SQLiteDatabase.beginTransaction(SQLiteDatabase.java:562)

    ReplyDelete
  15. Here is a benchmark of mine (1000 insert):

    * traditional way: ~ 41 sec
    * Android's InsertHelper: ~ 40 sec
    * your approach: 0,3 sec

    Thanks a lot :-)

    ReplyDelete
  16. thanks all for the stats. glad that it helps someone!

    @kishore sorry just saw your comment. it sounds like that your app doesn't release the db lock before restarting? or is the 1st instance of the app still running?

    ReplyDelete
  17. I inserted tens of thousands of records and went from taking several HOURS to just a few SECONDS! Unbelievable, thanks!

    ReplyDelete
  18. Could this also work with db.execSQL(..) ?

    ReplyDelete

HTML Tags allowed (e.g. <b>, <i>, <a>)

Disclaimer

ALL CONTENTS AND INFORMATION IN THIS WEB SITE ARE PROVIDED "AS IT" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED. THE ACCURACY AND AVAILABILITY OF THE CONTENTS, INFORMATION AND THE WEB SITE ITSELF ARE NOT GUARANTEED. THE AUTHOR TAKES NO RESPONSIBILITIES ON ANY COSTS OR DAMAGES (DIRECT OR INDIRECT) ARISING OUT OF OR IN CONNECTION WITH THE ACCESS, USAGE OR INABILITY OF USAGE OF THIS WEB SITE.