WFS performance issue

[Yewondwossen Assefa]

I would give it a shot if it gives me ideas but I am not prepared at
this point to make major changes beside integrating code to optimize things.

Later

Bart van den Eijnden wrote:
> 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
>>----------------------------------------------------------------
>>
>
>

--
----------------------------------------------------------------
Assefa Yewondwossen
Software Analyst

Email: assefa at dmsolutions.ca
http://www.dmsolutions.ca/

Phone: (613) 565-5056 (ext 14)
Fax:   (613) 565-0925
----------------------------------------------------------------