Performance Analysis of SQLLite on Android

Some people may not know that Android has some built-in class libraries that support SQL Lite database operations. He provides a good way to organize a small amount of data on Android. In any case, there are some traps when using these class libraries.

Depending on your version, the running time of the same query may vary from several milliseconds to several minutes. For example, a query may run less than one second on Galaxy S2 (which may be faster on iPhone 4), but it takes one minute to run on Atrix 2 and HTC Desire. All these phones have similar hardware, so what is the difference?

After studying the code for a few days, I found that the problem lies in the design of the query statement. When you use a large number of joins or unions, the problem arises. When combining a large data table and one or more data tables of medium size, you must be careful with optimization to ensure good performance on all devices. It is important to limit the size of a data table before creating unions or joins!

Take the following database and data table as an example:

· A Person table with fields such as name, height, and age

· A Family table that contains details about the Family

· A City table that contains information about all cities

In Android, all these tables are combined (assuming that the Person table has more than 2000 records) on most devices. However, if your user is using an old version of SQL Lite, your application will become unavailable slowly. You should try to minimize the number of join records as much as possible to ensure performance. For example, if you extract some records from the Person table and perform join operations, the performance will be much better.

The difficulty here is how to know what version of SQL Lite you are using. Although Android has a default version, it seems that different vendors use different SQL Lite versions on different devices. This causes us a lot of trouble.

There is some information about this in StackOverFlow. In short, it is very difficult to try to obtain the SQL Lite version of the device. You 'd better spend your effort on optimizing SQL to ensure good performance on all devices.

Another interesting aspect is when a query is executed on Android. Maybe you think that when you get the Cursor object, the query is finished. However, the query is not executed until the Cursor is accessed for the first time, such as the moveToNext and moveToFirst operations. Therefore, do not use cursor in the UI thread or related thread; otherwise, the interface will become stuck.