Wow...almost 6X difference....would you care to share your test code? I would imagine 50 threads would be MORE than 2X faster if caching is the cause.
So if you run a test and time 10,20,30..100 threads what kind of curve is seen? Then do the same for single connection. The ratio of those entries would be quite enlightening. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Sebastian Krysmanski [sql...@lists.manski.net] Sent: Thursday, September 20, 2012 8:46 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Store error messages in thread local memory 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
