Re: [sqlite] trying to build from sqlite-3.6.7 on ubuntu 11.04, missing sqlite3.c

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

>   after a quick inspection, i can see (i think) that part of the
> configuration and build process is to *create* the sqlite3.c source
> file to be used as part of the compilation, is that correct?

Yes as Pavel mentioned.  The single file is known as the amalgamation and
documented here:

  http://www.sqlite.org/amalgamation.html

Note that embedded folk often compile out various SQLite features.  If you
need to do that then you have to work with the original source files as
omissions require regenerating the grammar etc.  An amalgamation can still
be produced from the result.  More doc:

  http://www.sqlite.org/compile.html#omitfeatures

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk4veH4ACgkQmOOfHg372QR69wCeLrfZV9rNEAewZ8a8nATwplfn
tQAAn3RlbthEpLoS6u7wU1DFWYVoYtx+
=Lm7Z
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PRAGMA temp_store_directory not releasing resources

[Josh Gibbs]

We've just introduced some memory leak detection into our
code and have discovered that this pragma call is not having its
resources cleaned up at shutdown.  It's not a critical leak since
it's only called once at program start, but it would be nice to
have the system report zero memory leaks on exit.

The following code will reproduce the leak:

 sqlite3 *pdb;
 int res = sqlite3_open("c:/temp/memleak.db", );
 if( res == SQLITE_OK )
 {
 sqlite3_exec(pdb, "PRAGMA temp_store_directory = 'c:/temp/';", 
NULL, NULL, NULL);
 sqlite3_close(pdb);
 }


Any ideas why this might be occurring, or is it an sqlite bug?

We're using 3.7.5 amalgamation on Win32 in this instance.


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

Re: [sqlite] trying to build from sqlite-3.6.7 on ubuntu 11.04, missing sqlite3.c

[Pavel Ivanov]

>  as part of an embedded system build, i'm building a number of tools
> for the *host* system, including sqlite-3.6.7 from the tarball (along
> with a few patches which i will be examining shortly).

Apparently you are building from canonical sources. Why don't you use
amalgamation? It's much easier to build. If you still have to use
canonical sources then maybe you don't have TCL installed? It's needed
to build sqlite3.c.


Pavel


On Tue, Jul 26, 2011 at 6:38 AM, Robert P. J. Day  wrote:
>
>  not sure if this is the appropriate forum for this but i'm hoping
> someone can give me some useful pointers.
>
>  as part of an embedded system build, i'm building a number of tools
> for the *host* system, including sqlite-3.6.7 from the tarball (along
> with a few patches which i will be examining shortly).
>
>  the sqlite build is part of a much larger build, and when this
> process gets around to unpacking, configuring and compiling sqlite3,
> it fails with:
>
> ---
>  Compiling:  sqlite
> ---
> remake[4]: *** No rule to make target `sqlite3.c', needed by
> `sqlite3.lo'.  Stop.
>
>  after a quick inspection, i can see (i think) that part of the
> configuration and build process is to *create* the sqlite3.c source
> file to be used as part of the compilation, is that correct?
>
>  so what is the probable reason that that file wouldn't be created?
> i still have a fair bit of examination left to understand how this
> entire build system works but this is the issue that's stopping me
> from getting any further.
>
>  this is being done on a fully-updated ubuntu 11.04 system, so i'm
> open to suggestions as to what make rule or shell script might be the
> culprit here.  thanks.
>
> rday
>
> --
>
> 
> Robert P. J. Day                                 Ottawa, Ontario, CANADA
>                        http://crashcourse.ca
>
> Twitter:                                       http://twitter.com/rpjday
> LinkedIn:                               http://ca.linkedin.com/in/rpjday
> 
> ___
> 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] Possibility to use a in-mmeory database with two sqlite handles/connections

[Pavel Ivanov]

> is there a way to create more then one sqlite connection hadles for the
> same in-memory database?

No.

> There was a proposal:
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg35438.html
> from Markus Lehmann.
> Is this a safe sollution?

I guess if it's not in the mainline SQLite it's not safe. If you want
a proof just do the hack and test your application.


Pavel


On Tue, Jul 26, 2011 at 4:00 PM, Maik Scholz  wrote:
> Hi,
> is there a way to create more then one sqlite connection hadles for the
> same in-memory database?
> I know that I could share the connection pointer, but I would prefer to
> to have differrent indipendent
> connections.
>
> There was a proposal:
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg35438.html
> from Markus Lehmann.
> Is this a safe sollution?
>
> Regards
>
> Maik
> ___
> 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] SELECT query first run is VERY slow

[Luuk]

On 26-07-2011 10:30, Григорий Григоренко wrote:
>>
>> I thought that, if you have an index on a,b,c,d
>> than you should not have an index on a,b,c too
>> because if you use those 3 field in the where-clause, use can be made of
>> the 4-field index
>>
> 
> I'm not sure. Let me explain.
> 
> I need query to be ORDER BY id DESC. I've dropped this ORDER BY to simplify 
> my case. But in real-life app I need it.
> 
> So, index on (kind, computer) has these index records:
> 
> [ KIND ] [ COMPUTER ] [ ID ]
> ...
> 
> They are ordered by kind, computer and then by id. So ORDER BY id DESC comes 
> free.
> Query planner just need to retrieve records from subset of index records 
> starting at last one backwards.
> 
> Index on (kind,computer, process,who) has these index records:
> 
> [ KIND ] [ COMPUTER ] [ PROCESS ] [ WHO ] [ ID ]
> 
> This time having found subset of index records query planner cannot start 
> retrieving them from the last to first.
> It has to sort them by ID at first. Isn't it?
> 
> 
> Maybe I'm not getting it right?

I'm not sure, but you might have a good point

I was looking at your db, and did this, which did lead me to the
suggestion i made earlier:
C:\TEMP>sqlite3.exe 2011-07-24.dblite
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
CREATE TABLE T(t);
CREATE TABLE item(name text,value text);
CREATE TABLE kind(id integer,kind text);
CREATE TABLE log(id integer primary key autoincrement,msg
text,created_at int,kind,computer,process,who);
CREATE UNIQUE INDEX idxitem_name_value ON item(name,value);
CREATE INDEX idxlog_created_at ON log(created_at);
CREATE INDEX idxlog_kind_computer_process_who ON
log(kind,computer,process,who);
sqlite>
sqlite> .read sql.txt
0|0|TABLE log WITH INDEX idxlog_kind_computer_process_who
2011-07-26 20:13:43.423
2011-07-26 20:13:43.438
sqlite>


sql.txt:
CREATE TABLE IF NOT EXISTS T(t); DELETE FROM T; INSERT INTO T VALUES(
strftime("%Y-%m-%d %H:%M:%f", "now") );

EXPLAIN QUERY PLAN SELECT * FROM log  WHERE kind = 'info' AND computer=1
and id > 7070636 LIMIT 100;

SELECT * FROM log  WHERE kind = 'info' AND computer=1 and id > 7070636
LIMIT 100;

INSERT INTO T VALUES( strftime("%Y-%m-%d %H:%M:%f", "now") );

SELECT * FROM T ;


And, YES, i did do a VACUUM and an ANALYZE before doing the query.

-- 
Luuk

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

[sqlite] Possibility to use a in-mmeory database with two sqlite handles/connections

[Maik Scholz]

Hi,
is there a way to create more then one sqlite connection hadles for the 
same in-memory database?
I know that I could share the connection pointer, but I would prefer to 
to have differrent indipendent
connections.

There was a proposal:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg35438.html
from Markus Lehmann.
Is this a safe sollution?

Regards

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

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

