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:
db.beginTransaction();
for (IRemoteUser user : users) {
saveUser(user);
}
db.setTransactionSuccessful();
db.endTransaction();
As you can see this is pretty simple, start a transaction, write the objects to the db(in this case I’m doing this in a child method) and then commit the transaction.
jamesw
November 11, 2011
In isolation your code is of no help at all! How does this work with a content provider?
simonwdixon
November 11, 2011
Hi James,
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)
gazer
August 2, 2012
If you want to use transactions on insert with a ContentProvider you should use bulkInsert method instead of insert. Them inside that method you can wrap all the insert operations in a transaction.
Check http://eshyu.wordpress.com/2010/08/15/using-sqlite-transactions-with-your-contentprovider/ for an implementation example.