[sqlite] Syntax control
hi all, could you check me the syntax of this statment? UPDATE batchs SET batchs.department_id = (SELECT products.department_id FROM products WHERE products.product_id = batchs.product_id) WHERE EXISTS ( SELECT * FROM products WHERE products.product_id = batchs.product_id ) I would update the field named department_id on batchs table with the values of the same field on products table. the statement return near ".": syntax error but I don't see it. Sql seems that don't digest the call to batchs.department_id regards beppe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select ... where problem
Message: 25 Date: Thu, 20 Sep 2012 07:44:00 -0600 From: "Keith Medcalf" To: "General Discussion of SQLite Database" Subject: Re: [sqlite] select ... where problem Message-ID: Content-Type: text/plain; charset="utf-8" >>> >>> (select CURDATE()), >>> What is a CURDATE()? Is that a custom scalar function of your own? Did >>> you remember to compile it into the engine or load the module containing >>> the function? >>> >>> where datetime('now', 'localtime') > 15:00 >>> 15:00 is unparseable. Perhaps you mean >>> >>> where datetime('now', 'localtime') > '15:00' >>> But if so you can elimate the where clause entirely because '2' is always >>> greter than '1' ... >>> I am surprised the "Error Message" did not point out these errors. --- I means the sql cmd had no "from ", only "select", "where": insert into history (mdwkup,macdup,mdate,mdwkmd) SELECT (select count(*) FROM fxj where mdwkup=1), (select CURDATE()), (select round(avg(mdwkmd),2) from fxj) where datetime('now', 'localtime')>"15:00"; if run this cmd: insert into history (mdwkmd) select round(avg(mdwkmd),2) from fxj where datetime('now', 'localtime')>15:00 will successful. ! Sincerely, Cordialement / Best Regards / Mit freundlichen Grüßen / Cordiali saluti ! ___ --- YE YANHONG - DASI/DRDS/ITVC/APFS/AVPM PSA PEUGEOT CITROEN(CHINA) AUTOMOTIVE TRADE CO,. LTD SHANGHAI BRANCH Mobile : (+86) 138 1680 8338 Office : (+8621) 2419 5488 PSA internal call: 49 5488 Email: yanhong...@mpsa.com Address: 5th – 8th floor, A1 Bldg., 1528 Gumei Road, Xuhui District, Shanghai ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Store error messages in thread local memory
> 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. This is what one would expect. > * 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. This is also to be expected. Because thread access to the sqlite engine is serialized by a mutex, the OS ought to set thread affinity for all operations running through the same mutex to the same CPU. If you repeated the test using two connections only, and spreading your threads amongst connections, you ought to be able to get about 5 or 6 seconds with 10 to 20 threads per connection. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use of different System.Data.SQLite packages
Bernd wrote: > > Could you elaborate on why I should avoid doing as I do at the moment? > The mixed-mode assembly contains native code for one platform as well as the managed code. This prevents the same application from being easily deployed on both x86 and x64. I suppose if you only ever deal with one processor architecture on all the target machines, it's just fine to use the bundled packages. Also, mixed-mode assemblies are not supported by Mono or the .NET Compact Framework. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Use of different System.Data.SQLite packages
I just found the system.data.sqlite downloads page to have some rather new comments about which package to use in which case. I was a bit surprised to read this: All the "bundle" packages contain the "System.Data.SQLite.dll" mixed-mode assembly. These packages should only be used in cases where the assembly binary must be deployed to the Global Assembly Cache for some reason (e.g. to support some legacy application on customer machines). .. and later: Using the Global Assembly Cache (at all) is not recommended; therefore, the "bundle" packages should be avoided unless deemed absolutely necessary. Using the "static" assembly binaries on development machines is not recommended; therefore, the "static" packages should be avoided if all the target machines already have the Visual C++ runtime installed or it can be easily deployed beforehand. I don't use the GAC as well, but nonetheless usually deliver the statically linked mixed mode assembly that fits the user's platform together with my own assemblies. Could you elaborate on why I should avoid doing as I do at the moment? Thanks, Bernd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Store error messages in thread local memory
Well, I did some profiling and here are the results, if you're interested. Again, 2,000,000 SELECT queries. This time I used only shared connections (i.e. all threads share the same connection). With 2 threads: Overall hot path: http://imgur.com/DHXQj In thread: http://imgur.com/KX8KY With 20 threads: Overall hot path: http://imgur.com/s28Hs In thread: http://imgur.com/8NBB6 On Thursday, 20. September 2012 at 17:39, Black, Michael (IS) wrote: > What's your threading mode? > http://www.sqlite.org/threadsafe.html > > > 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 10:25 AM > To: sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > Subject: EXT :Re: [sqlite] Store error messages in thread local memory > > It's the whole process including creating threads, opening database > connections and waiting for the threads to finish. However, startup time is > negligible (as expected). Here are some results where opening and closing of > connections as well as compiling statements is excluded from the elapsed time: > > -- > SELECT_COUNT: 1,000,000 > THREAD_COUNT: 2 > Testing with one connections (ReadWrite) and filled table... > Elapsed: 91.0 s > Testing with one connections (ReadWrite) and filled table... > Elapsed: 66.3 s > > > -- > SELECT_COUNT: 133,333 > THREAD_COUNT: 15 > Testing with one connections (ReadWrite) and filled table... > Elapsed: 11.6 s > Testing with one connections (ReadWrite) and filled table... > Elapsed: 51.6 s > > > -- > SELECT_COUNT: 20,000 > THREAD_COUNT: 100 > Testing with one connections (ReadWrite) and filled table... > Elapsed: 11.5 s > Testing with one connections (ReadWrite) and filled table... > Elapsed: 55.9 s > > > On Thursday, 20. September 2012 at 16:22, Teg wrote: > > > 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: > > > > > > >
Re: [sqlite] Store error messages in thread local memory
Serialized On Thursday, 20. September 2012 at 17:39, Black, Michael (IS) wrote: > What's your threading mode? > http://www.sqlite.org/threadsafe.html > > > 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 10:25 AM > To: sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > Subject: EXT :Re: [sqlite] Store error messages in thread local memory > > It's the whole process including creating threads, opening database > connections and waiting for the threads to finish. However, startup time is > negligible (as expected). Here are some results where opening and closing of > connections as well as compiling statements is excluded from the elapsed time: > > -- > SELECT_COUNT: 1,000,000 > THREAD_COUNT: 2 > Testing with one connections (ReadWrite) and filled table... > Elapsed: 91.0 s > Testing with one connections (ReadWrite) and filled table... > Elapsed: 66.3 s > > > -- > SELECT_COUNT: 133,333 > THREAD_COUNT: 15 > Testing with one connections (ReadWrite) and filled table... > Elapsed: 11.6 s > Testing with one connections (ReadWrite) and filled table... > Elapsed: 51.6 s > > > -- > SELECT_COUNT: 20,000 > THREAD_COUNT: 100 > Testing with one connections (ReadWrite) and filled table... > Elapsed: 11.5 s > Testing with one connections (ReadWrite) and filled table... > Elapsed: 55.9 s > > > On Thursday, 20. September 2012 at 16:22, Teg wrote: > > > 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 > > > > > > >
Re: [sqlite] Store error messages in thread local memory
What's your threading mode? http://www.sqlite.org/threadsafe.html 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 10:25 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Store error messages in thread local memory It's the whole process including creating threads, opening database connections and waiting for the threads to finish. However, startup time is negligible (as expected). Here are some results where opening and closing of connections as well as compiling statements is excluded from the elapsed time: -- SELECT_COUNT: 1,000,000 THREAD_COUNT: 2 Testing with one connections (ReadWrite) and filled table... Elapsed: 91.0 s Testing with one connections (ReadWrite) and filled table... Elapsed: 66.3 s -- SELECT_COUNT: 133,333 THREAD_COUNT: 15 Testing with one connections (ReadWrite) and filled table... Elapsed: 11.6 s Testing with one connections (ReadWrite) and filled table... Elapsed: 51.6 s -- SELECT_COUNT: 20,000 THREAD_COUNT: 100 Testing with one connections (ReadWrite) and filled table... Elapsed: 11.5 s Testing with one connections (ReadWrite) and filled table... Elapsed: 55.9 s On Thursday, 20. September 2012 at 16:22, Teg wrote: > 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 r
Re: [sqlite] Store error messages in thread local memory
It's the whole process including creating threads, opening database connections and waiting for the threads to finish. However, startup time is negligible (as expected). Here are some results where opening and closing of connections as well as compiling statements is excluded from the elapsed time: -- SELECT_COUNT: 1,000,000 THREAD_COUNT: 2 Testing with one connections (ReadWrite) and filled table... Elapsed: 91.0 s Testing with one connections (ReadWrite) and filled table... Elapsed: 66.3 s -- SELECT_COUNT: 133,333 THREAD_COUNT: 15 Testing with one connections (ReadWrite) and filled table... Elapsed: 11.6 s Testing with one connections (ReadWrite) and filled table... Elapsed: 51.6 s -- SELECT_COUNT: 20,000 THREAD_COUNT: 100 Testing with one connections (ReadWrite) and filled table... Elapsed: 11.5 s Testing with one connections (ReadWrite) and filled table... Elapsed: 55.9 s On Thursday, 20. September 2012 at 16:22, Teg wrote: > 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
Re: [sqlite] Store error messages in thread local memory
Well, I thought that WAL and the old locking mechanisms allowed for concurrent read access. (That's why I thought this would be true for a shared connection as well - which apparently isn't true.) On Thursday, 20. September 2012 at 15:55, Keith Medcalf wrote: > > Two separate database connection can read concurrently. But you can only > > do one thing at a time with a single database connection. > > > > > You could prepare a select statement, one on each thread, and step them each > in their own thread, against the same database connection, even though only > one thread would be 'stepping' at a time, in much the same way that you can > have fifty programs writing to disk 'concurrently' (yet I/O is serialized so > only one write operation proceeds at a time). > > I think it all depends on how you define "concurrency" ... the database > connection is serialized so no operations occur concurrently, however, you > can execute separate operations against the same connection in separate > threads and all will "make progress" in an interleaved (serialized) fashion > within the constraints of the OS scheduler. > > --- > () ascii ribbon campaign against html e-mail > /\ www.asciiribbon.org (http://www.asciiribbon.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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Store error messages in thread local memory
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 differencewould 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
Re: [sqlite] Store error messages in thread local memory
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
Re: [sqlite] Store error messages in thread local memory
> Two separate database connection can read concurrently. But you can only > do one thing at a time with a single database connection. You could prepare a select statement, one on each thread, and step them each in their own thread, against the same database connection, even though only one thread would be 'stepping' at a time, in much the same way that you can have fifty programs writing to disk 'concurrently' (yet I/O is serialized so only one write operation proceeds at a time). I think it all depends on how you define "concurrency" ... the database connection is serialized so no operations occur concurrently, however, you can execute separate operations against the same connection in separate threads and all will "make progress" in an interleaved (serialized) fashion within the constraints of the OS scheduler. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Store error messages in thread local memory
Wow...almost 6X differencewould 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:808
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.or
Re: [sqlite] Store error messages in thread local memory
I should add that one reason I mention that is that as your database grows the speed difference to to caching disappears. All has to do with the probability of hitting the caches decreases as the database grows. You may find making smaller page sizes might help too as that will reduce the # of cache ejects. Depends on how scattered your select calls are. 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 Black, Michael (IS) [michael.bla...@ngc.com] Sent: Thursday, September 20, 2012 8:37 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Store error messages in thread local memory 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.o
Re: [sqlite] select ... where problem
>>> (select CURDATE()), What is a CURDATE()? Is that a custom scalar function of your own? Did you remember to compile it into the engine or load the module containing the function? >>> where datetime('now', 'localtime') > 15:00 15:00 is unparseable. Perhaps you mean >>> where datetime('now', 'localtime') > '15:00' But if so you can elimate the where clause entirely because '2' is always greter than '1' ... I am surprised the "Error Message" did not point out these errors. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Store error messages in thread local memory
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 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
Re: [sqlite] Store error messages in thread local memory
Ah - I didn't know that. Thanks for the clarification. On Thursday, 20. September 2012 at 15:28, Richard Hipp wrote: > On Thu, Sep 20, 2012 at 9:25 AM, Sebastian Krysmanski < > sql...@lists.manski.net (mailto:sql...@lists.manski.net)> wrote: > > > So you mean, that even read operations (SELECT) are not concurrent? > > Two separate database connection can read concurrently. But you can only > do one thing at a time with a single database connection. > > > > > > > > > > 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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Store error messages in thread local memory
On Thu, Sep 20, 2012 at 9:25 AM, Sebastian Krysmanski < sql...@lists.manski.net> wrote: > So you mean, that even read operations (SELECT) are not concurrent? > Two separate database connection can read concurrently. But you can only do one thing at a time with a single database connection. > > > > 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 > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Store error messages in thread local memory
So you mean, that even read operations (SELECT) are not concurrent? 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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Store error messages in thread local memory
On Thu, Sep 20, 2012 at 9:12 AM, Sebastian Krysmanski < 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 > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select ... where problem
On Donnerstag, 20. September 2012 15:16:29, 叶艳红 wrote: > insert into history (mdwkup,macdup,mdate,mdwkmd) SELECT > (select count(*) FROM fxj where mdwkup=1), > (select CURDATE()), > (select round(avg(mdwkmd),2) from fxj) where datetime('now', > 'localtime')>15:00 > > The sql command could not run Seems like you're missing the value for the second column to insert (macdup)... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] select ... where problem
insert into history (mdwkup,macdup,mdate,mdwkmd) SELECT (select count(*) FROM fxj where mdwkup=1), (select CURDATE()), (select round(avg(mdwkmd),2) from fxj) where datetime('now', 'localtime')>15:00 The sql command could not run ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Store error messages in thread local memory
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. 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.) Best regards Sebastian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential SQLITE_LOCKED bug?
You weren't handling your forks correctly This works and ends up with 200 rows...take out the srandom() line and you'll get 100 due to random() repeating itself across processes which I hope is what you intended. #include #include #include #include #define DBNAME "sessiontest.sqlite3" void execute_sql(sqlite3 *db, const char *sql, const char *param) { sqlite3_stmt *stmt; int rc; if(SQLITE_OK != (rc = sqlite3_prepare(db, sql, -1, &stmt, NULL))) { printf("sqlite3_prepare: %s\n", sqlite3_errmsg(db)); abort(); } if(param != NULL) if(SQLITE_OK != sqlite3_bind_text(stmt, 1, param, strlen(param), free)) abort(); while(SQLITE_ROW == (rc = sqlite3_step(stmt))) ; if(SQLITE_DONE != rc) { printf("sqlite3_step: %s\n", sqlite3_errmsg(db)); abort(); } if(SQLITE_OK != sqlite3_finalize(stmt)) { printf("sqlite3_finalize: %s\n", sqlite3_errmsg(db)); abort(); } } int main(void) { sqlite3 *db; int i, j, status; char *p; if(0 != unlink(DBNAME)) if(errno != ENOENT) abort(); if(0 != sqlite3_open(DBNAME, &db)) abort(); if(SQLITE_OK != sqlite3_exec(db, "BEGIN", NULL, NULL, NULL)) abort(); execute_sql(db, "CREATE TABLE sessions (sid TEXT, UNIQUE(sid));", NULL); if(SQLITE_OK != sqlite3_exec(db, "COMMIT", NULL, NULL, NULL)) abort(); if(0 != sqlite3_close(db); abort(); for(i = 0; i < 2; ++i) { /* TWO processes */ int childpid = fork(); if (childpid < 0) { perror("fork"); abort(); } if (childpid==0) { int mypid = getpid(); srandom(mypid); if(0 != sqlite3_open(DBNAME, &db)) abort(); for(i = 0; i < 100; ++i) { p = malloc(16); if(p == NULL) abort(); for(j = 0; j < 15; ++j) p[j] = 'a' + (random() % 26); p[j] = '\0'; if(SQLITE_OK != sqlite3_exec(db, "BEGIN", NULL, NULL, NULL)) { perror(sqlite3_errmsg(db)); abort(); } execute_sql(db, "INSERT OR REPLACE INTO sessions VALUES (?);", p); if(SQLITE_OK != sqlite3_exec(db, "COMMIT", NULL, NULL, NULL)) { printf("sqlite3_exec(COMMIT): %s\n", sqlite3_errmsg(db)); abort(); } } sqlite3_close(db); return; } else { // parent just waits for children to finish wait(&status); } } return 0; } 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 Helmut Grohne [hel...@subdivi.de] Sent: Thursday, September 20, 2012 5:45 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Potential SQLITE_LOCKED bug? Richard Hipp pointed out that the list strips attachments. For completeness I am copying the complete mail here. Please CC me in reply as I am not subscribed. Original message follows: I am occasionally receiving SQLITE_LOCKED in a forking Python application. Now according to the documentation http://www.sqlite.org/cvstrac/wiki/wiki?p=DatabaseIsLocked and http://www.sqlite.org/c_interface.html I should only get this error if two actions happen on the same connection in parallel (either via threads or via recursive calls). Since I have no clue why this happens I reproduced the problem in a small example C program. Find sessiontest.c attached. So compile sessiontest.c and link it against sqlite on a unixoid platform. Ensure that the filename "sessiontest.sqlite3" is absent and writeable in the working directory. Run the executable. Observed behaviour: sqlite3_exec(COMMIT): database is locked sqlite3_prepare: database is locked Expected behaviour: SQLITE_BUSY or no error, but certainly not SQLITE_LOCKED. Rationale: My example program first forks and then opens individual connections. So sharing via file descriptors or threads is not possible. Also my program does not pass handlers (besides free) to sqlite, so it cannot cause recursive calls. Therefore SQLITE_LOCKED should never happen. System used: Debian squeeze amd64 libsqlite3-0 3.7.3-1 Debian sid amd64 libsqlite3-0 3.7.13-1 Is this behaviour correct? If yes, why? Any proposed workarounds? Thanks in advance Helmut #include #include #include #include #include #include #define DBNAME "sessiontest.sqlite3" void execute_sql(sqlite3 *db, const char *sql, const char *param) { sqlite3_stmt *stmt; int rc; if(SQLITE_OK != (rc = sqlite3_prepare(db, sql, -1, &stmt, NULL))) { printf("sqlite3_prepare: %s\n", sqlite3_errmsg(db)); abort(); } if(param != NULL) if(SQLITE_OK != sqlite3_bind_text(stmt, 1, param, strlen(param), free)) abort(); while(SQLITE_ROW == (rc = sqlite3_step(stmt))) ; if(SQLITE_DONE != rc) abort(); if(SQLITE_OK !=
Re: [sqlite] minimizing flash writes
You could also set the TEMP_STORE to memory and copy the table to a temporary table. Then update the disk table from the temporary table using the 'changed' scheme below. Example: Pragma temp_store=2; Create table datatable (tag text, value real); Create unique index dtags on datatable (tag); create temporary table tdatatable as select tag, value, 0 as changed from datatable; create unique index ttags on tdatatable (tag); create index ttchg on tdatatable (changed, tag); then do your normal updates against tdatatable update tdatatable set value=?, changed=1 where tag=? Then periodically, Begin immediate; insert or replace into datatable select tag, value from tdatatable where changed=1; update tdatatable set changed=0 where changed=1; Commit; The temp table is only visible to the single process/connection which created it though ... --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Thursday, 20 September, 2012 06:21 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] minimizing flash writes > > > On 20 Sep 2012, at 6:42am, Rojo James wrote: > > > Is it possible to flush only the changed values from the in-memory database > > to the hard-disk and not backup the whole database (may be using something > > like a transaction log)? Is there some built-in function for this? > > Keep a column in your table(s) called 'changed'. After you startup and have > read the database into memory, set the 'changed' values in this memory copy > to 0/FALSE: > > UPDATE myTable SET changed=0 > > When a row is changed set its "changed" to 1. When you are ready to flush > memory look only at the rows with changed=1, write those away, then execute > the above UPDATE command again. > > > Is it possible to have a disk based database, with the WAL file residing in > > the RAM? In this case less IO cycles are required and the back-upping of > > data to flash drive is simpler. > > There are ways to mess with the drive used for temporary files, and point it > at a RAM drive. But that requires RAM drive support. I don't think you can > define it as ":memory:". > > > What other possibility do I have to minimize the write cycles to the flash > > memory? > > I think your idea of having a memory-based copy will work fine. And it has > the advantage of being completely standard SQLite without requiring any weird > coding or setup which another programmer might have trouble understanding. > > Simon. > ___ > 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
Re: [sqlite] Potential SQLITE_LOCKED bug?
On Tue, Sep 18, 2012 at 11:58 AM, Helmut Grohne wrote: > Dear sqlite users and developers, > > I am occasionally receiving SQLITE_LOCKED in a forking Python > application. Now according to the documentation > http://www.sqlite.org/cvstrac/wiki/wiki?p=DatabaseIsLocked and > http://www.sqlite.org/c_interface.html I should only get this error if > two actions happen on the same connection in parallel (either via > threads or via recursive calls). Since I have no clue why this happens I > reproduced the problem in a small example C program. Find sessiontest.c > attached. > > So compile sessiontest.c and link it against sqlite on a unixoid > platform. Ensure that the filename "sessiontest.sqlite3" is absent and > writeable in the working directory. Run the executable. > > Observed behaviour: > sqlite3_exec(COMMIT): database is locked > sqlite3_prepare: database is locked > > Expected behaviour: > SQLITE_BUSY or no error, but certainly not SQLITE_LOCKED. > On Debian Linux, I get back SQLITE_BUSY. The text error message for SQLITE_BUSY is (due to an historical accident) "database is locked". Perhaps that is the point of confusion. > > Rationale: > My example program first forks and then opens individual connections. So > sharing via file descriptors or threads is not possible. Also my program > does not pass handlers (besides free) to sqlite, so it cannot cause > recursive calls. Therefore SQLITE_LOCKED should never happen. > > System used: > Debian squeeze amd64 libsqlite3-0 3.7.3-1 > Debian sid amd64 libsqlite3-0 3.7.13-1 > > Is this behaviour correct? If yes, why? > > Any proposed workarounds? > > Thanks in advance > > Helmut > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL:2003 -- Window Functions
While i haven't needed this functionality, i have been thinking about how it could be solved in a performant way. IMHO, the solution would be to create a virtual table that takes, for example, as input a query that produces the following rows: C1 | C2 a1 | b1 a2 | b2 a3 | b3 and outputs the following: C1 | C2 | C1prev | C2prev -- a1 | b1 | null | null a2 | b2 | a1 | b1 a3 | b3 | a2 | b2 So in essence the window gets transformed to a single row. In a similar way, bigger windows could also be transformed. l. On 20/09/12 14:46, Gabor Grothendieck wrote: On Wed, Sep 19, 2012 at 12:51 PM, joe.fis...@tanguaylab.com wrote: Too bad SQLite doesn't yet support SQL Window Functions. Are there any SQLite Extension Libraries that support "SQL:2003 type Window Functions"? I specifically need LEAD and LAG to calculate an event integer timestamp delta between consecutive rows. I've played with some self-join code but that's proving to be complicated. SQL Window Functions is the number one feature that I could use as well. In R, sqlite can be used for manipulating R data frames via the sqldf package and this sort of functionality would be very useful. (sqldf also handles PostgreSQL which does have windowing functions but PostgreSQL requires more set up than sqlite so its not as accessible to 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
Re: [sqlite] minimizing flash writes
On 20 Sep 2012, at 6:42am, Rojo James wrote: > Is it possible to flush only the changed values from the in-memory database > to the hard-disk and not backup the whole database (may be using something > like a transaction log)? Is there some built-in function for this? Keep a column in your table(s) called 'changed'. After you startup and have read the database into memory, set the 'changed' values in this memory copy to 0/FALSE: UPDATE myTable SET changed=0 When a row is changed set its "changed" to 1. When you are ready to flush memory look only at the rows with changed=1, write those away, then execute the above UPDATE command again. > Is it possible to have a disk based database, with the WAL file residing in > the RAM? In this case less IO cycles are required and the back-upping of > data to flash drive is simpler. There are ways to mess with the drive used for temporary files, and point it at a RAM drive. But that requires RAM drive support. I don't think you can define it as ":memory:". > What other possibility do I have to minimize the write cycles to the flash > memory? I think your idea of having a memory-based copy will work fine. And it has the advantage of being completely standard SQLite without requiring any weird coding or setup which another programmer might have trouble understanding. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential SQLITE_LOCKED bug?
Richard Hipp pointed out that the list strips attachments. For completeness I am copying the complete mail here. Please CC me in reply as I am not subscribed. Original message follows: I am occasionally receiving SQLITE_LOCKED in a forking Python application. Now according to the documentation http://www.sqlite.org/cvstrac/wiki/wiki?p=DatabaseIsLocked and http://www.sqlite.org/c_interface.html I should only get this error if two actions happen on the same connection in parallel (either via threads or via recursive calls). Since I have no clue why this happens I reproduced the problem in a small example C program. Find sessiontest.c attached. So compile sessiontest.c and link it against sqlite on a unixoid platform. Ensure that the filename "sessiontest.sqlite3" is absent and writeable in the working directory. Run the executable. Observed behaviour: sqlite3_exec(COMMIT): database is locked sqlite3_prepare: database is locked Expected behaviour: SQLITE_BUSY or no error, but certainly not SQLITE_LOCKED. Rationale: My example program first forks and then opens individual connections. So sharing via file descriptors or threads is not possible. Also my program does not pass handlers (besides free) to sqlite, so it cannot cause recursive calls. Therefore SQLITE_LOCKED should never happen. System used: Debian squeeze amd64 libsqlite3-0 3.7.3-1 Debian sid amd64 libsqlite3-0 3.7.13-1 Is this behaviour correct? If yes, why? Any proposed workarounds? Thanks in advance Helmut #include #include #include #include #include #include #define DBNAME "sessiontest.sqlite3" void execute_sql(sqlite3 *db, const char *sql, const char *param) { sqlite3_stmt *stmt; int rc; if(SQLITE_OK != (rc = sqlite3_prepare(db, sql, -1, &stmt, NULL))) { printf("sqlite3_prepare: %s\n", sqlite3_errmsg(db)); abort(); } if(param != NULL) if(SQLITE_OK != sqlite3_bind_text(stmt, 1, param, strlen(param), free)) abort(); while(SQLITE_ROW == (rc = sqlite3_step(stmt))) ; if(SQLITE_DONE != rc) abort(); if(SQLITE_OK != sqlite3_finalize(stmt)) abort(); } int main(void) { sqlite3 *db; int i, j; char *p; if(0 != unlink(DBNAME)) if(errno != ENOENT) abort(); if(0 != sqlite3_open(DBNAME, &db)) abort(); if(SQLITE_OK != sqlite3_exec(db, "BEGIN", NULL, NULL, NULL)) abort(); execute_sql(db, "CREATE TABLE sessions (sid TEXT, UNIQUE(sid));", NULL); if(SQLITE_OK != sqlite3_exec(db, "COMMIT", NULL, NULL, NULL)) abort(); if(0 != sqlite3_close(db)) abort(); for(i = 0; i < 2; ++i) /* FOUR processes */ if(fork() < 0) abort(); if(0 != sqlite3_open(DBNAME, &db)) abort(); for(i = 0; i < 100; ++i) { p = malloc(16); if(p == NULL) abort(); for(j = 0; j < 15; ++j) p[j] = 'a' + (random() % 26); p[j] = '\0'; if(SQLITE_OK != sqlite3_exec(db, "BEGIN", NULL, NULL, NULL)) abort(); execute_sql(db, "INSERT OR REPLACE INTO sessions VALUES (?);", p); if(SQLITE_OK != sqlite3_exec(db, "COMMIT", NULL, NULL, NULL)) { printf("sqlite3_exec(COMMIT): %s\n", sqlite3_errmsg(db)); abort(); } } sqlite3_close(db); return 0; } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] minimizing flash writes
Hi all, We have an embedded device where we continuously receive latest values from our process. There are no new records added, but the existing values are updated almost every second. I thought of using sqlite as in-memory database for recording these values (so that writes to the flash drive are minimized) and then flushing these values after a fixed period (not every 1 second) to the database in the flash drive (so that the latest values are not lost in case of a restart). These values should also be read back during the next cycle of the process. The time required for flushing is critical since there can be a power failure condition and the changed data need to be saved in the flash drive within a very short time. Is it possible to flush only the changed values from the in-memory database to the hard-disk and not backup the whole database (may be using something like a transaction log)? Is there some built-in function for this? Is it possible to have a disk based database, with the WAL file residing in the RAM? In this case less IO cycles are required and the back-upping of data to flash drive is simpler. What other possibility do I have to minimize the write cycles to the flash memory? Thank you, Rojo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL:2003 -- Window Functions
On Wed, Sep 19, 2012 at 12:51 PM, joe.fis...@tanguaylab.com wrote: > Too bad SQLite doesn't yet support SQL Window Functions. > > Are there any SQLite Extension Libraries that support "SQL:2003 type Window > Functions"? > I specifically need LEAD and LAG to calculate an event integer timestamp > delta between consecutive rows. > I've played with some self-join code but that's proving to be complicated. > SQL Window Functions is the number one feature that I could use as well. In R, sqlite can be used for manipulating R data frames via the sqldf package and this sort of functionality would be very useful. (sqldf also handles PostgreSQL which does have windowing functions but PostgreSQL requires more set up than sqlite so its not as accessible to users.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index not used in query
On 09/20/2012 04:51 PM, Scholz Maik (CM-AI/PJ-CF42) wrote: Hi, I have a problem with a missing use of an index. My Example: sqlite> create table tab1 (o INT, a INT, b INT,UNIQUE(o,b)); sqlite> select * from sqlite_master; table|tab1|tab1|2|CREATE TABLE tab1 (o INT, a INT, b INT,UNIQUE(o,b)) index|sqlite_autoindex_tab1_1|tab1|3| => I expect that the index "sqlite_autoindex_tab1_1" is on column o and b? sqlite> explain query plan SELECT * from tab1 WHERE o=1; 0|0|0|SEARCH TABLE tab1 USING INDEX sqlite_autoindex_tab1_1 (o=?) (~10 rows) => OK: "sqlite_autoindex_tab1_1" is used sqlite> explain query plan SELECT * from tab1 WHERE a=1; 0|0|0|SCAN TABLE tab1 (~10 rows) => OK: "sqlite_autoindex_tab1_1" is not used because a is not indexed. sqlite> explain query plan SELECT * from tab1 WHERE b=1; 0|0|0|SCAN TABLE tab1 (~10 rows) => NOK: Why is "sqlite_autoindex_tab1_1" not used? Creating an index basically creates a sorted list on disk. In your case, the elements of the list are sorted in order of column "o", with column "b" used as a tie-breaker. In the same way as the entries in a phone book are sorted by surname with the first name or initial of the subscriber used as a tie-breaker. So with a phone book, you can do these easily enough: SELECT * FROM subscribers WHERE surname = 'Smith'; SELECT * FROM subscribers WHERE surname = 'Smith' AND fname = 'Joe'; But this is hard: SELECT * FROM subscribers WHERE fname = 'Joe'; To find all subscribers named 'Joe', you would have to search the whole phone book from beginning to end. The fact that it's in sorted order would not help. Perhaps you need a second index on column "b" only. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index not used in query
Hi, I have a problem with a missing use of an index. My Example: sqlite> create table tab1 (o INT, a INT, b INT,UNIQUE(o,b)); sqlite> select * from sqlite_master; table|tab1|tab1|2|CREATE TABLE tab1 (o INT, a INT, b INT,UNIQUE(o,b)) index|sqlite_autoindex_tab1_1|tab1|3| => I expect that the index "sqlite_autoindex_tab1_1" is on column o and b? sqlite> explain query plan SELECT * from tab1 WHERE o=1; 0|0|0|SEARCH TABLE tab1 USING INDEX sqlite_autoindex_tab1_1 (o=?) (~10 rows) => OK: "sqlite_autoindex_tab1_1" is used sqlite> explain query plan SELECT * from tab1 WHERE a=1; 0|0|0|SCAN TABLE tab1 (~10 rows) => OK: "sqlite_autoindex_tab1_1" is not used because a is not indexed. sqlite> explain query plan SELECT * from tab1 WHERE b=1; 0|0|0|SCAN TABLE tab1 (~10 rows) => NOK: Why is "sqlite_autoindex_tab1_1" not used? My Version: SQLite 3.7.7.1 2011-06-28 17:39:05 Thanks Maik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users