> 
> >
> > EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = 'three' and id > 290
> > LIMIT 10;
> >
> > SELECT * FROM foo WHERE bar = 'three' and id > 290 LIMIT 10;
> >
> >
> > Run query. Now using idx2 index SQLITE reads only about 20 Kbytes!
> >
> >
> >
> Grigory, it seems you just added a field and copied rowid column to it, so
> your fast query is equivalent to queries with general fields that works as
> expected (see my post about a,b,c fields). Id that is mapped to rowid is
> still has special treatment and not used effectively. This still has to be
> explained. But if your requirements to your base allows you to add extra
> field, I think you can use it, just support autoincrement feature some other
> way.
> 


Well, as I wrote it's workaround. One can use it to avoid massive reads until 
SQLITE behaviour is fixed.

I absolutely agree with you that this behaviour of SQLITE should be explained. 

Or considered as defect and fixed.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Simon Slavin]

On 26 Jul 2011, at 6:13pm, Black, Michael (IS) wrote:

> Part of the problem is it seems you can't create an index with rowid:
> 
> 
> 
> 3.7.5
> 
> sqlite> create table t(i int);
> sqlite> create index idx1 on t(i);
> sqlite> create index idx2 on t(i,rowid);
> Error: table t has no column named rowid
> 
> 
> 
> Any particular reason it can't be included in an index?

Because you didn't define such a column.  SQLite handles the various row 
aliases okay in an expression but not when you DEFINE things.  If you declare a 
column called 'rowid' it'll accept it in a CREATE command, even if it still 
just maps it to its internal 'id' INTEGER AUTOINCREMENT field.

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

Re: [sqlite] SELECT query first run is VERY slow

[Max Vlasov]

On Tue, Jul 26, 2011 at 8:50 PM, Григорий Григоренко wrote:

>
> EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = 'three' and id > 290
> LIMIT 10;
>
> SELECT * FROM foo WHERE bar = 'three' and id > 290 LIMIT 10;
>
>
> Run query. Now using idx2 index SQLITE reads only about 20 Kbytes!
>
>
>
Grigory, it seems you just added a field and copied rowid column to it, so
your fast query is equivalent to queries with general fields that works as
expected (see my post about a,b,c fields). Id that is mapped to rowid is
still has special treatment and not used effectively. This still has to be
explained. But if your requirements to your base allows you to add extra
field, I think you can use it, just support autoincrement feature some other
way.

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

Re: [sqlite] SELECT query first run is VERY slow

[Black, Michael (IS)]

Part of the problem is it seems you can't create an index with rowid:



3.7.5

sqlite> create table t(i int);
sqlite> create index idx1 on t(i);
sqlite> create index idx2 on t(i,rowid);
Error: table t has no column named rowid



Any particular reason it can't be included in an index?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Tuesday, July 26, 2011 11:50 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite]SELECT query first run is VERY slow

I found some kind of workaround to solve this problem.

Create new database and run:


CREATE TABLE foo(bar);

INSERT INTO foo VALUES(null);  INSERT INTO foo VALUES(null);  INSERT INTO foo 
VALUES(null);

BEGIN;

INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; 
INSERT INTO foo SELECT bar FROM foo;
INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; 
INSERT INTO foo SELECT bar FROM foo;
INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; 
INSERT INTO foo SELECT bar FROM foo;
INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; 
INSERT INTO foo SELECT bar FROM foo;
INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; 
INSERT INTO foo SELECT bar FROM foo;
INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; 
INSERT INTO foo SELECT bar FROM foo;
INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo;

COMMIT;

DELETE FROM foo WHERE rowid > 300;

UPDATE foo SET bar = 'one' WHERE rowid <= 100;
UPDATE foo SET bar = 'two' WHERE rowid > 100 AND rowid < 200;
UPDATE foo SET bar = 'three' WHERE rowid > 200;

CREATE INDEX idx1 ON foo(bar);


This creates foo table with 3 mln records: 1 mln with bar = 'one', 1 mln with 
bar = 'two' and 1 mln with bar = 'three'.


Now, the query.

EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = 'three' and rowid > 290 
LIMIT 10;

SELECT * FROM foo WHERE bar = 'three' and rowid > 290 LIMIT 10;


While running this query SQLITE  reads 18 Mbytes (and it uses idx1 index).

18 MBYTES.


Now, run this:

ALTER TABLE foo ADD COLUMN id; UPDATE foo SET id = rowid; CREATE INDEX idx2 ON 
foo(bar, id);


And update query: change rowid to id:

EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = 'three' and id > 290 LIMIT 
10;

SELECT * FROM foo WHERE bar = 'three' and id > 290 LIMIT 10;


Run query. Now using idx2 index SQLITE reads only about 20 Kbytes!


20 KBYTES.




This script proves that using rowid in index is possible and eliminates the 
need of reading lots of data.

But SQLITE is not using it.  I think this behaviour is SQLITE defect.










___
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] SELECT query first run is VERY slow

[Григорий Григоренко]

It's all about caching. 

If OS has already cached index data query executes in less than a second time.

To understand what is actually happening you should monitor reading count of 
SQLITE console process (or your app that is executing).

There shouldn't be reading of more than 1 Mb if SQLITE is using index properly.


26 июля 2011, 20:27 от "Black, Michael (IS)" :
> If I run your sql.txt script with the normalization of kind my first time 
> query shows 0 seconds.
> 
> D:\x>sqlite3 sq1  0|0|0|SEARCH TABLE log USING INDEX idxlog_kind_computer (kind=? AND 
> computer=?) (~406234 rows)
> Seconds elapsed: 0
> 
> 
> 
> Does yours show a longer time than that and/or a different plan?
> 
> 
> 
> 
> 
> Michael D. Black
> 
> Senior Scientist
> 
> NG Information Systems
> 
> Advanced Analytics Directorate
> 
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Григорий Григоренко [grigore...@mail.ru]
> Sent: Tuesday, July 26, 2011 8:16 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite]SELECT query first run is VERY slow
> 
> >
> > select count(*) as cnt,kind,computer
> > from log
> > group by kind,computer
> > order by cnt desc
> >
> > what happens here?
> >
> > SELECT *
> >  FROM log INDEXED BY idxlog_kind_computer
> >  WHERE kind = 'info' AND computer=1 and id > 7070636
> >  LIMIT 100;
> >
> > there are 3_022_148 identical entries 'info,1' in your index
> >
> > sqlite has to traverse near all of them and so it is not much help with
> > binary search.
> > Drop this index, run ANALYZE and sqlite will use your primary key quite
> > fast.
> 
> 
> The problem is: choosing primary key index is not always a good choice.
> Sometimes it can lead to scanning half of table records.
> 
> 
> Let's assume we have:
> 
> CREATE TABLE foo(bar);
> CREATE INDEX idx ON foo(bar);
> 
> INSERT INTO foo VALUES('one');
> .. 1 mln inserts on one..
> INSERT INTO foo VALUES('one');
> 
> INSERT INTO foo VALUES('two');
> .. 1 mln inserts of two..
> INSERT INTO foo VALUES('two');
> 
> INSERT INTO foo VALUES('three');
> .. 1 mln inserts of three..
> INSERT INTO foo VALUES('three');
> 
> Now, the query:
> 
> SELECT * FROM foo WHERE bar='three' and id > 1 LIMIT 10;
> 
> If query planner uses primary index it'll have to scan 2 mln records before 
> he hits first one matching query.
> 
> (speaking about "normal" settings, SQLITE scans from lesser rowid to greater)
> 
> And if query planner can somehow use index idx with both supported values { 
> bar:'three', id:1 } there will be no scanning.
> 
> It's binary search and it's what I need in my app.
> 
> 
> 
> ___
> 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] SELECT query first run is VERY slow

