WFS performance issue

Tue Feb 1 10:34:42 EST 2005

Have you tried these queries outside of the WFS context? That is,
straight MapServer? Or even straight Oracle Spatial. It'd be helpful to
get as many layers as possible out of the way.

Steve

>>> Arnulf Christl <arnulf.christl at ccgis.de> 02/01/05 9:24 AM >>>

> Attribute queries *should* use the underlying RDMS system so it's not
> MapServer per se that loops through records excepting in the case of
> shapefiles. With a shapefile I could understand why it would be slow.
[...]

Hi,
I disagree with it being a database problem - but without fully
understanding it the probability is high that you are right again...

I repost this report on our troubles with the WFS implementation in the
hope that Fernando has an idea.

>>>>Arnulf Christl <arnulf.christl at ccgis.de> 01/30/05 1:34 PM >>>
[...]
We have developed a performance problem using MapServer as WFS. The
problem is not caused by the spatial access which is great and fast as
ever but (we suspect) because of how MapServer implements a query based
on a feature attribute search. Would be great if somebody could verify
my gut instinct on this.
[it feels a little like a follow up on a getFeatureInfo Request problem
we reported. MapServer first requests for for the ids, then for the
rest].

Data
The database consists of 6.5 million land lots. Each can be found by
querying for 4 hierarchically distributed integers, similar to state,
province, county, address... Each has a unique oid "GEO_ID_". Some land
lots are only identified by three of those identifiers, the fourth being

Null. All of this has been accounted for when setting up and indexing
the database. The database has been tuned heavily, several other
applications run fine with it. The database fields concerned are indexed

in the order of relevance, the connection string in the MAP file also
respects this order.

What we need to do is navigate on the attribute data using MapServer as
WFS. That way we do not access the WFS with a spatial BBox but only with

attributes.

Database
We have run it with Oracle using thin OGR and thick oraclespatial and
also with PostgreSQL/PostGIS (some might have seen several posts to the
user list where we still thought that we did something wrong in the
connection string).

Request
this WFS is requested like below, but usually with all four filter
parameters (only two in this example).

map=alk_wfs_jl.map
&REQUEST=GetFeature
&VERSION=1.0.0
&SERVICE=WFS
&TYPENAME=flurst
&FILTER=
<Filter>
     <And>
         <PropertyIsEqualTo>
             <PropertyName>GMNR</PropertyName>
             <Literal>3704</Literal>
         </PropertyIsEqualTo>
         <PropertyIsEqualTo>
             <PropertyName>FLNR</PropertyName>
             <Literal>7</Literal>
         </PropertyIsEqualTo>
     </And>
</Filter>

We compiled MapServer with debug option and a patched maporaclespatial.c

that spills the SQLs to the command line. Here they are:

[Tue Jan 25 20:55:53 2005].940125 msOracleSpatialLayerWhichShapes: SQL
statement is SELECT GEO_ID_, GMNR, GEO_COORD_ FROM (SELECT GEO_COORD_ ,
GMNR, FLNR, FSNR, FUNR, QUELLE, GEO_ID_ FROM FLO.FLO_AL125_AGGREGA_VW)
WHERE SDO_FILTER( GEO_COORD_, MDSYS.SDO_GEOMETRY(2003, 82027,
NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(3435500,5509500,3436500,5510000)

),'querytype=window') = 'TRUE'

[Tue Jan 25 20:55:54 2005].812235 msOracleSpatialLayerWhichShapes: SQL
statement is SELECT GEO_ID_, FLNR, GEO_COORD_ FROM (SELECT GEO_COORD_ ,
GMNR, FLNR, FSNR, FUNR, QUELLE, GEO_ID_ FROM FLO.FLO_AL125_AGGREGA_VW)
WHERE SDO_FILTER( GEO_COORD_, MDSYS.SDO_GEOMETRY(2003, 82027,
NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(3435500,5509500,3436500,5510000)

),'querytype=window') = 'TRUE'

MapServer seems to consecutively make full table scans for each
parameter. The only spatial restriction obviously being what has been
set in the MAP file extent parameter - as this kind of request does not
have a spatial component.

Having finished this MapServer would like to retrieve the requested
information by querying the result set with the initial SELECT
statement. We simulated this by calling:

mapserv441-debug
"QUERY_STRING=map=alk_wfs_jl.map&REQUEST=GetFeature&VERSION=1.0.0&SERVICE=WFS&TYPENAME=flurst&FILTER=<Filter><PropertyIsLessThan><PropertyName>GMNR</PropertyName><Literal>99999</Literal></PropertyIsLessThan></Filter>"

