subject:"Re\: \[sqlite\] SELECT WHERE with RTREE and second condition slow"

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread Thomas Flemming


> Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"?
> That's joining two tables together.

This is just because of the rtree, which is in Pois_bb 
(http://www.sqlite.org/rtree.html), has nothing to do with the second 
condition "styleid IN .."




Am 30.05.2017 um 18:29 schrieb David Raymond:

If you scroll down in my previous reply I put the explain query plan outputs in 
with the queries. Guess I should have mentioned that. (Re-copied them below)

It was using the index on StyleId, thinking that was going to be faster. What 
Dr Hipp suggested in adding the unary + operator does is turn that into an 
expression rather than the raw field in the eyes of the planner, so it doesn't 
use that index on StyleId.

"but the query is not in two tables.
its two select-where in the same table."
Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"?
That's joining two tables together. The fields you wanted returned may only be 
from one of them, but it's still a join. The quick version(s) have Pois_bb as 
the outer loop and Pois as the inner loop. The slowed down version had Pois as 
the outer loop and Pois_bb as the inner loop.



SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
 AND x0 < 30.46203 AND  x1 > 30.00074766
 AND 18 BETWEEN z0 AND z1
 AND Pois_bb.Id = Pois.Id;

Run Time: real 0.109 user 0.00 sys 0.00
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)


SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
 AND x0 < 30.46203 AND  x1 > 30.00074766
 AND 18 BETWEEN z0 AND z1
 AND Pois_bb.Id = Pois.Id
 AND styleid IN (9,48,73,200,142,31,219);

Run Time: real 9.422 user 5.132433 sys 4.212027
selectid|order|from|detail
0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
0|0|0|EXECUTE LIST SUBQUERY 1
0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:


Forcing the order with CROSS JOIN
SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > 
-15.12862
 AND x0 < 30.46203 AND  x1 > 30.00074766
 AND 18 BETWEEN z0 AND z1
 AND Pois_bb.Id = Pois.Id
 AND styleid IN (9,48,73,200,142,31,219);

Run Time: real 0.078 user 0.00 sys 0.00
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
0|0|0|EXECUTE LIST SUBQUERY 1


With the unary + operator
SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > 
-15.12862
 AND x0 < 30.46203 AND  x1 > 30.00074766
 AND 18 BETWEEN z0 AND z1
 AND Pois_bb.Id = Pois.Id
 AND +styleid IN (9,48,73,200,142,31,219);
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
0|0|0|EXECUTE LIST SUBQUERY 1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread E.Pasma

Thomas Flemming Tue, 30 May 2017 09:43:15 -0700


>> Try putting a "+" symbol before "styleid".  Like this:
>>
>> AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762)


> THATS IT !! :-)))
> 
> 50ms with +, and 15000ms without the +
>
> How is that possible?

Hello, best explained here:
http://sqlite.org/optoverview.html#uplus
(a most interesting document anyway)
Regards, E.  Pasma

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

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread David Raymond

If you scroll down in my previous reply I put the explain query plan outputs in 
with the queries. Guess I should have mentioned that. (Re-copied them below)

It was using the index on StyleId, thinking that was going to be faster. What 
Dr Hipp suggested in adding the unary + operator does is turn that into an 
expression rather than the raw field in the eyes of the planner, so it doesn't 
use that index on StyleId.

"but the query is not in two tables.
its two select-where in the same table."
Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"?
That's joining two tables together. The fields you wanted returned may only be 
from one of them, but it's still a join. The quick version(s) have Pois_bb as 
the outer loop and Pois as the inner loop. The slowed down version had Pois as 
the outer loop and Pois_bb as the inner loop.



SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
AND x0 < 30.46203 AND  x1 > 30.00074766
AND 18 BETWEEN z0 AND z1
AND Pois_bb.Id = Pois.Id;

Run Time: real 0.109 user 0.00 sys 0.00
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)


SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
AND x0 < 30.46203 AND  x1 > 30.00074766
AND 18 BETWEEN z0 AND z1
AND Pois_bb.Id = Pois.Id
AND styleid IN (9,48,73,200,142,31,219);

Run Time: real 9.422 user 5.132433 sys 4.212027
selectid|order|from|detail
0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
0|0|0|EXECUTE LIST SUBQUERY 1
0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:


Forcing the order with CROSS JOIN
SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > 
-15.12862
AND x0 < 30.46203 AND  x1 > 30.00074766
AND 18 BETWEEN z0 AND z1
AND Pois_bb.Id = Pois.Id
AND styleid IN (9,48,73,200,142,31,219);

Run Time: real 0.078 user 0.00 sys 0.00
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
0|0|0|EXECUTE LIST SUBQUERY 1


