Hello Sebastian, Is this total time or time just of the DB access? I'm wondering how much of this is just "opening the connection" overhead time versus query time. Assuming the overhead of creating 100 threads is the same.
I'm be interested in knowing how long it takes assuming you don't start timing it till after all 100 threads have opened the connections to the file. Wonder if running this same test 100 times in a row for each mode, leaving the connections open in between, would show the timing's converging? Basically reducing the affect of the startup overhead. C Thursday, September 20, 2012, 9:46:07 AM, you wrote: SK> I tested with a database containing one table with 50,000 entries. SK> I then ran "SELECT *" on this table from 100 concurrent threads SK> where each thread randomly selected 20,000 table entries. SK> The results are: SK> * using a single connection for all threads: 11 seconds SK> * using one connection per thread: 59,3 seconds SK> 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 >> >> SK> _______________________________________________ SK> sqlite-users mailing list SK> sqlite-users@sqlite.org SK> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Teg mailto:t...@djii.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
