Thursday, March 29, 2012

SQL Lite Performance on Android

 

For those who are unaware, Android has some build-in classes to support the use of SQL Lite databases. It provides a great way to structure a small amount of data within Android. However, there are a few pitfalls of using these classes that you should watch out for though.

Depending on which version you are using, a given query could run in milliseconds or minutes. For example, a query that runs in less than a second on a Galaxy S2 (and even quicker on a iPhone 4) takes a full minute to complete on an Atrix 2 and the  HTC Desire. All of these phones are relatively similar in terms of hardware, so what is the difference?

After studying and analyzing the code in question for a few days, it has come down to how the queries are designed. The problem arises when any large joins or unions are used. On many phones, it’s difficult to see much of a slow down. Combining a large table with one or more medium size tables needs to be optimized very carefully to ensure good performance across all devices. It’s very important to limit the size of any large tables before any unions or joins are computed.

Think about the following example database with three tables.

  • A Person table, with details like a person’s name, height, age, etc
  • A Family table, which contains details about a family
  • A City table, which contains a handful of cities where all of the families are located

Joining all three tables together in Android (assuming the Persons table has at least 2000 entries) would work just fine on most devices. If your user is the unlucky one with a older SQL Lite version, your app becomes unusable. Making sure any major join between several large tables is as small as possible ensures acceptable performance across all devices. For example, if it’s possible to pull a subset from the Persons table (by removing an order of magnitude), you will see a huge increase in performance.

The tricky part is figuring what SQL Lite version a given device is actually using. Although Android does have a version baked into a given OS version, manufactures seem to have different SQL Lite versions on different devices. This can cause a major headache when trying to figure out the SQL Lite version of a given device.

Here is a bit of info on StackOverFlow. Figuring out what version of SQL Lite is installed on a given phone can be painful. You’re better off spending some extra time on your queries up front to ensure good performance across Android now rather than later.

One other interesting fact regarding the Android SQL Lite classes involves when a query is actually executed on a given database. You would think the query would actually be executed when you receive a Cursor object. But as it turns out, the query isn’t executed until the cursor is actually used (like when the first moveToNext or moveToFirst). Therefore it’s important to make sure the cursor isn’t being used anywhere near the UI Thread.

4 comments:

  1. Interesting that the differences re so large from device to device!
    Getting the version of the of the database is not that hard - just select sqlite_version() and you got it ;-)
    Andsen

    ReplyDelete
  2. How about indexing? Could it be that these tables are not indexed?

    ReplyDelete
  3. Ryan (Your Cousin)March 31, 2012 at 2:14 PM

    Indexing and proper structure are also things you need to look for. I've never used SQL lite before but if it is anything like the other big boys, structure and proper keying are very important.

    ReplyDelete
  4. Can you elaborate a bit more about the handling of the Cursor you mentioned in the last paragraph? Where is this information from?

    I've read some time ago that cursor.getCount() is a costly operation. For the reasons quite alike to those in your last paragraph. But I cannot find the source for this anymore and haven't found any proff of this.

    Actually cursor.getCount() is used in stock Android apps quite often.

    ReplyDelete