ORMLite's createOrUpdate seems slow - what is

Question

Calling the ORMLite RuntimeExceptionDao's createOrUpdate(...) method in my app is very slow.

I have a very simple object (Item) with a 2 ints (one is the generatedId), a String and a double. I test the time it takes (roughly) to update the object in the database (a 100 times) with the code below. The log statement logs:

time to update 1 row 100 times: 3069

Why does it take 3 seconds to update an object 100 times, in a table with only 1 row. Is this the normal ORMLite speed? If not, what might be the problem?

RuntimeExceptionDao<Item, Integer> dao =
    DatabaseManager.getInstance().getHelper().getReadingStateDao();
Item item = new Item();
long start = System.currentTimeMillis();
for (int i = 0; i < 100; i++) {
    item.setViewMode(i);
    dao.createOrUpdate(item);
}
long update = System.currentTimeMillis();
Log.v(TAG, "time to update 1 row 100 times: " + (update - start));

If I create 100 new rows then the speed is even slower.

Note: I am already using ormlite_config.txt. It logs "Loaded configuration for class ...Item" so this is not the problem.

Thanks.

Answer 1

This may be the "expected" speed unfortunately. Make sure you are using ORMLite version 4.39 or higher. createOrUpdate(...) was using a more expensive method to test for existing of the object in the database beforehand. But I suspect this is going to be a minimal speed improvement.

If I create 100 new rows then the speed is even slower.

By default Sqlite is in auto-commit mode. One thing to try is to wrap your inserts (or your createOrUpdates) using the the ORMLite Dao.callBatchTasks(...) method.

In by BulkInsertsTest android unit test, the following doInserts(...) method inserts 1000 items. When I just call it:

doInserts(dao);

It takes 7.3 seconds in my emulator. If I call using the callBatchTasks(...) method which wraps a transactions around the call in Android Sqlite:

dao.callBatchTasks(new Callable<Void>() {
    public Void call() throws Exception {
        doInserts(dao);
        return null;
    }
});

It takes 1.6 seconds. The same performance can be had by using the dao.setSavePoint(...) method. This starts a transaction but is not as good as the callBachTasks(...) method because you have to make sure you close your own transaction:

DatabaseConnection conn = dao.startThreadConnection();
Savepoint savePoint = null;
try {
    savePoint = conn.setSavePoint(null);
    doInserts(dao);
} finally {
    // commit at the end
    conn.commit(savePoint);
    dao.endThreadConnection(conn);
}

This also takes ~1.7 seconds.