[Григорий Григоренко]

I found some kind of workaround to solve this problem.

Create new database and run:


CREATE TABLE foo(bar);

INSERT INTO foo VALUES(null);  INSERT INTO foo VALUES(null);  INSERT INTO foo 
VALUES(null); 

BEGIN;

INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; 
INSERT INTO foo SELECT bar FROM foo;
INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; 
INSERT INTO foo SELECT bar FROM foo;
INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; 
INSERT INTO foo SELECT bar FROM foo;
INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; 
INSERT INTO foo SELECT bar FROM foo;
INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; 
INSERT INTO foo SELECT bar FROM foo;
INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; 
INSERT INTO foo SELECT bar FROM foo;
INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo;

COMMIT;

DELETE FROM foo WHERE rowid > 300;

UPDATE foo SET bar = 'one' WHERE rowid <= 100;
UPDATE foo SET bar = 'two' WHERE rowid > 100 AND rowid < 200;
UPDATE foo SET bar = 'three' WHERE rowid > 200;

CREATE INDEX idx1 ON foo(bar);


This creates foo table with 3 mln records: 1 mln with bar = 'one', 1 mln with 
bar = 'two' and 1 mln with bar = 'three'.


Now, the query.

EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = 'three' and rowid > 290 
LIMIT 10;

SELECT * FROM foo WHERE bar = 'three' and rowid > 290 LIMIT 10;


While running this query SQLITE  reads 18 Mbytes (and it uses idx1 index).

18 MBYTES.


Now, run this:

ALTER TABLE foo ADD COLUMN id; UPDATE foo SET id = rowid; CREATE INDEX idx2 ON 
foo(bar, id);


And update query: change rowid to id:

EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = 'three' and id > 290 LIMIT 
10;

SELECT * FROM foo WHERE bar = 'three' and id > 290 LIMIT 10;


Run query. Now using idx2 index SQLITE reads only about 20 Kbytes!


20 KBYTES.




This script proves that using rowid in index is possible and eliminates the 
need of reading lots of data.

But SQLITE is not using it.  I think this behaviour is SQLITE defect. 










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

Re: [sqlite] SELECT query first run is VERY slow

[Black, Michael (IS)]

If I run your sql.txt script with the normalization of kind my first time query 
shows 0 seconds.

D:\x>sqlite3 sq1 
> select count(*) as cnt,kind,computer
> from log
> group by kind,computer
> order by cnt desc
>
> what happens here?
>
> SELECT *
>  FROM log INDEXED BY idxlog_kind_computer
>  WHERE kind = 'info' AND computer=1 and id > 7070636
>  LIMIT 100;
>
> there are 3_022_148 identical entries 'info,1' in your index
>
> sqlite has to traverse near all of them and so it is not much help with
> binary search.
> Drop this index, run ANALYZE and sqlite will use your primary key quite
> fast.


The problem is: choosing primary key index is not always a good choice.
Sometimes it can lead to scanning half of table records.


Let's assume we have:

CREATE TABLE foo(bar);
CREATE INDEX idx ON foo(bar);

INSERT INTO foo VALUES('one');
.. 1 mln inserts on one..
INSERT INTO foo VALUES('one');

INSERT INTO foo VALUES('two');
.. 1 mln inserts of two..
INSERT INTO foo VALUES('two');

INSERT INTO foo VALUES('three');
.. 1 mln inserts of three..
INSERT INTO foo VALUES('three');

Now, the query:

SELECT * FROM foo WHERE bar='three' and id > 1 LIMIT 10;

If query planner uses primary index it'll have to scan 2 mln records before he 
hits first one matching query.

(speaking about "normal" settings, SQLITE scans from lesser rowid to greater)

And if query planner can somehow use index idx with both supported values { 
bar:'three', id:1 } there will be no scanning.

It's binary search and it's what I need in my app.



___
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] SELECT query first run is VERY slow

[Simon Slavin]

On 26 Jul 2011, at 1:56pm,  
 wrote:

> How to write a trigger [snip]

Nirmala, please start a new thread with your new query.  Do not intrude into 
another person's thread.

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

Re: [sqlite] New madIS v1.3 release

[Eleytherios Stamatogiannakis]

I've mainly used JSON because it is a well defined and widely used 
standard. JSON also contains associative arrays (which currently are not 
used in madIS).

 From what little i've read about Tcl lists, i believe that JSON lists 
are better for the eye. Compare this:

["this is the first", "second", "and third sentence"]

to this:

"this is the first" second "and third sentence"

In the top example the commas help the eye to distinguish between the 
values. Nevertheless you could devise an alternative example with a lot 
of commas inside the strings, which would make JSON lists more difficult 
to read.

So in the end i think it is a matter of taste which of the two is more 
preferable, and the kind of data that one has to deal with.

l.

On 26/07/11 10:08, Alexey Pechnikov wrote:
> Why JPack? May be Tcl lists will be more useful? The tcl dictionary
(also known as associative array) can be stored as list too.


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

Re: [sqlite] SELECT query first run is VERY slow

[Max Vlasov]

2011/7/26 Black, Michael (IS) 

>
> Turns out the if you include the primary key in an index it doesn't use the
> triple index but uses the primary key instead.  And analyze doesn't change
> it.
>
> This is with version 3.7.5
>
>
Not sure about the primary index , because with my data and 3.7.6.2 it still
uses the index,  but visits every entry.

the fast one:
WHERE a=10 and b=20 and c> 100
explain query plan > SEARCH TABLE abctable USING COVERING INDEX idxabc (a=?
AND b=? AND c>?) (~2 rows)

the "slow" one
WHERE a=10 and b=20 and id>100
explain query plan > SEARCH TABLE abctable USING COVERING INDEX idxabid (a=?
AND b=?) (~3 rows)

I assume the optimizer could use the following "plan"
... COVERING INDEX idxabid (a=? AND b=? AND rowid>?)
but it didn't.
Maybe for a reason...

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

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

> 
> select count(*) as cnt,kind,computer
> from log
> group by kind,computer
> order by cnt desc
> 
> what happens here?
> 
> SELECT *
>  FROM log INDEXED BY idxlog_kind_computer
>  WHERE kind = 'info' AND computer=1 and id > 7070636
>  LIMIT 100;
> 
> there are 3_022_148 identical entries 'info,1' in your index
> 
> sqlite has to traverse near all of them and so it is not much help with
> binary search.
> Drop this index, run ANALYZE and sqlite will use your primary key quite
> fast.


The problem is: choosing primary key index is not always a good choice. 
Sometimes it can lead to scanning half of table records.


Let's assume we have:

CREATE TABLE foo(bar);
CREATE INDEX idx ON foo(bar);

INSERT INTO foo VALUES('one');
.. 1 mln inserts on one..
INSERT INTO foo VALUES('one');

INSERT INTO foo VALUES('two');
.. 1 mln inserts of two..
INSERT INTO foo VALUES('two');

INSERT INTO foo VALUES('three');
.. 1 mln inserts of three..
INSERT INTO foo VALUES('three');

Now, the query:

SELECT * FROM foo WHERE bar='three' and id > 1 LIMIT 10;

If query planner uses primary index it'll have to scan 2 mln records before he 
hits first one matching query.

(speaking about "normal" settings, SQLITE scans from lesser rowid to greater)

And if query planner can somehow use index idx with both supported values { 
bar:'three', id:1 } there will be no scanning.

It's binary search and it's what I need in my app.



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

Re: [sqlite] SELECT query first run is VERY slow

