Exploration

db.insert() with and without an explicit transaction

The official training material for Android’s SQLite bindings provides an example of populating a table using the insert() method provided by the SQLiteDatabase object. Unfortunately they don’t give any advice on how to do a lot of inserts at once.

The naive approach for the simple table looks something like this:

ContentValues values = new ContentValues(1);

for (int i = 0; i < numIterations; i++) {

values.put('val', random.nextInt());

db.insert("inserts_1", null, values);

}

I thought that there could be some performance to be gained by running the calls to db.insert() within a transaction, so I made that my first experiment:

db.beginTransaction();

ContentValues values = new ContentValues(1);

for (int i = 0; i < numIterations; i++) {

values.put('val', random.nextInt());

db.insert("inserts_1", null, values);

}

db.setTransactionSuccessful();

db.endTransaction();

Comparing implicit and explicit transactions when inserting data.

Looking at the chart, it’s pretty clear that wrapping a bunch of calls to insert() within a transaction vastly improves performance compared to doing the inserts without a wrapping transaction.

But why is it so much faster?

It turns out that unless you explicitly execute your queries between calls to beginTransaction() and endTransaction() , SQLite itself will wrap every query with an implicit transaction. From the docs:

No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.

It’s important to note that SQLite only writes the inserts to disk once the transaction has been committed. Therefore, if you can minimize the number of transactions (regardless of whether they’re explicitly or implicitly started), you will minimize disk access and maximize performance. Converting the data in the chart to records inserted per second:

Without an explicit transaction: ~75 track records per second.

records per second. With an explicit transaction: ~950 track records per second.

That’s more than a 10x improvement, with only 3 lines of code!

Now that we know using transactions is such a huge advantage, we’ll use them from here on out as we play with other ways to insert data.

db.execSQL()

Another method exposed by SQLiteDatabase that will let us insert data into our tables is db.execSQL(String, Object[]) . It’s provided as a bare-bones way to execute non-selection statements. Here’s our experiment code:

db.beginTransaction();

Object[] values = new Object[1];

for (int i = 0; i < numIterations; i++) {

values[0] = random.nextInt();

db.execSQL("INSERT INTO inserts_1 (val) VALUES (?)", values);

}

db.setTransactionSuccessful();

db.endTransaction();

Comparing insert() and execSQL()

In this experiment, by using db.execSQL() we were able to improve our records per second metric slightly:

db.insert() : ~850 track records per second.

: ~850 records per second. db.execSQL() : ~925 track records per second.

It makes perfect sense if you think about it; db.insert() is essentially syntactic sugar which abstracts away the creation of the SQL statement for you. That abstraction layer, while not super expensive, isn’t free.

Batched Inserts with db.execSQL()

We got some improved performance working with raw statements and db.execSQL() , so I thought now that we’re constructing out our statements ourselves: what if we insert more than one record at a time?

I had originally discounted the idea thinking that because SQLite is an in-process database engine, we wouldn’t necessarily be saving anything by batching inserts (unlike with database servers, where you incur network latency with each statement).

My first attempt at putting together a batched insert: