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.

Reference: SQL Lite Performance on Android from our JCG partner Isaac Taylor at the Programming Mobile blog.

Related Whitepaper:

Rapid Android Development: Build Rich, Sensor-Based Applications with Processing

Create mobile apps for Android phones and tablets faster and more easily than you ever imagined

Use 'Processing', the free, award-winning, graphics-savvy language and development environment, to work with the touchscreens, hardware sensors, cameras, network transceivers, and other devices and software in the latest Android phones and tablets.

Get it Now!  

Leave a Reply

1 × five =

Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.

Sign up for our Newsletter

20,709 insiders are already enjoying weekly updates and complimentary whitepapers! Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

As an extra bonus, by joining you will get our brand new e-books, published by Java Code Geeks and their JCG partners for your reading pleasure! Enter your info and stay on top of things,

  • Fresh trends
  • Cases and examples
  • Research and insights
  • Two complimentary e-books