[ACCEPTED]-Optimizing batch inserts, SQLite-sqlite

Accepted answer
Score: 13

I'm a bit hazy on the Java API but I think 16 you should start a transaction first, otherwise 15 calling commit() is pointless. Do it with conn.setAutoCommit(false). Otherwise 14 SQLite will be journaling for each individual 13 insert / update. Which requires syncing 12 the file, which will contribute towards 11 slowness.

EDIT: The questioner updated to 10 say that this is already set true. In that 9 case:

That is a lot of data. That length 8 of time doesn't sound out of this world. The 7 best you can do is to do tests with different 6 buffer sizes. There is a balance between 5 buffer jitter from them being too small 4 and virtual memory kicking in for large 3 sizes. For this reason, you shouldn't try 2 to put it all into one buffer at once. Split 1 up the inserts into your own batches.

Score: 2

You are only executing executeBatchonce, which means 7 that all 10 million statements are send 6 to the database in the executeBatch call. This is way 5 too much to handle for a database. You should 4 additionally execute int[] updateCounts = prep.executeBatch(); in your loop let's 3 say all 1000 rows. Just make an if statement 2 which tests on counter % 1000 == 0. Then the database can asynchronously 1 already work on the data you sent.

More Related questions