Home > android > Some Android database performance observations

Some Android database performance observations

The application I’m working on now uses a database.

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.

	public long[] massDeleteInsertUpdate(Uri deleteUri, String deleteWhere, String[] deleteArgs,
			Uri insertUri, ContentValues[] insertValues, Uri update1Uri,
			ContentValues update1Values, Uri update2Uri, ContentValues update2Values) {
		SQLiteDatabase db = ...;
		try {
			db.beginTransaction();

			if (deleteTableName != null) {
				db.delete(deleteTableName, deleteWhere, deleteArgs);
			}

			if (insertTableName != null) {
				for (int i = 0; i < insertLen; ++i) {
					ContentValues val = insertValues[i];
					long rowId = db.insert(insertTableName, BaseColumns._ID, val);
				}
			}

			if (update1 != null) {
				update1.run(db);
			}
			if (update2 != null) {
				update2.run(db);
			}

			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}

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:

Motorola Milestone

External memory card


Test 1 took 45,81 seconds

count = 118, AVG del = 0,00, ins = 2,13, upd1 = 2,57, upd2 = 0,89, end = 74,03

Test 2 took 16,78 seconds

count = 61, AVG del = 0,00, ins = 1,46, upd1 = 3,31, upd2 = 1,30, end = 96,43

Internal memory


Test 1 took 30 seconds

count = 118, AVG del = 0,00, ins = 1,47, upd1 = 2,08, upd2 = 0,86, end = 17,97

Test 2 took 9,27 seconds

count = 61, AVG del = 0,00, ins = 1,72, upd1 = 2,25, upd2 = 0,93, end = 17,54

Throughput isn’t everything.

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

Samsung Galaxy S, firmware 2.2

Built-in memory card


Test 1 took 39,30 seconds

count = 118, AVG del = 0,00, ins = 2,82, upd1 = 3,98, upd2 = 2,43, end = 83,69

Test 2 took 13,01 seconds

count = 61, AVG del = 0,00, ins = 2,90, upd1 = 3,02, upd2 = 1,28, end = 65,18

This looks pretty good, actually faster than a Motorola Milestone’s external memory card, and actually makes me think that the higher latency is inherent in the microSD interface itself (the Galaxy S’s “large” internal storage area is actually a non-removable microSD memory card installed at the factory).

Now for the fun part – the below are timings for the Galaxy S’s built-in internal memory:

Samsung Galaxy S, true internal memory


Test 1 took 166,79 seconds

count = 118, AVG del = 0,00, ins = 3,09, upd1 = 5,06, upd2 = 1,83, end = 1026,36

Test 2 took 59,46 seconds

count = 61, AVG del = 0,00, ins = 3,34, upd1 = 3,93, upd2 = 3,16, end = 810,95

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.

Advertisements
Categories: android
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s