[mapserver-dev] Improving WFS GetFeature Performance

[Seth G]

Hi Steve,

I've added a WIP pull request based on your approach at https://github.com/mapserver/mapserver/pull/5997
I ran into a few issues, but it may be possible to resolve them. I'd appreciate your opinion on if it is worth progressing down this path. 

For the MVT case, do you think MVT merits its own METADATA keywords? E.g. "mvt_include_items" similar to the "wms_include_items" which is used for WMS GetFeatureInfo queries. 

Seth

--
web:http://geographika.co.uk
twitter: @geographika


On Sat, Feb 22, 2020, at 12:19 AM, Steve Lime wrote:
> The MVT case definitely represents an opportunity. It should be looking at the items metadata however you can limit items in SQL statements as a work around. I don’t know that you can limit items via WMS (definitely not via mode=tile). It does respect the items metadata in generating the tile.
> 
> The trick with WFS would be using manipulating the items metadata based on propertyName value while at the same time respecting the original setting. That way you wouldn’t have to change a bunch of code elsewhere.
> 
> On Fri, Feb 21, 2020 at 4:40 PM Seth G <sethg at geographika.co.uk> wrote:
>> Hi all,
>> 
>>  I've been trying to see where WFS database queries could be optimised, and there is one change that gave a huge performance boost in certain situations. 
>> 
>>  A WFS LAYER can be configured with "gml_include_items" "all" to allow maximum flexibility for the client. A WFS request allows a propertyName parameter to be passed via the query string to only return data for the requested fields e.g. &propertyName=ogr_fid,name will return features with only these 2 attributes. This greatly reduces the amount of data transferred to the client. 
>>  However the database query itself still selects data for all fields for the layer (confirmed in both the MSSQL and PostGIS drivers). 
>> 
>>  Selecting only the propertyName fields in the query can have a huge effect on the database query time, especially for layers which have 10s of fields, but where each request may only require 5-6 fields at any one time. In my test case 13,000 records with 50 fields takes 12 seconds compared to running instantly for 5 fields. 
>> 
>>  A related-case are vector tiles which also select all fields on every request [1], unlike their WMS equivalent which uses msLayerWhichItems to find the minimum required fields. 
>>  Perhaps this is another case that would benefit from selecting only the required fields?
>> 
>>  Any thoughts on why any attempts to implement the above might be flawed or have unforeseen consequences?
>> 
>>  Seth
>> 
>>  [1] https://github.com/mapserver/mapserver/blob/de351bbcc17360807ccae52302c330d96486c0fc/mapmvt.c#L415
>> 
>>  --
>>  web:http://geographika.co.uk
>>  twitter: @geographika
>>  _______________________________________________
>>  mapserver-dev mailing list
>> mapserver-dev at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/mapserver-dev
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-dev/attachments/20200224/aa4ab132/attachment.html>