It would be interesting to "measure" the effect of these ideas during the process of optimizing. I can profile and measure the execution times, but also interesting would be to know, how much pages are involved in a specific query.
Is there maybe a way to get the count of pages currently used? ------ Originalnachricht ------ Von: "Richard Hipp" <d...@sqlite.org> An: "SQLite mailing list" <sqlite-users@mailinglists.sqlite.org> Gesendet: 30.08.2018 13:48:30 Betreff: Re: [sqlite] Strategies to reduce page-loads? >On 8/30/18, t...@qvgps.com <t...@qvgps.com> wrote: >> >>Structure is simple: >>CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates >>BLOB, Flags INT, StyleId INT); >>And an rtree-index: >>CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0 >>FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT); > >Three points that might help, either separately or in combination: > >(1) Use exactly "INTEGER PRIMARY KEY". "LONG PRIMARY KEY" and "INT >PRIMARY KEY" are not the same thing and do not work as well. > >(2) In the very latest versions of SQLite, 3.24,0 and the beta for >3.25.0, you can put the "Lines" information directly in the RTree: > > CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0, >z1, +Label, +Coordinates, +Flags, +StyleId); > >The extra columns in r-tree are prefaced by a "+" character so that >the r-tree module knows that they are auxiliary columns and not extra >coordinates. > >(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on >a "Morton code" or "Z-Order curve" of the coordinates. >(https://en.wikipedia.org/wiki/Z-order_curve) That will cause >features that are close together geographically to tend to be close >together within the file. There is are two extension functions in the >https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the >SQLite source tree that might help you with this. Or you can do the >same using your own functions. >-- >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
