WFS performance issue

[Fernando S.]

Hi folks,
    WFS problem can occur with Oracle Spatial layers because the
function getextent for maporaclespatial.c was not implemented yet, I'm
implementing now. This bugs: #1109, #1110 and #1111 show this.
    About the segmentation fault in OracleSpatialGetShape function I
never saw this problem, I will try to find the solution, can be in
OracleSpatialLayerFreeItemInfo I will check.
    For OracleSpatialWhichShapes function I don't see any other way to
restrict the query, I used the extent (rectObj) of currently view (not
for the fixed map extent) to try find the best performance.
    Thanks.


Fernando Simon


Steve Lime wrote:

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