Re: [sqlite] Update Query

[Keith Medcalf]

> 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 - (
> >   select t2.BID from TICKDATA t2
> >   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> > IQ_T = ROUND(TRADEPRICE - (
> >   select t2.TRADEPRICE from TICKDATA t2
> >   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> > IQ_X = (select t2.timestamp from TICKDATA t2
> >   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1);
> >
> > The consolidated tick database is 1.5GB / 32M records and the above
> > query takes 5 minutes to run.

> On 24 May, 2012 20:11, Simon Slavin said:

> You appear to be doing the same sub-select four times.  I don't know whether
> SQLite optimizes these into one or not, but if you're using a programming
> language I bet you could speed the query up a great deal by doing that
> subselect first, and substituting the results into the UPDATE command.

It doesn't optimize correlated subqueries even with the same subselect criteria 
-- however the btree indexes are already positioned after the first so the next 
three just traverse the same tree already in memory.


However, here are two ways of moving the correlated queries from the inner loop 
to an outer loop:

-- need unique index on TICKDATA (SYMBOL, TIMESTAMP)
-- the view and the trigger can be defined in the database schema once
CREATE VIEW TICKMAX
AS
  SELECT ASK, BID, TRADEPRICE, TIMESTAMP, SYMBOL
FROM TICKDATA
   WHERE TIMESTAMP = MAX(TIMESTAMP)
GROUP BY SYMBOL;

CREATE TRIGGER TICKMAXUPDATE INSTEAD OF UPDATE ON TICKMAX FOR EACH ROW
BEGIN
  UPDATE TICKMAX
 SET IQ_A = ROUND(ASK - OLD.ASK, 4),
 IQ_B = ROUND(BID - OLD.BID, 4),
 IQ_T = ROUND(TRADEPRICE - OLD.TRADEPRICE, 4),
 IQ_X = OLD.TIMESTAMP
   WHERE SYMBOL = OLD.SYMBOL
 AND TIMESTAMP < OLD.TIMESTAMP;
END;

-- everytime you want to do an update, run this:
UPDATE TICKMAX SET ASK = ASK + 1;



Of course, if you are using a programming language you could do something like 
this:

cr.execute('BEGIN IMMEDIATE;')
for row in cr.execute('''SELECT ASK, BID, TRADEPRICE, TIMESTAMP, SYMBOL
FROM TICKDATA
   WHERE TIMESTAMP = MAX(TIMESTAMP)
GROUP BY SYMBOL;'''):
cr.execute('''UPDATE TICKMAX
  SET IQ_A = ROUND(ASK - :ASK, 4),
  IQ_B = ROUND(BID - :BID, 4),
  IQ_T = ROUND(TRADEPRICE - :TRADEPRICE, 4),
  IQ_X = :TIMESTAMP
WHERE SYMBOL = :SYMBOL
  AND TIMESTAMP < :TIMESTAMP;''', row)
cr.execute('COMMIT;)

In other words, select the values at the maximum timestamp for each symbol, 
then for each row returned execute the update statement updating the rest of 
the rows for that symbol ... both the trigger and the above code move the 
selection of the updating values from the inner loop to an outer loop.


Of course, they may not make much of a difference at all.  The latter two will 
do exactly count(distinct symbol) less row updates, but that is still pretty 
much every row in the database.  You will have to try it and see if the 
performance increase (if any) is worth the additional complexity.

---
()  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 
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Update Query
> 
> 
> 
> 
> 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] Advice on the proper use of sqlite3 - Database Connection Handle

[Igor Tandetnik]

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 there could be a few updates executed, that use the same connection 
object. So, the transaction that was opened with a SHARED lock would be 
converted to a RESERVED lock until committed.

Therefore, I am thinking of using separate (local) connections for the routines 
that do the updates, and leaving the global connection object for the selects 
only.


If you are willing to do that, then you can simply perform select and 
updates on the same connection but in separate transactions. Be aware 
that, in this scenario, the data you've obtained with SELECT might 
already be out of date by the time you get to inserts and updates. This 
is true whether you are doing updates on a separate connection, or in a 
separate transaction on the same connection.

--
Igor Tandetnik

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

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

[Andrew Cherednik]

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 same connection 
object. So, the transaction that was opened with a SHARED lock would be 
converted to a RESERVED lock until committed. 

Therefore, I am thinking of using separate (local) connections for the routines 
that do the updates, and leaving the global connection object for the selects 
only. I have estimated that there are not a lot of places in the program what 
do SELECT and then UPDATE, so, for these routines I will be doing the 
following: 
1. open new connection
2. execute BEGIN IMMEDIATE
3. do SELECT/UPDATE/DELETE (no change)
4. do the COMMIT (or ROLLBACK in case of an exception)
5. close connection.

For the rest of the program, I will leave the global connection object.

Andrew Cherednik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update Query

[Simon Slavin]

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 - (
>   select t2.BID from TICKDATA t2
>   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> IQ_T = ROUND(TRADEPRICE - (
>   select t2.TRADEPRICE from TICKDATA t2
>   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> IQ_X = (select t2.timestamp from TICKDATA t2
>   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1);
> 
> The consolidated tick database is 1.5GB / 32M records and the above
> query takes 5 minutes to run.

You appear to be doing the same sub-select four times.  I don't know whether 
SQLite optimizes these into one or not, but if you're using a programming 
language I bet you could speed the query up a great deal by doing that 
subselect first, and substituting the results into the UPDATE command.

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

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

[Jean-Christophe Deschamps]

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 that's nicely (and transparently) dealt with by using explicit 
BEGIN IMMEDIATE transaction (plus proper COMMIT) along with a timeout 
longer than the longest possible sequence of possible transactions.


I always use something like 20 minutes to allow for backup taking place.

Think of this timeout as "large bounded near-infinite time".  Whatever 
time the transaction causing BUSY will take (and other transactions 
created before yours can engage), it will finally find its way unless 
you have write transaction(s) repeating so quickly that yours doesn't 
have any chance to ever run.


JcD 


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

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

[Simon Slavin]

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 writing transaction in progress waiting for this
> transaction to finish.

So experimenting with BEING IMMEDIATE and BEGIN EXCLUSIVE might help.

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

Re: [sqlite] Update Query

[IQuant]

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 <
TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
IQ_B = ROUND(BID - (
   select t2.BID from TICKDATA t2
   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
IQ_T = ROUND(TRADEPRICE - (
   select t2.TRADEPRICE from TICKDATA t2
   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
IQ_X = (select t2.timestamp from TICKDATA t2
   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1);

The consolidated tick database is 1.5GB / 32M records and the above
query takes 5 minutes to run.
Found proper index is key for performance...  Symbol,Timestamp
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Pavel Ivanov]

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 with SQLITE_BUSY error.
>
> Hmm.  _BUSY with a timeout of 13 seconds suggests an underlying problem 
> rather than just a speed problem.

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.


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

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

[Igor Tandetnik]

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 mean, "still"? I don't recall ever professing such a belief.
--
Igor Tandetnik

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

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

[Simon Slavin]

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 underlying problem rather 
than just a speed problem.  If you are doing _finalize() properly, and you are 
checking the result codes returned by /every/ sqlite3 call you make, and you 
are grouping SQL commands with transactions where appropriate, then I think 
Igor's ideas are probably going to help you most.

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

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

[Andrew Cherednik]

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

> Please set a SQLite timeout to three seconds and try again.  If you're still 
> getting locks, set it to ten seconds and try again.

> 

> I'm not saying this will get rid of locks entirely, but it will help us to 
> know whether this has any effect on the number of errors you're seeing.

I have already done this. Tried different timeouts. The timeouts will basically 
make the program hang, but eventually the transaction will end with SQLITE_BUSY 
error.

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

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

[Andrew Cherednik]

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 mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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)? This situation may lead to a 
deadlock. If you have such transactions, start them with BEGIN IMMEDIATE 
statement.


Yes, I have a lot of transactions like this. Do I need to start with BEGIN 
IMMEDIATE before first select, or before an update command?


Before first select. Use BEGIN IMMEDIATE instead of regular BEGIN, to 
start the transaction.

--
Igor Tandetnik

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

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

[Simon Slavin]

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, and it does not go away, as if the 
> database is dead-locked

Please set a SQLite timeout to three seconds and try again.  If you're still 
getting locks, set it to ten seconds and try again.



I'm not saying this will get rid of locks entirely, but it will help us to know 
whether this has any effect on the number of errors you're seeing.

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

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

[Andrew Cherednik]

> 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 not make a difference.  THe transactions (which include 
> selects which will be exclusive) are not being finalized/commit'd 
> expeditiously enough.

> You would still need to expect and handle BUSY/LOCK conditions however as 
> there is certainty that two such clients will attempt to access the database 
> at the same instant. 

It is actually a multi-threaded application with each thread using the same 
connection object
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Andrew Cherednik]

> 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 
> reader, and then wants to upgrade to a writer)? This situation may lead to a 
> deadlock. If you have such transactions, start them with BEGIN IMMEDIATE 
> statement.

Yes, I have a lot of transactions like this. Do I need to start with BEGIN 
IMMEDIATE before first select, or before an update command?

Regards,
Andrew Cherednik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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


Yes, they are connecting to the same database over the network.


Then SQLite may not be a very good choice - see 
http://sqlite.org/whentouse.html , "Client/Server Applications" section.



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


Are you resetting your prepared 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 reader, and 
then wants to upgrade to a writer)? This situation may lead to a 
deadlock. If you have such transactions, start them with BEGIN IMMEDIATE 
statement.

--
Igor Tandetnik

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

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

[Keith Medcalf]

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 not make a difference.  THe transactions (which include 
selects which will be exclusive) are not being finalized/commit'd expeditiously 
enough.

You would still need to expect and handle BUSY/LOCK conditions however as there 
is certainty that two such clients will attempt to access the database at the 
same instant. 

---
()  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] Advice on the proper use of sqlite3 - Database Connection Handle

[Andrew Cherednik]

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 same database over the network.

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


Regards,
Andrew Cherednik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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* object
that has been used for database updates still remains in the
'exclusive lock' mode even when the program is doing normal 'selects'.


It that's what happens, it likely means you are forgetting to
sqlite3_reset or sqlite3_finalize prepared statements after executing them.


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?


What exactly do you mean by "database lockout problems"? What error in 
what API call are you getting?

--
Igor Tandetnik

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

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

[Igor Tandetnik]

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 updates still remains in the 'exclusive lock' mode even when the 
program is doing normal 'selects'.


It that's what happens, it likely means you are forgetting to 
sqlite3_reset or sqlite3_finalize prepared statements after executing them.



Therefore, before I proceed with the task, I wanted to ask what is the right 
way to use the sqlite3* object.


Yes, it is perfectly fine to use a single database connection.


Will I add an overhead by opening and closing the database connection for each 
database operation?


Yes - an overhead proportional to the size of database schema. SQLite 
reads and parses all table definitions, triggers and such when opening a 
connection.

--
Igor Tandetnik

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

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

[Simon Slavin]

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* object that has been used 
> for database updates still remains in the 'exclusive lock' mode even when the 
> program is doing normal 'selects'.

Which SQLite result code are you getting ?  Have you tried a SQLite timeout 
setting of, perhaps, three seconds ?

> I am thinking of converting the program, so that sqlite* objects are created 
> at the beginning of each 'database operation' and destroyed straight away. As 
> you can imagine, there are hundreds of lines of code that refer to the global 
> g_session object, and the task would be time consuming. Therefore, before I 
> proceed with the task, I wanted to ask what is the right way to use the 
> sqlite3* object. Is it better to use a global or a local connection object? 
> Will I add an overhead by opening and closing the database connection for 
> each database operation?

I hope a C++ programmer (which I'm not) can answer that.

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

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

[Andrew Cherednik]

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 data accessors, 
and now they all are sqlite3 interfaces. OK, this is the introduction.

Now, to the problem itself.

The program has a global object called g_session. It used to be an ATL 
CSession-derived class that instantiated an OLE-DB connection at the program 
startup and closed the connection at the program end. I have converted the 
class, so it contains a sqlite3* member that gets opened using sqlite3_open_v2 
at the program startup and closed using sqlite3_close at the end. As you can 
imagine, this global g_session object is used everywhere in the program for all 
the selects, updates, inserts and deletes.

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 updates still remains in the 'exclusive lock' mode even when the 
program is doing normal 'selects'.

I am thinking of converting the program, so that sqlite* objects are created at 
the beginning of each 'database operation' and destroyed straight away. As you 
can imagine, there are hundreds of lines of code that refer to the global 
g_session object, and the task would be time consuming. Therefore, before I 
proceed with the task, I wanted to ask what is the right way to use the 
sqlite3* object. Is it better to use a global or a local connection object? 
Will I add an overhead by opening and closing the database connection for each 
database operation?

Regards,
Andrew Cherednik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

[Keith Medcalf]

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 :  00:00:03.421

D:\Temp\Test>timethis custom\sqlite3 database.sqlite < query.sql

TimeThis :  Command Line :  custom\sqlite3 database.sqlite
TimeThis :Start Time :  Thu May 24 18:42:59 2012
TimeThis :  End Time :  Thu May 24 18:43:06 2012
TimeThis :  Elapsed Time :  00:00:07.078

D:\Temp\Test>timethis sqlite database.sqlite < query.sql

TimeThis :  Command Line :  sqlite database.sqlite
TimeThis :Start Time :  Thu May 24 18:43:09 2012
TimeThis :  End Time :  Thu May 24 18:43:13 2012
TimeThis :  Elapsed Time :  00:00:03.609

This latter is my build of the command line tools which is 800K.

This is on a 2Ghz AMD Turion.

---
()  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 Jonas Malaco Filho
> Sent: Thursday, 24 May, 2012 18:15
> To: General Discussion of SQLite Database
> Subject: 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 me (x64): ~1s (CPU Time: user 0.904806 sys 0.218401)
> 
> My SQLite compilation options differ from the default values (so this does
> not address the weird behavior you described), but maybe it can still be
> useful for you:
> 
>- x86: /DSQLITE_DEFAULT_CACHE_SIZE=2000 /DSQLITE_DEFAULT_FOREIGN_KEYS=1
>/DSQLITE_DEFAULT_MEMSTATUS=0 /DSQLITE_DEFAULT_PAGE_SIZE=4096
>/DSQLITE_DEFAULT_TEMP_CACHE_SIZE=2000 /DSQLITE_THREADSAFE=0
>/DSQLITE_TEMP_STORE=2 /DSQLITE_ENABLE_FTS3
> /DSQLITE_ENABLE_FTS3_PARENTHESIS
>/DSQLITE_ENABLE_FTS4 /DSQLITE_ENABLE_RTREE /DSQLITE_ENABLE_STAT3
>- x64: /DSQLITE_DEFAULT_CACHE_SIZE=4000 /DSQLITE_DEFAULT_FOREIGN_KEYS=1
>/DSQLITE_DEFAULT_MEMSTATUS=0 /DSQLITE_DEFAULT_PAGE_SIZE=8192
>/DSQLITE_DEFAULT_TEMP_CACHE_SIZE=4000 /DSQLITE_THREADSAFE=0
>/DSQLITE_TEMP_STORE=2 /DSQLITE_ENABLE_FTS3
> /DSQLITE_ENABLE_FTS3_PARENTHESIS
>/DSQLITE_ENABLE_FTS4 /DSQLITE_ENABLE_RTREE /DSQLITE_ENABLE_STAT3
> 
> Both use:
> 
>- Full Optimization (/Ox)
>- (Inlining) Any Suitable (/Ob2)
>- (Enable Intrinsic Functions) Yes (/Oi)
>- Favor fast code (/Ot)
>- Use MFC in a Static Library
> 
> Everything is always in my Dropbox:
> https://www.dropbox.com/sh/ecyzbn5kz4nf7ft/JHGD62iezx
> 
> 
> 
> *Jonas Malaco Filho*
> 
> 
> 
> 2012/5/24 Simon Slavin 
> 
> >
> > 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 code.
> >
> > Attachments don't work on this list (we don't want everyone sending us
> > their homework) but I hope the information you've provided gives someone a
> > hint about what's going on.
> >
> > 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



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

Re: [sqlite] SQLite SELECT performance problem

[Keith Medcalf]

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 routines devoid of extra spinlocks, critical sections, and mutexes 
(ie, to get the same performance as you would get linking with the standard 
LIBC.LIB single-threaded library).

Generated code is still of "Microsoft Quality" though (if you want proper 
instruction scheduling and optimization that actual optimizes anything, then 
use a real compiler, such as gcc or watcom).  I only use MSVC in order to 
integrate the runtimes with other things which use the MS compilers, not 
because they are of any worth.

Also, depending on what the data looks like that you are averaging the 
sum/count average builtin to sqlite may not be very accurate.  You may want to 
do use a running approximation as it is computationally insensitive to the data 
values, ordering, and magnitudes.


typedef struct meanCtx meanCtx;
struct meanCtx
{
long double m;
sqlite_int64 n;
};

static void meanStep(sqlite3_context *context, int argc, sqlite3_value **argv)
{
meanCtx* p = sqlite3_aggregate_context(context, sizeof(meanCtx));
if (p && sqlite3_value_numeric_type(argv[0]) != SQLITE_NULL)
{
p->n++;
if (p->n == 1)
p->m = (long double)sqlite3_value_double(argv[0]);
else
p->m += ((long double)sqlite3_value_double(argv[0]) - p->m) / (long 
double)p->n;
}
}

static void meanFinal(sqlite3_context *context)
{
meanCtx* p = sqlite3_aggregate_context(context, 0);
if (p && p->n > 0)
sqlite3_result_double(context, p->m);
}

You can look at http://www.dessus.com/files/sqlite.zip which is a zip of my 
build directory that has an autoload extension (SQLFunc.c) compiled in that 
adds various running statistical aggregates (I make no warranty as to 
correctness by the way -- if you find there is an error please let me know -- 
my brother is the mathematician, not me).  

All aggregates use successive approximation series computation methods.   Also 
includes some useful Windoze Security APIs (get username, check security 
groups, etc) and a hack-in of the test_spellfix1.c module which is also an 
autoloaded extension if you leave the /DINCL_SPELLFIX in the compiler options.  
I'm going to replace that with unifuzz.c recently mentioned since I like that 
better, and extend the selection process and autoinit process.  Eventually I'll 
make a proper makefile.  It has all the options recognized in the amalgamation 
code included.

No warranties expressed or implied.  If it breaks or you break it then you own 
both halves.

---
()  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 Richard Hipp
> Sent: Thursday, 24 May, 2012 14:10
> To: General Discussion of SQLite Database
> Subject: 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-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] SQLite SELECT performance problem

[Jonas Malaco Filho]

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 me (x64): ~1s (CPU Time: user 0.904806 sys 0.218401)

My SQLite compilation options differ from the default values (so this does
not address the weird behavior you described), but maybe it can still be
useful for you:

   - x86: /DSQLITE_DEFAULT_CACHE_SIZE=2000 /DSQLITE_DEFAULT_FOREIGN_KEYS=1
   /DSQLITE_DEFAULT_MEMSTATUS=0 /DSQLITE_DEFAULT_PAGE_SIZE=4096
   /DSQLITE_DEFAULT_TEMP_CACHE_SIZE=2000 /DSQLITE_THREADSAFE=0
   /DSQLITE_TEMP_STORE=2 /DSQLITE_ENABLE_FTS3 /DSQLITE_ENABLE_FTS3_PARENTHESIS
   /DSQLITE_ENABLE_FTS4 /DSQLITE_ENABLE_RTREE /DSQLITE_ENABLE_STAT3
   - x64: /DSQLITE_DEFAULT_CACHE_SIZE=4000 /DSQLITE_DEFAULT_FOREIGN_KEYS=1
   /DSQLITE_DEFAULT_MEMSTATUS=0 /DSQLITE_DEFAULT_PAGE_SIZE=8192
   /DSQLITE_DEFAULT_TEMP_CACHE_SIZE=4000 /DSQLITE_THREADSAFE=0
   /DSQLITE_TEMP_STORE=2 /DSQLITE_ENABLE_FTS3 /DSQLITE_ENABLE_FTS3_PARENTHESIS
   /DSQLITE_ENABLE_FTS4 /DSQLITE_ENABLE_RTREE /DSQLITE_ENABLE_STAT3

Both use:

   - Full Optimization (/Ox)
   - (Inlining) Any Suitable (/Ob2)
   - (Enable Intrinsic Functions) Yes (/Oi)
   - Favor fast code (/Ot)
   - Use MFC in a Static Library

Everything is always in my Dropbox:
https://www.dropbox.com/sh/ecyzbn5kz4nf7ft/JHGD62iezx



*Jonas Malaco Filho*



2012/5/24 Simon Slavin 

>
> 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 code.
>
> Attachments don't work on this list (we don't want everyone sending us
> their homework) but I hope the information you've provided gives someone a
> hint about what's going on.
>
> 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] SQLite SELECT performance problem

[Simon Slavin]

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

Attachments don't work on this list (we don't want everyone sending us their 
homework) but I hope the information you've provided gives someone a hint about 
what's going on.

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

