Re: [sqlite] Sqlite RTree nearest neighbour

2014-08-21 Thread Richard Hipp 
On Thu, Aug 21, 2014 at 8:54 PM, Peter Aronson  wrote:

> 1. You would need to access SQLite's R-Tree "shadow" tables
> (xx_node, xx_parent, xx_rowid) directly in to perform the traversals
> required by all of the algorithms -- I don't know if this is officially
> supported by SQLite's developers, or if these tables are guaranteed not to
> change;
>

The format of the shadow tables will not change in ways that would break
older versions of SQLite.

-- 
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 RTree nearest neighbour

2014-08-21 Thread Peter Aronson 
According to R-Trees: Theory and Applications by Yannis Manolopoulos, 
Alexandros Nanopoulos, Apostolos N. Papadopoulos and Yannis Theodoridis, there 
are a number of algorithms for efficiently determining the nearest neighbor(s) 
using an R-Tree (an internet search on the two terms will pull up several).  
There are two things to keep in mind about this:
1. You would need to access SQLite's R-Tree "shadow" tables (xx_node, 
xx_parent, xx_rowid) directly in to perform the traversals required by all of 
the algorithms -- I don't know if this is officially supported by SQLite's 
developers, or if these tables are guaranteed not to change;
2. If your dimension is > 1, the R-Tree alone can't give you a reliable 
answer about who is closer, you would also need a method to calculate the 
minimum distance between two indexed objects.
Peter


On Thursday, August 21, 2014 2:32 PM, skywind mailing lists 
 wrote:
 

>
>
>Hello,
>
>does anybody have any experience with implementing a nearest neighbor search 
>using SQLite's RTree functionality? Is a nearest neighbor search possible?
>
>Regards,
>Hartwig
>
>
>___
>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] Mixing journal modes from different threads

2014-08-21 Thread Teg 
Hello George,

I use different journal modes within the same application to different
database  files.   My  files don't all have the same protection level.
Some  files I turn it off completely for better speed. Others, I can't
afford to lose the data.

C

Thursday, August 21, 2014, 3:17:05 PM, you wrote:

GI> Good evening Dr. Hipp,

GI> ok, got it. Thank you for the quick clarification.

