Query optimization & Transactions

This post explores some of the concepts of query optimization & transaction management for Android apps. Most of the applications won't be using so much of the SQLite features but it's always good to know about possible optimizations when you deal with databases. I referenced the book Pro Android Apps Performance Optimization by published Apress.

1. Basic SQLite Statements

The basic SQLite statements are simple strings like-

• CREATE TABLE phone(devicename TEXT, company TEXT)
• INSERT INTO phone VALUES('M8','HTC')

The above statements are just simple strings & they have to be interpreted,or compiled before they can be executed. The compilation of these statements is down internally when we call the .execSQL().

The following code demonstrates how these statements are executed.

The problems with the above implementation is that SQLite statements takes some quite some time to execute. In addition to the compilation, the statements themselves may need to be created. String is also immutable(String is immutable means that you cannot change the object itself, but you can change the reference ) ,which poses performance issues here.

2. The INSERT Statement String

INSERT is the most used statement in any SQLite database as you are bound to insert several entries into the database.A basic implementation of inserting several values into the database is shown below. 

The problem with the above implementation is that for every insert statement, a String would be created and execSQL would be called.This prcedure takes a lot of time and memory.

One solution for this problem is to avoid using the + operator for String concatenation & instead use a StringBuilder object or calling String.format.The two new methods are shown below

3. The Compile Statement 

Now since we have addressed the issue of using & interpreting the sting statements, the next issue is of execSQL method.The execSQL does the actual compilation(parsing) of the statement.In the above examples the execSQL statement is called for every insertion to the database. All this can be avoided by only compiling the statement once & binding later.This is depicted below.

4. Using ContentValues

As we avoided the use of execSQL staement above, the same can be achieved using a ContentValues object. Its not better than the Compile statement method. but either way its an elegant method of getting things done.

5. Transactions

Transactions are automatically created when we perform any read/write from the the database. But creating explicit transaction can improve the performance tremendously.The implementation of  explicit transaction on compile statement is depicted below.

SHARE

Shobhit Chittora

Hi there, I am Shobhit Chittora. I am a college student and like to develop apps for Android platform.I started this blog to share my experiences with Android development and may be help you guys on the “roadtodroid”.

• 
•