Re: [sqlite] SQLite SELECT performance problem

[Nick]

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
both executables). I hope you can see the difference in the speed:

http://dl.dropbox.com/u/74970714/Custom%20built%20SQLite3.exe%20Performance%20problem.rar

There is also a database file. but it is quite large (71 Mb). You can
download it here, to be able to test the SQL query:

http://dl.dropbox.com/u/74970714/database.sqlite

Thanks to anyone who can help!

2012/5/25 Simon Slavin :
>
> 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.
>>
>> Then I downloaded sqlite3.c, sqlite3.h and shell.c, compiled them
>> altogether (and got again the command line tool, but now i've built it
>> by myself using Visual Studio) and executed the same query. It took 15
>> seconds this time.
>
> I'm very sorry I missed that.  I have now re-read your original post.
>
> Can you compare the size of the two executable files for us ?  Are the 
> various versions you're compiling (I understand you've tried several 
> different compilation options) all definitely bigger than the one supplied on 
> the SQLite site ?  It might give us something to investigate.  Also, I don't 
> know how to do this under Windows, but do you have a way to check whether the 
> versions made by Visual Studio address any DLLs or other libraries ?
>
> 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] SQLite SELECT performance problem

[Simon Slavin]

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.
> 
> Then I downloaded sqlite3.c, sqlite3.h and shell.c, compiled them
> altogether (and got again the command line tool, but now i've built it
> by myself using Visual Studio) and executed the same query. It took 15
> seconds this time.

