WFS performance issue

[Frank Warmerdam]

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,
--
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent