> 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, 30000ms:
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
