An update on this issue, which I think identifies the source of the problem
with the slow WFS query.
After experimenting with using the SDE Java API against the troublesome
table, I found that the order of API calls is very important. You might
expect that the API calls should look like this:
query = new SeQuery(conn, columns, construct);
query.setSpatialConstraints(...)
query.prepareQuery();
There's nothing in the API docs to suggest otherwise. However, this
results in the very slow query performance (I suspect the prepareQuery
wipes out the spatial filter, resulting in a full table scan being
performed).
If the statement order is the following, then the query completes very fast
(indicating the spatial index is now being used):
query = new SeQuery(conn, columns, construct);
query.setSpatialConstraints(...)
query.prepareQuery();
So the question is: does the WMS path through the SDE driver result in a
different statement order than the WFS path? And if so, how can this be
fixed?
On Tue, Mar 17, 2015 at 9:50 AM, Martin Davis <mtncl...@gmail.com> wrote:
> We have a GeoServer 2.6.0 connected to an SDE 10.2 backed by Oracle 11g.
> One layer is a grid layer with about 10M rectangles in it. WMS peformance
> against this layer is fine (a few seconds to draw about 100 features).
> However, WFS queries against the layer are VERY slow (many minutes to
> return even just 1 feature). This is for both queries using a BBOX and for
> raw queries using only a MAXFEATURES parameter (even with a very low
> setting - e.g MAXFEATURES=1)
>
> WFS queries against other SDE layers in the same instance are somewhat
> faster, but the layers have much less data, so it's possible that they are
> also impacted by the issue.
>
> Are there any known issues that might cause GeoServer SDE WFS queries to
> be very slow?
>
> (As an aside, the log shows the SDE driver reloading the type name cache
> every 60 secs. This seems a bit aggressive... and the value is hard-coded
> in the driver. Is this part of the issue?)
>
> A log snippet showing the problem ( I think the final "Axis length
> mismatch" indicates the query finally completing):
>
> 2015-03-17 09:20:10,077 INFO [org.geoserver.wfs] -
> Request: getServiceInfo
> 2015-03-17 09:20:10,087 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] -
> Creating new ArcSDEQuery
> 2015-03-17 09:20:10,087 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] - SQL
> portion of SDE Query: 'Filter.INCLUDE'
> 2015-03-17 09:20:10,087 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] -
> Spatial-Filter portion of SDE Query: 'Filter.INCLUDE'
> 2015-03-17 09:20:10,087 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] -
> Unsupported (and therefore ignored) portion of SDE Query: 'Filter.INCLUDE'
> 2015-03-17 09:20:16,091 INFO [org.geoserver.wfs] -
> Request: getFeature
> service = WFS
> version = 1.1.0
> baseUrl = https://i1geo.nrs.bcgov:443/geoserver/
> query[0]:
> srsName = EPSG:3857
> typeName[0] = {http://gov.bc.ca/nrs}MTA_MINERAL_PLACER_GRID_POLY
> maxFeatures = 5
> outputFormat = application/json
> resultType = results
> 2015-03-17 09:20:16,094 INFO [org.geoserver.wfs.json] - about to encode
> JSON
> 2015-03-17 09:20:16,096 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] -
> Creating new ArcSDEQuery
> 2015-03-17 09:20:16,096 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] - SQL
> portion of SDE Query: 'Filter.INCLUDE'
> 2015-03-17 09:20:16,096 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] -
> Spatial-Filter portion of SDE Query: 'Filter.INCLUDE'
> 2015-03-17 09:20:16,096 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] -
> Unsupported (and therefore ignored) portion of SDE Query: 'Filter.INCLUDE'
> 2015-03-17 09:20:20,547 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] -
> Creating new ArcSDEQuery
> 2015-03-17 09:20:20,547 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] - SQL
> portion of SDE Query: 'Filter.INCLUDE'
> 2015-03-17 09:20:20,547 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] -
> Spatial-Filter portion of SDE Query: 'Filter.INCLUDE'
> 2015-03-17 09:20:20,548 DEBUG [org.geotools.arcsde.data.ArcSDEQuery] -
> Unsupported (and therefore ignored) portion of SDE Query: 'Filter.INCLUDE'
> 2015-03-17 09:20:28,467 DEBUG [org.geotools.arcsde.data] -
> FeatureTypeCache background process running...
> 2015-03-17 09:20:34,038 DEBUG [org.geotools.arcsde.data] - Ignoring ArcSDE
> registered table SDE.GDB_TABLES_LAST_MODIFIED as it has no row id column
> 2015-03-17 09:20:34,040 DEBUG [org.geotools.arcsde.data] - Finished
> updated type name cache
> 2015-03-17 09:21:34,040 DEBUG [org.geotools.arcsde.data] -
> FeatureTypeCache background process running...
> 2015-03-17 09:21:39,816 DEBUG [org.geotools.arcsde.data] - Ignoring ArcSDE
> registered table SDE.GDB_TABLES_LAST_MODIFIED as it has no row id column
> 2015-03-17 09:21:39,818 DEBUG [org.geotools.arcsde.data] - Finished
> updated type name cache
> 2015-03-17 09:22:39,819 DEBUG [org.geotools.arcsde.data] -
> FeatureTypeCache background process running...
> 2015-03-17 09:22:45,405 DEBUG [org.geotools.arcsde.data] - Ignoring ArcSDE
> registered table SDE.GDB_TABLES_LAST_MODIFIED as it has no row id column
> 2015-03-17 09:22:45,407 DEBUG [org.geotools.arcsde.data] - Finished
> updated type name cache
> 2015-03-17 09:23:45,407 DEBUG [org.geotools.arcsde.data] -
> FeatureTypeCache background process running...
> 2015-03-17 09:23:51,264 DEBUG [org.geotools.arcsde.data] - Ignoring ArcSDE
> registered table SDE.GDB_TABLES_LAST_MODIFIED as it has no row id column
> 2015-03-17 09:23:51,266 DEBUG [org.geotools.arcsde.data] - Finished
> updated type name cache
> 2015-03-17 09:24:51,266 DEBUG [org.geotools.arcsde.data] -
> FeatureTypeCache background process running...
> 2015-03-17 09:24:56,925 DEBUG [org.geotools.arcsde.data] - Ignoring ArcSDE
> registered table SDE.GDB_TABLES_LAST_MODIFIED as it has no row id column
> 2015-03-17 09:24:56,927 DEBUG [org.geotools.arcsde.data] - Finished
> updated type name cache
> 2015-03-17 09:25:56,927 DEBUG [org.geotools.arcsde.data] -
> FeatureTypeCache background process running...
> 2015-03-17 09:26:02,570 DEBUG [org.geotools.arcsde.data] - Ignoring ArcSDE
> registered table SDE.GDB_TABLES_LAST_MODIFIED as it has no row id column
> 2015-03-17 09:26:02,574 DEBUG [org.geotools.arcsde.data] - Finished
> updated type name cache
> 2015-03-17 09:27:02,574 DEBUG [org.geotools.arcsde.data] -
> FeatureTypeCache background process running...
> 2015-03-17 09:27:08,164 DEBUG [org.geotools.arcsde.data] - Ignoring ArcSDE
> registered table SDE.GDB_TABLES_LAST_MODIFIED as it has no row id column
> 2015-03-17 09:27:08,166 DEBUG [org.geotools.arcsde.data] - Finished
> updated type name cache
> 2015-03-17 09:28:08,166 DEBUG [org.geotools.arcsde.data] -
> FeatureTypeCache background process running...
> 2015-03-17 09:28:13,764 DEBUG [org.geotools.arcsde.data] - Ignoring ArcSDE
> registered table SDE.GDB_TABLES_LAST_MODIFIED as it has no row id column
> 2015-03-17 09:28:13,767 DEBUG [org.geotools.arcsde.data] - Finished
> updated type name cache
> 2015-03-17 09:29:13,767 DEBUG [org.geotools.arcsde.data] -
> FeatureTypeCache background process running...
> 2015-03-17 09:29:19,456 DEBUG [org.geotools.arcsde.data] - Ignoring ArcSDE
> registered table SDE.GDB_TABLES_LAST_MODIFIED as it has no row id column
> 2015-03-17 09:29:19,457 DEBUG [org.geotools.arcsde.data] - Finished
> updated type name cache
> 2015-03-17 09:30:19,458 DEBUG [org.geotools.arcsde.data] -
> FeatureTypeCache background process running...
> 2015-03-17 09:30:25,022 DEBUG [org.geotools.arcsde.data] - Ignoring ArcSDE
> registered table SDE.GDB_TABLES_LAST_MODIFIED as it has no row id column
> 2015-03-17 09:30:25,024 DEBUG [org.geotools.arcsde.data] - Finished
> updated type name cache
> 2015-03-17 09:30:29,557 DEBUG [org.geotools.parameter] - Axis length
> mismatch.
>
>
>
------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
