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.
Reference: SQL Lite Performance on Android from our JCG partner Isaac Taylor at the Programming Mobile blog.
