[sqlite] Can't load sqlite 3.75 under tcl 8.4.4.0

[thoselaings]

% package require sqlite
couldn't load library "C:/Tcl/lib/sqlite3/sqlite3/.dll": this library or a 
dependent library could not be found in library path

What is sqlite3/.dll ?
Where did that "/" come from?

The pkgindex file contains -

package ifneeded sqlite 3.7.5 [list load [file join $dir \
sqlite3[info sharedlibextension]] sqlite3]

(I'm using 8.4 because I want to continue using BLT - so don't ask me to 
upgrade to 8.5)

Any help gratefully received

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

[sqlite] Sqlite3_step

[Guilherme Batista]

Hi,

since sqlite does a nested loop to evaluate queries, is it possible to know
how many interactions have each loop, i.e. how many rows in the table that
loop will interact,  and which table is that?

And how can I use the WHERETRACE function that prints the optimization
steps?

Thanks!

-- 
Att.,

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

Re: [sqlite] Automatic index yields bad performance for a simple join

[Martin Gadbois]

On Sat, Apr 9, 2011 at 2:49 PM, Richard Hipp  wrote:

>
>
> On Sat, Apr 9, 2011 at 8:36 AM, Martin Gadbois  wrote:
>
>>
>> If ANALYZE is ran, the speed is fast: there are no auto-index.
>>
>
> There you go.
>
> Without ANALYZE, the query planner has no idea what your database contains,
> and so it guesses that both the tags and events table hold 1,000,000 rows
> each.  In that case, creating a automatic index definitely improves
> performance - it turns an N*N algorithm into NlogN (with N=100).  But in
> your case, with only 12 rows in tags, creating the automatic index is a big
> loser.  The stats that ANALYZE generate help the query planner to realize
> this and thus avoid generating the automatic index.
>
> You might also try:
>
> CREATE INDEX some_name ON event(event_type);
>
>
Thank you for your answer.

I read the documentation on auto-index, and I do come up with the same
conclusion.

My original question still holds: is there a way for SQLite to optimize so
that the auto-index re-uses the same index it just created so that this type
of query runs faster?

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

Re: [sqlite] completion of sql words

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/09/2011 02:03 PM, Edzard Pasma wrote:
> I just made an alias in the shell (Unix-) for this tool:
> 
> alias apsw='python -c "import apsw;apsw.main()"'

I keep trying to make it so you can do "python -m apsw " and the code is
structured to allow that, but in my investigations it seems like Python
internals will only do it for a module written in Python and not one written
in C.

> Also reported an issue

Anyone who is interested can follow along at the following link.  The APSW
shell supports a superset of SQLite shell's invocation and hence the
behaviours are a little inconsistent:

  http://code.google.com/p/apsw/issues/detail?id=115

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk2g1JgACgkQmOOfHg372QTjRQCcDVMaLLR27OQWCVI/4R7yTVZ3
j/sAoK8RD+R6VextcxboId7GI9O1jNRY
=QjF4
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] completion of sql words

[Edzard Pasma]

Op 9-apr-2011, om 21:29 heeft Roger Binns het volgende geschreven:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 04/08/2011 02:40 PM, Edzard Pasma wrote:
>> Just found that the APSW shell does tabcomplete (and even for
>> tablenames). It is described here:
>>
>>   http://apidoc.apsw.googlecode.com/hg/shell.html
>
> (I'm the APSW author). It also does output colouring.  Makes it a  
> lot easier
> to distinguish numbers from strings of digits etc.  I'd also submit  
> that the
> .dump output is a thing of beauty :-)
>
> It also completes database names, column names, builtin SQLite  
> function
> names, collations, etc(*).  However the completion is not context  
> sensitive
> (ie using a grammar) so it is really just completing a sequence of
> independent words.  This is just fine most of the time and in many  
> cases
> almost any word is allowable at almost any point anyway.  I did  
> look into
> trying to hack the grammar (which really is a bunch of grammar rules
> interspersed with C code) or using the railroad diagram input in  
> order to
> make a SQLite SQL parser.  In the case of completion that is even more
> difficult since it would have to tolerate an incomplete string.   
> Maybe one
> day...
>
> Pragmas are also completed and they are context dependent so if you do
> "pragma journal_mode =" it will show the journal modes available.
>
> Unfortunately I do have hard coded tables of many things since it  
> isn't
> possible to find them by asking SQLite at runtime.  Some such as  
> the list of
> registered functions and how many arguments they take can only be  
> provided
> by code changes to SQLite itself.  It would be nice if there were  
> virtual
> tables allowing dynamic introspection of SQLite.
>
> (*) In a fit of OCD it even pays attention to what case you are  
> using and
> does completions in the same case even if you mix cases in the same  
> word!
>
> Roger
>
I just made an alias in the shell (Unix-) for this tool:

alias apsw='python -c "import apsw;apsw.main()"'

Thats worth it. So far I'd only used the shell programmatically from  
Python, an other great  feature. This was particularly for its  
dataload capabilities.

Also reported an issue that has nothing to do with tabcompletion. The  
tool does not quit as the SQLite shell does when queries are passed  
as parameter. The SQLite behaviour is probably more convenient. But  
anyway I understand that all SQLite examples should work  
correspondingly.

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

Re: [sqlite] completion of sql words

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/08/2011 02:40 PM, Edzard Pasma wrote:
> Just found that the APSW shell does tabcomplete (and even for  
> tablenames). It is described here: 
>
>   http://apidoc.apsw.googlecode.com/hg/shell.html

(I'm the APSW author). It also does output colouring.  Makes it a lot easier
to distinguish numbers from strings of digits etc.  I'd also submit that the
.dump output is a thing of beauty :-)

It also completes database names, column names, builtin SQLite function
names, collations, etc(*).  However the completion is not context sensitive
(ie using a grammar) so it is really just completing a sequence of
independent words.  This is just fine most of the time and in many cases
almost any word is allowable at almost any point anyway.  I did look into
trying to hack the grammar (which really is a bunch of grammar rules
interspersed with C code) or using the railroad diagram input in order to
make a SQLite SQL parser.  In the case of completion that is even more
difficult since it would have to tolerate an incomplete string.  Maybe one
day...

Pragmas are also completed and they are context dependent so if you do
"pragma journal_mode =" it will show the journal modes available.

Unfortunately I do have hard coded tables of many things since it isn't
possible to find them by asking SQLite at runtime.  Some such as the list of
registered functions and how many arguments they take can only be provided
by code changes to SQLite itself.  It would be nice if there were virtual
tables allowing dynamic introspection of SQLite.

(*) In a fit of OCD it even pays attention to what case you are using and
does completions in the same case even if you mix cases in the same word!

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk2gs3kACgkQmOOfHg372QS18QCeIKqTyHGTHdTknVoqoEteO+F5
KHYAni17eEPBwxFF6cte+OkgA6M8WmHo
=4Ia7
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite works strange

[Simon Slavin]

On 9 Apr 2011, at 8:18pm, Igor Tandetnik wrote:

> Simon Slavin  wrote:
>> On 8 Apr 2011, at 2:34pm, Vadim Smirnov wrote:
>> 
>>> Tbl1(id1 INTEGER, id2 INTEGER, str TEXT)  2 records
>>> Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) ~5000 records
>>> And SQL-query: SELECT Tbl2.id, MY_FUNC(Tbl1.str, Tbl2.str) FROM Tbl2 JOIN 
>>> Tbl1 ON Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND
>>> Tbl1.id2=1 LIMIT 50 We expected that MY_FUNC would be evaluated 50 times at 
>>> most, but it appeared 1!
>>> Why so?
>> 
>> Your SELECT consists of a JOIN which tells SQLite to try every combination 
>> of one record from each table.  For each combination
>> it checks to see that all the conditions you gave are true. 
> 
> But the custom function is not involved in the WHERE clause. There doesn't 
> seem to be any reason to call it for rows that aren't going to be returned.

I guess either EXPLAIN or EXPLAIN QUERY PLAN would be helpful in figuring out 
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 works strange

[Igor Tandetnik]

Simon Slavin  wrote:
> On 8 Apr 2011, at 2:34pm, Vadim Smirnov wrote:
> 
>> Tbl1(id1 INTEGER, id2 INTEGER, str TEXT)  2 records
>> Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) ~5000 records
>> And SQL-query: SELECT Tbl2.id, MY_FUNC(Tbl1.str, Tbl2.str) FROM Tbl2 JOIN 
>> Tbl1 ON Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND
>> Tbl1.id2=1 LIMIT 50 We expected that MY_FUNC would be evaluated 50 times at 
>> most, but it appeared 1!
>> Why so?
> 
> Your SELECT consists of a JOIN which tells SQLite to try every combination of 
> one record from each table.  For each combination
> it checks to see that all the conditions you gave are true. 

