Filter Encoding : WFS performance issue

[assefa]

This bug is to track the changes related to how the filter encoding is 
interpreted/used according to diffrent underlying layer types.

Right now the OGC Filter Encoding is transformed into Mapserver class 
expression regardless of the underlying layer type. This is very inefficient 
when the underlying data is a database since It does not take advantage of the 
filtering that could be done (Layer's FILTER) on the db before doing a query.

[assefa]

e-mail exgange on the mapserver-dev list :

>   The WFS code to modify should not be that bad since the functions to
>> transalte from filter encoding xml syntax to mapserver expression are
>> localized in separate functions and I could twick the code to generate
>> sql statements instead of Mapserver expressions and set the layers'
>> FILTER.
>>   The problem I have is how would I test this ? I do not have Orcacle
>> setup here ? What would be my best option to do testing on this ? Could
>> I use OGR layers with underlaying layers other than oracle ?


Assefa,

My suggestion would be that you translate to SQL suitable for use
with PostGIS and test that.  Once that is working smoothly, you
could invoke the same code for Oracle and leave it up to Arnulf
to test it.   Hopefully pure attribute WHERE clauses would be pretty
similar between Oracle and Postgres.

In fact, as you suggest you could also just drive this against OGR
as it understands WHERE style FILTER clauses as long as the
filter string is prepended with "WHERE".  It won't generally give
much performance benefit unless the backend is a database.  But
you could setup tests against a given shapefile using direct
mapserver shapefile access (traditional) and then routing the same
requests for OGR with WHERE clauses to verify that both forms are
giving the same results.

One nice thing about OGR based tests is that it would be easy
to add to a test suite.  Also, OGR SQL is so basic that if an SQL
where clause works against OGR SQL it will likely work fine in
Oracle or Postgres.

[bartvde@…]

Added myself to the cc.

[assefa]

Commited the first version. Some notes on this:

- applies only for postgis and oracle layers

 - The initial support is to translate "simple" filter encoding xml into SQL 
expressions that are set on the layers's FILTER parameter.  
Simple filters are filters that contain only comparision operators plus 
logical operators. It can also contain *one* BBOX operator.

I have done some testing using postgis layers and also OGR layers (although I 
have disabled the OGR supprt for now).


Next step is to wait for user tests to see if It adresses the performance 
issues.  I will then upgrade it to other layer types.
 

[arnulf@…]

Performance still does not work. Menawhile we have been searching the problem
elsewhere because GeoServer has the same problem and so we thought its something
else. Now i see there is somebody in the list with a simliar problem. 

We have tested 4.6 with the same results. Ben will send the query strings
printed in debug mode. 

We have a sort of deadline with an Oracle client next week. So it will also be
tested with Oracle. Ben will report whatever came out of it. 

[assefa]

Extended support for OGR layers : ans sql type expression is generated and the 
layer's filter is set using the expression preceded with a WHERE clause.

[elzouavo]

WMS and WFS tests on a PostGIS database

[elzouavo]

I have done WMS and WFS requests on a PostGIS database with no BBOX operator. WMS requests are fast. WFS requests are fast except when PropertyIsLike is used.

Here is a summary of my tests :

• WMS request with PropertyIsEqualTo : 0.162s
• WMS request with PropertyIsLike : 0.157s
• WFS request with PropertyIsEqualTo : 1.798s
• WFS request with PropertyIsLike : 68.399s

It seems that there is a big problem with WFS requests using PropertyIsLike in the filter.

[assefa]

closing. PropertyIsLike should work in 6.0. Please reopen if there is a problem