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)
Change History (14)
by , 16 years ago
Attachment: | mapserver-5.2.0-2725.patch added |
---|
comment:1 by , 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 , 16 years ago
Cc: | added |
---|---|
Milestone: | → 5.4 release |
Owner: | changed from | to
comment:3 by , 14 years ago
Milestone: | 5.6 release → 6.0 release |
---|
comment:4 by , 14 years ago
Cc: | added |
---|
Bart,
I think we should revisit this bug and see if this is still valid. Here are some situation regarding calls to getextent done on a layer when doing a wfs request:
1- without a bbox in the query: calls getextent
2 - with a bbox in the query: no calls to getextent http://127.0.0.1/cgi-bin/mapserv.exe?map=f:/projects/mapserver-buildkit-2008/mapserver/msautotest/wxs/wfs_filter_mine.map&&SERVICE=WFS&VERSION=1.0.0&REQUEST=GetFeature&TYPENAME=popplace_oracle&bbox=-67.5725,42,-58.9275,48.5
3- simple filters: no bbox: no call to getextent http://127.0.0.1/cgi-bin/mapserv.exe?map=f:/projects/mapserver-buildkit-2008/mapserver/msautotest/wxs/wfs_filter_mine.map&&SERVICE=WFS&VERSION=1.0.0&REQUEST=GetFeature&TYPENAME=popplace_oracle&FILTER=<Filter><PropertyIsEqualTo><PropertyName>OGR_FID</PropertyName><Literal>13</Literal></PropertyIsEqualTo</Filter>
4 - filter with bbox:no call to getextent
http://127.0.0.1/cgi-bin/mapserv.exe?map=f:/projects/mapserver-buildkit-2008/mapserver/msautotest/wxs/wfs_filter_mine.map&&SERVICE=WFS&VERSION=1.0.0&REQUEST=GetFeature&TYPENAME=popplace_oracle&FILTER=<Filter><BBOX><PropertyName>POINT</PropertyName><Box srsName="EPSG:4326"><coordinates>-65.86,44.56 -65.76,44.68</coordinates></Box></BBOX></Filter>
comment:5 by , 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 , 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 , 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 , 14 years ago
Cc: | 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 , 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 , 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.
preliminary patch (needs more work)