[narmada.jammula]

Hi,

How to write a trigger so that it will log the updates on all tables in 
database into a auditlog tables?.
For Example:
The database contains 3 tables 1)country 2) state 3) auditlog
List of fields on each table

Country table:
Cid name

Sate table:
Sid name countryid

Auditlog table:
Aid TableName GUID ChangedDate DataChanged Action

Tablename: is the name of the table the action(insert/update/delete) has 
performed
Guid: is the primary key value of the table
DataChanged: contains the xml in string format.
Ex:If an operation is performed on Country table
Insert into country(cid, name) values (1, india)
Then the xml format will be: 
1
India>


I need a trigger in such a way that it will log all the actions performed on 
country & state tables into the auditlog table.

Please help me in writing the trigger for the above scenario and also provide 
example if possible


Regards,
Nirmala.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of  ??
Sent: Tuesday, July 26, 2011 6:04 PM
To: Black, Michael (IS)
Cc: General Discussion of SQLite Database
Subject: Re: [sqlite] SELECT query first run is VERY slow



> Could you post your timings and read stats again?  Are you happy with what 
> you're seeing now?
> 

Actually, there was ~ 50% speed-up. More or less. The idea of normalizing 
worked great, thank you.

I'm concerned about SQLITE indexes. 


> 
> 
> I think when you drop the index and recreate it still scatters.  But re-doing 
> your data with the new kind will tell for sure.  I take it you have to build 
> the index while running and can't wait until it's done.


To me problem is narrowed to the fact that SQLITE is ignoring ROWID value while 
scanning index.

Using "INDEX ON Foo(Bar)"  and condtion like "Bar = XXX and Rowid > YYY" SQLITE 
reads too much.

Looks like in this case SQLITE reads all of index records matching condition 
Bar = XXX and scan them.

And if Bar has only a few allowed values (= bad selectivity ) this takes lot's 
of time and disk i/o cause there are lots of records with the same Bar value.

Looks like SQLITE is not using knowledge that index records are sorted by Bar 
and then by Rowid.


This is something I don't (and didn't) expect...




> 
> 
> 
> 
> 
> Michael D. Black
> 
> Senior Scientist
> 
> NG Information Systems
> 
> Advanced Analytics Directorate
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Please do not print this email unless it is absolutely necessary. 

The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments. 

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email. 

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

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

26 июля 2011, 16:42 от res...@googlemail.com:
> Think about the distribution of your Data.
> 
> select count(*) as cnt,kind,computer
> from log
> group by kind,computer
> order by cnt desc
> 
> what happens here?
> 
> SELECT *
>  FROM log INDEXED BY idxlog_kind_computer
>  WHERE kind = 'info' AND computer=1 and id > 7070636
>  LIMIT 100;
> 
> there are 3_022_148 identical entries 'info,1' in your index


If there wasn't id condition in WHERE I would agree. 

But in my opinion this is not that simple.

Each index record includes ROWID. At the end. 

http://www.sqlite.org/queryplanner.html

"An index is another table similar to the original "fruitsforsale" table but 
with the content (the fruit column in this case) stored in front of the rowid 
and with all rows in content order."

http://www.sqlite.org/images/qp/idx1.gif

"The "fruit" column is the primary key used to order the elements of the table 
and the "rowid" is the secondary key used to break the tie when two or more 
rows have the same "fruit". In the example, the rowid has to be used as a 
tie-breaker for the "Orange" rows. Notice that since the rowid is always unique 
over all elements of the original table, the composite key of "fruit" followed 
by "rowid" will be unique over all elements of the index."

And this makes each index record different.

WHERE condition is providing query planner with 3 nice values to match exact 
index record.

And SQLITE ignores this knowledge and uses only first 2 values to search index. 

Why? 



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

Re: [sqlite] SELECT query first run is VERY slow

[reseok]

Think about the distribution of your Data.

select count(*) as cnt,kind,computer
from log
group by kind,computer
order by cnt desc

what happens here?

SELECT *
 FROM log INDEXED BY idxlog_kind_computer
 WHERE kind = 'info' AND computer=1 and id > 7070636
 LIMIT 100;

there are 3_022_148 identical entries 'info,1' in your index

sqlite has to traverse near all of them and so it is not much help with
binary search.
Drop this index, run ANALYZE and sqlite will use your primary key quite
fast.

Also, if you want your data in reverse order try:

PRAGMA legacy_file_format=0;
CREATE TABLE log
(
id integer primary key DESC autoincrement,
msg text,
created_at int,
kind,
computer,
process,
who
);




Григорий Григоренко schrieb:
> Did that. Timings has decreased.  As I understand it it's about decreasing 
> index size (that includes kind column).
> 
> 
> 
> To me the problem is still there. If my database will have 10 mln log records 
> first running query will stuck again :(
> 
> 
> 
> I don't understand SQLITE strategy. Let me explain.
> 
> Index is an array of index records. They fill pages in database. 
> 
> Searching with B-Tree index is similar to binary search in ordered array, 
> isn't it? You pick record in a middle of array subset and compare to 
> conditional value.
> 
> This step let you drop half of index subset from search.
> 
> Let's say size of index is 100 Mb and it contains 4 mln index records. 
> 
> This is 100 Mb / 8 Kb (size of page) ~ 12000 pages.
> 
> While doing binary search for 4 mln records we do 22 compares at most (2^22 ~ 
> 4 mln).
> 
> Assume worst case - all of these comparings use different pages. 
> 
> So, we need to read 22 pages = 180 Kb.
> 
> Surely there's additional data to be read for index. Like some intermediate 
> nodes in B-Tree.
> 
> Let's triple the number of pages, 66 pages = 540 Kb.
> 
> 
> But SQLITE reads in this case ~ 50 Mb!! 
> 
> 
> This leads us to conclusion: index in SQLITE database if scattered and cannot 
> be "jumped directly" to N-th element. SQLITE has to read it somehow 
> consecutively. 
> 
> And so SQLITE has to read half of index (!) to find matching index record.
> 
> 
> Am I getting it right?
> 
> 
> 
> 
> 
> 
> 25 июля 2011, 19:35 от "Black, Michael (IS)" :
>> You need to normalize your "kind" value.
>>
>>
>>
>> .pragma cache_size=15000;
>>
>> drop index idxlog_kind_computer;
>>
>> create table kind(id integer,kind text);
>> insert into kind values(1,'debug');
>> insert into kind values(2,'error');
>> insert into kind values(3,'info');
>> insert into kind values(4,'timing');
>> insert into kind values(5,'warn');
>> update log set kind=1 where kind='debug';
>> update log set kind=2 where kind='error';
>> update log set kind=3 where kind='info';
>> update log set kind=4 where kind='timing';
>> update log set kind=5 where kind='warn';
>> create index idxlog_kind_computer ON log(kind,computer);
>>
>>
>>
>> Then see how long your first query takes.
>>
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> NG Information Systems
>>
>> Advanced Analytics Directorate
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
>> behalf of Григорий Григоренко [grigore...@mail.ru]
>> Sent: Monday, July 25, 2011 8:45 AM
>> To: sqlite-users@sqlite.org
>> Subject: EXT :Re: [sqlite]SELECT query first run is VERY slow
>>
>> I think I narrowed the problem a bit.  Guys, hope I'm not bothering you too 
>> much :)
>>
>>
>> I've calculated size of index (it is index on log (kind,computer) ) of its 
>> own: dropped index, run VACUUM and re-created index.
>>
>> Database file increased by 105 Mb (and sqlite3 process counter shows that 
>> there were ~105 Mb written to disk).
>>
>>
>> This means that index on log(kind, computer) takes 105 Mb of database file 
>> (and whole size of database is 1259 Mb).
>>
>>
>> Now, I'm running query which is using this index (and is not returning any 
>> data) and monitor that sqlite3 process reads ~50 Mb.
>>
>>
>> So there are  two major problems here.
>>
>> 1) SQLITE has to read about _half of index_ before it can use it (and 
>> understand there are no records matching query).
>>
>> If cache is enough to hold 50 Mb then on subsuquent queries sqlite process 
>> is not reading at all.
>>
>> 2) SQLITE is reading abnormally slowly during this first-time running query  
>> (waiting for something a lot?).
>>
>> During index creation I monitored sqlite3 process and it was consuming CPU 
>> at ~20% rate and it's doing I/O at ~10 Mb per second rate.
>> That's what I call "normal load"!
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> ___
>> 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
> 

