On 22 January 2016 at 06:33, Warren Young <wyml at etr-usa.com> wrote:
> With SQLite?s lack of row-level locking, your usage pattern should distill > to ?get in, get done, and get out, ASAP.? Many fine-grained queries are > better than heroic multi-statement queries that change the world. > 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. This was on standard desktop hardware running CentOS, with no other connections open against the database (located on a local ext4 filesystem). The tradeoff of course is that no one will be able to write to the database for the duration of this transaction, but in this case the ~7 minute saving is certainly worth it. -Rowan
