in reply to
multi threading DBI
Nope ... in this case ... wrong answer. (And this is meant in the most impersonal manner possible, so nobody get their feelings hurt please.) If “SQLite is too slow,” then the problem is going to turn out to be that: “you must use transactions.” Until you do that, SQLite will be dog-slow; after you do that, it’s extremely fast. Blisteringly fast.
Outside of the auspices of a transaction, SQLite physically verifies every disk-write, by flushing to disk, re-reading the block and comparing it. And, nothing gets held in memory. In-memory copies of a block are not re-used in case the underlying data might have changed. This is a fundamental design-aspect of this system. When a transaction is under way, however, SQLite does as much as it can in in-memory buffers, with “lazy writes” and all of the things that you would ordinarily expect. Whether you are reading or writing, always do anything-and-everything within the scope of a transaction, and your performance problems will be solved.
Obviously, I am being a little-bit brash here, but I do in this case speak from very first-hand experience. I had concluded okay, she’s a dog, then discovered just how dramatically(!) wrong I was. (“Even for reading?!” Yes, even for reading. “Will it really make that much difference?” Yes, that much.)