9

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.

Gray
  • 115,027
  • 24
  • 293
  • 354
Frank
  • 12,010
  • 8
  • 61
  • 78

1 Answers1

24

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.

Gray
  • 115,027
  • 24
  • 293
  • 354
  • Thanks! The callBatchTasks method makes it a lot faster, it makes the speed alright, if not perfect. 300 ms will result in a small hickup when the user is scrolling (I'm guessing). The only solution to that is moving it into a seperate thread? – Frank Aug 01 '12 at 15:22
  • Moving to a separate thread, if you can indeed do it in the background, will certainly not block the UI. You might try it @Frank. – Gray Aug 01 '12 at 15:25
  • 1
    On a sidenote to this, the [documentation](http://ormlite.com/javadoc/ormlite-core/doc-files/ormlite_5.html#index-callBatchTasks) shows an invalid (old?) syntax in the example for `callBatchTasks` (first parameter is ConnectionSource). – Czechnology Jun 28 '15 at 10:21