GI> I only asked on the mailing list because I couldn't find this kind of
GI> information in the docs (or I didn't look thoroughly enough); if it's the
GI> first case, perhaps it should be mentioned somewhere...

GI> Thanks again,
GI> George.


GI> On Thu, Aug 21, 2014 at 10:07 PM, Richard Hipp  wrote:

>> On Thu, Aug 21, 2014 at 3:05 PM, George Ionescu 
>> wrote:
>>
>> > Hello dear sqlite users,
>> >
>> > is it ok to mix journal modes from different threads accessing the same
>> > database?
>> >
>>
>> SQLite does not allow you to mix WAL mode with other journal modes.
>> Wal-mode is a property of the database file.  If you change to WAL-mode,
>> then *all* database connections must go to WAL-mode.
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
GI> ___
GI> sqlite-users mailing list
GI> sqlite-users@sqlite.org
GI> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Sqlite RTree nearest neighbour

2014-08-21 Thread Carlos Ferreira 
I am not an expert in Sqlite R-tree, but it seems that if you want to solve
a nearest neighbor you need not only to search the objects in the leaf
containing the object you testing, but also some adjacent leaves around.

Another option would be to search for objects inside a  centered box or
sphere over the object, starting with a small box containing anything else
other than the object and start increasing the size until you get one or
more objects inside...From there you would just have to select the closest
one...

Probably there is some much easier way and I am not familiar with it...


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: quinta-feira, 21 de Agosto de 2014 23:09
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sqlite RTree nearest neighbour


On 21 Aug 2014, at 10:32pm, skywind mailing lists 
wrote:

> does anybody have any experience with implementing a nearest neighbor
search using SQLite's RTree functionality? Is a nearest neighbor search
possible?

How much have you read ?  Are you familiar with SpaciaLite ?  Have you tried
implementing Nearest Neighbour without RTree ?

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

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

Re: [sqlite] Sqlite RTree nearest neighbour

2014-08-21 Thread Simon Slavin 

On 21 Aug 2014, at 10:32pm, skywind mailing lists  
wrote:

> does anybody have any experience with implementing a nearest neighbor search 
> using SQLite's RTree functionality? Is a nearest neighbor search possible?

How much have you read ?  Are you familiar with SpaciaLite ?  Have you tried 
implementing Nearest Neighbour without RTree ?

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

[sqlite] Sqlite RTree nearest neighbour

2014-08-21 Thread skywind mailing lists 
Hello,

does anybody have any experience with implementing a nearest neighbor search 
using SQLite's RTree functionality? Is a nearest neighbor search possible?

Regards,
Hartwig


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

Re: [sqlite] Mixing journal modes from different threads

2014-08-21 Thread Simon Slavin 

On 21 Aug 2014, at 8:17pm, George Ionescu  wrote:

> I only asked on the mailing list because I couldn't find this kind of
> information in the docs (or I didn't look thoroughly enough); if it's the
> first case, perhaps it should be mentioned somewhere...



"This pragma queries or sets the journal mode for databases associated with the 
current database connection."

Note that it says that it changes the journalling mode for the databases.  Not 
for the connection to the databases, but for the databases themselves.

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

Re: [sqlite] Mixing journal modes from different threads

2014-08-21 Thread George Ionescu 
Good evening Dr. Hipp,

ok, got it. Thank you for the quick clarification.

I only asked on the mailing list because I couldn't find this kind of
information in the docs (or I didn't look thoroughly enough); if it's the
first case, perhaps it should be mentioned somewhere...

Thanks again,
George.


On Thu, Aug 21, 2014 at 10:07 PM, Richard Hipp  wrote:

> On Thu, Aug 21, 2014 at 3:05 PM, George Ionescu 
> wrote:
>
> > Hello dear sqlite users,
> >
> > is it ok to mix journal modes from different threads accessing the same
> > database?
> >
>
> SQLite does not allow you to mix WAL mode with other journal modes.
> Wal-mode is a property of the database file.  If you change to WAL-mode,
> then *all* database connections must go to WAL-mode.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mixing journal modes from different threads

2014-08-21 Thread Richard Hipp 
On Thu, Aug 21, 2014 at 3:05 PM, George Ionescu 
wrote:

> Hello dear sqlite users,
>
> is it ok to mix journal modes from different threads accessing the same
> database?
>

SQLite does not allow you to mix WAL mode with other journal modes.
Wal-mode is a property of the database file.  If you change to WAL-mode,
then *all* database connections must go to WAL-mode.

-- 
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] Mixing journal modes from different threads

2014-08-21 Thread George Ionescu 
Hello dear sqlite users,

is it ok to mix journal modes from different threads accessing the same
database?

The typical scenario is that I use one thread for reading using normal
(default) journalling (e.g. only SELECT queries are performed) and two to
four threads for writing using WAL journalling.

I'm using it like that because most of the times I'm using SELECT queries
only and I don't want journal files to be created (as it's the case with
WAL).

Is this the right way of doing it?

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

Re: [sqlite] Severe performance degradation between 3.8.4.3 and

2014-08-21 Thread Richard Hipp 
On Thu, Aug 21, 2014 at 2:15 PM, Mario M. Westphal  wrote:

>
> When I understand you correctly, I should/must run an ANALYSIS on existing
> databases
>

SQLite will get the correct answer regardless.  But you might get the
answer *faster* if you run ANALYZE after significant changes to the
database.  But you should always get an equivalent answer.

Additional discussion can be found at:

 http://www.sqlite.org/optoverview.html
 http://www.sqlite.org/queryplanner-ng.html

-- 
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] Severe performance degradation between 3.8.4.3 and

2014-08-21 Thread Mario M. Westphal 
The sample database was produced by a version of my software which runs the 
previous (or even an older version of SQLite). 

My software runs an Analysis as part of a weekly database maintenance 
procedure. But the users can turn this off or delay it for weeks. Various 
versions of my software are in use, and each version links against a different 
version of SQLite. Not all users keep up with upgrades, or skip some of the 
monthly updates...

When I understand you correctly, I should/must run an ANALYSIS on existing 
databases after shipping a new version of SQLite with my application? In case 
the statistics data in existing databases causes the updated optimizer to 
choose slower execution paths? This can be arranged. 

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

Re: [sqlite] Severe performance degradation between 3.8.4.3 and

