I'd say you should consider switching to some sort of queue feeding a worker pool, then experimenting with pool sizes. Often problems reward the first few threads you add, but at some point additional threads become a negative unless the system is specifically designed for high thread counts (and such design can be annoying for low-thread-count users). There also may be caching interactions which improve with a smaller number of threads.
Something else to try is to have multiple databases which are not sharing page caches (to reduce locking). It is entirely possible that having 4 databases each with 8 threads could be faster than one database with 32 threads, because they each keep out of each other's way, more. [None of the above is really SQLite specific.] -scott On Fri, Mar 3, 2017 at 3:37 PM, Andrew Brown < andrew.br...@economicmodeling.com> wrote: > Well, in the situation I'm working with, my API is constructing 1835 small > SQL jobs to be run, and then passing them off to a structure in which 72 > threads are running, each with their own db connection (I assume that's > what you mean by a database handle, a DB connection, but please, correct me > if I'm wrong!). So in this case, 72 database handles on my bigger server. > > Unfortunately, I'm not running the same queries over and over (one example > is a 400gb database with 3-5 dimension columns and a few data columns, and > this is slicing on that data) so preparing them will have somewhat less > benefit in that sense than in other cases. That said, I can still try > preparing all the statements before I run any. > > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Jens Alfke > Sent: Friday, March 3, 2017 3:25 PM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Massively multithreaded SQLite queries > > > > On Mar 3, 2017, at 2:52 PM, Josh Hunsaker <josh+sql...@nispio.net> > wrote: > > > > Your problem might be mitigated if you could compile your queries in > advance. > > Precompiled statements are a must if you want the best performance (and > you’re running the same queries over and over.) > > Andrew, how many database handles are open? > > —Jens > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > https://na01.safelinks.protection.outlook.com/?url= > http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin% > 2Fmailman%2Flistinfo%2Fsqlite-users&data=01%7C01%7Candrew. > brown%40economicmodeling.com%7C5fba3607e2164acbf45808d4628c85a6% > 7C7cc1677566a34e8b80fd5b1f1db15061%7C0&sdata= > 1xM5isGNHfqUu7yWfoohbYBryDxcgzed0Qlz37K0FDw%3D&reserved=0 > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