I'm very sorry I missed that.  I have now re-read your original post.

Can you compare the size of the two executable files for us ?  Are the various 
versions you're compiling (I understand you've tried several different 
compilation options) all definitely bigger than the one supplied on the SQLite 
site ?  It might give us something to investigate.  Also, I don't know how to 
do this under Windows, but do you have a way to check whether the versions made 
by Visual Studio address any DLLs or other libraries ?

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

Re: [sqlite] SQLite SELECT performance problem

[Nick]

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 Slavin 
>
>>
>> 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 figuring out the
>> parameters in the command.  Purely out of interest, and not because I know
>> it's faster, have you tried executing the query using sqlite3_exec() rather
>> than _prepare(), _step(), _finalize() ?  Don't do a lot of reworking of
>> your code unless it turns out to be faster.
>>
>> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

[Nick]

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
altogether (and got again the command line tool, but now i've built it
by myself using Visual Studio) and executed the same query. It took 15
seconds this time.

I can't understand why.. I set the code optimization level to "max" in
the Visual Studio's Settings.
I also executed in the original (downloaded from sqlite.org)
sqlite3.exe a command:

  pragma compile_options;

and made sure all these options (#defines) were set in my own built of
sqlite3.exe



2012/5/25 Simon Slavin :
>
> 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 figuring out the 
> parameters in the command.  Purely out of interest, and not because I know 
> it's faster, have you tried executing the query using sqlite3_exec() rather 
> than _prepare(), _step(), _finalize() ?  Don't do a lot of reworking of your 
> code unless it turns out to be faster.
>
> 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] SQLite SELECT performance problem

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

>
> 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 figuring out the
> parameters in the command.  Purely out of interest, and not because I know
> it's faster, have you tried executing the query using sqlite3_exec() rather
> than _prepare(), _step(), _finalize() ?  Don't do a lot of reworking of
> your code unless it turns out to be faster.
>
> 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] SQLite SELECT performance problem

[Simon Slavin]

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 figuring out the 
parameters in the command.  Purely out of interest, and not because I know it's 
faster, have you tried executing the query using sqlite3_exec() rather than 
_prepare(), _step(), _finalize() ?  Don't do a lot of reworking of your code 
unless it turns out to be faster.

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

Re: [sqlite] SQLite SELECT performance problem

[Nick]

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, 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-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] SQLite SELECT performance problem

[Richard Hipp]

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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite SELECT performance problem

[Nick]

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. Then it returns the average value for each of these
groups.

The query looks like this:

SELECT strftime('%s',Min(Stamp)) AS DateTimeStamp,
AVG(P) AS MeasuredValue,
((100 * (strftime('%s', [Stamp]) - 1334580095)) /
(1336504574 - 1334580095)) AS SubIntervalNumber
FROM LogValues
WHERE ((DeviceID=1) AND (Stamp >=  datetime(1334580095, 'unixepoch')) AND
(Stamp <= datetime(1336504574, 'unixepoch')))
GROUP BY ((100 * (strftime('%s', [Stamp]) - 1334580095)) /
(1336504574 - 1334580095)) ORDER BY MIN(Stamp)