2014-08-21 Thread Richard Hipp 
On Thu, Aug 21, 2014 at 7:35 AM, Mario M. Westphal  wrote:

> Hi, Richard
>
> I have prepared a sample database, sample statements and some additional
> details and sent it to your email address.
>

Thanks for sending the sample data.  Here is what I found:

Your ANALYZE information (stored in the sqlite_stat1 and sqlite_stat3
tables) is out-of-date and no longer reflects the shape of the actual data
in the database.  You can fix this in either of two ways:

(1) Rerun ANALYZE

(2) Remove the analysis using:  "DROP TABLE sqlite_stat1; DROP TABLE
sqlite_stat3;"

If you do either of the above, the second query is fast again.  I don't
know about the first query because it depends on the "_temptable" table
which is not a part of the package you sent, so I am unable to run it.


-- 
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] How do I know the python functions registered on SQLite ?

2014-08-21 Thread Stephan Beal 
On Thu, Aug 21, 2014 at 3:46 PM, Baruch Burstein 
wrote:

> Any explanation? I ran into this issue today when using the fossil built-in
> sqlite shell, and I thought I remembered that it has a function registered
> for getting a raw blob, but couldn't remember the name.


There is one (select content('trunk')), but it's not available via the
'sqlite' command, which is basically just a thin shell (as it were) around
the standard sqlite3 shell. Before it launches, the Fossil instance gets
shut down, so the underlying sqlite handle has no association with Fossil,
i.e. with features like blob-fetching.

It's unfortunate, but i don't see a way to solve it without forking the
sqlite shell (which i'm not willing to do). OTOH, libfossil can do this ;)


http://fossil.wanderinghorse.net/repos/libfossil/index.cgi/wiki?name=f-tools
(specifically, f-query)

http://fossil.wanderinghorse.net/repos/libfossil/index.cgi/wiki?name=DbFunctions

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I know the python functions registered on SQLite ?

2014-08-21 Thread Dominique Devienne 
On Thu, Aug 21, 2014 at 3:46 PM, Baruch Burstein 
wrote:

> On Sun, May 11, 2014 at 12:58 PM, Baruch Burstein 
> > On Thu, May 8, 2014 at 11:46 PM, Roger Binns 
> >> SQLite could provide the information as a virtual table or similar.  A
> >> ticket was created 7 years ago asking for it, and closed 2 months ago by
> >> the team with resolution "Rejected":
> >>
> >>   https://www.sqlite.org/src/tktview?name=5896edbe46
> >
> > Just out of curiosity, why was this rejected? Especially after being left
> > open (supposedly "might do someday") for almost 7 years. What changed? It
> > seems like a logical request.
> >
>
> Any explanation? I ran into this issue today when using the fossil built-in
> sqlite shell, and I thought I remembered that it has a function registered
> for getting a raw blob, but couldn't remember the name. So I was looking
> for a sqlite shell dot-command for listing registered functions, but of
> course there is no such thing because it would need this API implemented. I
> had to go dig in the fossil code to figure it out.
>

I also don't get the absence of this "feature", and of course the rejection
mentioned above. So logical, and needed. --DD

uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>

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

Re: [sqlite] How do I know the python functions registered on SQLite ?

2014-08-21 Thread Baruch Burstein 
On Sun, May 11, 2014 at 12:58 PM, Baruch Burstein 
wrote:

>
> On Thu, May 8, 2014 at 11:46 PM, Roger Binns 
> wrote:
>
>>
>> SQLite could provide the information as a virtual table or similar.  A
>> ticket was created 7 years ago asking for it, and closed 2 months ago by
>> the team with resolution "Rejected":
>>
>>   https://www.sqlite.org/src/tktview?name=5896edbe46
>
>
> Just out of curiosity, why was this rejected? Especially after being left
> open (supposedly "might do someday") for almost 7 years. What changed? It
> seems like a logical request.
>

Any explanation? I ran into this issue today when using the fossil built-in
sqlite shell, and I thought I remembered that it has a function registered
for getting a raw blob, but couldn't remember the name. So I was looking
for a sqlite shell dot-command for listing registered functions, but of
course there is no such thing because it would need this API implemented. I
had to go dig in the fossil code to figure it out.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about coalesce and data types

2014-08-21 Thread Keith Medcalf 


>Is there a way to write my own coalesce-Function (or indeed any
>function) so that its result has an affinity? The documentation of the
>sqlite3_result_* family of functions suggests not.

No.  But you can cast the result to whatever type you wish:

cast(coalesce(a, 5) as TEXT)

and it will then have a type affinity.




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

Re: [sqlite] Severe performance degradation between 3.8.4.3 and

2014-08-21 Thread Richard Hipp 
On Thu, Aug 21, 2014 at 7:35 AM, Mario M. Westphal  wrote:

> Hi, Richard
>
> I have prepared a sample database, sample statements and some additional
> details and sent it to your email address.
>

Thanks for the info.  I'll look into the performance regression as soon as
I get a chance.  Right now we have a more pressing problem to deal with:
www.sqlite.org/src/tktview/369d57fb8e5c - sorry for the delay.

-- 
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] Severe performance degradation between 3.8.4.3 and