Re: [sqlite] SELECT query first run is VERY slow

[Black, Michael (IS)]

That's because they have 2 completely different query plans.

I created the table so that id,a,b,c all had the same values so the indexing 
would be indentical.



#include 
main()
{
  int i;
  for(i=1;i<=10;++i) {
char sql[4096];
sprintf(sql,"insert into abctable(a,b,c) values(%d,%d,%d);",i,i,i);
puts(sql);
  }
}



Turns out the if you include the primary key in an index it doesn't use the 
triple index but uses the primary key instead.  And analyze doesn't change it.

This is with version 3.7.5

But...if you change "c" to be "unique" then it uses an auto_index.



sqlite> pragma cache_size=15;
sqlite> CREATE TABLE [abctable] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, a, b, 
c);
sqlite> begin;
sqlite> .read x.sql
sqlite> commit;
sqlite> create index idxabc on abctable(a,b,c);
sqlite> create index idxabid on abctable(a,b,id);
sqlite> explain query plan select * from abctable where a=10 and b=20 and id=30;
0|0|0|SEARCH TABLE abctable USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
sqlite> explain query plan select * from abctable where a=10 and b=20 and c=30;
0|0|0|SEARCH TABLE abctable USING COVERING INDEX idxabc (a=? AND b=? AND c=?) 
(~8 rows)
sqlite> analyze;
sqlite> select * from sqlite_stat1;
abctable|idxabid|10 1 1 1
abctable|idxabc|10 1 1 1



Re-do with "c unique" in table:

sqlite> explain query plan select * from abctable where a=10 and b=20 and c=30;
0|0|0|SEARCH TABLE abctablsqlite> explain query plan select * from abctable 
where a=10 and b=20 and id>30;
0|0|0|SEARCH TABLE abctable USING COVERING INDEX idxabc (a=? AND b=?) (~3 rows)
sqlite> explain query plan select * from abctable where a=10 and b=20 and c>30;
0|0|0|SEARCH TABLE abctable USING COVERING INDEX idxabc (a=? AND b=? AND c>?) 
(~2 rows)e USING INDEX sqlite_autoindex_abctable_1 (c=?) (~1 rows)

Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Max Vlasov [max.vla...@gmail.com]
Sent: Tuesday, July 26, 2011 6:50 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] SELECT query first run is VERY slow

On Tue, Jul 26, 2011 at 2:18 PM, Simon Slavin  wrote:

>
> On 26 Jul 2011, at 11:05am, Григорий Григоренко wrote:
>
> > This leads us to conclusion: index in SQLITE database if scattered and
> cannot be "jumped directly" to N-th element. SQLITE has to read it somehow
> consecutively.
> >
> > And so SQLITE has to read half of index (!) to find matching index
> record.
>
> I don't think it's SQLite itself that's reading half the index.  I think
> it's some part of your operating system that's trying to cache all of your
> database file as SQLite reads lots of different parts of it spread about
> randomly.  Unfortunately I don't see how an OS can reasonably do that since
> it will produce the slow speeds you're complaining about.
>
>
Simon,

actually I narrowed down the problem  (cmiiw).

The simple table

CREATE TABLE [abctable] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, a, b, c)

add many records (100,000)
INSERT INTO abctable (a, b, c) VALUES (10, 20, 30)

Good variant
CREATE INDEX idxabc ON abctable (a, b, c)
SELECT * FROM abctable WHERE a=10 and b=20 and c > 100
Sqlite reads few data (3k actually for 100,000 records) to show empty result

Another variant
CREATE INDEX idxabid ON abctable (a, b, id)
SELECT * FROM abctable WHERE a=10 and b=20 and id > 100

Sqlite reads much (1,7MB)

Checked with 3.7.6.2,
I suppose the latter due to some special meaning of the id/rowid, but I
suppose the second case should work with fewer reads

Max
___
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] SELECT query first run is VERY slow

[Григорий Григоренко]

> Could you post your timings and read stats again?  Are you happy with what 
> you're seeing now?
> 

Actually, there was ~ 50% speed-up. More or less. The idea of normalizing 
worked great, thank you.

I'm concerned about SQLITE indexes. 


> 
> 
> I think when you drop the index and recreate it still scatters.  But re-doing 
> your data with the new kind will tell for sure.  I take it you have to build 
> the index while running and can't wait until it's done.


To me problem is narrowed to the fact that SQLITE is ignoring ROWID value while 
scanning index.

Using "INDEX ON Foo(Bar)"  and condtion like "Bar = XXX and Rowid > YYY" SQLITE 
reads too much.

Looks like in this case SQLITE reads all of index records matching condition 
Bar = XXX and scan them.

And if Bar has only a few allowed values (= bad selectivity ) this takes lot's 
of time and disk i/o cause there are lots of records with the same Bar value.

Looks like SQLITE is not using knowledge that index records are sorted by Bar 
and then by Rowid.


This is something I don't (and didn't) expect...




> 
> 
> 
> 
> 
> Michael D. Black
> 
> Senior Scientist
> 
> NG Information Systems
> 
> Advanced Analytics Directorate
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Max Vlasov]

On Tue, Jul 26, 2011 at 2:18 PM, Simon Slavin  wrote:

>
> On 26 Jul 2011, at 11:05am, Григорий Григоренко wrote:
>
> > This leads us to conclusion: index in SQLITE database if scattered and
> cannot be "jumped directly" to N-th element. SQLITE has to read it somehow
> consecutively.
> >
> > And so SQLITE has to read half of index (!) to find matching index
> record.
>
> I don't think it's SQLite itself that's reading half the index.  I think
> it's some part of your operating system that's trying to cache all of your
> database file as SQLite reads lots of different parts of it spread about
> randomly.  Unfortunately I don't see how an OS can reasonably do that since
> it will produce the slow speeds you're complaining about.
>
>
Simon,

actually I narrowed down the problem  (cmiiw).

The simple table

CREATE TABLE [abctable] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, a, b, c)

add many records (100,000)
INSERT INTO abctable (a, b, c) VALUES (10, 20, 30)

Good variant
CREATE INDEX idxabc ON abctable (a, b, c)
SELECT * FROM abctable WHERE a=10 and b=20 and c > 100
Sqlite reads few data (3k actually for 100,000 records) to show empty result

Another variant
CREATE INDEX idxabid ON abctable (a, b, id)
SELECT * FROM abctable WHERE a=10 and b=20 and id > 100

Sqlite reads much (1,7MB)

Checked with 3.7.6.2,
I suppose the latter due to some special meaning of the id/rowid, but I
suppose the second case should work with fewer reads

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

Re: [sqlite] SQLite3.dll for Win 64

[Everton Vieira]

Hey people let's consider provide 64bit binaries in sqlite.org


2011/7/26 Everton Vieira 

