Clarification of Java/SQLite batch and auto-commit

I copied the following example from an SQLite Java library website:

PreparedStatement prep =
         conn.prepareStatement("insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);

I’m struggling to understand the significance of toggling autoCommit() either side of executeBatch(). Does it merely prevent a commit being made for each individual batch operation? Thus a single ‘bulk’ commit is will be made by setAutoCommit(true).

Answer

The auto commit is disabled before batch because enabling auto commit will commit (i.e. wait for sync to happen which means it will wait the data is actually written to persistent storage like hard disk) after every row that is inserted.

If auto commit is false, it will not wait for sync.

The difference in waiting for sync and not waiting is the guaranty that whether data is actually to hard disk or it is in the buffer (that could be buffered IO or buffer of hard disk).

In short, disabling auto commit gives you performance boost. And I think by default auto commit is enabled.

Another way of optimization

If you want to have auto commit ON and still need performance boost just try to start as transaction before the batch operation and commit the transaction after. This way sqlite wont auto commit after every insert and it will give good performance boost.

EDIT:

When you starting a transaction you are only disabling auto commit for that transaction and it will be again ‘on’ once transaction is over. What auto commit helps is when you are inserting/updating rows separately (not as batch), then you dont have to start a transaction explicitly for every insert/update. And regarding setting auto-commit to true, after the fact, does not do call for commit. If you make auto-commit true and whatever you have already inserted/updated wont have any effect and won’t have same guaranties as auto-commit true prior to making those insert/update.

Here’s some information about speeding up Sqlite INSERTs.