Which part of a DB bulk insert takes the most time?

Question

One of the most common tip with regards to DB writing is to group your inserts and execute them as a single transaction. My question is, which part of the [begin transaction] - insert - [commit transaction] is taking the bulk of the processing time?

From what I've read the insert statements are first saved in the cache then executed all at once when the commit transaction is executed. If I understand that correctly, wouldn't the commit transaction part take the most time?

The reason I'm asking is because we have this code where we try to limit the size of inserts within a single transaction with the aim to minimize the whole processing time to around 200ms.

The way were doing it is we're constantly checking the time if it has exceeded 200ms whenever we do an insert. Once 200ms has been reached (or all insert statements have been performed) then we execute the commit transaction. But if what I mentioned above is true, wouldn't our code still be prone to significantly exceeding 200ms execution time?

Edit: using SQLite3, btw.

Answer 1

The database page cache is written to disk during the commit. But even that is fast because the OS writes to its own cache. What takes the most time is, at the end of the commit, waiting for the data to be actually written to disk (FlushFileBuffers()).

If you want to minimize the overall time needed for a transaction, you have to limit the amount of data.

Alternatively, consider using asynchronous writes by enabling WAL mode.