> Done! Probably something with gawk was occurring because after i've copy
> all gawk files inside the folder of the source they work out.
>
> Thanks to all.
>
> 2011/7/25 Shane Harrelson 
>
>> The makefile builds lemon.exe from lemon.c as part of the build process,
>> so
>> make sure it was generated correctly as well.
>>
>> "lemon -x" should print a version number.
>> "lemon -?" should print an error and help message.
>>
>> Again, HTH.
>> -Shane
>>
>>
>> On Mon, Jul 25, 2011 at 5:51 PM, Shane Harrelson
>> wrote:
>>
>> >
>> > The TK_* identifiers are all defined in parse.h which is generated by
>> > lemon.exe from parse.y.
>> > parse.h is "included" in the amalgamation file, sqlite3.c.
>> > Try deleting your parse.c and parse.h and sqlite3.c and re-running your
>> > make.
>> > Check that parse.h was generated correctly and subsequently concatenated
>> > into sqlite3.c correctly.
>> >
>> > HTH.
>> > -Shane
>> >
>> >
>> >
>> > On Mon, Jul 25, 2011 at 3:49 PM, Everton Vieira > >wrote:
>> >
>> >> So, now i'm stock on this errors:
>> >>
>> >> sqlite3.c
>> >> sqlite3.c(40972) : warning C4244: '=' : conversion from '__int64' to
>> >> 'int',
>> >> poss
>> >> ible loss of data
>> >> sqlite3.c(57259) : error C2065: 'TK_REGISTER' : undeclared identifier
>> >> sqlite3.c(57266) : error C2065: 'TK_UMINUS' : undeclared identifier
>> >> sqlite3.c(57266) : error C2065: 'TK_INTEGER' : undeclared identifier
>> >> sqlite3.c(57266) : error C2065: 'TK_FLOAT' : undeclared identifier
>> >> sqlite3.c(57273) : error C2065: 'TK_STRING' : undeclared identifier
>> >> sqlite3.c(57273) : error C2065: 'TK_FLOAT' : undeclared identifier
>> >> sqlite3.c(57273) : error C2065: 'TK_INTEGER' : undeclared identifier
>> >> sqlite3.c(57282) : error C2065: 'TK_FLOAT' : undeclared identifier
>> >> sqlite3.c(57284) : error C2065: 'TK_INTEGER' : undeclared identifier
>> >> sqlite3.c(57284) : error C2065: 'TK_FLOAT' : undeclared identifier
>> >> sqlite3.c(57293) : error C2065: 'TK_UMINUS' : undeclared identifier
>> >> sqlite3.c(57307) : error C2065: 'TK_NULL' : undeclared identifier
>> >> sqlite3.c(57312) : error C2065: 'TK_BLOB' : undeclared identifier
>> >> sqlite3.c(61966) : error C2065: 'TK_VARIABLE' : undeclared identifier
>> >> sqlite3.c(63498) : error C2196: case value '0' already used
>> >> sqlite3.c(63752) : error C2196: case value '0' already used
>> >> sqlite3.c(63822) : error C2196: case value '0' already used
>> >> sqlite3.c(63823) : error C2196: case value '0' already used
>> >> sqlite3.c(63824) : error C2196: case value '0' already used
>> >> sqlite3.c(63825) : error C2196: case value '0' already used
>> >> sqlite3.c(63826) : error C2196: case value '0' already used
>> >> sqlite3.c(63847) : error C2196: case value '0' already used
>> >> sqlite3.c(63848) : error C2196: case value '0' already used
>> >> sqlite3.c(63849) : error C2196: case value '0' already used
>> >> sqlite3.c(63870) : error C2196: case value '0' already used
>> >> sqlite3.c(63871) : error C2196: case value '0' already used
>> >> sqlite3.c(63872) : error C2196: case value '0' already used
>> >> sqlite3.c(64070) : error C2196: case value '0' already used
>> >> sqlite3.c(64071) : error C2196: case value '0' already used
>> >> sqlite3.c(64072) : error C2196: case value '0' already used
>> >> sqlite3.c(64073) : error C2196: case value '0' already used
>> >> sqlite3.c(64191) : error C2196: case value '0' already used
>> >> sqlite3.c(64214) : error C2196: case value '0' already used
>> >> sqlite3.c(64238) : error C2196: case value '0' already used
>> >> sqlite3.c(64254) : error C2196: case value '0' already used
>> >> sqlite3.c(64272) : error C2196: case value '0' already used
>> >> sqlite3.c(64354) : error C2196: case value '0' already used
>> >> sqlite3.c(64355) : error C2196: case value '0' already used
>> >> sqlite3.c(64356) : error C2196: case value '0' already used
>> >> sqlite3.c(64357) : error C2196: case value '0' already used
>> >> sqlite3.c(64358) : error C2196: case value '0' already used
>> >> sqlite3.c(64359) : error C2196: case value '0' already used
>> >> sqlite3.c(64410) : error C2196: case value '0' already used
>> >> sqlite3.c(64411) : error C2196: case value '0' already used
>> >> sqlite3.c(64412) : error C2196: case value '0' already used
>> >> sqlite3.c(64413) : error C2196: case value '0' already used
>> >> sqlite3.c(64546) : error C2196: case value '0' already used
>> >> sqlite3.c(64547) : error C2196: case value '0' already used
>> >> sqlite3.c(64588) : error C2196: case value '0' already used
>> >> sqlite3.c(64605) : error C2196: case value '0' already used
>> >> sqlite3.c(64654) : error C2196: case value '0' already used
>> >> sqlite3.c(64666) : error C2196: case value '0' already used
>> >> sqlite3.c(69909) : error C2065: 'TK_COLUMN' : undeclared identifier
>> 

Re: [sqlite] SQLite3.dll for Win 64

[Everton Vieira]

Done! Probably something with gawk was occurring because after i've copy
all gawk files inside the folder of the source they work out.

Thanks to all.

2011/7/25 Shane Harrelson 

