On Jan 21, 2016, at 9:01 PM, Rowan Worth <rowanw at dugeo.com> wrote: > > On 22 January 2016 at 06:33, Warren Young <wyml at etr-usa.com> wrote: > >> get in, get done, and get out, ASAP. > > To a point I agree, but in reality there's a fixed amount of work involved > with each write transaction. I recently profiled an operation involving > ~75,000 rows that took ~8.5 minutes to complete, and found that 80% of the > time was spent waiting for COMMIT to complete. Rewriting the code so that > all the work happened in a single transaction immediately dropped the > overall time down to ~1.66 minutes.
Yes, it?s a well known fact that batching up many INSERTs and such into a single transaction makes things faster. It?s why sqlite3 .dump wraps its output in a transaction, for example. But implicit in that comment is the assumption from up-thread that we?re using a 10 second timeout, which would result in the BUSY errors the OP is seeing. If the OP has talked himself into large transactions for reasons similar to yours and is unwilling to split them, then his timeout needs to be *at least* as long as the longest possible transaction running time. So, more like 2 minutes in a case like yours, not 10 seconds.
