[sqlite] Disable trigger?
Is there a way in SQLite, to temporary disable and enable a trigger without dropping and recreating ? 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
[sqlite] Undo-Redo
Hi Sqlite-users,
I'm busy doing a undo-redo with triggers as described here:
https://sqlite.org/undoredo.html
and wondering, if there is a function to build the string for the
insert-command automatically with all fields from that table.
In the sample its only 3 fields, but in real life, this is much more so this
could safe a lot of typing.
CREATE TEMP TRIGGER ex1_dt BEFORE DELETE ON ex1 BEGIN
INSERT INTO undolog VALUES(NULL,'INSERT INTO ex1(rowid,a,b,c)
VALUES('||old.rowid||','||quote(old.a)||','||quote(old.b)||
','||quote(old.c)||')');
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 INTO TEMP TABLE takes long
Thanks guys for all the information. Now I know, how to proceed. Tom :) Am 31.05.2017 um 22:02 schrieb R Smith: On 2017/05/31 9:31 PM, Thomas Flemming wrote: Hi, maybe, hopefully, I missed something, its still about this database: http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip Copying just the ids from 12mio records ordered in a temp-table takes 60 seconds. There is a COLLATE NOCASE index on label. Is this normal or can this also be done faster? DROP TABLE IF EXISTS RowCursor; CREATE TEMP TABLE RowCursor (Id int); INSERT INTO RowCursor SELECT Id from Pois ORDER BY Label COLLATE NOCASE; CREATE INDEX RowCursor_Id on RowCursor(Id); At the end of the day it IS 12m records (or 11.42m to be more precise)... so it should take a bit of time. That said, it could probably be faster - My first attempt using your database included changing the page-size from 1024 to 4096 using: PRAGMA page_size = 4096; VACUUM; -- (Bytes) 4KB (Ensure the drive with your TEMP folder has more than 5GB free else the above may fail). Then setting Synchronous mode from FULL to Normal using: PRAGMA synchronous = 1; -- Normal Then jacked up the cache size from 2000 bytes to 8000 pages using: PRAGMA cache_size = 8000; -- (Pages) Made sure Journal mode is DELETE, Made sure Threads is set to 8. Next I dropped the "CREATE INDEX..." bit at the end - it only consumed circa 3 seconds, but it is not needed, your row-id is already indexed and you only use the Id field for reference look-ups in the other table, no need to index it here. The result is what seems to be about half your time. I use a good processor but the DB itself sat on a platter drive, so I doubt the gains are due to system differences, though some of it might be. Herewith the result: -- SQLite version 3.17.0 [ Release: 2017-02-13 ] on SQLitespeed version 2.0.2.4. -- DROP TABLE IF EXISTS RowCursor; CREATE TEMP TABLE RowCursor (Id int); INSERT INTO RowCursor SELECT Id from Pois ORDER BY Label COLLATE NOCASE; --Item Stats: Item No: 3 Query Size (Chars): 74 -- VM Work Steps: 102790606 Rows Modified: 11421177 -- Full Query Time: 0d 00h 00m and 28.471s -- Query Result: Success. -- -- Script Stats: Total Script Execution Time: 0d 00h 00m and 29.389s -- Total Script Query Time: 0d 00h 00m and 28.522s -- Total Database Rows Changed: 11421177 -- Total Virtual-Machine Steps: 205581259 -- Last executed Item Index:4 -- Last Script Error: -- On another tangent: No person can ever look at 12 million records in one sitting. You should limit the span of results to something plausible, like 100K rows. It's still a lot, but it is conceivable a person can spend a few hours paging from one page to the next traversing an actual 100K rows... Improbable, but possible. Scanning 12 mil or even 1 mil records is just implausible. If you are going to view the entire table in Alphabetical order, an even faster tactic would be to just permanently keep that Table with all the rows in alphabetical order as a kind-of index table, filling it with a trigger from the main table when changes happen. That way you can at any time search for any Label, and when found, just look up the Pois ID in the RowCursor table, and start paging from that row_id. How your UI will work and how you want it to work will of course dictate what is the best solution. Good luck! Ryan ___ 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
[sqlite] SELECT INTO TEMP TABLE takes long
Hi, maybe, hopefully, I missed something, its still about this database: http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip Copying just the ids from 12mio records ordered in a temp-table takes 60 seconds. There is a COLLATE NOCASE index on label. Is this normal or can this also be done faster? DROP TABLE IF EXISTS RowCursor; CREATE TEMP TABLE RowCursor (Id int); INSERT INTO RowCursor SELECT Id from Pois ORDER BY Label COLLATE NOCASE; CREATE INDEX RowCursor_Id on RowCursor(Id); 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
> 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
> 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 <t...@qvgps.com> 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
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
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
> 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
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
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 <t...@qvgps.com> 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
[sqlite] SELECT WHERE with RTREE and second condition slow
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] SQLite3.Step fast forward / skipping rows
Like this it works. OK, building the temporary table takes some seconds with a table of 10 mio records, but the queries are really fast then, 10 to 30 ms!! Thanks so much guys for helping me with this :) Tom Am 24.05.2017 um 18:42 schrieb Keith Medcalf: On Wednesday, 24 May, 2017 07:21 > there is no system in existence that will do I was working a lot with Valentina-DB and they have a cursor class: var cursor=database.SqlSelect( "SELECT... WHERE... ORDER..."); then you can just get any the ListView wants, forward and backwards, very fast: cursor.Position = rownumber; I'm quiet new to SQLite and was surprised, that its so difficult to write this kind of cursor. > Another way is to Query to a temporary table with an automatic incremented > This is extremely fast, only the initial query will take some time. yeah, this might work, but imagine how much time and memory this would cost for 10mio records... This is exactly how things that "pretend" to have cursors work. Except they have the support "built-in" to either the client or the server. Basically, you do the following: pragma journal_mode=WAL; begin; drop table if exists temp.myPhonyCursor; create temporary table if not exists myPhonyCursor as SELECT table.RowID as tableRowID FROM TABLE WHERE ORDER BY ... your queries to retrieve rows go here -- proceed to drop/commit when you are done with the cursor ... drop table if exists temp.myPhonyCursor; commit; Now, whenever you want to retrieve some data, you can do something like: SELECT table.* FROM myPhonyCursor, table where table.RowID == myPhonyCursor.tableRowID and myPhonyCursor.RowID between @StartingRow and @EndingRow order by myPhonyCursor.RowID; -- for a forwards read and SELECT table.* FROM myPhonyCursor, table where table.RowID == myPhonyCursor.tableRowID and myPhonyCursor.RowID between @StartingRow and @EndingRow order by myPhonyCursor.RowID desc; -- to see the rows in reverse order. of course, for this to be effective you need to be either (a) the only user of the database or (b) have to wrap the whole thing in a transaction and be using WAL mode in order to achieve repeatable read isolation across multiple queries or you are liable to have result rows "disappear" or "appear out of order". if you want "page numbers", zero based, then you can do the following: SELECT table.* FROM myPhonyCursor, table where table.RowID == myPhonyCursor.tableRowID and myPhonyCursor.RowID > (@pageNumber * @PageSize) order by myPhonyCursor.RowID limit @PageSize; When you need to change the sort order or whatever you simply regenerate myPhonyCursor. If the temp tables are in memory and you have the appropriate indexes to process the ordered query, generating the myPhonyCursor table is quite fast, even for millions of rows. ___ 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] SQLite3.Step fast forward / skipping rows
Hi Ron, > there is no system in existence that will do I was working a lot with Valentina-DB and they have a cursor class: var cursor=database.SqlSelect( "SELECT... WHERE... ORDER..."); then you can just get any the ListView wants, forward and backwards, very fast: cursor.Position = rownumber; I'm quiet new to SQLite and was surprised, that its so difficult to write this kind of cursor. > Another way is to Query to a temporary table with an automatic incremented > This is extremely fast, only the initial query will take some time. yeah, this might work, but imagine how much time and memory this would cost for 10mio records... Tom Am 24.05.2017 um 13:20 schrieb R Smith: You are asking the DB to give you all the 8000...+ results, sort them and then you opt to only look at some of them, there is no way this can ever be fast in any system, you need to rethink how you ask for information. First things first, you should never be using the sqlite (or any other database's) STEP to support user scrolling, you should be using it to load the results you want to see, and then in a different method show those results to the user. What if the user wants to move up by one line? You can't un-step in a database. There are many ways this can be overcome, first with dynamic listviews: The way to set up a dynamic listview is to get a query of the ID's of the entire list of possible values, sorted and so on, that you might want to display into your own list object or array. Then populate the listview with the ID's only and determine which are visible, for the visible ones, load the data from a query using only those ID's, perhaps something like: SELECT v1, v2 FROM MyMainTable WHERE ID IN (7001, 7002, 7003...for all IDs visible...); and set them tot he screen. If the user scrolls loads, you update only when needed, perhaps using a time difference function or such, and when the view "settles" load those results that are visible. Almost all programming systems with visual components like "Listview" has a function or callback that can tell you the current visible items AND whether the visible index/count changed or not. It is often enough to catch this and simply update the visible items when such a change happens. Another way is to Query to a temporary table with an automatic incremented primary key, and simply read from that table the paginated values, i.e. if your listview scrolls to line 50013 you can query the temp table like this: SELECT * FROM TempResults WHERE ID BETWEEN ?1 AND ?2; where ?1 = current_idx (such as 50013) and ?2 = current_idx + page_items_count as defined in your software; This is extremely fast, only the initial query will take some time. What you can't do is query an insane amount of rows EVERY time the user moves the cursor or scrolls the page, there is no system in existence that will do that quick, ever. Good luck! Ryan On 2017/05/24 11:53 AM, Thomas Flemming wrote: Yes, but this would still be slow, because lastValue is lets say page 50 in the telephone directory, but I need to go to page 800. So this query would still return all pages from 50 to 800, which I dont need. Am 24.05.2017 um 10:45 schrieb Andy Ling: Then when you detect a jump you'll need to use a new search to "jump" to the page you want. Something like SELECT WHERE sortedColumn > lastValue ORDER BY sortedColumn And make sure you have indexes on all the columns that you can sort by. Andy Ling -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Wed 24 May 2017 10:37 To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SQLite3.Step fast forward / skipping rows Almost, but when you compare to a telephone directory, then the use case of fast scrolling down in a listbox would be going directly to page 800 and not going to "Smithson". And yes, there is a unique key, but this doesn't help, because the list can also be sorted to various columns. Am 24.05.2017 um 10:27 schrieb Keith Medcalf: You need to write your application like a telephone directory. To get to the page with the "Smithson" entry on it, you do not read all the entries starting from the begining until you get there -- you turn directly to the page you want by doing a search. Surely you have a unique key for the list? -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Wednesday, 24 May, 2017 02:09 To:sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SQLite3.Step fast forward / skipping rows Hi SQLite Users, I have a SELECT query, which returns some 10 records and is displayed in a scrollable ListView. When the user scrolls down the list, each new row is loaded with SQL
Re: [sqlite] SQLite3.Step fast forward / skipping rows
Yes, but this would still be slow, because lastValue is lets say page 50 in the telephone directory, but I need to go to page 800. So this query would still return all pages from 50 to 800, which I dont need. Am 24.05.2017 um 10:45 schrieb Andy Ling: Then when you detect a jump you'll need to use a new search to "jump" to the page you want. Something like SELECT WHERE sortedColumn > lastValue ORDER BY sortedColumn And make sure you have indexes on all the columns that you can sort by. Andy Ling -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Wed 24 May 2017 10:37 To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SQLite3.Step fast forward / skipping rows Almost, but when you compare to a telephone directory, then the use case of fast scrolling down in a listbox would be going directly to page 800 and not going to "Smithson". And yes, there is a unique key, but this doesn't help, because the list can also be sorted to various columns. Am 24.05.2017 um 10:27 schrieb Keith Medcalf: You need to write your application like a telephone directory. To get to the page with the "Smithson" entry on it, you do not read all the entries starting from the begining until you get there -- you turn directly to the page you want by doing a search. Surely you have a unique key for the list? -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Wednesday, 24 May, 2017 02:09 To:sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SQLite3.Step fast forward / skipping rows Hi SQLite Users, I have a SELECT query, which returns some 10 records and is displayed in a scrollable ListView. When the user scrolls down the list, each new row is loaded with SQLite3.Step(). The problem is, when the user scrolls fast with the scroll-slider, lots of rows are skipped, but SQLite still needs to load them all with SQLite3.Step until it reaches the row which is actually needed. This is very slow. Is there a way to skip all these unnecessary rows? For example going directly from row 1000 to row 10 ? I tried SELECT ... OFFSET 10 but this is also very slow the more down we go. Thanks Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sql -- / ** 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] SQLite3.Step fast forward / skipping rows
Almost, but when you compare to a telephone directory, then the use case of fast scrolling down in a listbox would be going directly to page 800 and not going to "Smithson". And yes, there is a unique key, but this doesn't help, because the list can also be sorted to various columns. Am 24.05.2017 um 10:27 schrieb Keith Medcalf: You need to write your application like a telephone directory. To get to the page with the "Smithson" entry on it, you do not read all the entries starting from the begining until you get there -- you turn directly to the page you want by doing a search. Surely you have a unique key for the list? -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Wednesday, 24 May, 2017 02:09 To:sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SQLite3.Step fast forward / skipping rows Hi SQLite Users, I have a SELECT query, which returns some 10 records and is displayed in a scrollable ListView. When the user scrolls down the list, each new row is loaded with SQLite3.Step(). The problem is, when the user scrolls fast with the scroll-slider, lots of rows are skipped, but SQLite still needs to load them all with SQLite3.Step until it reaches the row which is actually needed. This is very slow. Is there a way to skip all these unnecessary rows? For example going directly from row 1000 to row 10 ? I tried SELECT ... OFFSET 10 but this is also very slow the more down we go. Thanks Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sql -- / ** 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
[sqlite] SQLite3.Step fast forward / skipping rows
Hi SQLite Users, I have a SELECT query, which returns some 10 records and is displayed in a scrollable ListView. When the user scrolls down the list, each new row is loaded with SQLite3.Step(). The problem is, when the user scrolls fast with the scroll-slider, lots of rows are skipped, but SQLite still needs to load them all with SQLite3.Step until it reaches the row which is actually needed. This is very slow. Is there a way to skip all these unnecessary rows? For example going directly from row 1000 to row 10 ? I tried SELECT ... OFFSET 10 but this is also very slow the more down we go. 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
