Some Android database performance observations

The code often needs to do a bunch of operations together, and for that, I use SQLite transactions with a method that looks roughly like below. This code starts a transaction, then performs all requested database operations, and finally ends the transaction.

I tested this code with the database located in the phone’s internal storage, and also on the memory card. This is where it gets interesting.

According to the “J Disk Benchmark” (available in Market), my Motorola Milestone’s microSD memory card is significantly faster than internal storage. The benchmarks shows 1,5M/4,2M write/read throughput for the memory card, and only 0,3M/2,9M throughput for the internal storage.

However, I observed that my application runs faster (by about 30%) when its database is located in internal storage. Quite a surprise, if you conisder the above numbers, right?

To understand why, I added code to collect timings of each individual operation in the above function, including the time taken by endTransaction. Looking at the numbers collected inside the function, it all made sense. Here they are:

The average timings (the values are milliseconds) are lower for the internal memory, and the difference is quite dramatic for “end”, which is the average time taken by endTransaction (this is where the database is actually updated). I believe that the difference is due to higher latency of writing to the microSD memory card – perhaps it’s the overhead of the hardware interface, perhaps it’s a driver issue, I don’t really know. But the numbers speak for themselves: ending a transaction takes a lot longer if the database is located on the external memory card.

And just for laughs, I also ran this test on a Samsung Galaxy S with Android 2.2 (this phone is world-famous for constant stuttering / freezing):

Finishing the transaction (where the data is actually written out) on the Galaxy S can take, on the average, up to a full second or even more.

No wonder this phone’s interface continually stutters and freezes for a few seconds at a time. To be fair, this issue is fixed in Samsung’s 2.2.1 firmware update, but it breaks other things. This is my primary phone (that I carry around), so at this point, I’m not sure if I’m going to upgrade it to 2.2.1, or wait until 2.3 is released.

And the bottom line is – when it comes to database performance, file system thoughput doesn’t tell the whole story, write latency is just as, or more, important.