SQLite batch commit vs commit in shot for huge bulk inserts

Question

I need to dump huge (~ 10-40 million rows) huge data set into a SQLite database. Is there an advantage of doing a commit for every n number of inserts (n could 50,000, 100,000, etc) vs. doing a commit only after whole 40 millions rows got inserted.

Obviously, in theory a single commit will be fastest way to do it. But is there an advantage of doing commit by batches? In my case it is either all data got inserted or non gets inserted. Is there any danger of doing extremely large amount of inserts in SQLite before doing a commit (i.e. Do I need to have bigger diskspace for sqlite as it needs to use bigger temp files?)?

I'm using Perl DBI to insert the data.

Yes, there is very big advantage. Let's assume that commit takes 0.01s. 40 000 000 * 0.01 = 40 000s => about 11 hours. Do it every 100 000: 40 000 000l / 100 000 * 0.01 = You do the math :) Commit it is like put a stamp. If You have 1 record pending or 100 000 - it will took same amount of time. Rollback on the other hand.. — q4za4
Are you computing the records before you insert them, or does this data come from some file? — simbabque

Pradeep Pradeep · Accepted Answer · 2019-03-04T14:01:13

I have had some performance improvements by using the following things:

set PRAGMA synchronous = OFF this prevents SQLite engine from waiting for OS-level write to complete.

set PRAGMA journal_mode = MEMORY this tells the SQLite engine to store the journal in RAM instead of disk, only drawback is that the database can't be recovered in case of a OS crash or power failure.

next, create indexes after all inserts. Also, you may issue a commit after every 100,000 records.

SQLite batch commit vs commit in shot for huge bulk inserts

1 Answers