How to speed up large data insertion in Database (SQLite) in Android application

Question

I have a situation where I have to insert data in the database, and the data is very large which is in an ArrayList. The data is inserted in a loop like this:

for( ..... )
ContentValues cv = new ContentValues();

// cv.put statements

sqLiteDB.insert ......

Now it takes around more than 1 minute in the test case and is expected to exceed 10 minutes with real data, while the target is to keep the time below 30 seconds. Data cannot be reduced. My questions are:

1. How to improve the speed
2. Is there a way to avoid repeated calls to sqLiteDB.insert function and do the insertion of all the rows in one batch?

Answer 1

One of the possible speed improvement in your case is to have a code like this:

ContentValues cv = new ContentValues();
for(....) {
    //cv put
    sqLiteDB.insert
    cv.clear();
}

Moreover, here is a blog post how to improve insertion speed.

EDIT I also checked SO and here is a similar question. It's claimed that the use of transactions improves the speed.

Answer 2

I haven't try this for sqlite but done something similiar in mssql. most cases open/close transaction takes more time than insert. so you can do all in one transaction.. worth to try.

db.beginTransaction();
try {
    SQLiteStatement insert = null;
    insert = db.compileStatement("INSERT OR REPLACE INTO \"MyTable\" ("
            + "\"MyColumnName\") VALUES (?)");

    for (i = 0; i++; i < 10000)
    {
        insert.bindLong(1, i);
        insert.execute();
        insert.clearBindings();
    }

    db.setTransactionSuccessful();
}
catch (Exception e) {
    String errMsg = (e.getMessage() == null) ? "bulkInsert failed" : e.getMessage();
    Log.e("bulkInsert:", errMsg);
}
finally {
    db.endTransaction();
}