The numbers in this request are substituted by my application with
some values. I don't know if i can optimize this request more (if
anyone could help me to do so, i'd really appreciate)..

This SQL query can be executed using an SQLite command line shell
(sqlite3.exe). On my Intel Core i5 machine it takes 4 seconds to
complete (there are 10 records in the database that are being
processed).

Now, if i write a C program, using sqlite.h C interface, I am waiting
for 14 seconds for exactly the same query to complete. This C program
"waits" during these 14 seconds on the first sqlite3_step() function
call (any following sqlite3_step() calls are executed immediately).

>From the Sqlite download page I have downloaded SQLite command line
shell's source code and build it using Visual Studio 2008. I ran it
and executed the query. Again 14 seconds.

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 am running Windows 64 bit. The prebuilt tool is an x86 process. It
also does not seem to be multicore optimized - in a Task Manager,
during query execution, I can see only one core busy, for both
built-by-mine and prebuilt SQLite shells.
I have tried different Visual Studio's optimization options, tried to
match "Pragma compile_options;" output by defining preprocessor
directives in sqlite3.c file to output generated by downloaded
sqlite3.exe file. To no avail.

Any way I could make my C program execute this query as fast as the
prebuilt command line tool does it?

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

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

[Joe Mistachkin]

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

--
Joe Mistachkin

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