With the unary + operator
SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > 
-15.12862
AND x0 < 30.46203 AND  x1 > 30.00074766
AND 18 BETWEEN z0 AND z1
AND Pois_bb.Id = Pois.Id
AND +styleid IN (9,48,73,200,142,31,219);
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
0|0|0|EXECUTE LIST SUBQUERY 1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread Thomas Flemming

> Try putting a "+" symbol before "styleid".  Like this:
>
> AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762)

THATS IT !! :-)))

50ms with +, and 15000ms without the +

How is that possible?

Am 30.05.2017 um 17:36 schrieb Richard Hipp:

On 5/27/17, Thomas Flemming  wrote:

Hi,

I have a table Pois with points of interest (geogr. coordinate, label,
styleid) where I do regional querys using a rtree-index:

SELECT Pois.* FROM Pois_bb, Pois WHERE  y0 < -14.8600 AND  y1 > -15.12862
AND   x0 < 30.46203 AND  x1 > 30.00074766
AND   18 BETWEEN z0 AND z1
AND   Pois_bb.Id = Pois.Id
Thats very fast, 50ms.

The problem is, when I add a second condition to get certain poi-types only
in the area:

AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762

Try putting a "+" symbol before "styleid".  Like this:

AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762)

The query becomes really slow, 800ms.
There is of course also an index on styleid.

I also realized, just this query:

SELECT * FROM Pois WHERE styleid IN
1351,1362,1371,1374,1376,1542,1595,1597,1643,1762

is also slow for the first call. The second call is fast.

(Using SQLite Expert Professional 3.5 for testing).

Any ideas, how to speed this up?

Thanks
Tom

--
/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread Richard Hipp

On 5/27/17, Thomas Flemming  wrote:
> Hi,
>
> I have a table Pois with points of interest (geogr. coordinate, label,
> styleid) where I do regional querys using a rtree-index:
>
> SELECT Pois.* FROM Pois_bb, Pois WHERE  y0 < -14.8600 AND  y1 > -15.12862
>AND   x0 < 30.46203 AND  x1 > 30.00074766
>AND   18 BETWEEN z0 AND z1
>AND   Pois_bb.Id = Pois.Id
> Thats very fast, 50ms.
>
> The problem is, when I add a second condition to get certain poi-types only
> in the area:
>
> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762

Try putting a "+" symbol before "styleid".  Like this:

   AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762)


>
> The query becomes really slow, 800ms.
> There is of course also an index on styleid.
>
> I also realized, just this query:
>
> SELECT * FROM Pois WHERE styleid IN
> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> is also slow for the first call. The second call is fast.
>
> (Using SQLite Expert Professional 3.5 for testing).
>
> Any ideas, how to speed this up?
>
> Thanks
> Tom
>
>
> --
> /
> **   Flemming Software Development CC
> **   Thomas Flemming
> **   PO Box 81244
> **   Windhoek, Namibia
> **   http://www.quovadis-gps.com
> **   mail  t...@qvgps.com
> **   +264 (0)81 3329923
> **   +49  (0)6182 8492599
> ***/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread Thomas Flemming


but the query is not in two tables.
its two select-where in the same table.

Am 30.05.2017 um 17:18 schrieb Hick Gunter:

Just like any other join, but with tables in the desired order and the word 
CROSS added

SELECT ... FROM  CROSS JOIN  ...

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Flemming
Gesendet: Dienstag, 30. Mai 2017 18:15
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow


force it to go the way you want by using "cross join" to force the
ordering of


How would such "cross join" statemant look like?


Am 30.05.2017 um 16:38 schrieb David Raymond:

It looks like it thinks that using the index on StyleId is going to be
the most beneficial, as opposed to the rtree index. How it compares a
normal index's stat1 to a virtual table's stat1 I don't know. In this
case you can force it to go the way you want by using "cross join" to
force the ordering of

the join.



-Original Message-
From: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of
Thomas Flemming
Sent: Monday, May 29, 2017 9:28 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition
slow

Ok, here is a sample to try these queries:

http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
(825mb, 12 mio records)

Before I change my app-logic to do the styleid-query on the app-side,
I would like to know, if there might be a chance to get this fast on the 
sqlite-side.


very fast, 77 records, 49ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
  AND x0 < 30.46203 AND  x1 > 30.00074766
  AND 18 BETWEEN z0 AND z1
  AND Pois_bb.Id = Pois.Id;

Run Time: real 0.109 user 0.00 sys 0.00
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)


fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);

Run Time: real 0.094 user 0.093601 sys 0.00
selectid|order|from|detail
0|0|0|SEARCH TABLE Pois USING COVERING INDEX Pois_StyleId (StyleId=?)
0|0|0|EXECUTE LIST SUBQUERY 1


very slow: 55 records, 3ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
  AND x0 < 30.46203 AND  x1 > 30.00074766
  AND 18 BETWEEN z0 AND z1
  AND Pois_bb.Id = Pois.Id
  AND styleid IN
(9,48,73,200,142,31,219);

Run Time: real 9.422 user 5.132433 sys 4.212027
selectid|order|from|detail
0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
0|0|0|EXECUTE LIST SUBQUERY 1
0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:


Forcing the order with CROSS JOIN
SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > 
-15.12862
  AND x0 < 30.46203 AND  x1 > 30.00074766
  AND 18 BETWEEN z0 AND z1
  AND Pois_bb.Id = Pois.Id
  AND styleid IN
(9,48,73,200,142,31,219);

Run Time: real 0.078 user 0.00 sys 0.00
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
0|0|0|EXECUTE LIST SUBQUERY 1


CREATE TABLE Pois(
Id LONG PRIMARY KEY,
Label VARCHAR(50),
Info TEXT,
Lat FLOAT,
Lon FLOAT,
Z FLOAT,
Flags INT,
StyleId INT
);
CREATE INDEX Pois_StyleId ON Pois(StyleId); CREATE VIRTUAL TABLE
Pois_bb USING rtree(
Id LONG PRIMARY KEY,
X0 FLOAT,
X1 FLOAT,
Y0 FLOAT,
Y1 FLOAT,
Z0 FLOAT,
Z1 FLOAT
);
CREATE TABLE IF NOT EXISTS "Lines_bb_node"(nodeno INTEGER PRIMARY KEY,
data BLOB); CREATE TABLE IF NOT EXISTS "Lines_bb_rowid"(rowid INTEGER
PRIMARY KEY, nodeno INTEGER); CREATE TABLE IF NOT EXISTS
"Lines_bb_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER);

sqlite_stat1
tbl|idx|stat
Pois|Pois_StyleId|11421177 16996
Pois|sqlite_autoindex_Pois_1|11421177 1
Pois_bb_rowid||11421177
Pois_bb_node||611106
Pois_bb_parent||611105
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
*

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread Hick Gunter

Just like any other join, but with tables in the desired order and the word 
CROSS added

SELECT ... FROM  CROSS JOIN  ...

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Flemming
Gesendet: Dienstag, 30. Mai 2017 18:15
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow

> force it to go the way you want by using "cross join" to force the
> ordering of

How would such "cross join" statemant look like?


Am 30.05.2017 um 16:38 schrieb David Raymond:
> It looks like it thinks that using the index on StyleId is going to be
> the most beneficial, as opposed to the rtree index. How it compares a
> normal index's stat1 to a virtual table's stat1 I don't know. In this
> case you can force it to go the way you want by using "cross join" to
> force the ordering of
the join.
>
>
> -Original Message-
> From: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of
> Thomas Flemming
> Sent: Monday, May 29, 2017 9:28 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition
> slow
>
> Ok, here is a sample to try these queries:
>
> http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
> (825mb, 12 mio records)
>
> Before I change my app-logic to do the styleid-query on the app-side,
> I would like to know, if there might be a chance to get this fast on the 
> sqlite-side.
>
>
> very fast, 77 records, 49ms:
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>  AND x0 < 30.46203 AND  x1 > 30.00074766
>  AND 18 BETWEEN z0 AND z1
>  AND Pois_bb.Id = Pois.Id;
>
> Run Time: real 0.109 user 0.00 sys 0.00
> selectid|order|from|detail
> 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
> 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
>
>
> fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
> SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);
>
> Run Time: real 0.094 user 0.093601 sys 0.00
> selectid|order|from|detail
> 0|0|0|SEARCH TABLE Pois USING COVERING INDEX Pois_StyleId (StyleId=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
>
> very slow: 55 records, 3ms:
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>  AND x0 < 30.46203 AND  x1 > 30.00074766
>  AND 18 BETWEEN z0 AND z1
>  AND Pois_bb.Id = Pois.Id
>  AND styleid IN
> (9,48,73,200,142,31,219);
>
> Run Time: real 9.422 user 5.132433 sys 4.212027
> selectid|order|from|detail
> 0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:
>
>
> Forcing the order with CROSS JOIN
> SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > 
> -15.12862
>  AND x0 < 30.46203 AND  x1 > 30.00074766
>  AND 18 BETWEEN z0 AND z1
>  AND Pois_bb.Id = Pois.Id
>  AND styleid IN
> (9,48,73,200,142,31,219);
>
> Run Time: real 0.078 user 0.00 sys 0.00
> selectid|order|from|detail
> 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
> 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
>
> CREATE TABLE Pois(
>Id LONG PRIMARY KEY,
>Label VARCHAR(50),
>Info TEXT,
>Lat FLOAT,
>Lon FLOAT,
>Z FLOAT,
>Flags INT,
>StyleId INT
> );
> CREATE INDEX Pois_StyleId ON Pois(StyleId); CREATE VIRTUAL TABLE
> Pois_bb USING rtree(
>Id LONG PRIMARY KEY,
>X0 FLOAT,
>X1 FLOAT,
>Y0 FLOAT,
>Y1 FLOAT,
>Z0 FLOAT,
>Z1 FLOAT
> );
> CREATE TABLE IF NOT EXISTS "Lines_bb_node"(nodeno INTEGER PRIMARY KEY,
> data BLOB); CREATE TABLE IF NOT EXISTS "Lines_bb_rowid"(rowid INTEGER
> PRIMARY KEY, nodeno INTEGER); CREATE TABLE IF NOT EXISTS
> "Lines_bb_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER);
>
> sqlite_stat1
> tbl|idx|stat
> Pois|Pois_StyleId|11421177 16996
> Pois|sqlite_autoindex_Pois_1|11421177 1
> Pois_bb_rowid||11421177
> Pois_bb_node||611106
> Pois_bb_parent||611105
> ___

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread Thomas Flemming


force it to go the way you want by using "cross join" to force the ordering of


How would such "cross join" statemant look like?


Am 30.05.2017 um 16:38 schrieb David Raymond:

It looks like it thinks that using the index on StyleId is going to be the most 
beneficial, as opposed to the rtree index. How it compares a normal index's stat1 to a 
virtual table's stat1 I don't know. In this case you can force it to go the way you want 
by using "cross join" to force the ordering of

the join.



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Thomas Flemming
Sent: Monday, May 29, 2017 9:28 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Ok, here is a sample to try these queries:

http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
(825mb, 12 mio records)

Before I change my app-logic to do the styleid-query on the app-side, I would
like to know, if there might be a chance to get this fast on the sqlite-side.


very fast, 77 records, 49ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
 AND x0 < 30.46203 AND  x1 > 30.00074766
 AND 18 BETWEEN z0 AND z1
 AND Pois_bb.Id = Pois.Id;

Run Time: real 0.109 user 0.00 sys 0.00
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)


fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);

Run Time: real 0.094 user 0.093601 sys 0.00
selectid|order|from|detail
0|0|0|SEARCH TABLE Pois USING COVERING INDEX Pois_StyleId (StyleId=?)
0|0|0|EXECUTE LIST SUBQUERY 1


very slow: 55 records, 3ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
 AND x0 < 30.46203 AND  x1 > 30.00074766
 AND 18 BETWEEN z0 AND z1
 AND Pois_bb.Id = Pois.Id
 AND styleid IN (9,48,73,200,142,31,219);

Run Time: real 9.422 user 5.132433 sys 4.212027
selectid|order|from|detail
0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
0|0|0|EXECUTE LIST SUBQUERY 1
0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:


Forcing the order with CROSS JOIN
SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > 
-15.12862
 AND x0 < 30.46203 AND  x1 > 30.00074766
 AND 18 BETWEEN z0 AND z1
 AND Pois_bb.Id = Pois.Id
 AND styleid IN (9,48,73,200,142,31,219);

Run Time: real 0.078 user 0.00 sys 0.00
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
0|0|0|EXECUTE LIST SUBQUERY 1


CREATE TABLE Pois(
   Id LONG PRIMARY KEY,
   Label VARCHAR(50),
   Info TEXT,
   Lat FLOAT,
   Lon FLOAT,
   Z FLOAT,
   Flags INT,
   StyleId INT
);
CREATE INDEX Pois_StyleId ON Pois(StyleId);
CREATE VIRTUAL TABLE Pois_bb USING rtree(
   Id LONG PRIMARY KEY,
   X0 FLOAT,
   X1 FLOAT,
   Y0 FLOAT,
   Y1 FLOAT,
   Z0 FLOAT,
   Z1 FLOAT
);
CREATE TABLE IF NOT EXISTS "Lines_bb_node"(nodeno INTEGER PRIMARY KEY, data 
BLOB);
CREATE TABLE IF NOT EXISTS "Lines_bb_rowid"(rowid INTEGER PRIMARY KEY, nodeno 
INTEGER);
CREATE TABLE IF NOT EXISTS "Lines_bb_parent"(nodeno INTEGER PRIMARY KEY, 
parentnode INTEGER);

sqlite_stat1
tbl|idx|stat
Pois|Pois_StyleId|11421177 16996
Pois|sqlite_autoindex_Pois_1|11421177 1
Pois_bb_rowid||11421177
Pois_bb_node||611106
Pois_bb_parent||611105
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread David Raymond

