Re: [sqlite] Update Query

> On 25 May 2012, at 3:04am, IQuant wrote: > > > update TICKDATA set IQ_A = ROUND(ASK - ( > > select t2.ASK from TICKDATA t2 > > where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP < > > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4), > > IQ_B =

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 5/24/2012 10:17 PM, Andrew Cherednik wrote: Thanks guys. You really helped me. I think I know what I am going to do. You see, as I am using a single connection object there is at least one selection statement executed at the beginning of each process. Then, during the program lifecycle

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

Thanks guys. You really helped me. I think I know what I am going to do. You see, as I am using a single connection object there is at least one selection statement executed at the beginning of each process. Then, during the program lifecycle there could be a few updates executed, that use the

Re: [sqlite] Update Query

On 25 May 2012, at 3:04am, IQuant wrote: > update TICKDATA set IQ_A = ROUND(ASK - ( > select t2.ASK from TICKDATA t2 > where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP < > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4), > IQ_B = ROUND(BID - ( >

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

Also it suggests that transaction was began as read-only (with a select statement) and then there was attempt to transform it to a writing transaction (with insert, update or delete statement) when there was another writing transaction in progress waiting for this transaction to finish. and

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 25 May 2012, at 2:52am, Pavel Ivanov wrote: > Also it suggests that transaction was began as read-only (with a > select statement) and then there was attempt to transform it to a > writing transaction (with insert, update or delete statement) when > there was another

Re: [sqlite] Update Query

Thanks for your suggestion Igor Tandetnik: Scope creep expanded the original query to the actual trading instruments and the refactored code has evolved to:: update TICKDATA set IQ_A = ROUND(ASK - ( select t2.ASK from TICKDATA t2 where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On Thu, May 24, 2012 at 9:48 PM, Simon Slavin wrote: > > On 25 May 2012, at 2:45am, Andrew Cherednik > wrote: > >> Tried different timeouts. The timeouts will basically make the program hang, >> but eventually the transaction will end

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 5/24/2012 9:41 PM, Andrew Cherednik wrote: On 5/24/2012 11:36 AM, Igor Tandetnik wrote: Before first select. Use BEGIN IMMEDIATE instead of regular BEGIN, to start the transaction. Thanks. Will do. Do you still believe I need to get rid of the global connection object? What do you

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 25 May 2012, at 2:45am, Andrew Cherednik wrote: > Tried different timeouts. The timeouts will basically make the program hang, > but eventually the transaction will end with SQLITE_BUSY error. Hmm. _BUSY with a timeout of 13 seconds suggests an

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 5/24/2012 11:35 AM, Simon Slavin wrote: > On 5/24/2012 9:06 PM, Igor Tandetnik wrote: > >> What exactly do you mean by "database lockout problems"? What error in what >> API call are you getting? > > I am getting SQLITE_BUSY error very often, and it does not go away, as if the > database is

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 5/24/2012 11:36 AM, Igor Tandetnik wrote: > Before first select. Use BEGIN IMMEDIATE instead of regular BEGIN, to start > the transaction. Thanks. Will do. Do you still believe I need to get rid of the global connection object? ___ sqlite-users

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 5/24/2012 9:27 PM, Andrew Cherednik wrote: Do you use explicit transactions? If yes, do any of those follow the pattern where you start with a SELECT, and then perform an INSERT or UPDATE or DELETE (in other words, a transaction starts as a reader, and then wants to upgrade to a writer)?

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 25 May 2012, at 2:13am, Andrew Cherednik wrote: > On 5/24/2012 9:06 PM, Igor Tandetnik wrote: > >> What exactly do you mean by "database lockout problems"? What error in what >> API call are you getting? > > I am getting SQLITE_BUSY error very often,

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

> I believe from the description that this is one single threaded application > per computer, for N computers, each using a single connection object pointing > to the same database file on a remote filesystem. > Therefore, each database access is locking out all the others. Multiple >

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

> Are you resetting your prepared statements? I believe I am resetting all the statements. > Do you use explicit transactions? If yes, do any of those follow the pattern > where you start with a SELECT, and then perform an INSERT or UPDATE or DELETE > (in other words, a transaction starts as a

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 5/24/2012 9:13 PM, Andrew Cherednik wrote: On 5/24/2012 9:06 PM, Igor Tandetnik wrote: Actually, scratch that. Multiple statements running on the same connection will never lock each other out. Are multiple instances of your application running at the same time, connecting to the same

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

I believe from the description that this is one single threaded application per computer, for N computers, each using a single connection object pointing to the same database file on a remote filesystem. Therefore, each database access is locking out all the others. Multiple connections will

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 5/24/2012 9:06 PM, Igor Tandetnik wrote: > Actually, scratch that. Multiple statements running on the same connection > will never lock each other out. Are multiple instances of your application > running at the same time, connecting to the > same database? Yes, they are connecting to the

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 5/24/2012 9:06 PM, Igor Tandetnik wrote: On 5/24/2012 8:55 PM, Andrew Cherednik wrote: The program is a multi-user program that runs across the network in Windows environment. The users constantly experience database lockout problems. I suspect that it is due to the fact that the sqlite*

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 5/24/2012 8:55 PM, Andrew Cherednik wrote: The program is a multi-user program that runs across the network in Windows environment. The users constantly experience database lockout problems. I suspect that it is due to the fact that the sqlite* object that has been used for database

Re: [sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

On 25 May 2012, at 1:55am, Andrew Cherednik wrote: > The program is a multi-user program that runs across the network in Windows > environment. The users constantly experience database lockout problems. I > suspect that it is due to the fact that the

[sqlite] Advice on the proper use of sqlite3 - Database Connection Handle

Hi, I have converted a legacy program that has used a Microsoft Jet (Access) database. The program is written in Visual C++. It is an client-server MFC application that used OLE-DB interfaces to directly connect to the database. I have managed to write a drop-in replacement for all the OLE-DB

Re: [sqlite] SQLite SELECT performance problem

I tried and got this: D:\Temp\Test>timethis custom\sqlite3console database.sqlite < query.sql TimeThis : Command Line : custom\sqlite3console database.sqlite TimeThis :Start Time : Thu May 24 18:42:51 2012 TimeThis : End Time : Thu May 24 18:42:54 2012 TimeThis : Elapsed Time :

Re: [sqlite] SQLite SELECT performance problem

And don't forget /D_CRT_DISABLE_PERFCRIT_LOCKS And make sure you link with the static library (/MT) not the dynamic runtime. Visual Studio no longer has a single threaded library. You have to define _CRT_DISABLE_PERFCRIT_LOCKS in order to get the compiler to generate code and use library

Re: [sqlite] SQLite SELECT performance problem

I've tested your database and query, using the official sqlite3.exe and the versions I compile myself (Microsoft Visual Studio 2010, x86 and x64): - Official (x86): ~5s (CPU Time: user 1.965613 sys 2.527216) - Compiled by me (x86): ~1s (CPU Time: user 1.154407 sys 0.187201) - Compiled by

Re: [sqlite] SQLite SELECT performance problem

On 24 May 2012, at 11:49pm, Nick wrote: > The sizes of the executable files are almost identical - there's a few > kilobytes difference. > I have attached the original (downloaded from sqlite.org) sqlite3.exe, > a compiled-by-myself sqlite3console.exe. And the source

Re: [sqlite] SQLite SELECT performance problem

The sizes of the executable files are almost identical - there's a few kilobytes difference. I have attached the original (downloaded from sqlite.org) sqlite3.exe, a compiled-by-myself sqlite3console.exe. And the source code. Also there's import tables dump (import tables are also very similar for

Re: [sqlite] SQLite SELECT performance problem

On 24 May 2012, at 11:13pm, Nick wrote: > In my initial message I described some proof-of-concept that I've done. > > I downloaded sqlite3.exe (An SQLite command line tool) from the > SQLite's website. I executed my query and I had to wait 4 seconds for > it to complete.

Re: [sqlite] SQLite SELECT performance problem

I tried defining "SQLITE_TEMP_STORE 2" as well. Unfortunately it doesn't influence the speed much, in my case... 2012/5/25 Jonas Malaco Filho : > Why TEMP_STORE=1 (file by default) and not TEMP_STORE=2 (memory by default)? > > *Jonas Malaco Filho* > > 2012/5/24 Simon

Re: [sqlite] SQLite SELECT performance problem

In my initial message I described some proof-of-concept that I've done. I downloaded sqlite3.exe (An SQLite command line tool) from the SQLite's website. I executed my query and I had to wait 4 seconds for it to complete. Then I downloaded sqlite3.c, sqlite3.h and shell.c, compiled them

Re: [sqlite] SQLite SELECT performance problem

Why TEMP_STORE=1 (file by default) and not TEMP_STORE=2 (memory by default)? *Jonas Malaco Filho* 2012/5/24 Simon Slavin > > On 24 May 2012, at 8:59pm, Nick wrote: > > > So why does a prebuilt, downloaded from the sqlite website, command > > line

Re: [sqlite] SQLite SELECT performance problem

On 24 May 2012, at 8:59pm, Nick wrote: > So why does a prebuilt, downloaded from the sqlite website, command > line tool takes only 4 seconds, while the same tool, built by me, > takes 4 times longer time to execute? I'm wondering whether the speed increase is related to

Re: [sqlite] SQLite SELECT performance problem

Yes, i have tried it. Here's what sqlite3.exe, that i just built, returns: sqlite> pragma compile_options ; TEMP_STORE=1 THREADSAFE=0 sqlite> Still getting these 14 seconds. I am using Visual Studio 2008 for building.. 2012/5/24 Richard Hipp : > On Thu, May 24, 2012 at 3:59 PM,

Re: [sqlite] SQLite SELECT performance problem

On Thu, May 24, 2012 at 3:59 PM, Nick wrote: > > Any way I could make my C program execute this query as fast as the > prebuilt command line tool does it? > Have you tried compiling with the -DSQLITE_THREADSAFE=0 option? -- D. Richard Hipp d...@sqlite.org

[sqlite] SQLite SELECT performance problem

Hello! I have a program that does some math in an SQL query. There are hundreds of thousands rows (some device measurements) in an SQLite table, and using this query, the application breaks these measurements into groups of, for example, 1 records, and calculates the average for each group.

Re: [sqlite] ADO.NET Provider, targeting any cpu

Damien wrote: > > It is working for a classic application. But for a website (or a web > application), the directories x86 and x64 should be in the "~/Bin" > directory or in "~/" ? > The "x86" and "x64" directories should be just inside the directory where the "System.Data.SQLite.dll" file is

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

On Thu, May 24, 2012 at 2:04 PM, Simon Slavin wrote: > > On 24 May 2012, at 6:56pm, Sidney Cadot wrote: > > >> Why risk ending up with an unexpected (possibly old) version > >> by linking at runtime just to save users less than 300K of disk > >> space? >

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

On 24 May 2012, at 6:56pm, Sidney Cadot wrote: >> Why risk ending up with an unexpected (possibly old) version >> by linking at runtime just to save users less than 300K of disk >> space? > > But that's an argument against shared linking in general. > > I am just curious

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

> However, the OP has indicated little that would allow us to guess whether > his project should follow the norm or not. I think my question is independent of my particular project; in fact, I am not working on an SQLite project at the moment. The reason I asked this question is that I have a

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

That page appears to specifically be in regards to compiling SQLite from sources. It means, don't use the individual files, but use the amalgamation because it's a lot simpler to deal with. How you compile it, or in what form the compiled object is used is not mentioned. -Original

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

> Why risk ending up with an unexpected (possibly old) version > by linking at runtime just to save users less than 300K of disk > space? But that's an argument against shared linking in general. I am just curious what idea this particular statement on this particular help-page (specific to

Re: [sqlite] GROUP BY question

Thanks everyone! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

> Yes.  SQLite is so small there's really no reason to make a separate library > of it. Well, my Linux distribution may provide a "libsqlite3-dev" package, which makes linking to a recent version of sqlite as simple as adding LDLIBS=-lsqlite3 to the Makefile. By going that path you ensure that

Re: [sqlite] GROUP BY question

On May 24, 2012, at 7:22 PM, Mike King wrote: > Is this Select statement valid? In SQLite, yes. > In Oracle, it wouldn't be because > what is the aggregate of A. Right. SQLite tries nonetheless to return "something" . A bit of a (mis)feature IMO. > Is this behavior defined anywhere?

Re: [sqlite] GROUP BY question

On Thu, May 24, 2012 at 1:22 PM, Mike King wrote: > Is this Select statement valid?  In Oracle, it wouldn't be because > what is the aggregate of A.  Is this behavior defined anywhere? > > create table T (A,B); > insert into  T (A,B) values (1,3); > insert into  T (A,B) values

Re: [sqlite] GROUP BY question

On Thu, May 24, 2012 at 1:22 PM, Mike King wrote: > Is this Select statement valid? In Oracle, it wouldn't be because > what is the aggregate of A. Is this behavior defined anywhere? > > create table T (A,B); > insert into T (A,B) values (1,3); > insert into T (A,B) values

[sqlite] GROUP BY question

Is this Select statement valid? In Oracle, it wouldn't be because what is the aggregate of A. Is this behavior defined anywhere? create table T (A,B); insert into T (A,B) values (1,3); insert into T (A,B) values (2,3); select A,B from T group by B;

Re: [sqlite] (no subject)

On 5/24/2012 11:46 AM, IQuant wrote: ie. I have a table with 1,000,000 + records collecting real time stats from many devices with many columns but the main ones of interest are 'timestamp', 'latency' and 'DeviceID'. 2012-05-01 13:12:11.103 Null 14356 2012-05-01 13:12:11.103 Null 14372

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

On May 24, Dan Kennedy wrote: On 05/24/2012 10:53 PM, Larry Brasfield wrote: >> On the support page http://www.sqlite.org/howtocompile.html, it says: >> >> "The use of the amalgamation is recommended for all applications." >> >> Is this a general recommendation, to use the amalgamated source

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

On 05/24/2012 10:53 PM, Larry Brasfield wrote: On the support page http://www.sqlite.org/howtocompile.html, it says: "The use of the amalgamation is recommended for all applications." Is this a general recommendation, to use the amalgamated source file as the preferred way of including SQLite

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

On 24 May 2012, at 4:43pm, Sidney Cadot wrote: > On the support page http://www.sqlite.org/howtocompile.html, it says: > >"The use of the amalgamation is recommended for all applications." > > Is this a general recommendation, to use the amalgamated source file > as the

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

On the support page http://www.sqlite.org/howtocompile.html, it says: "The use of the amalgamation is recommended for all applications." Is this a general recommendation, to use the amalgamated source file as the preferred way of including SQLite functionality in one's application, rather

[sqlite] (no subject)

How can I construct a update query to calculate and set a record field "latency" with the difference between "timestamps" by "deviceid"? Appears sqlite doesn't support lag and lead. ie. I have a table with 1,000,000 + records collecting real time stats from many devices with many columns but the

[sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

Dear all, On the support page http://www.sqlite.org/howtocompile.html, it says: "The use of the amalgamation is recommended for all applications." Is this a general recommendation, to use the amalgamated source file as the preferred way of including SQLite functionality in one's

Re: [sqlite] Possible memory leaks on sqlite3_close

Hi! Did the call to sqlite3_close() _succeed_? It will fail (iirc) if any statements are still open. - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal On May 24, 2012 2:33 PM, "Alfred Sawaya" wrote: > Hello, > > SQLite version 3.7.9

[sqlite] Possible memory leaks on sqlite3_close

Hello, SQLite version 3.7.9 2011-11-01 00:52:41 Valgrind reports some memory leaks : ==9709== HEAP SUMMARY: ==9709== in use at exit: 94,248 bytes in 42 blocks ==9709== total heap usage: 1,338 allocs, 1,296 frees, 1,066,595 bytes allocated ==9709== ==9709== 80 bytes in 1 blocks are

Re: [sqlite] ADO.NET Provider, targeting any cpu

Hi, It is working for a classic application. But for a website (or a web application), the directories x86 and x64 should be in the "~/Bin" directory or in "~/" ? Thanks. Damien 2012/5/23 Joe Mistachkin > > Rob Richardson wrote: > > > > This is the first I have heard

Re: [sqlite] SQLite reading old data

I just wanted to bring this one again up. Unfortunately there was no reaction from the system.data.sqlite nor from the SQLite maintainers. I've patched the crypt.c module to ignore the change counter, so it will never be encrypted. But that's not the best solution. Is there at least a public