But the custom function is not involved in the WHERE clause. There doesn't seem 
to be any reason to call it for rows that aren't going to be returned.
-- 
Igor Tandetnik

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

Re: [sqlite] Automatic index yields bad performance for a simple join

[Richard Hipp]

On Sat, Apr 9, 2011 at 8:36 AM, Martin Gadbois  wrote:

>
> If ANALYZE is ran, the speed is fast: there are no auto-index.
>

There you go.

Without ANALYZE, the query planner has no idea what your database contains,
and so it guesses that both the tags and events table hold 1,000,000 rows
each.  In that case, creating a automatic index definitely improves
performance - it turns an N*N algorithm into NlogN (with N=100).  But in
your case, with only 12 rows in tags, creating the automatic index is a big
loser.  The stats that ANALYZE generate help the query planner to realize
this and thus avoid generating the automatic index.

You might also try:

CREATE INDEX some_name ON event(event_type);



>
> sqlite> select * from sqlite_stat1;
> tags||12
> ev_descr||11
> events||10
>
> Again, the database contains test data, so I can provide it to you (maybe
> not on the list?). The size is around 7Mb.
>
> PS: I am now a registered sqlite-user.
> PPS: thanks for a great product! Much smaller than Berkeley DB and so much
> powerful!
>
> --
> Martin
>



-- 
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] SQLite works strange

[Simon Slavin]

On 8 Apr 2011, at 2:34pm, Vadim Smirnov wrote:

> Tbl1(id1 INTEGER, id2 INTEGER, str TEXT)  2 records
> Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) ~5000 records
> And SQL-query: SELECT Tbl2.id, MY_FUNC(Tbl1.str, Tbl2.str) FROM Tbl2 JOIN 
> Tbl1 ON Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1.id2=1 LIMIT 50
> We expected that MY_FUNC would be evaluated 50 times at most, but it appeared 
> 1!
> Why so?

Your SELECT consists of a JOIN which tells SQLite to try every combination of 
one record from each table.  For each combination it checks to see that all the 
conditions you gave are true.

Since you have 2 records in one TABLE and ~5000 records in the other, that 
gives 1 times it needs to run the tests.

Try creating an index on id1 for Tbl1.  Also try creating an index on id2 for 
Tbl1.

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

Re: [sqlite] sqlite-users Digest, Vol 40, Issue 9

[Pete]

Yes, I already figured out what the problem was.  I was asking for a
solution.  The SELECT scalar query gets me what I wanted.

Pete
Molly's Revenge 




> Message: 1
> Date: Fri, 8 Apr 2011 08:17:22 -0400
> From: "Igor Tandetnik" 
> Subject: Re: [sqlite] GROUP BY Problem
> To: sqlite-users@sqlite.org
> Message-ID: 
> Content-Type: text/plain;   charset="iso-8859-1"
>
> Pete  wrote:
> > I am trying to use GROUP BY to summarise information from a main table
> and
> > two sub tables, e.g.:
> >
> > SELECT c1,c2,sum(t2.c3),count(t3.c4) FROM t1 LEFT JOIN t2 on
> t2.key2=t1.key1
> > LEFT JOIN t3.key3=t1.key1 GROUP BY t1.key1
> >
> > The result is that the count column returns the count of (the number of
> t2
> > entries * the number of t3 entries), and the sum column returns (the t2
> sum
> > value * the count of entries in t3).
>
> Of course - you are doing your sums and counts on a cartesian product of
> these two tables. I suspect you want
>
> select c1, c2,
>(select sum(c3) from t2 where key2 = t1.key1),
>(select count(c4) from t3 where key3 = t1.key1)
> from t1;
>
> --
> Igor Tandetnik
>
>
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite database corrupted

[Simon Slavin]

On 9 Apr 2011, at 02:47 PM, giorgi giorgi  wrote:

> The application is only issuing select statements (no insert/update/delete
> or vacuum) so I cannot understand what could have caused database
> corruption.

Almost definitely a problem with your hardware, or possibly an operating-system 
glitch.  I'm not going to pretend that SQLite is bug-free but if you're not 
executing write commands it has no reason to write anything at all.