It looks like it thinks that using the index on StyleId is going to be the most 
beneficial, as opposed to the rtree index. How it compares a normal index's 
stat1 to a virtual table's stat1 I don't know. In this case you can force it to 
go the way you want by using "cross join" to force the ordering of the join.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Thomas Flemming
Sent: Monday, May 29, 2017 9:28 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Ok, here is a sample to try these queries:

http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
(825mb, 12 mio records)

Before I change my app-logic to do the styleid-query on the app-side, I would 
like to know, if there might be a chance to get this fast on the sqlite-side.


very fast, 77 records, 49ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
AND x0 < 30.46203 AND  x1 > 30.00074766
AND 18 BETWEEN z0 AND z1
AND Pois_bb.Id = Pois.Id;

Run Time: real 0.109 user 0.00 sys 0.00
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)


fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);

Run Time: real 0.094 user 0.093601 sys 0.00
selectid|order|from|detail
0|0|0|SEARCH TABLE Pois USING COVERING INDEX Pois_StyleId (StyleId=?)
0|0|0|EXECUTE LIST SUBQUERY 1


very slow: 55 records, 3ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
AND x0 < 30.46203 AND  x1 > 30.00074766
AND 18 BETWEEN z0 AND z1
AND Pois_bb.Id = Pois.Id
AND styleid IN (9,48,73,200,142,31,219);

Run Time: real 9.422 user 5.132433 sys 4.212027
selectid|order|from|detail
0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
0|0|0|EXECUTE LIST SUBQUERY 1
0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:


Forcing the order with CROSS JOIN
SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > 
-15.12862
AND x0 < 30.46203 AND  x1 > 30.00074766
AND 18 BETWEEN z0 AND z1
AND Pois_bb.Id = Pois.Id
AND styleid IN (9,48,73,200,142,31,219);

Run Time: real 0.078 user 0.00 sys 0.00
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
0|0|0|EXECUTE LIST SUBQUERY 1


CREATE TABLE Pois(
  Id LONG PRIMARY KEY,
  Label VARCHAR(50),
  Info TEXT,
  Lat FLOAT,
  Lon FLOAT,
  Z FLOAT,
  Flags INT,
  StyleId INT
);
CREATE INDEX Pois_StyleId ON Pois(StyleId);
CREATE VIRTUAL TABLE Pois_bb USING rtree(
  Id LONG PRIMARY KEY,
  X0 FLOAT,
  X1 FLOAT,
  Y0 FLOAT,
  Y1 FLOAT,
  Z0 FLOAT,
  Z1 FLOAT
);
CREATE TABLE IF NOT EXISTS "Lines_bb_node"(nodeno INTEGER PRIMARY KEY, data 
BLOB);
CREATE TABLE IF NOT EXISTS "Lines_bb_rowid"(rowid INTEGER PRIMARY KEY, nodeno 
INTEGER);
CREATE TABLE IF NOT EXISTS "Lines_bb_parent"(nodeno INTEGER PRIMARY KEY, 
parentnode INTEGER);

sqlite_stat1
tbl|idx|stat
Pois|Pois_StyleId|11421177 16996
Pois|sqlite_autoindex_Pois_1|11421177 1
Pois_bb_rowid||11421177
Pois_bb_node||611106
Pois_bb_parent||611105
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread J. King

On May 30, 2017 10:07:45 AM EDT, Thomas Flemming  wrote:

>Style.Id doesn't need to be LONG, you're right. I changed it but it
>doesn't 
>make a difference.
>Pois.Id need to be LONG because the source for this column is really 
>containing 64-bit values 
Integers in SQLite are of variable size; if an 8-byte size is required, it will 
scale accordingly. Specifying LONG will not yield an integer affinity like 
specifying INTEGER would, and for primary keys is not as efficient because the 
column is not an alias for rowid if you specify LONG. 

In short, you should always use INTEGER PRIMARY KEY if the column is whole 
numbers, regardless of size. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread Joseph L. Casale

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Thomas Flemming
Sent: Tuesday, May 30, 2017 8:08 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow

>  > Do you know which SQLite version is being used by SQLite Expert
>  > Professional 3.5?
> sqlite 3.10.0
> 
> I tried SQLite Expert Professional 4, using sqlite 3.18.0, but its the same 
> slow.

On that matter, if you place another SQLite dll in the installation folder with 
a
new name, it becomes available in Tools->Options->SQLite Library as an alternate
choice over the shipped version.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread Hick Gunter

INTEGER and LONG can both store 64 bits in SQlite. The difference is that 
"INTEGER PRIMARY KEY" makes the column an alias for the rowid, whereas "LONG 
PRIMARY KEY" defines a second, possibly redundant index.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Flemming
Gesendet: Dienstag, 30. Mai 2017 16:08
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow

 > Do you know which SQLite version is being used by SQLite Expert  > 
 > Professional 3.5?
