Lessons Learned from Bulk Parsing and Database Insertion

I’m beginning to work on an STO application from an API that was created by Philippe Casgrain.  The data was stored in a pretty big JSON file, and I decided that instead of breaking it into smaller pieces I would simply insert everything into an SQLite database.  Previously the only time I had worked with Android databases had simply been via insertions with ContentValues, which is a lot slower than I had thought at first.

Previously this is what I was doing

void insertData(List objects, SQLiteDatabase db) {
    ContentValues cvs = new ContentValues();
    for(Foo obj : objects){
        cvs.put("FooStringColumn", obj.fooString());
        cvs.put("FooIntegerColumn", obj.fooInteger());
        long result = db.insertWithOnConflict("Foo", null, cvs, SQLiteDatabase.CONFLICT_REPLACE);
    }
}

This works alright, though when you are generating thousands of entries from your JSON data it results in something that’s so slow it’s painful. At first I thought my problems were stemming from using the org.json tools included in Android so I switched over to using Google GSON instead, which did help reduce a ton of unnecessary garbage collection. Though, everything was still slow.

My database knowledge isn’t extremely deep, and I recall being able to compile PreparedStatements to speed up interaction with the DB. After a bit of searching on the internet I came across an alternative way of doing database insertion using this thing known as an InsertHelper. Using an InsertHelper is pretty straightforward, though it requires writing a bit more code than ContentValues, but it’s way faster.

void insertData(List objects, SQLiteDatabase db){
    InsertHelper helper = new InsertHelper(db, "Foo");
    final int fooStrCol = helper.getColumnIndex("FooStringColumn");
    final int fooIntCol = helper.getColumnIndex("FooIntegerColumn");
    for(Foo obj : objects){
        helper.prepareForInsert();

        helper.bind(fooStrCol, obj.fooString());
        helper.bind(fooIntCol, obj.fooInteger());

        helper.execute();
    }
}

To also help speed things up, I extracted all of my data first then inserted everything in a transaction. This helped reduce overhead and I was able to get the database initialized in about 10 seconds. Surely I can speed it up further, but for now this seems to solve my problems.

References

• Android: Using DatabaseUtils.InsertHelper for faster insertions into SQLite database
• Android SQLite database: slow insertion