Android SQLite database: slow insertion

You should do batch inserts.

Pseudocode:

db.beginTransaction();for (entry : listOfEntries) {    db.insert(entry);}db.setTransactionSuccessful();db.endTransaction();

That increased the speed of inserts in my apps extremely.

Update:
@Yuku provided a very interesting blog post: Android using inserthelper for faster insertions into sqlite database

Since the InsertHelper mentioned by Yuku and Brett is deprecated now (API level 17), it seems the right alternative recommended by Google is using SQLiteStatement.

I used the database insert method like this:

database.insert(table, null, values);

After I also experienced some serious performance issues, the following code speeded my 500 inserts up from 14.5 sec to only 270 ms, amazing!

Here is how I used SQLiteStatement:

private void insertTestData() {    String sql = "insert into producttable (name, description, price, stock_available) values (?, ?, ?, ?);";    dbHandler.getWritableDatabase();    database.beginTransaction();    SQLiteStatement stmt = database.compileStatement(sql);    for (int i = 0; i < NUMBER_OF_ROWS; i++) {        //generate some values        stmt.bindString(1, randomName);        stmt.bindString(2, randomDescription);        stmt.bindDouble(3, randomPrice);        stmt.bindLong(4, randomNumber);        long entryID = stmt.executeInsert();        stmt.clearBindings();    }    database.setTransactionSuccessful();    database.endTransaction();    dbHandler.close();}

Compiling the sql insert statement helps speed things up. It can also require more effort to shore everything up and prevent possible injection since it's now all on your shoulders.

Another approach which can also speed things up is the under-documented android.database.DatabaseUtils.InsertHelper class. My understanding is that it actually wraps compiled insert statements. Going from non-compiled transacted inserts to compiled transacted inserts was about a 3x gain in speed (2ms per insert to .6ms per insert) for my large (200K+ entries) but simple SQLite inserts.

Sample code:

SQLiteDatabse db = getWriteableDatabase();//use the db you would normally use for db.insert, and the "table_name"//is the same one you would use in db.insert()InsertHelper iHelp = new InsertHelper(db, "table_name");//Get the indices you need to bind data to//Similar to Cursor.getColumnIndex("col_name");                 int first_index = iHelp.getColumnIndex("first");int last_index = iHelp.getColumnIndex("last");try{   db.beginTransaction();   for(int i=0 ; i<num_things ; ++i)   {       //need to tell the helper you are inserting (rather than replacing)       iHelp.prepareForInsert();       //do the equivalent of ContentValues.put("field","value") here       iHelp.bind(first_index, thing_1);       iHelp.bind(last_index, thing_2);       //the db.insert() equilvalent       iHelp.execute();   }   db.setTransactionSuccessful();}finally{    db.endTransaction();}db.close();