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.