Bulk Insertion on Android device

Normally, each time db.insert() is used, SQLite creates a transaction (and resulting journal file in the filesystem), which slows things down.

If you use db.beginTransaction() and db.endTransaction() SQLite creates only a single journal file on the filesystem and then commits all the inserts at the same time, dramatically speeding things up.

Here is some pseudo code from: Batch insert to SQLite database on Android

try
{
  db.beginTransaction();

  for each record in the list
  {
    do_some_processing();

    if (line represent a valid entry)
    {
      db.insert(SOME_TABLE, null, SOME_VALUE);
    }

    some_other_processing();
  }

  db.setTransactionSuccessful();
}
catch (SQLException e) {}
finally
{
  db.endTransaction();
}

If you wish to abort a transaction due to an unexpected error or something, simply db.endTransaction() without first setting the transaction as successful (db.setTransactionSuccessful()).

Another useful method is to use db.inTransaction() (returns true or false) to determine if you are currently in the middle of a transaction.

Documentation here

I've found that for bulk insertions, the (apparently little-used) DatabaseUtils.InsertHelper class is several times faster than using SQLiteDatabase.insert.

Two other optimizations also helped with my app's performance, though they may not be appropriate in all cases:

• Don't bind values that are empty or null.
• If you can be certain that it's safe to do it, temporarily turning off the database's internal locking can also help performance.

I have a blog post with more details.

This example below will work perfectly

 String sql = "INSERT INTO " + DatabaseHelper.TABLE_PRODUCT_LIST
                + " VALUES (?,?,?,?,?,?,?,?,?);";

        SQLiteDatabase db = this.getWritableDatabase();
        SQLiteStatement statement = db.compileStatement(sql);
        db.beginTransaction();
        for(int idx=0; idx < Produc_List.size(); idx++) {
            statement.clearBindings();
            statement.bindLong(1, Produc_List.get(idx).getProduct_id());
            statement.bindLong(2,  Produc_List.get(idx).getCategory_id());
            statement.bindString(3, Produc_List.get(idx).getName());
//            statement.bindString(4, Produc_List.get(idx).getBrand());
            statement.bindString(5, Produc_List.get(idx).getPrice());
            //statement.bindString(6, Produc_List.get(idx).getDiscPrice());
            statement.bindString(7, Produc_List.get(idx).getImage());
            statement.bindLong(8, Produc_List.get(idx).getLanguage_id());
            statement.bindLong(9, Produc_List.get(idx).getPl_rank());
            statement.execute();

        }
        db.setTransactionSuccessful();
        db.endTransaction();