> The makefile builds lemon.exe from lemon.c as part of the build process, so
> make sure it was generated correctly as well.
>
> "lemon -x" should print a version number.
> "lemon -?" should print an error and help message.
>
> Again, HTH.
> -Shane
>
>
> On Mon, Jul 25, 2011 at 5:51 PM, Shane Harrelson
> wrote:
>
> >
> > The TK_* identifiers are all defined in parse.h which is generated by
> > lemon.exe from parse.y.
> > parse.h is "included" in the amalgamation file, sqlite3.c.
> > Try deleting your parse.c and parse.h and sqlite3.c and re-running your
> > make.
> > Check that parse.h was generated correctly and subsequently concatenated
> > into sqlite3.c correctly.
> >
> > HTH.
> > -Shane
> >
> >
> >
> > On Mon, Jul 25, 2011 at 3:49 PM, Everton Vieira  >wrote:
> >
> >> So, now i'm stock on this errors:
> >>
> >> sqlite3.c
> >> sqlite3.c(40972) : warning C4244: '=' : conversion from '__int64' to
> >> 'int',
> >> poss
> >> ible loss of data
> >> sqlite3.c(57259) : error C2065: 'TK_REGISTER' : undeclared identifier
> >> sqlite3.c(57266) : error C2065: 'TK_UMINUS' : undeclared identifier
> >> sqlite3.c(57266) : error C2065: 'TK_INTEGER' : undeclared identifier
> >> sqlite3.c(57266) : error C2065: 'TK_FLOAT' : undeclared identifier
> >> sqlite3.c(57273) : error C2065: 'TK_STRING' : undeclared identifier
> >> sqlite3.c(57273) : error C2065: 'TK_FLOAT' : undeclared identifier
> >> sqlite3.c(57273) : error C2065: 'TK_INTEGER' : undeclared identifier
> >> sqlite3.c(57282) : error C2065: 'TK_FLOAT' : undeclared identifier
> >> sqlite3.c(57284) : error C2065: 'TK_INTEGER' : undeclared identifier
> >> sqlite3.c(57284) : error C2065: 'TK_FLOAT' : undeclared identifier
> >> sqlite3.c(57293) : error C2065: 'TK_UMINUS' : undeclared identifier
> >> sqlite3.c(57307) : error C2065: 'TK_NULL' : undeclared identifier
> >> sqlite3.c(57312) : error C2065: 'TK_BLOB' : undeclared identifier
> >> sqlite3.c(61966) : error C2065: 'TK_VARIABLE' : undeclared identifier
> >> sqlite3.c(63498) : error C2196: case value '0' already used
> >> sqlite3.c(63752) : error C2196: case value '0' already used
> >> sqlite3.c(63822) : error C2196: case value '0' already used
> >> sqlite3.c(63823) : error C2196: case value '0' already used
> >> sqlite3.c(63824) : error C2196: case value '0' already used
> >> sqlite3.c(63825) : error C2196: case value '0' already used
> >> sqlite3.c(63826) : error C2196: case value '0' already used
> >> sqlite3.c(63847) : error C2196: case value '0' already used
> >> sqlite3.c(63848) : error C2196: case value '0' already used
> >> sqlite3.c(63849) : error C2196: case value '0' already used
> >> sqlite3.c(63870) : error C2196: case value '0' already used
> >> sqlite3.c(63871) : error C2196: case value '0' already used
> >> sqlite3.c(63872) : error C2196: case value '0' already used
> >> sqlite3.c(64070) : error C2196: case value '0' already used
> >> sqlite3.c(64071) : error C2196: case value '0' already used
> >> sqlite3.c(64072) : error C2196: case value '0' already used
> >> sqlite3.c(64073) : error C2196: case value '0' already used
> >> sqlite3.c(64191) : error C2196: case value '0' already used
> >> sqlite3.c(64214) : error C2196: case value '0' already used
> >> sqlite3.c(64238) : error C2196: case value '0' already used
> >> sqlite3.c(64254) : error C2196: case value '0' already used
> >> sqlite3.c(64272) : error C2196: case value '0' already used
> >> sqlite3.c(64354) : error C2196: case value '0' already used
> >> sqlite3.c(64355) : error C2196: case value '0' already used
> >> sqlite3.c(64356) : error C2196: case value '0' already used
> >> sqlite3.c(64357) : error C2196: case value '0' already used
> >> sqlite3.c(64358) : error C2196: case value '0' already used
> >> sqlite3.c(64359) : error C2196: case value '0' already used
> >> sqlite3.c(64410) : error C2196: case value '0' already used
> >> sqlite3.c(64411) : error C2196: case value '0' already used
> >> sqlite3.c(64412) : error C2196: case value '0' already used
> >> sqlite3.c(64413) : error C2196: case value '0' already used
> >> sqlite3.c(64546) : error C2196: case value '0' already used
> >> sqlite3.c(64547) : error C2196: case value '0' already used
> >> sqlite3.c(64588) : error C2196: case value '0' already used
> >> sqlite3.c(64605) : error C2196: case value '0' already used
> >> sqlite3.c(64654) : error C2196: case value '0' already used
> >> sqlite3.c(64666) : error C2196: case value '0' already used
> >> sqlite3.c(69909) : error C2065: 'TK_COLUMN' : undeclared identifier
> >> sqlite3.c(69911) : error C2065: 'TK_AS' : undeclared identifier
> >> sqlite3.c(70079) : error C2065: 'TK_DELETE' : undeclared identifier
> >> sqlite3.c(70082) : error C2065: 'TK_INSERT' : undeclared identifier
> >> 

[sqlite] trying to build from sqlite-3.6.7 on ubuntu 11.04, missing sqlite3.c

[Robert P. J. Day]

  not sure if this is the appropriate forum for this but i'm hoping
someone can give me some useful pointers.

  as part of an embedded system build, i'm building a number of tools
for the *host* system, including sqlite-3.6.7 from the tarball (along
with a few patches which i will be examining shortly).

  the sqlite build is part of a much larger build, and when this
process gets around to unpacking, configuring and compiling sqlite3,
it fails with:

---
 Compiling:  sqlite
---
remake[4]: *** No rule to make target `sqlite3.c', needed by
`sqlite3.lo'.  Stop.

  after a quick inspection, i can see (i think) that part of the
configuration and build process is to *create* the sqlite3.c source
file to be used as part of the compilation, is that correct?

  so what is the probable reason that that file wouldn't be created?
i still have a fair bit of examination left to understand how this
entire build system works but this is the issue that's stopping me
from getting any further.

  this is being done on a fully-updated ubuntu 11.04 system, so i'm
open to suggestions as to what make rule or shell script might be the
culprit here.  thanks.

rday

-- 


Robert P. J. Day Ottawa, Ontario, CANADA
http://crashcourse.ca

Twitter:   http://twitter.com/rpjday
LinkedIn:   http://ca.linkedin.com/in/rpjday

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

Re: [sqlite] SELECT query first run is VERY slow

[Simon Slavin]

On 26 Jul 2011, at 11:05am, Григорий Григоренко wrote:

> This leads us to conclusion: index in SQLITE database if scattered and cannot 
> be "jumped directly" to N-th element. SQLITE has to read it somehow 
> consecutively. 
> 
> And so SQLITE has to read half of index (!) to find matching index record.

I don't think it's SQLite itself that's reading half the index.  I think it's 
some part of your operating system that's trying to cache all of your database 
file as SQLite reads lots of different parts of it spread about randomly.  
Unfortunately I don't see how an OS can reasonably do that since it will 
produce the slow speeds you're complaining about.

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

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

> 
> > I need query to be ORDER BY id DESC. I've dropped this ORDER BY to simplify 
> > my case. But in real-life app I need it.
> > 
> > So, index on (kind, computer) has these index records:
> > 
> > [ KIND ] [ COMPUTER ] [ ID ]
> 
> I don't know that SQLite does an inherent addition of the 'id' column to all 
> INDEXes.  Some index implementations do this because their algorithm requires 
> that all positions in an index are unique, but I don't think SQLite works 
> like that.  I think that if you want 'id' to be part of an index you have to 
> say that in the definition.

SQLITE always stores rowid in every index record. This rowid let SQLITE find 
approriate database record. You can read more here: 
http://www.sqlite.org/queryplanner.html 

> 
> Nevertheless this still doesn't explain why your first run of a query is so 
> much slower than subsequent runs.  SQLite's cache size is set here:
> 
> http://www.sqlite.org/pragma.html#pragma_cache_size
> 
> If the cache size described there doesn't explain the behaviour you're 
> seeing, the problem isn't with SQLite, it's in your application or the OS or 
> your hardware.


The problem is filling the cache. SQLITE wants too much data to execute simple 
query.

Cache size of SQLITE doesn't matter because OS does it's own caching. After 
query has been run for a first time OS caches disk pages.

And subsequent queries immediately gets data from RAM not from disk.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

Did that. Timings has decreased.  As I understand it it's about decreasing 
index size (that includes kind column).