2014-08-21 Thread Mario M. Westphal 
Hi, Richard

I have prepared a sample database, sample statements and some additional 
details and sent it to your email address.

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

Re: [sqlite] Question about coalesce and data types

2014-08-21 Thread Martin Engelschalk 


Am 21.08.2014 11:39, schrieb Clemens Ladisch:

Martin Engelschalk wrote:

It seems the solution is to actually pass all bind variable values by
their appropriate sqlite3_bind_* - function instead of just using
sqlite3_bind_text. However, this means quite a lot of work for me.

Isn't it also work for you to converting your values to text?
Yes, but this would mean to change a LOT of SQL and then testing it, 
Also, the SQL is generic and has to work on Postgres and Oracle too.

I will change the binding.



Is there a way to write my own Function so that its result has
an affinity?

No.


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

Thank you very much, i see my way now.

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

Re: [sqlite] Severe performance degradation between 3.8.4.3 and

2014-08-21 Thread Richard Hipp 
On Thu, Aug 21, 2014 at 5:18 AM, Richard Hipp  wrote:

>
>
>
> On Thu, Aug 21, 2014 at 3:21 AM, Mario M. Westphal  wrote:
>
>> Hi,
>>
>>
>>
>> Information provided as requested.
>>
>
> But not in a form that we can use.
>
> Please bring up your database file in a new 3.8.6 sqlite3.exe shell and
> type ".fullschema" and then post the output.  You might want to make use of
> the ".once" command too:
>
>   .once out.txt
>   .fullschema
>
> The above will put the needed information in the file out.txt.  Exit the
> shell.  Type "start out.txt".  Then scrap the text and post it here.
>

Or, if you wish, you can send me the complete database file as an
attachment in a private email.



>
> Thanks.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>



-- 
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] Question about coalesce and data types

2014-08-21 Thread Clemens Ladisch 
Martin Engelschalk wrote:
> It seems the solution is to actually pass all bind variable values by
> their appropriate sqlite3_bind_* - function instead of just using
> sqlite3_bind_text. However, this means quite a lot of work for me.

Isn't it also work for you to converting your values to text?

> Is there a way to write my own Function so that its result has
> an affinity?

No.


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

Re: [sqlite] Severe performance degradation between 3.8.4.3 and

2014-08-21 Thread Clemens Ladisch 
Mario M. Westphal wrote:
> _temptable is a temporary table which contains a list of oids (integer, ~ 10 
> rows) to consider.

The information that oid is INTEGER PRIMARY KEY would have been helpful ...

> For query 2.1
>
> selectid order from  detail
> 1  0  0  SCAN TABLE stack_elem AS e USING 
> INDEX idx_stack_elem_oid
> ...

Slow or fast?  And the output for the other version is needed, too.


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

Re: [sqlite] Severe performance degradation between 3.8.4.3 and

2014-08-21 Thread Richard Hipp 
On Thu, Aug 21, 2014 at 3:21 AM, Mario M. Westphal  wrote:

> Hi,
>
>
>
> Information provided as requested.
>

But not in a form that we can use.

Please bring up your database file in a new 3.8.6 sqlite3.exe shell and
type ".fullschema" and then post the output.  You might want to make use of
the ".once" command too:

  .once out.txt
  .fullschema

The above will put the needed information in the file out.txt.  Exit the
shell.  Type "start out.txt".  Then scrap the text and post it here.