[Richard Hipp]

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?
> >
> > 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 SQLite) is trying to convey.
>
> It means "We make both the entire SQLite source code (many different .c
> and .h files) and the amalgamation (one .c file, one .h file) available for
> download.  Use the amalgamation version in your project.".
>
> The many-file sourcecode files are for debugging, figuring out how SQLite
> works, and for the team developing SQLite.  You might need them if you're
> making your own custom changes to SQLite to make it do something
> non-standard.  But for mundane use, just use the amalgamation.
>
> The above is an unofficial informal translation.
>

I approve of your translation.



>
> Simon.
> ___
> 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] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

[Simon Slavin]

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 what idea this particular statement on this
> particular help-page (specific to SQLite) is trying to convey.

It means "We make both the entire SQLite source code (many different .c and .h 
files) and the amalgamation (one .c file, one .h file) available for download.  
Use the amalgamation version in your project.".

The many-file sourcecode files are for debugging, figuring out how SQLite 
works, and for the team developing SQLite.  You might need them if you're 
making your own custom changes to SQLite to make it do something non-standard.  
But for mundane use, just use the amalgamation.

The above is an unofficial informal translation.

Simon.
___
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?

[Sidney Cadot]

> 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 discussion about
what this particular phrase means, with a friend and fellow
programmer. The two interpretations I propose reflect our different
readings of that particular statement.

I do feel that a statement that is on a generic help page should be
unambiguous, and not allow different interpretations. Is it known who
wrote this particular page, and does he perhaps follow this mailing
list? I'd be curious to ask him about the intended meaning. Perhaps
the wording as given could be made more explicit.
___
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?

[Marc L. Allen]

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 Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Sidney Cadot
Sent: Thursday, May 24, 2012 1:56 PM
To: General Discussion of SQLite Database
Subject: 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 SQLite) is trying to convey.
___
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] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

[Sidney Cadot]

> 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 SQLite) is trying to convey.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP BY question

[Mike King]

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?

[Sidney Cadot]

> 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-making the package will link against newer versions of sqlite as
they come available, at zero effort. That is a reason.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP BY question

[Petite Abeille]

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?

http://sqlite.org/lang_select.html#resultset

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

Re: [sqlite] GROUP BY question

[Pavel Ivanov]

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 (2,3);
>
> select A,B
> from T
> group by B;

For SQLite this statement is valid but behavior is undefined - you can
get either 1 or 2 for A without any determinism.


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

Re: [sqlite] GROUP BY question

[Richard Hipp]

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 (2,3);
>
> select A,B
> from T
> group by B;
>

This is equivalent to the PostgreSQL:

SELECT DISTINCT ON(b) a,b FROM t;

There have been numerous complaints about the syntax and the fact that it
does not generate an error like Oracle, but there are also many legacy
applications that depend on this behavior.  So we cannot change it without
breaking applications and causing general grief and woe amongst developers.


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

[sqlite] GROUP BY question

[Mike King]

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;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Igor Tandetnik]

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
2012-05-01 13:12:11.103  Null   4356
2012-05-01 13:12:07.103  Null   14356
.
2012-05-01  13:12:11.221 Null14356

I want to update the latency field for each record with the latest
previous timestamp for the same DeviceID.


update MyTable set latency = (
  select max(t2.timestamp) from MyTable t2
  where t2.DeviceId = MyTable.DeviceId and t2.timestamp < 
MyTable.timestamp);


--
Igor Tandetnik

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

[Larry Brasfield]

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 file
>> as the preferred way of including SQLite functionality in one's
>> application, rather than using a separately compiled library?
>>
>> Or should I read this as a recommendation just in case I need to
>> compile SQLite from source, and need to decide between using the
>> amalgamation or using the individual source files?
>
>  From the context of the recommendation, one can clearly infer that it
> addresses only how SQLite source is presented to the compiler. Whether
> the compiler's output is embedded in its own object file, a DLL, or
> directly into a calling program is not addressed.
>

The statement above is accurate.

But at the same time, it is a general recommendation that
you just pull sqlite3.c into your project instead of messing
around with shared libraries and so on.

Why risk ending up with an unexpected (possibly old) version
by linking at runtime just to save users less than 300K of disk
space?


I don't want to quibble about language here, but I see nothing at the 
quoted link about whether sqlite3.c should be used as a shared library, 
(or a static library).


