How to insert bulk data in android sqlite database using ormlite efficiently

Question

I'm trying to insert 100000 records in android sqlite database at a time. I'm using following two different methods.

 private void bulkInsertDataBySavePoint(final List<User> users) {
    log.debug("bulkInsertDataBySavePoint()");
    DatabaseConnection conn = null;
    Savepoint savepoint = null;
    try {
        conn = userDao.startThreadConnection();
        savepoint = conn.setSavePoint("bulk_insert");
        for (User user : users) {
            userDao.create(user);
        }
    } catch (SQLException e) {
        log.error("Something went wrong in bulk Insert", e);
    } finally {
        if (conn != null) {
            try {
                conn.commit(savepoint);
                userDao.endThreadConnection(conn);
            } catch (SQLException e) {
                log.error("Something went wrong in bulk Insert", e);
            }
        }
    }
}

And

   private void bulkInsertDataByCallBatchTasks(final List<User> users) {
    log.debug("bulkInsertDataByCallBatchTasks()");
    try {
        userDao.callBatchTasks(new Callable<Void>() {
            @Override
            public Void call() throws Exception {
                for (User user : users) {
                    userDao.create(user);
                }
                return null;
            }
        });
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Both methods work fine. On average they take 140 seconds and take 60-65% CPU which is not ok, I think.

The idea is, I have to consume an api which will provide json data. I have to parse that json data and then insert into sqlite database for offline usage.

I'm looking for an efficient way to solve this issue.

Any thought?

Answer 1

I'm trying to insert 100000 records in android sqlite database at a time... On average they take 140 seconds and take 60-65% CPU which is not ok in my opinion.

Unfortunately I don't have an easy answer for you. You may have to do this sort of insert directly using raw SQL to achieve faster performance on the limited Android CPU. Once you have the data inserted then you can turn to ORMLite to query or manipulate the data faster.

Answer 2

I've had the same problem, and found a reasonable workaround. This took insert time from 2 seconds to 150ms:

final OrmLiteSqliteOpenHelper myDbHelper = ...;
final SQLiteDatabase db = myDbHelper.getWritableDatabase();
db.beginTransaction();
try{
    // do ormlite stuff as usual, no callBatchTasks() needed

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

Answer 3

Hrm. Good idea @FarrukhNajmi. I've just added it to trunk. It will be in version 4.49.

@Gray Is it still unstable? when can we see it in maven?

And if com.j256.ormlite.dao.ForeignCollection#addAll make only one request it would be nice too.