Android SQLite tip 1 – Wrap batches of writes in a transaction

I’ve been working on the Shhmooze Android app. When the app is first run there are quite large amounts of data that need to be downloaded and persisted to an Android SQLLite db on the device(around 200o records). I was running into an issue where these writes to the db were taking a very long time. After running a trace using the Debug class I discovered that the main issues were the compilation of the SQL statements and the individual writes to the db each row was taking about 15-30ms to write , this was not good.

I experimented and found a couple of small tweaks that make a huge difference, this first is wrapping the batch of writes in a transaction. The code looks like this:

This code is not specific to content providers(it can be used anywhere you do an insert to a SQLite db) and you probably wouldn’t be writing a ContentProvider if you used this code(the ContentProvider insert() method only supports the insertion of a single row.) This code is really useful if you are inserting a LOT of rows, such as filling the DB content when the app first runs(I use the above code to fill the local SQLite DB from a webservice call when the app first runs)