in reply to Sqlite: Threads and inserts into a different database for each one. (1 thread fast, >1 slow)
It clearly appears to me that each thread, and without using transactions, is opening a database-file at random, doing two inserts into it, and then closing it again. This, emphatically, is not the right way to do this with the SQLite system. There are two key changes that you should make here:
- You should have one thread for each database that you wish to insert into, and you should queue the work that is destinated for a given database to a queue that corresponds to that thread and to that database.
- The work must be placed inside of a transaction, lest SQLite physically-verify every single disk write that occurs.
But, first, step back and seriously reconsider your whole design. You have very seriously over-engineered the whole thing. Why not have a single process that takes a database-name as a command-line parameter, then greps through the input file considering only lines-of-interest, and inserting those lines into the one database that it is then working with, doing it all in reasonably-sized transactions. Run this one process in parallel with other copies of itself, or one at a time.
One bit of overhead that you want to eliminate, however you choose to do it, is the constant opening and closing of files. The other piece of overhead that you must eliminate, through the use of transactions, is the physical verification of every disk-write that occurs, which is SQLite’s by-design default behavior.