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.

@property (readonly, strong, nonatomic) NSManagedObjectContext *managedObjectContext;

@property (readonly, strong, nonatomic) NSManagedObjectModel *managedObjectModel;

@property (readonly, strong, nonatomic) NSPersistentStoreCoordinator *persistentStoreCoordinator;

3. In the /src/ file, let’s start
by adding a few class variables, initializing an empty database, and wiring up the
package com.authorwjf.bulkinsertdemo;

import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.TextView;
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;

protected void onCreate(Bundle savedInstanceState) {

private void initDB() {
sampleDB = this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);
" (FirstNumber INT, SecondNumber INT," +
" Result INT);");
sampleDB.delete(SAMPLE_TABLE_NAME, null, null);

public void onClick(View v) {
sampleDB.delete(SAMPLE_TABLE_NAME, null, null);
long startTime = System.currentTimeMillis();
if (v.getId() {
} else {
long diff = System.currentTimeMillis() – startTime;
((TextView)findViewById("Exec Time: "+Long.toString(diff)+"ms");

protected void 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);

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

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.