SQLite does not have "query caching". It does have a "page cache" that will
keep heavily used pages iin memory. There is also the possibility of a
file-system/os-level cache. To break down the 1.6 seconds required for the
first query, try executing an sql script. In linux this would be along the
lines of:
> date; sqlite3 file.db < script.sql;date
With script.sql containing:
Select strftime('%Y-%m-%d %H:%M:%S.%f');
Select strftime('%Y-%m-%d %H:%M:%S.%f');
.describe adas_link_geometry
Select strftime('%Y-%m-%d %H:%M:%S.%f');
.describe idx_adas_link_geometry
Select strftime('%Y-%m-%d %H:%M:%S.%f');
Select <your statement>;
Select strftime('%Y-%m-%d %H:%M:%S.%f');
Select <your statement>;
Select strftime('%Y-%m-%d %H:%M:%S.%f');
Select <your statement>;
Select strftime('%Y-%m-%d %H:%M:%S.%f');
This should give you an idea of where the time is being spent.
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von David Ashman - Zone 7 Engineering, LLC
Gesendet: Dienstag, 20. März 2018 16:34
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] R*Trees query data cached?
Hello -
I have a question on SQLite query data buffering.
I'm successfully using SQLite v3.22.0 on an embedded ARM processor from ST with
SD card. The database file size is about 750MB. The file system is Segger
emFile FAT32. I've configured SQLite to use 6MB RAM for heap. I've done some
query time benchmarking and found that the very first R*Trees query takes about
1.6 seconds to complete. Each successive R*Trees query (same query string with
slightly different search parameters) takes about 11ms to complete. Being new
to SQLite and spatial queries, I'm trying to understand the substantial query
time differences... does SQLite cache data from each query for future queries?
The initial query:SELECT LINK_ID, FROM_REF_ELEVATION, TO_REF_ELEVATION FROM
adas_link_geometry, idx_adas_link_geometry WHERE adas_link_geometry.ROWID =
idx_adas_link_geometry.id AND minLat > 454760320 AND maxLat < 454800320 AND
minLong > -1226807072 AND maxLong < -1226767072;
Thanks in advance,Dave
_______________________________________________
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 |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
