How to use Dao.setAutoCommit()?

Question

I'm parsing JSON from a server, using ORMLite to persist objects to the database as I read them out. The files could get very large, so my thought is to commit objects one at a time as they are read, instead of reading hundreds of objects into memory and doing a mass commit (memory being scarce on smartphones, after all). However, each object will contain a collection with multiple values (I am attempting to use ForeignCollection for those values, so each value needs to be treated as a separate object) and think it would be better to commit all the items in an object's collection at once, rather than committing, say, each individual String or Integer.

I believe that to make a group of commits in a batch, I would simply call dao.setAutoCommit(false), call dao.create() for each item, call dao.commit() afterwards and then call dao.setAutoCommit(true) to go back to piecemeal commits.

I have three questions:

1. Is it right to commit as I go, or should I to a batch commit--even if it means up to a thousand objects would get committed at once?
2. Is it better to commit the collection of items one at a time, or in a batch?
3. Where do I get the databaseConnection value from that dao.setAutoCommit() and dao.commit() require? Maybe I missed it, but I can't find it in the documentation or examples.

Answer 1

Is it right to commit as I go, or should I to a batch commit--even if it means up to a thousand objects would get committed at once?

If you have a large number of items it may be better commit a couple of 100 objects at once instead of 1000s. It depends a lot on the size of the objects so running it with various different batch sizes and timing them would be a good thing to try. As you mention, you need to balance memory and database performance. I'd be curious of the results so please comment with your timing data.

Is it better to commit the collection of items one at a time, or in a batch?

It is certainly better to do things in a batch. This is true of just about every database I've ever worked on.

If you are talking about a foreign collection then there is really no difference between a foreign collection of objects and a large list of objects that you want to create. Both will benefit from batching.

I would not commit after each outer object was created. I'd rather batch up 100 (let's say) outer objects and whatever associated foreign objects they have. Then commit the changes for the 100 and do the next 100. Then change the 100 to 200 and then try 50, etc.. You should see inflection points.

Did I answer this one right?

Where do I get the databaseConnection value from that dao.setAutoCommit() and dao.commit() require? Maybe I missed it, but I can't find it in the documentation or examples.

Edit #2:

As @Chad pointed out, I was wrong to recommend using auto-commit under Android. Although Sqlite drivers usually support auto-commit by starting a transaction and committing it when done. Android does not seem to support this however. In the Android database connection, the code is a no-op.

Under Android, the proper way to do batch tasks with ORMLite is to use the dao.callBatchTasks(Callable) method instead. Something like:

accountDao.callBatchTasks(connectionSource,
  new Callable<Void>() {
    public Void call() throws SQLException {
        // insert a number of accounts at once
        for (Account account : accountsToInsert) {
           // update our account object
           accountDao.create(account);
        }
        return null;
    }
});