Re: [sqlite] select ... where problem

[YAN HONG YE]

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

[Keith Medcalf]

> 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

[sqlite] Use of different System.Data.SQLite packages

[Bernd]

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

[Sebastian Krysmanski]

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

[Sebastian Krysmanski]

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

[Black, Michael (IS)]

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 

Re: [sqlite] Store error messages in thread local memory

[Sebastian Krysmanski]

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
> > > > > > 

Re: [sqlite] Store error messages in thread local memory

[Sebastian Krysmanski]

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

[Sebastian Krysmanski]

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

[Teg]

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 

Re: [sqlite] Store error messages in thread local memory

[Keith Medcalf]

> 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

[Black, Michael (IS)]

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)
> > > > 

Re: [sqlite] Store error messages in thread local memory

[Sebastian Krysmanski]

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 

Re: [sqlite] Store error messages in thread local memory

[Black, Michael (IS)]

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

Re: [sqlite] select ... where problem

[Keith Medcalf]

>>> (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

[Black, Michael (IS)]

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

[Sebastian Krysmanski]

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

[Richard Hipp]

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

[Sebastian Krysmanski]

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

[Richard Hipp]

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

[Marcus Ilgner]

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

Re: [sqlite] minimizing flash writes

[Keith Medcalf]

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?

[Richard Hipp]

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

[Eleytherios Stamatogiannakis]

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

[Simon Slavin]

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?

[Helmut Grohne]

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, , 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, ))
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, ))
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

[Rojo James]

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

[Gabor Grothendieck]

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

[Dan Kennedy]

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

[Scholz Maik (CM-AI/PJ-CF42)]

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