Simon
-- 
Sent while away from my computer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite database corrupted

[giorgi giorgi]

Hi,

I have developed an application which is using sqlite database for querying
data from the database which ships with it. Several days ago the database
somehow got corrupted.

The application is only issuing select statements (no insert/update/delete
or vacuum) so I cannot understand what could have caused database
corruption. The database is accessed via .Net provider and is password
protected. Database size is about 500MB. Sqlite version is 3.6.23.1 There is
only one table, one index and one system table produced by Analyze command.
No triggers, no joins, no foreign keys.

The result from integrity check is:

*** in database main ***
Page 9: btreeInitPage() returns error code 11
On tree page 5 cell 13: Child page depth differs
On tree page 5 cell 14: Child page depth differs
Page 44355 is never used
Page 44356 is never used
Page 44357 is never used
Page 44358 is never used
Page 44359 is never used
Page 44360 is never used
.
.
.
Page 44577 is never used
Page 44578 is never used
Page 44579 is never used
Page 44580 is never used

Could the corruption have been caused by sqlite engine?

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

Re: [sqlite] Automatic index yields bad performance for a simple join

[Martin Gadbois]

On Sat, Apr 9, 2011 at 7:06 AM, Richard Hipp  wrote:

>
>
> On Fri, Apr 8, 2011 at 10:55 AM, Martin Gadbois wrote:
>
>> Hi there!
>>
>> I noticed a heavy slow-down due to automatic indexes. Look at the
>> following
>> output, where table "events" has 100k entries, and "tags" has ~10 entries:
>>
>
> Thanks for the details in your trouble report.  However, you left out the
> details we need the most, which are (in order):
>
> (1) The complete database scheme
> (2) The content of the sqlite_stat1 and sqlite_stat2
>
> Note that (2) won't exist if you haven't run ANALYZE, which is fine - we
> just need to know that.
>
> --
> D. Richard Hipp
> d...@sqlite.org
>

1)
The schema is as follows:
CREATE TABLE ev_descr ( event_type integer, description text);
CREATE TABLE events(
timestamp integer,
event_type integer,
parameter text,
source text,
ap blob,
ap_name text,
sc blob,
sc_name text,
port text,
vlan integer,
sta text,
vsc text,
bssid blob,
user text
);
CREATE TABLE tags ( event_type integer, tag text);

2)
ANALYZE has not been run.

If ANALYZE is ran, the speed is fast: there are no auto-index.

sqlite> select * from sqlite_stat1;
tags||12
ev_descr||11
events||10

Again, the database contains test data, so I can provide it to you (maybe
not on the list?). The size is around 7Mb.

PS: I am now a registered sqlite-user.
PPS: thanks for a great product! Much smaller than Berkeley DB and so much
powerful!

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

Re: [sqlite] How is the page-cache filled?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/08/2011 12:11 PM, Clemens Eisserer wrote:
> * Which version of the source should I use? The amalgamized source
> isn't really useful, however for all other source-archives its stated
> its not recommended using.

You should check out the code and use Fossil (the SQLite source code control
tool).  That way you will be using the same source and files as the authors.
 You'll also be able to keep up to date with their changes.  See the very
bottom of http://sqlite.org/download.html

This will also allow you to work with the test suite and add your own tests.
 Your code changes won't actually be useful if they break things!

> * Where are pages read and written? I found the page-cache, but not
> the functions that read/write those pages to disk. A small hint would
> be really helpful :)

Pavel already pointed you to where the actual read/write calls are made -
VFS.  However controlling when and why they are made are in the pager code.
 When you work on the real SQLite source you'll find pager.c and pcache.c.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk2gSg8ACgkQmOOfHg372QQzzgCgq4khVN7a43Y4qQhW1TXcixOS
LIAAn1rmnOyIklV/z6STvvE9BN5F3XEn
=2tds
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Automatic index yields bad performance for a simple join

[Richard Hipp]

On Fri, Apr 8, 2011 at 10:55 AM, Martin Gadbois  wrote:

> Hi there!
>
> I noticed a heavy slow-down due to automatic indexes. Look at the following
> output, where table "events" has 100k entries, and "tags" has ~10 entries:
>

Thanks for the details in your trouble report.  However, you left out the
details we need the most, which are (in order):

(1) The complete database scheme
(2) The content of the sqlite_stat1 and sqlite_stat2

