WFS performance issue

[Bart van den Eijnden]

Hi Assefa,

just a thought, would it not be wise to look into the Geotools/Geoserver
code for translating Filter into SQL? It could provide some ideas. Or at
least get some more information from them on this before implementing?

Best regards,
Bart

> I have looked into it a bit yestrady night and here how I think of
> implemeting the changes :
>
>    - for simple filter encoding cases (by simple I mean a filer encoding
> that has only comparion operators bundled with logical operators. Eg
> Attribute1 eq value1 and/or/not alltibute2 > value2), The fiter encoding
> xml would be transformed into one SQL statment and this statement will
> be applied to the Layer's FILTER parameter. Then the wfs code will do
> mapserver query using the map extents. Any class elements that were
> intially set on the layer would be removed. I think this would adress
> most of the cases as I understand it.
>
>   - When the filter encoding become complexe (combination of spatial
> filters (such as bbox, within), with comparison operators and logical
> opertaors, It becomes quite impossible to transform that into *one*
> statement and only do one query. In this case, the filter would be
> parsed into several simple filters and these simple filters will be
> applied separatly. For example if we have something like this
>    AND
>      Property1 = value 1
>      Pripety2 > value 2
>    Or
>      bbox xmin, ymin, xmax, ymax
>
>    This would result :
>      - 1 sql statment for Property1 = value 1 and a query using the map
> extent
>      - 1 sql statement for Pripety2 > value 2  1 and a query using the
> map extent
>      - merge (AND) of the 2 results
>      - 1 query using the bbox
>      - merge (or) with the prvious set of elements found.
>
>    I understand the this way of implementing complexe filters is not the
> most optimal but Filters can become so complex that I need some kind of
> logic and limitation to be able to adress all the cases.
>
>
>   Comments ?
>
> Later,
>
> Frank Warmerdam wrote:
>> On Thu, 24 Mar 2005 14:23:50 +0100, Arnulf Christl
>> <arnulf.christl at ccgis.de> wrote:
>>
>>>What got us stuck was the way MapServer did the query. If i got that one
>>>right: MS first starts a full table scan using the only bbox it could
>>>find - which is the WFS_EXTENT to retrieve the oid of all relevant
>>>geometries. Then it starts another query with the parameters i'd expect
>>>to be in the WHERE clause on this "subset" (which still comprises all
>>>6.5 million elements). This is too heavy for the underlying Oracle
>>>database, Apache timeouts, etc. - it just wont do.
>>
>>
>> Arnulf,
>>
>> My understanding is that there are two issues.  One is the unnecessary
>> or
>> confusing spatial query imposed by the .map bbox even when the wfs
>> query has no spatial restriction.  The other is the fact that
>> attribute filtering
>> is done as a post-processing step by mapserver instead of being passed
>> into the RDBMS as a WHERE style filter.
>>
>> I propose to dig into making MapServer queries able to handle NULL
>> query rects in cases where no spatial filtering makes sense.  This can
>> reduce query optimization confusion in some spatial databases (and OGR!)
>> as well as help avoid problems like Tom K. ran into recently with the
>> unexpected effect of his .map EXTENT on WFS queries.
>>
>> Assefa is offering to rewrite WFS attribute queries in a backend
>> specific
>> format (likely just adding support for SQL WHERE for Oracle, PostGIS
>> and OGR) so that the attribute filtering can go down to the database
>> instead of being done in MapServer as a postprocessing step.
>>
>> So I believe it is Assefa's change that will make a several orders
>> of magnitude improvement in performance for your case.  My change
>> might also be helpful in some cases.
>>
>>
>>>The fun thing is that we *can* do it by using MapServer as a warped WMS.
>>>We simply activate the internal (not OGC) FILTER expression which we add
>>>to the WMS getMap as a vendor parameter. Setting DUMP=true you get a
>>>valid GML - which is all we need to gazetteer, highlight, download and
>>>edit the land parcel.
>>
>>
>> Wow, thats pretty neat though I don't follow it completely.  But
>> hopefully
>> we will reach a point where things can be a little easier to optimize
>> "out of the box".
>>
>> Best regards,
>
> --
> ----------------------------------------------------------------
> Assefa Yewondwossen
> Software Analyst
>
> Email: assefa at dmsolutions.ca
> http://www.dmsolutions.ca/
>
> Phone: (613) 565-5056 (ext 14)
> Fax:   (613) 565-0925
> ----------------------------------------------------------------
>