Opened 16 years ago

Closed 13 years ago

#2725 closed defect (fixed)

attribute queries are slow through WFS on Oracle Spatial

Reported by: bartvde Owned by: assefa
Priority: normal Milestone: 6.0 release
Component: WFS Server Version: unspecified
Severity: normal Keywords:
Cc: dmorissette, tomkralidis, michael.smith@…, aboudreault

Description

Hi list,

when using an attribute query (a Filter without a spatial entry such as BBOX) through WFS, Mapserver is doing the following:

1) trying to get the extent of the table, something like:

SELECT SDO_GEOM.SDO_CONVEXHULL(SHAPE, 0.001000) AS GEOM from (SELECT SHAPE FROM VW_VEXPLOITATIE

2) and then using this extent in the query

SELECT ID, OBJECTSUBCATEGORIE, SHAPE FROM VW_VEXPLOITATIE WHERE OBJECTNAAM like '%nieuw%' escape '!' AND SDO_FILTER( SHAPE, MDSYS.SDO_GEOMETRY(2003, NULL, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(14165.52,307293.125,276564.813,619315.626) ),'querytype=window') = 'TRUE'

Ofcourse this is kind of madness, the better thing to do is leave out the whole spatial part of the query. It will speed up things by many factors.

1) can be stopped by setting ows_extent METADATA on the LAYER 2) can be stopped by setting USING NONE in the DATA part

It this something the WFS code should do? If so, I can open up an enhancement bug for this and possibly a patch.

Best regards, Bart

Attachments (1)

mapserver-5.2.0-2725.patch (1.2 KB ) - added by bartvde 16 years ago.
preliminary patch (needs more work)

Download all attachments as: .zip

Change History (14)

by bartvde, 16 years ago

Attachment: mapserver-5.2.0-2725.patch added

preliminary patch (needs more work)

comment:1 by bartvde, 16 years ago

It seems the second part of the patch is being performed too late, since the layer extent will still be calculated.

comment:2 by dmorissette, 16 years ago

Cc: dmorissette added
Milestone: 5.4 release
Owner: changed from mapserverbugs to assefa

comment:3 by assefa, 14 years ago

Milestone: 5.6 release6.0 release

comment:4 by assefa, 14 years ago

Cc: tomkralidis added

comment:5 by bartvde, 14 years ago

Hi Assefa, I can confirm 1) is not an issue anymore in trunk, no SDO_CONVEXHULL is done even if the layer has no OWS_EXTENT set.

However, wrt 2), the spatial part of the query is still included even if the WFS query only contains an attribute filter, which is inefficient.

Request used:

<wfs:GetFeature xmlns:wfs="http://www.opengis.net/wfs" service="WFS" version="1.1.0" maxFeatures="100" xsi:schemaLocation="http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.1.0/wfs.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><wfs:Query typeName="feature:AAA35" srsName="EPSG:28992" xmlns:feature="http://mapserver.gis.umn.edu/mapserver"><wfs:PropertyName>OBJECTID</wfs:PropertyName><wfs:PropertyName>VOOR</wfs:PropertyName><wfs:PropertyName>LETTER</wfs:PropertyName><wfs:PropertyName>CYFER</wfs:PropertyName><wfs:PropertyName>KALK</wfs:PropertyName><wfs:PropertyName>ACHTER</wfs:PropertyName><wfs:PropertyName>SCHOP</wfs:PropertyName><wfs:PropertyName>HEL</wfs:PropertyName><wfs:PropertyName>GWT</wfs:PropertyName><wfs:PropertyName>CODE</wfs:PropertyName><wfs:PropertyName>OMSCHR</wfs:PropertyName><wfs:PropertyName>EERSTE_BOD</wfs:PropertyName><wfs:PropertyName>EERSTE_GWT</wfs:PropertyName><wfs:PropertyName>KLEUR_BOD</wfs:PropertyName><wfs:PropertyName>KLEUR_GWT</wfs:PropertyName><wfs:PropertyName>AREA</wfs:PropertyName><wfs:PropertyName>PERIMETER</wfs:PropertyName><ogc:Filter xmlns:ogc="http://www.opengis.net/ogc"><ogc:PropertyIsEqualTo matchCase="true"><ogc:PropertyName>GWT</ogc:PropertyName><ogc:Literal>II</ogc:Literal></ogc:PropertyIsEqualTo></ogc:Filter></wfs:Query></wfs:GetFeature>

