In my last article I talked about SQL Lite inserts on Android being optimised using a transaction. Another method I’ve found that speeds things up dramatically is to use compiled SQL statements with the SQLiteStatement class.
I can’t believe I hadn’t used these before, I actually think working with a compiled statement is much nicer than working with the standard SQL insert/update methods but there are a couple of points to remember.
- They only work for INSERTS, UPDATES, DELETES or single long or string SELECTS.
- They are not syncronized, so if using multiple threads you must roll your own syncronization.
SQLiteDatabase db = DBHelper.getWritableDatabase(); mInsertAttributeStatement = db.compileStatement("INSERT INTO UserProfileAttributes (UserId, AttributeKey, AttributeValue) VALUES (?,?,?)");
mInsertAttributeStatement.bindLong(1, userId); mInsertAttributeStatement.bindString(2, key); mInsertAttributeStatement.bindString(3, value); mInsertAttributeStatement.execute();