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

[Rahkonen Jukka (MML)]

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.

-Jukka Rahkonen-





-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20161220/1cedbc5d/attachment.html>