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.

	public void basic_queries(){
	SQLiteDatabase db = SQLiteDatabase.create(null);
	db.execSQL("CREATE TABLE phone (devicename TEXT, company TEXT)");
	db.execSQL("INSERT INTO phone VALUES ('M8','HTC')");
	db.close(); // remember to close database when you’re done with it
	}

view raw basic_satements.java hosted with ❤ by GitHub

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. 

	private static final String[] phoneNames={"M8","X1","Grand"};
	private static final String[] compNames={"HTC","MOTO","SAMSUNG"};
	private final SQLiteDatabase db;
	public void populateWithData () {
	db = SQLiteDatabase.create(null); // memory-backed database
	db.execSQL("CREATE TABLE phone(devicename TEXT, company TEXT)");
	int i = 0;
	for (String name : phoneNames) {
	String company = compNames[i++];
	String sql = "INSERT INTO phone VALUES(\"" + name + "\",\"" + company +
	"\")";
	db.execSQL(sql);
	}
	}

view raw populate_the_database.java hosted with ❤ by GitHub

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

	private static final String[] phoneNames={"M8","X1","Grand"};
	private static final String[] compNames={"HTC","MOTO","SAMSUNG"};
	private final SQLiteDatabase db;
	public void init () {
	db = SQLiteDatabase.create(null); // memory-backed database
	db.execSQL("CREATE TABLE phone(devicename TEXT, company TEXT)");
	}
	public void UsingStingFormat(){
	int i = 0;
	for (String name : phoneNames) {
	String company = compNames[i++];
	String sql = String.format("INSERT INTO phone VALUES(\"%s\",\"%s\")",name,company);
	db.execSQL(sql);
	}
	}
	public void UsingStringBuilder(){
	StringBuilder builder = new StringBuilder();
	builder.append("INSERT INTO phone VALUES(\"");
	int resetLength = builder.length();
	int i = 0;
	for (String name : phoneNames) {
	String company = compNames[i++];
	builder.setLength(resetLength); // reset position
	builder.append(name).append("\",\"").append(company).append("\")"); // chain calls
	db.execSQL(builder.toString());
	}
	}

view raw String_format_and_Builder.java hosted with ❤ by GitHub

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.

	public void UseCompileStatement () {
	SQLiteStatement stmt = db.compileStatement("INSERT INTO phone VALUES(?,?)");
	int i = 0;
	for (String name : phoneNames) {
	String company = compNames[i++];
	stmt.clearBindings();
	stmt.bindString(1, name); // replace first question mark with name
	stmt.bindString(2, company); // replace second question mark with company
	stmt.executeInsert();
	}
	}

view raw compile_statement.java hosted with ❤ by GitHub

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.

	public void UseContentValues () {
	ContentValues values = new ContentValues();
	int i = 0;
	for (String name : phoneNames) {
	String comp= compNames[i++];
	values.clear();
	values.put("devicename", name);
	values.put("company", comp);
	db.insert("phone", null, values);
	}
	}

view raw content_values.java hosted with ❤ by GitHub

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.

	public void UseCompileStatementOneTransaction () {
	try {
	db.beginTransaction();//strat a transaction
	SQLiteStatement stmt = db.compileStatement("INSERT INTO phone
	VALUES(?,?)");
	int i = 0;
	for (String name : phoneNames) {
	String comp = compNames[i++];
	stmt.clearBindings();
	stmt.bindString(1, name);
	stmt.bindString(2, comp);
	stmt.executeInsert();
	}
	db.setTransactionSuccessful(); // remove that call and none of the changes will be committed!
	} catch (Exception e) {
	// handle exception here
	} finally {
	db.endTransaction(); // this must be in the finally block
	}
	}

view raw transaction.java hosted with ❤ by GitHub

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”.

• 
•