[mapserver-users] Speeding up WFS paging with Spatialite and GeoPackage

[Even Rouault]

On mardi 20 décembre 2016 10:36:55 CET Rahkonen Jukka (MML) wrote:
> Hi,
> 
> It seems that because of how SQLite works it is not possible to make fast
> paging for big tables by using simple SQL with LIMIT and OFFSET.  Making a
> query as "LIMIT 100 OFFSET 1000000" makes SQLite to read 1000100 rows and
> throw away the first million ones. The bigger the offset the slower the
> query.
> 
> I guess that Mapserver must still use LIMIT and OFFSET because they work
> even with combined attribute and spatial filters. But I wonder if it could
> be possible to make a WFS 2.0 stored query for a not so uncommon use case
> of loading pages without other filters from very big tables. This stored
> query would also take count and startindex as inputs but place them into a
> query like
> 
> SELECT * FROM table WHERE rowid>startindex AND rowid<=(startindex+count)
> 
> An optional parameter could be used for defining some other unique, numeric
> and indexed attribute for paging. This query should work very fine with a
> SQLite table that is freshly  created for example with ogr2ogr because then
> rowids start from one and they do not have gaps. If table has been edited
> there may be missing rowids but that is not critical because query would
> just return sometimes less rows than "count".
> 
> Unfortunately by reading
> http://mapserver.org/ogc/wfs_server.html#stored-queries-wfs-2-0 it is not
> obvious for me how to write such "GetPageByRowid" query and I would
> appreciate getting some help.

I'm not completely sure it is legal to reuse the standard STARTINDEX and COUNT parameters 
as query parameters, but that would probably work in the current implementation. But you'll 
be stuck with the addition of the startindex and count. There's no way to do that in a Filter 
understood by MapServer. You could however have a startindex and lastindex params. You 
would need to have the rowid column explictly exposed as a field with something like DATA 
"SELECT rowid as the_rowid, * FROM table". Hum but then you'd get in the slow path since 
that wouldn't be recognized as a regular OGR layer.

Even


-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20161220/0224f0bd/attachment.html>