The result is:

[Tue Jan 25 20:59:07 2005].331593 msOracleSpatialLayerOpen called with:
GEO_COORD_ FROM (SELECT GEO_COORD_ , GMNR, FLNR, FSNR, FUNR, QUELLE,
GEO_ID_ FROM FLO.FLO_AL125_AGGREGA_VW) USING UNIQUE GEO_ID_ SRID 82027

[Tue Jan 25 20:59:08 2005].31715 msOracleSpatialLayerOpen. Shared
connection not available. Creating one.
[Tue Jan 25 20:59:08 2005].31759
msConnPoolRegister(flurst,***/*********@***,0x8136908)
[Tue Jan 25 20:59:08 2005].50329 msOracleSpatialLayerFreeItemInfo was
called.
[Tue Jan 25 20:59:08 2005].50378 msOracleSpatialLayerInitItemInfo was
called.
[Tue Jan 25 20:59:08 2005].50421 msOracleSpatialLayerWhichShapes was
called.
[Tue Jan 25 20:59:08 2005].50533 msOracleSpatialLayerWhichShapes: SQL
statement is SELECT GEO_ID_, GMNR, GEO_COORD_ FROM (SELECT GEO_COORD_ ,
GMNR, FLNR, FSNR, FUNR, QUELLE, GEO_ID_ FROM FLO.FLO_AL125_AGGREGA_VW)
WHERE SDO_FILTER( GEO_COORD_, MDSYS.SDO_GEOMETRY(2003, 82027,
NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(3435500,5509500,3436500,5510000)

),'querytype=window') = 'TRUE'

[Tue Jan 25 20:59:08 2005].328734 msOracleSpatialLayerFreeItemInfo was
called.
[Tue Jan 25 20:59:08 2005].328790 msOracleSpatialLayerClose was called.
Layer connection: ***/*********@***
[Tue Jan 25 20:59:08 2005].328816 msOracleSpatialLayerClose. Cleaning
layerinfo handlers.
[Tue Jan 25 20:59:08 2005].329023 msOracleSpatialLayerClose. Cleaning
Oracle handlers.
[Tue Jan 25 20:59:08 2005].329046
msConnPoolRelease(flurst,***/*********@***,0x8136908)
[Tue Jan 25 20:59:08 2005].329072
msConnPoolClose(***/*********@***,0x8136908)
[Tue Jan 25 20:59:08 2005].374601 msOracleSpatialLayerOpen called with:
GEO_COORD_ FROM (SELECT GEO_COORD_ , GMNR, FLNR, FSNR, FUNR, QUELLE,
GEO_ID_ FROM FLO.FLO_AL125_AGGREGA_VW) USING UNIQUE GEO_ID_ SRID 82027

[Tue Jan 25 20:59:08 2005].573113 msOracleSpatialLayerOpen. Shared
connection not available. Creating one.
[Tue Jan 25 20:59:08 2005].573152
msConnPoolRegister(flurst,***/*********@***,0x81a2910)
[Tue Jan 25 20:59:08 2005].583712 msOracleSpatialLayerFreeItemInfo was
called.
[Tue Jan 25 20:59:08 2005].583752 msOracleSpatialLayerInitItemInfo was
called.
[Tue Jan 25 20:59:08 2005].583777 msOracleSpatialLayerGetShape was
called. Using the record = 4393231.
[Tue Jan 25 20:59:08 2005].583815 msOracleSpatialLayerGetShape. Sql:
SELECT  GMNR, GEO_COORD_ FROM (SELECT GEO_COORD_ , GMNR, FLNR, FSNR,
FUNR, QUELLE, GEO_ID_ FROM FLO.FLO_AL125_AGGREGA_VW) WHERE GEO_ID_ =
4393231

MapServer dies with a seg fault here, we didn't find out why.

As we are deep into the project already we have to decide now whether to

switch to GeoServer for this particular question (not knowing whether it

will work there either :-) or wait for MapServer to somehow get the
request done correctly.

---
Never tired of a workaround we temporarily do it with a standard
MapServer request with DUMP=True and the request FILTER parameters as
[variables-] in the MAP file. This is far from being OGC WFS but it
works fine. For some reason this currently will only work with OGR, we
are still debugging thick oraclespatial.
---

Best, Arnulf.

--
------------------------
Arnulf B. Christl
------------------------
http://www.ccgis.org
http://www.mapbender.org
------------------------