Android optimize

Turbocharge your SQLite inserts on Android

Find out how William J. Francis sped up his database inserts by using the SQLiteStatment class in his Android app.

The internal database I was using in an Android application was too slow at startup. I was doing a lot of SQL inserts, building a table to bootstrap the application for future operations. I mentioned the problem to a coworker, and he suggested I use the Android SDK InsertHelper class. He pointed me to a couple of blogs, and it looked to be just what I needed until I tried implementing the code in my recently updated development environment. As of API 17 (Jelly Bean 4.2), the InsertHelper class was deprecated.

The same documentation that announced in big bold letters the InsertHelper was deprecated suggested I use the SQLiteStatment class. However, after following the link and scrolling down the page, I didn't see any implementation samples, and querying Google returned similar results. It didn't seem all that different from InsertHelper, so I decided to give it a try. Not only did it work, but using the SQLiteStatment class in conjunction with the start and end transaction tags, I was able to speed up my database inserts by a factor of 8x.

Below is a demo application I wrote that creates 100 records programmatically, inserts them using one of two methods, and then displays the time the operation took on the display. You can follow along with the step-by-step tutorial or download and import the entire project directly into Eclipse.

1. Start a new Android project in Eclipse. Target Android 2.2 or higher.

2. In the /res/layout folder, open activity_main.xml. You will use a linear layout, a couple of buttons, and a text view.

activity_main.xml 
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Bulk Insert Demonstration" />
    
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Standard Insert" 
        android:id="@+id/standard_insert_button"/>
    
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Bulk Insert" 
        android:id="@+id/bulk_insert_button"/>
    
     <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Execution Time: xxx" 
        android:id="@+id/exec_time_label"/>

</LinearLayout>

3. In the /src/MainActivity.java file, let's start by adding a few class variables, initializing an empty database, and wiring up the buttons.

MainActivity.java
package com.authorwjf.bulkinsertdemo;

import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.TextView;
import android.app.Activity;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

public class MainActivity extends Activity implements OnClickListener {

	private static final String SAMPLE_DB_NAME = "MathNerdDB";
	private static final String SAMPLE_TABLE_NAME = "MulitplicationTable";
	private SQLiteDatabase sampleDB;
	
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		initDB();
		findViewById(R.id.standard_insert_button).setOnClickListener(this);
		findViewById(R.id.bulk_insert_button).setOnClickListener(this);
	}

	private void initDB() {
		sampleDB =  this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);
		sampleDB.execSQL("CREATE TABLE IF NOT EXISTS " +
                SAMPLE_TABLE_NAME +
                " (FirstNumber INT, SecondNumber INT," +
                " Result INT);");
		sampleDB.delete(SAMPLE_TABLE_NAME, null, null);
	}

	@Override
	public void onClick(View v) {
		sampleDB.delete(SAMPLE_TABLE_NAME, null, null);
		long startTime = System.currentTimeMillis();
		if (v.getId()==R.id.standard_insert_button) {
			insertOneHundredRecords();
		} else {
			bulkInsertOneHundredRecords();
		}
		long diff = System.currentTimeMillis() - startTime;
		((TextView)findViewById(R.id.exec_time_label)).setText("Exec Time: "+Long.toString(diff)+"ms");
	}
	
	@Override
	protected void onDestroy() {
		sampleDB.close();
		super.onDestroy();
	}

}

4. Add our two database insert functions: one based on content values and the other on SQLite transactions.

private void insertOneHundredRecords() {
          for (int i = 0; i<100; i++) {
                     ContentValues values = new ContentValues();
                     values.put("FirstNumber", i);
                     values.put("SecondNumber", i);
                     values.put("Result", i*i);
                     sampleDB.insert(SAMPLE_TABLE_NAME,null,values);
           }
}

private void bulkInsertOneHundredRecords() {
          String sql = "INSERT INTO "+ SAMPLE_TABLE_NAME +" VALUES (?,?,?);";
          SQLiteStatement statement = sampleDB.compileStatement(sql);
          sampleDB.beginTransaction();
          for (int i = 0; i<100; i++) {
                    statement.clearBindings();
                    statement.bindLong(1, i);
                    statement.bindLong(2, i);
                    statement.bindLong(3, i*i);
                    statement.execute();
           }
           sampleDB.setTransactionSuccessful();	
           sampleDB.endTransaction();
} 

Now you are ready to try the application on the emulator (this is not production code). I'm purposely performing all the work on the UI, so it becomes painfully obvious how long the operations are taking. I still think you will agree there is more than enough code to make a convincing argument for using the transactional inserts. And since they say a picture is worth a thousand words, take a look at these illustrations.

Pressing the first button, our application reports the insert operations took just over 1600 milliseconds (Figure A).

Figure A

android_turbo_sqlite_1_090313.png

The bulk insert method was able to initialize the same table in under 100 milliseconds (Figure B).

Figure B

android_turbo_sqlite_2_090313.png

It's a phenomenal speed gain in exchange for a very minor increase in code complexity. Now that I've experienced these speed gains firsthand, I can't imagine many scenarios in which I won't be use bulk inserts going forward.

About

William J Francis began programming computers at age eleven. Specializing in embedded and mobile platforms, he has more than 20 years of professional software engineering under his belt, including a four year stint in the US Army's Military Intellige...

2 comments
sometxdude
sometxdude

Thanks for the tip.  The test results speak for themselves!

David.Coronel
David.Coronel

Thank you very much!!!

I had this pain in my app connected with an azureDB, wich gives me tousands of rows per each table, and was like 20 tables, the app was spending like 2 minutes in the complete inserts.

I'll try this and comment back.