I know the CLI has the .stats option and the .scanstats option. I don't exactly
what they implement, but here's an example output from a very quick query on
something after turning both of those on. I think the page cache numbers are
probably what you're most interested in. (I vaguely recall that things like the
Virtual Machine steps will quietly overflow, so may show negative or way lower
than you might expect)
More interesting queries will probably give more interesting numbers to look at.
sqlite> select recordtype, count(*) from foo where bar = 'something' group by
recordtype order by recordtype;
QUERY PLAN
`--SEARCH TABLE foo USING COVERING INDEX idx_foo (bar=?)
recordtype|count(*)
F|48
G|1
H|540
P|68
S|2133
Memory Used: 885360 (max 888328) bytes
Number of Outstanding Allocations: 1142 (max 1166)
Number of Pcache Overflow Bytes: 710416 (max 710416) bytes
Largest Allocation: 120000 bytes
Largest Pcache Allocation: 4360 bytes
Lookaside Slots Used: 45 (max 100)
Successful lookaside attempts: 906
Lookaside failures due to size: 20
Lookaside failures due to OOM: 35
Pager Heap Usage: 708616 bytes
Page cache hits: 46
Page cache misses: 162
Page cache writes: 0
Page cache spills: 0
Schema Heap Usage: 46624 bytes
Statement Heap/Lookaside Usage: 37168 bytes
Fullscan Steps: 0
Sort Operations: 0
Autoindex Inserts: 0
Virtual Machine Steps: 22395
Reprepare operations: 0
Number of times run: 1
Memory used by prepared stmt: 37168
-------- scanstats --------
-------- subquery 8 -------
Loop 1: SEARCH TABLE foo USING COVERING INDEX idx_foo (bar=?)
nLoop=1 nRow=2790 estRow=1024 estRow/Loop=1024
---------------------------
Run Time: real 0.032 user 0.000000 sys 0.000000
sqlite>
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of t...@qvgps.com
Sent: Thursday, August 30, 2018 12:28 PM
To: SQLite mailing list
Subject: Re: [sqlite] Strategies to reduce page-loads?
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