sqlite 3.10.0

I tried SQLite Expert Professional 4, using sqlite 3.18.0, but its the same 
slow.

Style.Id doesn't need to be LONG, you're right. I changed it but it doesn't 
make a difference.
Pois.Id need to be LONG because the source for this column is really containing 
64-bit values (osm-ids).

Tom


Am 30.05.2017 um 13:29 schrieb Wolfgang Enzinger:
> Am Mon, 29 May 2017 14:27:56 +0100 schrieb Thomas Flemming:
>
>> Ok, here is a sample to try these queries:
>>
>> http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
>> (825mb, 12 mio records)
>
> Just a few quick observations ...
>
> First, I would replace all column declarations like
>
> LONG PRIMARY KEY
>
> to
>
> INTEGER PRIMARY KEY
>
> This can make a huge difference AFAIK.
>
>> Before I change my app-logic to do the styleid-query on the app-side,
>> I would like to know, if there might be a chance to get this fast on the 
>> sqlite-side.
>>
>>
>> very fast, 77 records, 49ms:
>> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>>  AND x0 < 30.46203 AND  x1 > 30.00074766
>>  AND 18 BETWEEN z0 AND z1
>>  AND Pois_bb.Id = Pois.Id;
>>
>>
>> fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
>> SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);
>>
>>
>> very slow: 55 records, 3ms:
>> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>>  AND x0 < 30.46203 AND  x1 > 30.00074766
>>  AND 18 BETWEEN z0 AND z1
>>  AND Pois_bb.Id = Pois.Id
>>  AND styleid IN
>> (9,48,73,200,142,31,219);
>
> Do you know which SQLite version is being used by SQLite Expert
> Professional 3.5? Just wondering because my quick tests on this reveal
> different query plans, depending on the presence of a sqlite_stat4
> table (absent in your database) and the SQLite version (I ran some
> quick tests using SQLite 3.13 and 3.18). I'll have to dig deeper into
> this in the next days though.
>
> Wolfgang
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

--
/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread Thomas Flemming


> Do you know which SQLite version is being used by SQLite Expert
> Professional 3.5?
sqlite 3.10.0

I tried SQLite Expert Professional 4, using sqlite 3.18.0, but its the same 
slow.

Style.Id doesn't need to be LONG, you're right. I changed it but it doesn't 
make a difference.
Pois.Id need to be LONG because the source for this column is really 
containing 64-bit values (osm-ids).


Tom


Am 30.05.2017 um 13:29 schrieb Wolfgang Enzinger:

Am Mon, 29 May 2017 14:27:56 +0100 schrieb Thomas Flemming:


Ok, here is a sample to try these queries:

http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
(825mb, 12 mio records)


Just a few quick observations ...

First, I would replace all column declarations like

LONG PRIMARY KEY

to

INTEGER PRIMARY KEY

This can make a huge difference AFAIK.


Before I change my app-logic to do the styleid-query on the app-side, I would
like to know, if there might be a chance to get this fast on the sqlite-side.


very fast, 77 records, 49ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
 AND x0 < 30.46203 AND  x1 > 30.00074766
 AND 18 BETWEEN z0 AND z1
 AND Pois_bb.Id = Pois.Id;


fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);


very slow: 55 records, 3ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
 AND x0 < 30.46203 AND  x1 > 30.00074766
 AND 18 BETWEEN z0 AND z1
 AND Pois_bb.Id = Pois.Id
 AND styleid IN (9,48,73,200,142,31,219);


Do you know which SQLite version is being used by SQLite Expert
Professional 3.5? Just wondering because my quick tests on this reveal
different query plans, depending on the presence of a sqlite_stat4 table
(absent in your database) and the SQLite version (I ran some quick tests
using SQLite 3.13 and 3.18). I'll have to dig deeper into this in the next
days though.

Wolfgang

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



--
/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-30 Thread Wolfgang Enzinger

Am Mon, 29 May 2017 14:27:56 +0100 schrieb Thomas Flemming:

> Ok, here is a sample to try these queries:
> 
> http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
> (825mb, 12 mio records)

Just a few quick observations ...

First, I would replace all column declarations like

LONG PRIMARY KEY

to

INTEGER PRIMARY KEY

This can make a huge difference AFAIK.

> Before I change my app-logic to do the styleid-query on the app-side, I would 
> like to know, if there might be a chance to get this fast on the sqlite-side.
> 
> 
> very fast, 77 records, 49ms:
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
> AND x0 < 30.46203 AND  x1 > 30.00074766
> AND 18 BETWEEN z0 AND z1
> AND Pois_bb.Id = Pois.Id;
> 
> 
> fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
> SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);
> 
> 
> very slow: 55 records, 3ms:
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
> AND x0 < 30.46203 AND  x1 > 30.00074766
> AND 18 BETWEEN z0 AND z1
> AND Pois_bb.Id = Pois.Id
> AND styleid IN (9,48,73,200,142,31,219);