For most projects, I expect that the recommendation you suggest makes 
sense.  However, the OP has indicated little that would allow us to 
guess whether his project should follow the norm or not.  There may well 
be issues other than disk space at stake.  For example, the .NET 
adaptation of SQLite benefits from being a shared library as this fits 
well into the "black box with well defined interface" usage model the 
.NET developers routinely use with a variety of packages.


Best regards,
--
Larry Brasfield
___
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?

[Dan Kennedy]

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 functionality in one's
application, rather than using a separately compiled library?

Or should I read this as a recommendation just in case I need to
compile SQLite from source, and need to decide between using the
amalgamation or using the individual source files?


 From the context of the recommendation, one can clearly infer that it
addresses only how SQLite source is presented to the compiler. Whether
the compiler's output is embedded in its own object file, a DLL, or
directly into a calling program is not addressed.



The statement above is accurate.

But at the same time, it is a general recommendation that
you just pull sqlite3.c into your project instead of messing
around with shared libraries and so on.

Why risk ending up with an unexpected (possibly old) version
by linking at runtime just to save users less than 300K of disk
space?



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

[Simon Slavin]

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 preferred way of including SQLite functionality in one's
> application, rather than using a separately compiled library?

Yes.  SQLite is so small there's really no reason to make a separate library of 
it.  And using libraries causes problems when different apps on your computer 
try to use different versions of SQLite.

> Or should I read this as a recommendation just in case I need to
> compile SQLite from source, and need to decide between using the
> amalgamation or using the individual source files?

Whatever you're compiling, a stand-alone app or a library, use the amalgamation 
rather than the complicated mass of separate source files.  The source files 
are very useful when trying to understand how SQLite works, but the 
amalgamation is better when compiling.

Simon.
___
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?

[Larry Brasfield]

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 than using a separately compiled library?

Or should I read this as a recommendation just in case I need to
compile SQLite from source, and need to decide between using the
amalgamation or using the individual source files?


From the context of the recommendation, one can clearly infer that it 
addresses only how SQLite source is presented to the compiler.  Whether 
the compiler's output is embedded in its own object file, a DLL, or 
directly into a calling program is not addressed.


--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[IQuant]

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 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
2012-05-01 13:12:11.103  Null   4356
2012-05-01 13:12:07.103  Null   14356

2012-05-01  13:12:11.221 Null14356

The data is collected inconsistently...  many times out of sequence

I want to update the latency field for each record with the latest
previous timestamp for the same DeviceID.

Run this update query every time new data is appended to the table.
Periodically recalc the entire table if an out of sequence file is
processed or at minimum recalc from the earliest out of sequence time
to present.

I can do this fairly easily in excel but am struggling in sql/sqlite.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Sidney Cadot]

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
application, rather than using a separately compiled library?

Or should I read this as a recommendation just in case I need to
compile SQLite from source, and need to decide between using the
amalgamation or using the individual source files?


Kind regards,
  Sidney
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible memory leaks on sqlite3_close

