I've compiled some test results that are quite interesting. You can find the details here:
http://pastebin.com/yGhDepr6 (I forgot to change the locale for the "Elapsed" lines, so "," means ".") In all test cases 2,000,000 select operations are executed (evenly distributed among the threads). With two threads, using only one connection (87.8 s) is actually slower than using two connections (66.7 s). The performance of using only one connection drastically increased until a thread count somewhere between 10 and 20 where it settles at about 11 seconds. Using one connection per thread reduces the elapsed time only from 66 to 55 seconds. I probably should note a couple of things about these tests: * I'm using a .NET wrapper (P/Invoke) that I've written myself (https://bitbucket.org/mayastudios/sqlite.net ). So, it may not be bug free (and thus may influence the results). * I'm running Windows 7 x64 inside a virtual machine (VMWare) so elapsed times may be higher than usual. * The virtual machine has 2 CPU cores assigned. When using only one connection, only one core seems to be used. When using one connection per thread, both cores are used. * Each thread gets its own compiled/prepared statement. I've posted the test code here: http://pastebin.com/RHXWfrdx Again, it's C# but should be understandable anyway. - Sebastian On Thursday, 20. September 2012 at 15:52, Black, Michael (IS) wrote: > 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 > (mailto:sqlite-users-boun...@sqlite.org) [sqlite-users-boun...@sqlite.org > (mailto:sqlite-users-boun...@sqlite.org)] on behalf of Sebastian Krysmanski > [sql...@lists.manski.net (mailto: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 (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
