I tested with a database containing one table with 50,000 entries. I then ran "SELECT *" on this table from 100 concurrent threads where each thread randomly selected 20,000 table entries.
The results are: * using a single connection for all threads: 11 seconds * using one connection per thread: 59,3 seconds On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote: > You don't say how much speed difference you see.... > > But a separate connection will have separate caches. So you could just be > seeing a difference in caching behavior. > > One connection uses one cache so will be in L1/L2/L3 cache more often than > multiple threads thrashing the cache. > > http://www.tomshardware.com/forum/266376-28-intel-cache > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Advanced GEOINT Solutions Operating Unit > Northrop Grumman Information Systems > > > > > > > > > > > > > > > > On Thursday, 20. September 2012 at 15:23, Richard Hipp wrote: > > > > > > > On Thu, Sep 20, 2012 at 9:12 AM, Sebastian Krysmanski < > > > > sql...@lists.manski.net (mailto:sql...@lists.manski.net)> wrote: > > > > > > > > > Hi, > > > > > > > > > > I'm trying to use SQLite in a multi-threaded application. I've done > > > some > > > > > tests and it seems that using the same connection on multiple threads > > > > > > > > > > > > > is > > > > > faster than having one connection per thread. > > > > > > > > > > However, http://www.sqlite.org/c3ref/errcode.html states: > > > > > > > > > > "When the serialized threading mode ( > > > http://www.sqlite.org/threadsafe.html) > > > > > is in use, it might be the case that a second error occurs on a > > > > > > > > > > > > > separate > > > > > thread in between the time of the first error and the call to these > > > > > interfaces. When that happens, the second error will be reported since > > > > > these interfaces always report the most recent result." > > > > > > > > > > So, this is a problem in my application (and I definitely need multi > > > > > threading). > > > > > > > > > > Obtaining an exclusive lock for the database connection, as suggested > > > in > > > > > the documentation, is not an option for me because even read only > > > > > statements (SELECT) can potentially return an error. And obtaining an > > > > > exclusive lock for a read statement eliminates all concurrency there > > > > > > > > > > > > > > > > > > is in > > > > > SQLite. > > > > > > > > > > > > > > > > > > > > Every operation on an SQLite database connection operates under an > > > > exclusive mutex on that database connection, so you don't have any > > > > concurrency anyhow. > > > > > > > > > > > > > > > > > > So the only solution I can come up with is to make "sqlite3_errmsg()" > > > (and > > > > > related functions) use thread local memory. > > > > > > > > > > Is there (or has there ever been made) any attempt on storing the > > > > > error > > > > > message in thread local memory? (I'm a C# and Java developer, so I'm > > > > > > > > > > > > > > > > > > not > > > > > sure whether thread local memory even exists in C. It does in C# and > > > > > > > > > > > > > Java > > > > > though.) > > > > > > > > > > > > > > > > > > > > Thread local storage has been available to C code since long before Java > > > > and C# were even invented. But it is accessed through library routines > > > > that are not cross-platform, so we are not interested in using it in > > > > SQLite. Furthermore, making such a change would break backwards > > > > compatibility, which is a huge no-no with SQLite. > > > > > > > > Best regards > > > > > Sebastian > > > > > > > > > > _______________________________________________ > > > > > sqlite-users mailing list > > > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > D. Richard Hipp > > > > d...@sqlite.org (mailto:d...@sqlite.org) > > > > _______________________________________________ > > > > sqlite-users mailing list > > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > > > > > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > > > -- > > D. Richard Hipp > > d...@sqlite.org (mailto:d...@sqlite.org) > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