SQL:

[Thu Dec 10 14:34:45 2009] [error] [client 145.50.148.45] msOracleSpatialLayerWhichShapes. Using this Sql to retrieve the data: SELECT OBJECTID, OBJECTID, VOOR, LETTER, CYFER, KALK, ACHTER, SCHOP, HEL, GWT, CODE, OMSCHR, EERSTE_BOD, EERSTE_GWT, KLEUR_BOD, KLEUR_GWT, AREA, PERIMETER, null, SHAPE FROM BODEM.BODEMKAART50 WHERE   (GWT= 'II')   AND  ROWNUM<=100  AND SDO_FILTER( SHAPE, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') = 'TRUE'
[Thu Dec 10 14:34:45 2009] [error] [client 145.50.148.45] msOracleSpatialLayerWhichShapes. Bind values: srid:-1   minx:10000.000000   miny:305000.000000  maxx:280000.000000   maxy:619000.000000

comment:6 by assefa, 14 years ago

Bart,

In the case presented, the ogcfilter sets the layers's filter parameter and a queryby rect is then done (using the extents). I guess depending on the function set on the data parameter, the query executes using either spatial or non spatial sql statements. I am not sure that we should modify the function to NONE in all cases. I guess my problem is that depending on the data and request it make sense sometimes to use a non spatial request and sometimes It does not. But I do not see a way to know which is best for any particular wfs/filter request. The one setting up the map file (and the oracle function to use) is more apt to know what is best in general.

comment:7 by bartvde, 14 years ago

Hi Assefa, but does the WFS/filter code not know if the filter has a spatial part? I remember somewhere a boolean called bSpatial? If there is no spatial part in the filter, it is always best to leave out the spatial part of the query and hence to use NONE in the DATA statement. The person setting up the MAP file cannot create an optimum for all cases, that's why code-wise would be better. I remember in my use cases there was a great difference. Maybe it is wise to check this with the Oracle Spatial people (Mike, Alan)?

comment:8 by assefa, 14 years ago

Cc: michael.smith@… aboudreault added

I have ccd Mike and Alan on this.

The OFC filter knows if it has a spatial filter in it. But the question still remains for me: which one of these is faster:

  • get me all the features in the DB that meet my attribute and spatial criteria
  • or get me all the features that only meets my attribute criteria and then let MapServer do a filtering (for features that do not fit in the map) when drawing the map.

I guess for me that depends on the data and the query.

comment:9 by bartvde, 14 years ago

You are right it probably depends on the data and the query in general, but in this case the BBOX in the SQL query will be equal to the MAP->EXTENT and then it will surely slow down the query IMHO. And if there is no spatial part in the ogc:Filter, the MAP->EXTENT is used in the query which is therefore a negative influence on performance. Most people will have the MAP->EXTENT set to the full area.

comment:10 by bartvde, 14 years ago

Hi Assefa, could we then implement the case where the user has no USING clause explicitly defined in the MAP file DATA statement, and then "override" this to USING NONE for simple SQL filters without a spatial part. Then when the WFS query is done, reset the DATA statement to the original one. I think this is a safe bet and would work for my use cases.

comment:11 by assefa, 14 years ago

works with me. looking into updating the code ...

comment:12 by assefa, 14 years ago

committed in r9713.

comment:13 by assefa, 13 years ago

Resolution: fixed
Status: newclosed

committed a while ago

Note: See TracTickets for help on using tickets.