[mapserver-dev] Improving WFS GetFeature Performance

[Seth G]

Thanks Mike - it would be interesting if it did - when debugging it seems to be core mapquery.c code that defines the fields used. 

Seth

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

On Fri, Feb 21, 2020, at 11:47 PM, Michael Smith wrote:
> IIRC, the oraclespatial driver does only fetch the required columns on 
> the database query. I'll setup a test to verify.
> 
> Mike
> 
> 
> -- 
> Michael Smith
> US Army Corps of Engineers
> Remote Sensing/GIS Center
>  
> 
> On 2/21/20, 5:40 PM, "mapserver-dev on behalf of Seth G" 
> <mapserver-dev-bounces at lists.osgeo.org on behalf of 
> 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
> 
> 
>