Do you know which SQLite version is being used by SQLite Expert
Professional 3.5? Just wondering because my quick tests on this reveal
different query plans, depending on the presence of a sqlite_stat4 table
(absent in your database) and the SQLite version (I ran some quick tests
using SQLite 3.13 and 3.18). I'll have to dig deeper into this in the next
days though.

Wolfgang

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

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-29 Thread Thomas Flemming


Ok, here is a sample to try these queries:

http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
(825mb, 12 mio records)

Before I change my app-logic to do the styleid-query on the app-side, I would 
like to know, if there might be a chance to get this fast on the sqlite-side.



very fast, 77 records, 49ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
   AND x0 < 30.46203 AND  x1 > 30.00074766
   AND 18 BETWEEN z0 AND z1
   AND Pois_bb.Id = Pois.Id;


fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);


very slow: 55 records, 3ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
   AND x0 < 30.46203 AND  x1 > 30.00074766
   AND 18 BETWEEN z0 AND z1
   AND Pois_bb.Id = Pois.Id
   AND styleid IN (9,48,73,200,142,31,219);

Thanks,
Tom

Am 27.05.2017 um 12:04 schrieb Thomas Flemming:

Hi,

I have a table Pois with points of interest (geogr. coordinate, label, 
styleid) where I do regional querys using a rtree-index:


SELECT Pois.* FROM Pois_bb, Pois WHERE  y0 < -14.8600 AND  y1 > -15.12862
   AND   x0 < 30.46203 AND  x1 > 30.00074766
   AND   18 BETWEEN z0 AND z1
   AND   Pois_bb.Id = Pois.Id
Thats very fast, 50ms.

The problem is, when I add a second condition to get certain poi-types only in 
the area:


AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762

The query becomes really slow, 800ms.
There is of course also an index on styleid.

I also realized, just this query:

SELECT * FROM Pois WHERE styleid IN 
1351,1362,1371,1374,1376,1542,1595,1597,1643,1762


is also slow for the first call. The second call is fast.

(Using SQLite Expert Professional 3.5 for testing).

Any ideas, how to speed this up?

Thanks
Tom




--
/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-29 Thread Thomas Flemming


Morning,

> Does ANALYZE gather statistical data about rtree virtual tables? I seem to
ANALYZE doesn't help.
I'm busy preparing and uploading a sample-db, then it might be easier to 
figure that out.

Tom

Am 28.05.2017 um 11:01 schrieb Wolfgang Enzinger:

Am Sat, 27 May 2017 19:20:00 -0400 schrieb Richard Hipp:


On 5/27/17, Thomas Flemming  wrote:

Hi,

I have a table Pois with points of interest (geogr. coordinate, label,
styleid) where I do regional querys using a rtree-index:

SELECT Pois.* FROM Pois_bb, Pois WHERE  y0 < -14.8600 AND  y1 > -15.12862
AND   x0 < 30.46203 AND  x1 > 30.00074766
AND   18 BETWEEN z0 AND z1
AND   Pois_bb.Id = Pois.Id
Thats very fast, 50ms.

The problem is, when I add a second condition to get certain poi-types only
in the area:

AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762

The query becomes really slow, 800ms.
There is of course also an index on styleid.

I also realized, just this query:

SELECT * FROM Pois WHERE styleid IN
1351,1362,1371,1374,1376,1542,1595,1597,1643,1762

is also slow for the first call. The second call is fast.

(Using SQLite Expert Professional 3.5 for testing).

Any ideas, how to speed this up?


Have you tried running ANALYZE on your database?


Does ANALYZE gather statistical data about rtree virtual tables? I seem to
remember that this is not the case.

I don't have an appropriate database at hand right now since I'm busy
currently with a different project, but I seem to remember that I observed
something similar: queries with an rtree table involved seem to *always*
prefer the spatial index over any other index, even if the clipping
embraces the complete extent of graphical data. This is unefficient
especially when the "related table" (that is, another table that is linked
to the rtree virtual table by an object ID) holds much more selective (and
indexed) criteria.

This is just out of the top of my head; anyway, if you don't hear back from
Thomas, please let me know, and I'll gladly provide more details (not
before Wednesday though).

And thanks again for then LEFT JOIN VIEW optimization - sorry that it
caused so much trouble ... I was completely unaware of the many pitfalls
this topic involves.

Wolfgang

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



--
/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-28 Thread Wolfgang Enzinger

Am Sat, 27 May 2017 19:20:00 -0400 schrieb Richard Hipp:

> On 5/27/17, Thomas Flemming  wrote:
>> Hi,
>>
>> I have a table Pois with points of interest (geogr. coordinate, label,
>> styleid) where I do regional querys using a rtree-index:
>>
>> SELECT Pois.* FROM Pois_bb, Pois WHERE  y0 < -14.8600 AND  y1 > -15.12862
>>AND   x0 < 30.46203 AND  x1 > 30.00074766
>>AND   18 BETWEEN z0 AND z1
>>AND   Pois_bb.Id = Pois.Id
>> Thats very fast, 50ms.
>>
>> The problem is, when I add a second condition to get certain poi-types only
>> in the area:
>>
>> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>>
>> The query becomes really slow, 800ms.
>> There is of course also an index on styleid.
>>
>> I also realized, just this query:
>>
>> SELECT * FROM Pois WHERE styleid IN
>> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>>
>> is also slow for the first call. The second call is fast.
>>
>> (Using SQLite Expert Professional 3.5 for testing).
>>
>> Any ideas, how to speed this up?
> 
> Have you tried running ANALYZE on your database?

Does ANALYZE gather statistical data about rtree virtual tables? I seem to
remember that this is not the case.

I don't have an appropriate database at hand right now since I'm busy
currently with a different project, but I seem to remember that I observed
something similar: queries with an rtree table involved seem to *always*
prefer the spatial index over any other index, even if the clipping
embraces the complete extent of graphical data. This is unefficient
especially when the "related table" (that is, another table that is linked
to the rtree virtual table by an object ID) holds much more selective (and
indexed) criteria.

This is just out of the top of my head; anyway, if you don't hear back from
Thomas, please let me know, and I'll gladly provide more details (not
before Wednesday though).

And thanks again for then LEFT JOIN VIEW optimization - sorry that it
caused so much trouble ... I was completely unaware of the many pitfalls
this topic involves.

Wolfgang

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

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-27 Thread Richard Hipp

On 5/27/17, Thomas Flemming  wrote:
> Hi,
>
> I have a table Pois with points of interest (geogr. coordinate, label,
> styleid) where I do regional querys using a rtree-index:
>
> SELECT Pois.* FROM Pois_bb, Pois WHERE  y0 < -14.8600 AND  y1 > -15.12862
>AND   x0 < 30.46203 AND  x1 > 30.00074766
>AND   18 BETWEEN z0 AND z1
>AND   Pois_bb.Id = Pois.Id
> Thats very fast, 50ms.
>
> The problem is, when I add a second condition to get certain poi-types only
> in the area:
>
> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> The query becomes really slow, 800ms.
> There is of course also an index on styleid.
>
> I also realized, just this query:
>
> SELECT * FROM Pois WHERE styleid IN
> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> is also slow for the first call. The second call is fast.
>
> (Using SQLite Expert Professional 3.5 for testing).
>
> Any ideas, how to speed this up?

Have you tried running ANALYZE on your database?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

2017-05-27 Thread Richard Hipp

Please post the output of the following command from the command-line shell:

.fullschema --indent

To capture the output of the above command, you can preceed it by
".once out.txt":

.once out.txt
.fullschema --indent

To emphasize, use the command-line shell "sqlite3.exe" which you can
obtain from the https://sqlite.org/download.html page.  This is not a
3rd-party tool like SQLite Expert Professional.

On 5/27/17, Thomas Flemming  wrote:
> Hi,
>
> I have a table Pois with points of interest (geogr. coordinate, label,
> styleid) where I do regional querys using a rtree-index:
>
> SELECT Pois.* FROM Pois_bb, Pois WHERE  y0 < -14.8600 AND  y1 > -15.12862
>AND   x0 < 30.46203 AND  x1 > 30.00074766
>AND   18 BETWEEN z0 AND z1
>AND   Pois_bb.Id = Pois.Id
> Thats very fast, 50ms.
>
> The problem is, when I add a second condition to get certain poi-types only
> in the area:
>
> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> The query becomes really slow, 800ms.
> There is of course also an index on styleid.
>
> I also realized, just this query:
>
> SELECT * FROM Pois WHERE styleid IN
> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> is also slow for the first call. The second call is fast.
>
> (Using SQLite Expert Professional 3.5 for testing).
>
> Any ideas, how to speed this up?
>
> Thanks
> Tom
>
>
> --
> /
> **   Flemming Software Development CC
> **   Thomas Flemming
> **   PO Box 81244
> **   Windhoek, Namibia
> **   http://www.quovadis-gps.com
> **   mail  t...@qvgps.com
> **   +264 (0)81 3329923
> **   +49  (0)6182 8492599
> ***/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

19 matches

Site Navigation

Mail list logo

Footer information