Note that (2) won't exist if you haven't run ANALYZE, which is fine - we
just need to know that.



>
> SQLite version 3.7.4
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .stats on
> sqlite> .timer on
> sqlite> SELECT events.rowid FROM tags, events WHERE tags.tag = "security"
> AND tags.event_type = events.event_type limit 1;
> 1
> Memory Used: 2618712 (max 4011800) bytes
> Number of Allocations:   2127 (max 3211)
> Number of Pcache Overflow Bytes: 2543752 (max 3910408) bytes
> Number of Scratch Overflow Bytes:0 (max 8384) bytes
> Largest Allocation:  48000 bytes
> Largest Pcache Allocation:   1272 bytes
> Largest Scratch Allocation:  8384 bytes
> Lookaside Slots Used:7 (max 40)
> Pager Heap Usage:2544416 bytes
> Schema Heap Usage:   3784 bytes
> Statement Heap/Lookaside Usage:  2928 bytes
> Fullscan Steps:  5
> Sort Operations: 0
> Autoindex Inserts:   *9*
> CPU Time: user 0.22 sys 0.01
> sqlite> pragma automatic_index = 0;
> sqlite> SELECT events.rowid FROM tags, events WHERE tags.tag = "security"
> AND tags.event_type = events.event_type limit 1;
> 1
> Memory Used: 2618488 (max 4011800) bytes
> Number of Allocations:   2127 (max 3211)
> Number of Pcache Overflow Bytes: 2543752 (max 3910408) bytes
> Number of Scratch Overflow Bytes:0 (max 8384) bytes
> Largest Allocation:  48000 bytes
> Largest Pcache Allocation:   1272 bytes
> Largest Scratch Allocation:  8384 bytes
> Lookaside Slots Used:4 (max 40)
> Pager Heap Usage:2544416 bytes
> Schema Heap Usage:   3784 bytes
> Statement Heap/Lookaside Usage:  2416 bytes
> Fullscan Steps:  5
> Sort Operations: 0
> Autoindex Inserts:   0
> CPU Time: user 0.00 sys 0.00
>
> Re-enabling autoindex yields again poor results with the same query.
> I can work around it by adding "ORDER BY events.rowid" to the query.
>
> Would SQLite "remember" the automatic query instead of creating 9
> temporary automatic indexes? Is this a bug or mis-use?
> I can provide the database if necessary.
>
> PS: Please CC me directly, my corporate firewall does not allow me to
> register to the list (port 8080).
>
> --
> Martin
> ___
> 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] Automatic index yields bad performance for a simple join

[Martin Gadbois]

Hi there!

I noticed a heavy slow-down due to automatic indexes. Look at the following
output, where table "events" has 100k entries, and "tags" has ~10 entries:

SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .stats on
sqlite> .timer on
sqlite> SELECT events.rowid FROM tags, events WHERE tags.tag = "security"
AND tags.event_type = events.event_type limit 1;
1
Memory Used: 2618712 (max 4011800) bytes
Number of Allocations:   2127 (max 3211)
Number of Pcache Overflow Bytes: 2543752 (max 3910408) bytes
Number of Scratch Overflow Bytes:0 (max 8384) bytes
Largest Allocation:  48000 bytes
Largest Pcache Allocation:   1272 bytes
Largest Scratch Allocation:  8384 bytes
Lookaside Slots Used:7 (max 40)
Pager Heap Usage:2544416 bytes
Schema Heap Usage:   3784 bytes
Statement Heap/Lookaside Usage:  2928 bytes
Fullscan Steps:  5
Sort Operations: 0
Autoindex Inserts:   *9*
CPU Time: user 0.22 sys 0.01
sqlite> pragma automatic_index = 0;
sqlite> SELECT events.rowid FROM tags, events WHERE tags.tag = "security"
AND tags.event_type = events.event_type limit 1;
1
Memory Used: 2618488 (max 4011800) bytes
Number of Allocations:   2127 (max 3211)
Number of Pcache Overflow Bytes: 2543752 (max 3910408) bytes
Number of Scratch Overflow Bytes:0 (max 8384) bytes
Largest Allocation:  48000 bytes
Largest Pcache Allocation:   1272 bytes
Largest Scratch Allocation:  8384 bytes
Lookaside Slots Used:4 (max 40)
Pager Heap Usage:2544416 bytes
Schema Heap Usage:   3784 bytes
Statement Heap/Lookaside Usage:  2416 bytes
Fullscan Steps:  5
Sort Operations: 0
Autoindex Inserts:   0
CPU Time: user 0.00 sys 0.00

