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.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

Leave a Reply


six − 4 =



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
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.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close