This article originates from christophersaunders.ca
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
