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! :)