SQLite is a very important asset of every Android programmer. It offers a really easy to use solution to cache all your data. As a local database, most of the time, it is not going to suffer of heavy insertions. But sometimes you are going to have to insert a lot of rows and, if you are not very well informed, you are going to discover in a painful way that the common way of insert data using Android api is astoundingly slow. I had to deal with that blow when programming DroidTerm because I needed a local database of USB vids and pids (Naively I started to look for REST api or something like that. Eventually I found something…that was merely a web hosted text file).

These examples are based on code I wrote working on this and code is available here.

Mainstream insertions
This is what you are going to find first time you have to populate a SQLite database in Android.

private SQLiteDatabase db;
/*
...............
*/
public long insertEntryVersion(String version, String date)
{
    ContentValues values = new ContentValues();
    values.put(VERSION_COLUMN, version);
    values.put(DATE_COLUMN, date);
    values.put(ID_VERSION, currentVersion);
    return db.insert(VERSION_TABLE, null, values);
}

It is an easy way using Bundle-like ContentValues class to store data per column and inserting through SQLiteDatabase.insert(String table, String nullHack, ContentValues values) without messing with your own SQL statement, nice huh? Well, most of the times I use this and works fine but if you are going to perform a heavy insertion you are going to discover that inserting…let´s say… thousands of rows can last for more than a minute or even more!

But it is not a big deal when you know what you need to speed up insert operations. Compiled statements, and even more important, SQL transactions are going to save the day.

Not all is lost
Compile your insert instructions as follows:

private SQLiteDatabase db;
/*...*/
private static final String INSERT_VENDOR = "insert into vendorTable(vid,vid_name) values(?,?)";
/*... */
insertVendor = db.compileStatement(INSERT_VENDOR);

Our SQL statement is now compiled so We are going to create a function to handle it.

public void insertEntryVendor(String vid, String vidName)
{
    insertVendor.bindString(1, vid);
    insertVendor.bindString(2, vidName);
    insertVendor.execute();
    insertVendor.clearBindings();
}

And it is all ready to insert all our data much faster!

String[] vidPid = new String[2];
db.beginTransaction();
while(vidPid != null)
{
    vidPid = vidPidGenerator(); // Returns null when no more vid and pids are available
    if(vidPid != null)
    {
        insertEntryVendor(vidPid[0], vidPid[1]);
    }
}
db.setTransactionSuccessful();
db.endTransaction();

It is that simple! I hope you found this little help useful 🙂

Happy crafting!