Implementing bulkInsert for a custom Android ContentProvider

One of our apps implements a custom ContentProvider for managing the data it uses. The insert method gets a writable instance of the database, matches against the Uri, makes sure the appropriate values are included, inserts the entry and returns the Uri of the new entry. This is fine for when the app finds a new entry or two to add, but this seemed a little wasteful for the first update (when a user first subscribes, all of the current existing entries are pulled and added at once). With a little searching I found a related StackOverflow post, where user jcwenger explains it nicely:

However, since each Operation in the Batch can have a different URI and so on, there’s a huge amount of overhead. “Oh, a new operation! I wonder what table it goes in… Here, I’ll insert a single row… Oh, a new operation! I wonder what table it goes in…” ad infinitium. Since most of the work of turning URIs into tables involves lots of string comparisons, it’s obviously very slow.

By contrast, bulkInsert applies a whole pile of values to the same table. It goes, “Bulk insert… find the table, okay, insert! insert! insert! insert! insert!” Much faster.

It will, of course, require your ContentResolver to implement bulkInsert efficiently. Most do, unless you wrote it yourself, in which case it will take a bit of coding.

But wait, requires your ContentResolver to implement bulkInsert efficiently? How does one do that? Sure enough, checking the source code and comments of the abstract ContentProvider class for the bulkInsert method verifies that we have to implement bulkInsert ourselves.

By default, SQLite creates a journal file for each transaction (which is deleted once the transaction completes). For the bulk insert, we should specify a single transaction. A single Uri is passed in, so we should also only check that once. Here is what my bulkInsert logic looks like:

@Override
public int bulkInsert(Uri uri, ContentValues[] allValues) {
	SQLiteDatabase db = mHelper.getWritableDatabase();
	switch (URI_MATCHER.match(uri)) {
	case URI_EVENTS:
		return insertEvents(db, allValues);
	case URI_NEWS_POSTS:
		return insertNewsPosts(db, allValues);
	// ...
	default:
		throw new IllegalArgumentException("Unknown URI " + uri);
	}
}

private int insertEvents(SQLiteDatabase db, ContentValues[] allValues) {
	int rowsAdded = 0;
	int rowId;
	ContentValues values;
	try {
		db.beginTransaction();
		
		for (ContentValues initialValues : allValues) {
			values = initialValues == null ? new ContentValues() : new ContentValues(initialValues);
			rowId = insertEvent(db, values);
			if (rowId > 0)
				rowsAdded++;
		}
		
		db.setTransactionSuccessful();
	} catch (SQLException ex) {
		Log.e(LOG_TAG, "There was a problem with the bulk insert: " + StringUtils.exceptionToString(ex));
	} finally {
		db.endTransaction();
	}
	
	return rowsAdded;
}

private int insertEvent(SQLiteDatabase db, ContentValues values) {
	if (!values.containsKey(TeamCaptainData.EventColumns.ORGANIZER))
		throw new IllegalArgumentException("Missing event column '" + TeamCaptainData.EventColumns.ORGANIZER + "'");
	// ...do some processing (check for more missing fields, set default values, etc.)
	return db.insert(TABLE_EVENTS, null, values);
}

Note that in this particular application, most bulkInsert calls will be outside of the SQLiteOpenHelper callback methods (onCreate, onUpgrade and onOpen); SQLite creates a transaction before invoking these methods, so when working within them it’s unnecessary to add explicit transaction control. Also, only one of the data columns can be empty (and empty is not the norm). A large batch of insertions is also usually only necessary when the user first subscribes to a particular organizer, which is not expected to happen often. If you’re looking at a different scenario or are just curious about possibly optimizing your bulkInsert implementation further, you might want to check out the DatabaseUtils.InsertHelper class. Out Of What Box? has a blog post, Android: Using DatabaseUtils.InsertHelper for faster insertions into SQLite database, that covers this in more detail.

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>