inserting a large number of records with SQLiteStatement.

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

 

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

Figure B

 

 

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.