> On Feb 6, 2017, at 12:07 AM, Hick Gunter <h...@scigames.at> wrote: > > The optimal number of inserts/transaction depends on your hardware setup and > who else needs access to CPU and I/O resources.
Too many transactions can definitely be a problem! It depends on the OS, but the filesystem flush at the end of the commit can cause the hardware disk controller to block for “a long time” (tens or hundreds of ms) while it writes all the blocks from its internal cache to the physical storage medium. This can be bad for real-time threads that are dependent on disk I/O. Ten years ago, back when I worked at Apple and was first using SQLite, I was too eager about running transactions. In some cases there’d be multiple events in a second that triggered a database write in a transaction; when this happened down in my humble process, it could cause iTunes playback to stutter and video capture to lose frames. Fortunately this was caught early on by internal testers, and I tweaked my insertion code to batch up changes into larger more widely-spaced transactions before release. [Disclaimer: Some of this may be not be true anymore. SSDs have different performance characteristics than hard disks, and OS kernels have advanced. But it’s still true that achieving durability is expensive and has trade-offs.] —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
