Hi Guys,
we are using SQlite for storing OpenStreetMap ways (lines).
Once filled its readonly.
Then the lines is queried for specific areas to draw a map.
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);
Queries are always all lines of a specific geographical ares, which is
very fast because of the rtree.
SELECT Lines.* FROM Lines_bb , Lines WHERE y0 < ? AND y1 > ? AND x0 < ?
AND x1 > ? AND ? BETWEEN z0 AND z1 AND Lines_bb.Id = Lines.Id ;
Then the rows are fetched and here starts the problems:
var stmt = SQLite3.Prepare2 (handle, select);
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
// fetch row
}
The larger the db, the slower is the fetching!
We compared a small db (50mb) and a big db (500mb), both containing the
same small area:
Reading the same area of for example 1000 lines from the small db is 2x
faster then from the large db.
After doing some profiling, it turned out, that the extra time was spent
in SQLite3.Step.
My assumption is, that in the big db, these 1000 lines are just spread
over a much higher count of pages.
So more page-loads resulting in more time.
We changed page_size to the maximum value of 64k and it became much
better, but still I would lke to improve it.
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 Nam mobile
** +49 (0)175 7313081 D mobile
** +49 (0)6182 8492599 D office
***************************************/
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