[Stephan Beal]

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 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 possibly lost in loss record 7 of 28
> ==9709==at 0x4C2779D: malloc (in
> /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
> ==9709==by 0x437230: sqlite3MemMalloc (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x437C47: mallocWithAlarm (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x437CE2: sqlite3Malloc (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x437D30: sqlite3_malloc (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x43E1BC: findInodeInfo (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x4405CC: fillInUnixFile (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x440EA5: unixOpen (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x436D1D: sqlite3OsOpen (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x448292: sqlite3PagerOpen (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x4509F6: sqlite3BtreeOpen (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x4B44E4: openDatabase (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==
> ==9709== 94,168 (840 direct, 93,328 indirect) bytes in 1 blocks are
> definitely lost in loss record 28 of 28
> ==9709==at 0x4C2779D: malloc (in
> /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
> ==9709==by 0x437230: sqlite3MemMalloc (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x437C47: mallocWithAlarm (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x437CE2: sqlite3Malloc (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x438351: sqlite3MallocZero (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x4B41DD: openDatabase (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x4B4735: sqlite3_open_v2 (in
> /home/huji/undev/vhs/Sondes/unix/sondes.exe)
> ==9709==by 0x409792: main (main.c:191)
> ==9709==
>
> The seconde seems to come from sqlite3 *db, which should be entirely freed
> in sqlite3_close().
> For the first one, I didn't inspect.
>
> Alfred.
> ___
> 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

[sqlite] Possible memory leaks on sqlite3_close

[Alfred Sawaya]

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 possibly lost in loss record 7 of 28
==9709==at 0x4C2779D: malloc (in
/usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
==9709==by 0x437230: sqlite3MemMalloc (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x437C47: mallocWithAlarm (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x437CE2: sqlite3Malloc (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x437D30: sqlite3_malloc (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x43E1BC: findInodeInfo (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x4405CC: fillInUnixFile (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x440EA5: unixOpen (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x436D1D: sqlite3OsOpen (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x448292: sqlite3PagerOpen (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x4509F6: sqlite3BtreeOpen (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x4B44E4: openDatabase (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==
==9709== 94,168 (840 direct, 93,328 indirect) bytes in 1 blocks are
definitely lost in loss record 28 of 28
==9709==at 0x4C2779D: malloc (in
/usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
==9709==by 0x437230: sqlite3MemMalloc (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x437C47: mallocWithAlarm (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x437CE2: sqlite3Malloc (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x438351: sqlite3MallocZero (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x4B41DD: openDatabase (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x4B4735: sqlite3_open_v2 (in
/home/huji/undev/vhs/Sondes/unix/sondes.exe)
==9709==by 0x409792: main (main.c:191)
==9709==

The seconde seems to come from sqlite3 *db, which should be entirely freed
in sqlite3_close().
For the first one, I didn't inspect.

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

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

[Damien]

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 of this feature or requirement or whatever
> this is.
> > This statement seems to be saying that the "System.Data.SQLite
> managed-only assembly"
> > is different from the "System.Data.SQLite assembly".  Is that true?  When
> I download
> > a new version of the System.Data.SQLite installation package, how will I
> tell the
> > difference between the two?
> >
>
> The download page for System.Data.SQLite refers to packages that contain
> one
> of the two
> kinds of System.Data.SQLite assemblies:
>
> 1. The "managed-only assembly", which contains only managed (CLR) code and
> uses the
>   SQLite.Interop.dll native library.
>
> 2. The "mixed-mode assembly", which contains native (x86 -OR- x64) and
> managed (CLR)
>   code.  This assembly does not use the SQLite.Interop.dll native library
> because all
>   that code is built into the mixed-mode assembly itself.
>
> The FAQ also contains some useful information on mixed-mode assemblies:
>
>https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki
>
> --
> Joe Mistachkin
>
> ___
> 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] SQLite reading old data

[Fabrizio Steiner]

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 
documentation of the SQLite Encryption Extension API, to get informations if 
it's allowed to encrypt the first page or if it needs to be excluded?

Kind Regards
Fabrizio

> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] Im Auftrag von Fabrizio Steiner
> Gesendet: Freitag, 16. Dezember 2011 15:49
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] SQLite reading old data
> 
> Yeah, I see the same two solutions. The first solution would also ensure
> currently encrypted databases will still be working. Whereas changing the
> encryption would make live a lot harder for currently deployed databases,
> which would need a conversion.
> 
> For testing purposes I just changed the encryption algorithm in
> System.Data.SQLite to exclude the change counter, so the change counter
> remains plaintext. My tests with the 3 threads did run fine and without any
> problems for several runs I've executed.
> 
> It would be great if there's a statement from the SQLite developers what the
> requirements for the encryptor are. And what their opinion is regarding this
> issue.
> 
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] Im Auftrag von Michael Stephenson
> > Gesendet: Freitag, 16. Dezember 2011 15:27
> > An: 'General Discussion of SQLite Database'
> > Betreff: Re: [sqlite] SQLite reading old data
> >
> > I think the only solutions would be:  1) SQLIte changes so that it
> > does not use a direct file read/write for the change counter.  2) Have
> > the page-level encryptor specifically not encrypt certain parts of the
> > database header, such as the change counter, when saving page 1 to disk.
> >
> > I imagine that the direct reads/writes are to help ensure consistency
> > and recovery in the face of an application crash.
> >
> > You might try turning on WAL and see what happens.
> >
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org
> > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Fabrizio Steiner
> > Sent: Thursday, December 15, 2011 3:00 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] SQLite reading old data
> >
> > The answers to your questions :
> > 1)Yes I'm still using System.Data.SQLite but without any key.
> > 2)No I don't use WAL.
> > 3)Running only one thread didn't bring up the problem.
> > 4)Didn't have any time yet.
> > 5)Standard System.Data.SQLite with RC4 algorithm impleemnted there.
> >
> > I've taken a look into the the caching of SQLite during the afternoon
> > because I suspected some issues with caching. And I found a problem
> > with the change counter exactly as you thought.
> >
> > I took a look into the source code of the pager and the pager cache.
> > I've found the pager cache decides if the cache needs to be flushed
> > whenever a shared lock gets requested. In order to determine if a
> > flush is needed or not, the change counter of the db file is compared
> > to the stored one in the pager structure. The change counter is
> > directly retrieved from the underlying OS (sqlite3OsRead), so if the
> > database is encrypted, the encrypted bytes will be retrieved here
> > (Sytem.Data.SQLite encrypts the entire database, including page 1 and the
> SQLite header).
> >
> > So dbFileVers of the pager structure is always the encrypted value. I
> > started checking if there was a situation where it was used as the plaintext
> value.
> >
> > In the function pager_write_changecounter the change counter will get
> > incremented, but the encrypted value pPg->pPager->dbFileVer is used.
> > After incrementation the value will be put back into the page buffer.
> > During the write of the page 1, the is again encrypted (see,
> pager_write_pagelist).
> > After the page has been written the dbFileVers gets updated
> > (pager_write_pagelist Line 4049) with the encrypted value.
> >
> > So at least for incrementing the change counter the plaintext value
> > should be used.
> >
> > When RC4 with the full database encryption (as implemented in
> > System.Data.SQLite) is used the following happens.
> > RC4 basically XORs a plaintext byte with a pseudo random byte. Let's
> > assume the following change counter values, for simplicity consider
> > only the last 4 bits of it. As we've seen the encrypted change counter
> > is incremented, encrypted and stored back into the file. Let's
> > consider the following operations.
> >
> > DB Initial State
> > - Encrypted DB File counter:  X   Y   Z
> > 0(The bits X, Y, Z are unknown, but the last bit is 0.)
> >
> > First update of