WFS performance issue

[Yewondwossen Assefa]

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
----------------------------------------------------------------