To me the problem is still there. If my database will have 10 mln log records 
first running query will stuck again :(



I don't understand SQLITE strategy. Let me explain.

Index is an array of index records. They fill pages in database. 

Searching with B-Tree index is similar to binary search in ordered array, isn't 
it? You pick record in a middle of array subset and compare to conditional 
value.

This step let you drop half of index subset from search.

Let's say size of index is 100 Mb and it contains 4 mln index records. 

This is 100 Mb / 8 Kb (size of page) ~ 12000 pages.

While doing binary search for 4 mln records we do 22 compares at most (2^22 ~ 4 
mln).

Assume worst case - all of these comparings use different pages. 

So, we need to read 22 pages = 180 Kb.

Surely there's additional data to be read for index. Like some intermediate 
nodes in B-Tree.

Let's triple the number of pages, 66 pages = 540 Kb.


But SQLITE reads in this case ~ 50 Mb!! 


This leads us to conclusion: index in SQLITE database if scattered and cannot 
be "jumped directly" to N-th element. SQLITE has to read it somehow 
consecutively. 

And so SQLITE has to read half of index (!) to find matching index record.


Am I getting it right?






25 июля 2011, 19:35 от "Black, Michael (IS)" :
> You need to normalize your "kind" value.
> 
> 
> 
> .pragma cache_size=15000;
> 
> drop index idxlog_kind_computer;
> 
> create table kind(id integer,kind text);
> insert into kind values(1,'debug');
> insert into kind values(2,'error');
> insert into kind values(3,'info');
> insert into kind values(4,'timing');
> insert into kind values(5,'warn');
> update log set kind=1 where kind='debug';
> update log set kind=2 where kind='error';
> update log set kind=3 where kind='info';
> update log set kind=4 where kind='timing';
> update log set kind=5 where kind='warn';
> create index idxlog_kind_computer ON log(kind,computer);
> 
> 
> 
> Then see how long your first query takes.
> 
> 
> 
> Michael D. Black
> 
> Senior Scientist
> 
> NG Information Systems
> 
> Advanced Analytics Directorate
> 
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Григорий Григоренко [grigore...@mail.ru]
> Sent: Monday, July 25, 2011 8:45 AM
> To: sqlite-users@sqlite.org
> Subject: EXT :Re: [sqlite]SELECT query first run is VERY slow
> 
> I think I narrowed the problem a bit.  Guys, hope I'm not bothering you too 
> much :)
> 
> 
> I've calculated size of index (it is index on log (kind,computer) ) of its 
> own: dropped index, run VACUUM and re-created index.
> 
> Database file increased by 105 Mb (and sqlite3 process counter shows that 
> there were ~105 Mb written to disk).
> 
> 
> This means that index on log(kind, computer) takes 105 Mb of database file 
> (and whole size of database is 1259 Mb).
> 
> 
> Now, I'm running query which is using this index (and is not returning any 
> data) and monitor that sqlite3 process reads ~50 Mb.
> 
> 
> So there are  two major problems here.
> 
> 1) SQLITE has to read about _half of index_ before it can use it (and 
> understand there are no records matching query).
> 
> If cache is enough to hold 50 Mb then on subsuquent queries sqlite process is 
> not reading at all.
> 
> 2) SQLITE is reading abnormally slowly during this first-time running query  
> (waiting for something a lot?).
> 
> During index creation I monitored sqlite3 process and it was consuming CPU at 
> ~20% rate and it's doing I/O at ~10 Mb per second rate.
> That's what I call "normal load"!
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> ___
> 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] SELECT query first run is VERY slow

[Simon Slavin]

On 26 Jul 2011, at 9:30am, Григорий Григоренко wrote:

> I need query to be ORDER BY id DESC. I've dropped this ORDER BY to simplify 
> my case. But in real-life app I need it.
> 
> So, index on (kind, computer) has these index records:
> 
> [ KIND ] [ COMPUTER ] [ ID ]

I don't know that SQLite does an inherent addition of the 'id' column to all 
INDEXes.  Some index implementations do this because their algorithm requires 
that all positions in an index are unique, but I don't think SQLite works like 
that.  I think that if you want 'id' to be part of an index you have to say 
that in the definition.

Nevertheless this still doesn't explain why your first run of a query is so 
much slower than subsequent runs.  SQLite's cache size is set here:

http://www.sqlite.org/pragma.html#pragma_cache_size

If the cache size described there doesn't explain the behaviour you're seeing, 
the problem isn't with SQLite, it's in your application or the OS or your 
hardware.

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

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

> >
> > 1) SQLITE has to read about _half of index_ before it can use it (and 
> > understand there are no records matching query).
> >
> > If cache is enough to hold 50 Mb then on subsuquent queries sqlite process 
> > is not reading at all.
> >
> 
> Please, post your query. To understand whether sqlite reads too much
> or not it's better to know what exactly you want to select.
> 


This is script I ran:

CREATE TABLE IF NOT EXISTS T(t); DELETE FROM T; INSERT INTO T VALUES( 
strftime('%s', 'now' ) );

EXPLAIN QUERY PLAN SELECT * FROM log  INDEXED BY idxlog_kind_computer WHERE 
kind = 'info' AND computer=1 and id > 7070636 LIMIT 100;

SELECT * FROM log INDEXED BY idxlog_kind_computer WHERE kind = 'info' AND 
computer=1 and id > 7070636 LIMIT 100;

SELECT "Seconds elapsed: " || (strftime('%s', 'now' ) - t) FROM T ;



> 
> > 2) SQLITE is reading abnormally slowly during this first-time running query 
> >  (waiting for something a lot?).
> >
> 
> Is this with the recreated index or still the one that was created
> during the lifetime of your program?
> 


It doesn't matter. Tried it with old and with recreated index, same behaviour.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

> 
> I thought that, if you have an index on a,b,c,d
> than you should not have an index on a,b,c too
> because if you use those 3 field in the where-clause, use can be made of
> the 4-field index
> 

I'm not sure. Let me explain.

I need query to be ORDER BY id DESC. I've dropped this ORDER BY to simplify my 
case. But in real-life app I need it.

So, index on (kind, computer) has these index records:

[ KIND ] [ COMPUTER ] [ ID ]
...

They are ordered by kind, computer and then by id. So ORDER BY id DESC comes 
free.
Query planner just need to retrieve records from subset of index records 
starting at last one backwards.

Index on (kind,computer, process,who) has these index records:

[ KIND ] [ COMPUTER ] [ PROCESS ] [ WHO ] [ ID ]

This time having found subset of index records query planner cannot start 
retrieving them from the last to first.
It has to sort them by ID at first. Isn't it?


Maybe I'm not getting it right?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New madIS v1.3 release

[Alexey Pechnikov]

Why JPack? May be Tcl lists will be more useful? The tcl dictionary
(also known as associative array) can be stored as list too.

SELECT TCLCMD('dict', 'get', 'key 1 mykey 2', 'mykey');
2

SELECT TCLCMD('lindex', 'key 1 mykey 2', 0);
key

SELECT TCLCMD('join', 'key 1 mykey 2', '-');
key-1-mykey-2

SELECT TCLCMD('lreplace', 'key 1 mykey 2', -1, -1, 'test');
test key 1 mykey 2

SELECT TCLCMD('lreplace', 'key 1 mykey 2', 'end', 'end', 'test');
key 1 mykey test

SELECT TCLCMD('linsert', 'a b', 0, 'c');
c a b

SELECT TCLCMD('linsert', 'a b', 'end', 'c');
a b c

SELECT TCLCMD('lsort', 'a c b');
a b c

SELECT TCLCMD('lsort', '-decreasing', 'a c b');
c b a

SELECT TCLCMD('lreverse', 'a c b');
b c a

SELECT TCLCMD('lsearch', 'a c b', 'b');
2

SELECT TCLCMD('lsearch', 'a c b', 'd');
-1

See details here: http://sqlite.mobigroup.ru/wiki?name=ext_tcl

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users