Thanks.


-- 
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] Question about coalesce and data types

2014-08-21 Thread Martin Engelschalk 

Hello Clemens,

thank you for your answer; i understand now why the where - condition 
returns 'false'. Also, the effect is independent of the function used.


It seems the solution is to actually pass all bind variable values by 
their appropriate sqlite3_bind_* - function instead of just using 
sqlite3_bind_text. However, this means quite a lot of work for me.
Is there a way to write my own coalesce-Function (or indeed any 
function) so that its result has an affinity? The documentation of the 
sqlite3_result_* family of functions suggests not.


Thank you
Martin


Am 20.08.2014 12:03, schrieb Clemens Ladisch:

Martin Engelschalk wrote:

create table TestTable (col_a numeric);
insert into  TestTable (col_a) values (1);

retrieve the row, as expected:

select * from TestTable where col_a = '1';

do not retrieve the row:

select * from TestTable where coalesce(col_a, 5) = '1'

Can someone please explain this to me or point me to some documentation?

The col_a column has numeric affinity, so the string '1' is converted
into a number.  

The return value of the function has NONE affinity, so no automatic
conversion happens.  


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


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] Severe performance degradation between 3.8.4.3 and

2014-08-21 Thread Mario M. Westphal 
Hi,

 

Information provided as requested.

 

_temptable is a temporary table which contains a list of oids (integer, ~ 10 
rows) to consider.

 

 

Stats3

 

tbl   idx  neqnlt   ndltsample

stack_elem idx_rel_stack_elem_soid 4  0 
 0  406

stack_elem idx_rel_stack_elem_oid   1  0
  0  4

stack_elem idx_rel_stack_elem_oid   1  1
  1  5

stack_elem idx_rel_stack_elem_oid   1  2
  2  6

stack_elem idx_rel_stack_elem_oid   1  3
  3  133

stack_elem idx_rel_stack_elem_soid_oid4  0  
0  406

 

Stats2

 

 

tbl   idx  stat

stack  idx_rel_stack_toid_rtype 210 1 1

stack_elem idx_rel_stack_elem_soid 4 4

stack_elem idx_rel_stack_elem_oid   4 1

stack_elem idx_rel_stack_elem_soid_oid4 4 1

 

 

For query 2.1

 

selectid order from  detail

1  0  0  SCAN TABLE stack_elem AS e USING 
INDEX idx_stack_elem_oid

1  1  1  SEARCH TABLE stack AS s USING 
INTEGER PRIMARY KEY (rowid=?)

1  2  2  SEARCH TABLE _temptable AS _t 
USING INTEGER PRIMARY KEY (rowid=?)

1  3  3  SEARCH TABLE _temptable AS _t2 
USING INTEGER PRIMARY KEY (rowid=?)

2  0  1  SCAN TABLE stack AS s

2  1  2  SEARCH TABLE _temptable AS _t 
USING INTEGER PRIMARY KEY (rowid=?)

2  2  0  SEARCH TABLE rel_rel AS r USING 
COVERING INDEX idx_rel_rel (moid=?)

2  0  0  USE TEMP B-TREE FOR DISTINCT

2  0  0  USE TEMP B-TREE FOR ORDER BY

0  0  0  COMPOUND SUBQUERIES 1 AND 2 (UNION)

 

For query 2.2

 

selectid order from  detail

0  0  0  SEARCH TABLE stack USING INTEGER 
PRIMARY KEY (rowid=?)

0  0  0  EXECUTE LIST SUBQUERY 0

0  0  0  SCAN TABLE stack USING COVERING 
INDEX idx_stack_toid_rtype

0  1  1  SEARCH TABLE stack_elem USING 
COVERING INDEX idx_stack_elem_soid_oid (soid=?)

 

 

 

 

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

Re: [sqlite] Severe performance degradation between 3.8.4.3 and 3.8.6

2014-08-21 Thread Clemens Ladisch 
Mario M. Westphal wrote:
> The new version is 10 or more times slower than the previous build I used 
> (3.8.4.3).
> [...]
> If more information or sample data is needed, let me know.

What is _temptable?
If you have run ANALZYE, what are the contents of the sqlite_stat* tables?

What is the EXPLAIN QUERY PLAN output in both versions?


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