Re-enabling autoindex yields again poor results with the same query.
I can work around it by adding "ORDER BY events.rowid" to the query.

Would SQLite "remember" the automatic query instead of creating 9
temporary automatic indexes? Is this a bug or mis-use?
I can provide the database if necessary.

PS: Please CC me directly, my corporate firewall does not allow me to
register to the list (port 8080).

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

[sqlite] SQLite works strange

[Vadim Smirnov]

Hello!

We have performance trouble with SQLite.
We have created custom function MY_FUNC(a TEXT, b TEXT) and bind it with 
sqlite3_create_function.
So, we have 2 tables:
Tbl1(id1 INTEGER, id2 INTEGER, str TEXT)  2 records
Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) ~5000 records
And SQL-query: SELECT Tbl2.id, MY_FUNC(Tbl1.str, Tbl2.str) FROM Tbl2 JOIN Tbl1 
ON Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1.id2=1 LIMIT 50
We expected that MY_FUNC would be evaluated 50 times at most, but it appeared 
1!
Why so?
We using SQLite ver. 3.6.23.1

Sincerely Yours,
Smirnov Wadim Yurievich
System architector
Positive Technologies

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

[sqlite] Bug Report: Prepared statement doesn't drop previously not defined table

[Robert Lehmkühler]

Version: SQLite 3.7.5
OS: Windows 7 Professional / Ubuntu 10.10
Compiler: gcc 4.4.5 (Ubuntu/Linaro 4.4.4-14ubuntu5) / Visual Studio
2010 Version 10.0.30319.1  [cl.exe(Version 16.00.30319.01)]

SQLite doesn't recognize the creation of a table for updating a
prepared statement that could drop this table. I've written a small
test to show the effect. At the end of this test the database named
'test' shouldn't have any tables except 'sqlite_master' but you will
also find 'test'.

Begin:
#include 
#include "sqlite3.h"

int main(int argc,char* argv[])
{
sqlite3 *db;
sqlite3_stmt *create;
sqlite3_stmt *drop;

int ret=0;
printf("Delete 'test'\n");
remove("test");
printf("Create 'test'\n");
ret = sqlite3_open("test",);
if(ret != SQLITE_OK)
{
printf("%s",sqlite3_errmsg(db));
return -1;
}

printf("Create prepared statement for table creation\n");
ret = sqlite3_prepare_v2(db,"Create Table test (a 
INTEGER)",-1,,0);
if(ret != SQLITE_OK)
{
printf("%s",sqlite3_errmsg(db));
return -2;
}

printf("Create prepared statement for table dropping\n");
ret = sqlite3_prepare_v2(db,"Drop Table If Exists test",-1,,0);
if(ret != SQLITE_OK)
{
printf("%s",sqlite3_errmsg(db));
return -3;
}

printf("Create table 'test' by prepared statement\n");
ret = sqlite3_step(create);
if(ret != SQLITE_DONE)
{
printf("%s",sqlite3_errmsg(db));
return -4;
}
ret = sqlite3_reset(create);
if(ret != SQLITE_OK)
{
printf("%s",sqlite3_errmsg(db));
return -5;
}

printf("Should drop table 'test' by prepared statement\n");
ret = sqlite3_step(drop);
if(ret != SQLITE_DONE)
{
printf("%s",sqlite3_errmsg(db));
return -6;
}
ret = sqlite3_reset(drop);
if(ret != SQLITE_OK)
{
printf("%s",sqlite3_errmsg(db));
return -7;
}

ret = sqlite3_finalize(drop);
if(ret != SQLITE_OK)
{
printf("%s",sqlite3_errmsg(db));
return -8;
}
ret = sqlite3_finalize(create);
if(ret != SQLITE_OK)
{
printf("%s",sqlite3_errmsg(db));
return -9;
}
ret = sqlite3_close(db);
if(ret != SQLITE_OK)
{
printf("%s",sqlite3_errmsg(db));
return -10;
}
printf("Now you can test 'test' if there is a table named 'test'
where it shouldn't\n");
return 0;
}
End
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] completion of sql words

[Edzard Pasma]

Here is the unbroken link:

http://apidoc.apsw.googlecode.com/hg/shell.html

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