[Geoserver-users] Slow WFS query against SDE?

GeoServer GeoServer User
1

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.

2

On Tue, Mar 17, 2015 at 5:50 PM, Martin Davis <mtnclimb@anonymised.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?

I believe no core developer has had access to a SDE server in quite some
time, as a result, we had very little changes
on it and knowledge about what works, what does not, is sparse.
I see MapServer is in the same boat and they were voting on dropping native
SDE support recently (I believe the motion actually passed).

If you have access to a SDE instance, you're in much better position that
anyone else I know to debug and rectify the issue (and to answer your
question, no, no known issues that I'm aware of)

Cheers
Andrea

-

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

3

Just in case you didn’t already know it I would like to inform you that ArcSDE is a deprecated technology and 10.2.2 was its last working release. As for last available information from ESRI 10.3, actual release of ArcGIS products family, and future releases do not use it.
Some information, more relevant to ESRI’s users, may be found at this document:

http://downloads2.esri.com/support/TechArticles/W28703_DEPRECATION_PLAN_FOR_ARCGIS_10_1_and_10_2__Updated_for_10_2_1d_final.pdf

Cheers

Stefano

···

2015-03-17 18:03 GMT+01:00 Andrea Aime <andrea.aime@anonymised.com>:

I believe no core developer has had access to a SDE server in quite some time, as a result, we had very little changes

on it and knowledge about what works, what does not, is sparse.

I see MapServer is in the same boat and they were voting on dropping native SDE support recently (I believe the motion actually passed).

If you have access to a SDE instance, you’re in much better position that anyone else I know to debug and rectify the issue (and to answer your question, no, no known issues that I’m aware of)

Cheers

Andrea

41.95581N 12.52854E

http://www.linkedin.com/in/stefanoiacovella

http://twitter.com/#!/Iacovellas

4

Thanks, Andrea. If we are desperate enough I will have to debug my way into the depths of the SDE driver to see what’s going on.

I see the MapServer discussion here: http://osgeo-org.1560.x6.nabble.com/FW-mapserver-dev-Motion-Retire-SDE-Support-for-MapServer-7-0-td5186626.html
Doesn’t look like a firm decision, but clearly the desire to dump SDE is there. It seems ESRI is not interested in promoting the SDE client libs either. So perhaps GeoServer will follow the same path (and good riddance!) However, we have a lot of legacy SDE DBs, so we’ll have to think hard about whether to try and move them to Oracle or to try and fix the driver issues.

···

On Tue, Mar 17, 2015 at 10:03 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Tue, Mar 17, 2015 at 5:50 PM, Martin Davis <mtnclimb@anonymised.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?

I believe no core developer has had access to a SDE server in quite some time, as a result, we had very little changes

on it and knowledge about what works, what does not, is sparse.

I see MapServer is in the same boat and they were voting on dropping native SDE support recently (I believe the motion actually passed).

If you have access to a SDE instance, you’re in much better position that anyone else I know to debug and rectify the issue (and to answer your question, no, no known issues that I’m aware of)

Cheers

Andrea

==

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

5

Oh yes, we’ve seen this as well. Unfortunately vendor timelines are not always aligned with organizational capacity for change.

···

On Tue, Mar 17, 2015 at 10:11 AM, Stefano Iacovella <stefano.iacovella@anonymised.com> wrote:

2015-03-17 18:03 GMT+01:00 Andrea Aime <andrea.aime@anonymised.com>:

I believe no core developer has had access to a SDE server in quite some time, as a result, we had very little changes

on it and knowledge about what works, what does not, is sparse.

I see MapServer is in the same boat and they were voting on dropping native SDE support recently (I believe the motion actually passed).

If you have access to a SDE instance, you’re in much better position that anyone else I know to debug and rectify the issue (and to answer your question, no, no known issues that I’m aware of)

Cheers

Andrea

6

On Tue, Mar 17, 2015 at 6:11 PM, Stefano Iacovella <
stefano.iacovella@anonymised.com> wrote:

2015-03-17 18:03 GMT+01:00 Andrea Aime <andrea.aime@anonymised.com>:

I believe no core developer has had access to a SDE server in quite some
time, as a result, we had very little changes
on it and knowledge about what works, what does not, is sparse.
I see MapServer is in the same boat and they were voting on dropping
native SDE support recently (I believe the motion actually passed).

If you have access to a SDE instance, you're in much better position that
anyone else I know to debug and rectify the issue (and to answer your
question, no, no known issues that I'm aware of)

Cheers
Andrea

Just in case you didn't already know it I would like to inform you that
ArcSDE is a deprecated technology and 10.2.2 was its last working release.
As for last available information from ESRI 10.3, actual release of ArcGIS
products family, and future releases do not use it.
Some information, more relevant to ESRI's users, may be found at this
document:

http://downloads2.esri.com/support/TechArticles/W28703_DEPRECATION_PLAN_FOR_ARCGIS_10_1_and_10_2__Updated_for_10_2_1d_final.pdf

Interesting... I thought SDE was mandatory for versioned edits, what is
going to replace that functionality, geodatabase against a
spatial db like oracle/sqlserver/postgis?

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

7

Yes, I think ESRI is moving all SDE services into the client tools, which connect directly (may even be called Direct Connect) to spatial databases. I believe that there is still SDE metadata required, and possibly a special SDE schema where needed to supporting versioning (but I"m just guessing here). So it may not be totally trivial to support SDE via the standard Oracle driver, but it should be more possible.

In the case of Oracle I’ve even heard that they are preferring to use native Oracle SDO format, rather than their ST_GEOMETRY format.

You have to pedal pretty fast to keep your data centre up with ESRI’s developers…

···

On Tue, Mar 17, 2015 at 10:19 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

Just in case you didn’t already know it I would like to inform you that ArcSDE is a deprecated technology and 10.2.2 was its last working release. As for last available information from ESRI 10.3, actual release of ArcGIS products family, and future releases do not use it.
Some information, more relevant to ESRI’s users, may be found at this document:

http://downloads2.esri.com/support/TechArticles/W28703_DEPRECATION_PLAN_FOR_ARCGIS_10_1_and_10_2__Updated_for_10_2_1d_final.pdf

Interesting… I thought SDE was mandatory for versioned edits, what is going to replace that functionality, geodatabase against a

spatial db like oracle/sqlserver/postgis?

8

On Tue, Mar 17, 2015 at 6:25 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

You have to pedal pretty fast to keep your data centre up with ESRI's
developers...

Hahaha, well, as we say in Italy "You wanted the bycicle... now pedal!" :wink:

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

9

2015-03-17 18:19 GMT+01:00 Andrea Aime <andrea.aime@anonymised.com>:

Interesting... I thought SDE was mandatory for versioned edits, what is
going to replace that functionality, geodatabase against a
spatial db like oracle/sqlserver/postgis?

Cheers

The internal structure on an ESRI Enterprise Geodatabase, what once was
ArcSDE, is not going to change. So the way versioning, archiving and
editing tracking is performed will be the same, or at least is the same at
release 10.3
I have a main customer that is going trough the migration stage and we are
looking at the best path.

From a GeoServer point of view, to remain in topic and to discuss about

what is more interesting for GeoServer's users and developers, what ESRI is
dismissing are the possibility of native connection, that is having a
custom service that accept proprietary connection from client, and the
ArcSDE SDK.
These have huge impact on the ArcSDE plugin, indeed it is built against the
jar included in the SDK so if in the future you have to avoid upgrading
your Geodatabase to 10.3 and following or you have to use an old, and
unsupproted, SDK release against a 10.3+ Geodatabase.
As for the native versus direct connection I have not a great experience of
using GeoServer with ArcSDE, unfortunately customers paying big bucks for
ArcGIS for Server are not so keen to use GeoServer for publishing their
data. Anyway in the few occasions I have always have to use the native
connection because direct connection didn't work for me.

With regard to timing I have to say, altough I am not too keen to defend
ESRI usually :slight_smile: , that ArcSDE was on the way of being deprecated since
release 9.2, when ESRI stopped to distribute it as a stand alone package
and included it in ArcGIS for Server, the GeoServer similar product made by
them. Every customer or developer knows since then, some years ago, that
ArcSDE was going to be dismissed. But human being are probably more keen to
be retroactive than proactive :smiley:

Cheers

Stefano

---------------------------------------------------
41.95581N 12.52854E

http://www.linkedin.com/in/stefanoiacovella

http://twitter.com/#!/Iacovellas

10

2015-03-17 18:25 GMT+01:00 Martin Davis <mtnclimb@anonymised.com>:

In the case of Oracle I've even heard that they are preferring to use
native Oracle SDO format, rather than their ST_GEOMETRY format.

What ESRI recommends is to use ST_GEOMETRY, but on ORacle you have to mind
to use SDE.ST_Geometry version of objects and function as the Oracle
version doesn't work.

---------------------------------------------------
41.95581N 12.52854E

http://www.linkedin.com/in/stefanoiacovella

http://twitter.com/#!/Iacovellas

11

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 <mtnclimb@anonymised.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.

12

On Tue, Mar 31, 2015 at 10:48 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

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();

Confused, where is the difference? :slight_smile:

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?

Hum... it may be, but a store is just a store, and
featureSource.getFeatures(Query) is the same method.
Maybe the contents of Query are different and this triggers a different
code path in the store?
One likely difference is that WFS (depending on your config and WFS version
used) needs to perform a count before getting
the actual data (part of the response headers), WMS does not.

CC'ing Gabriel, he's the last one to have worked on the SDE store.

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

13

Argh… that code snippet for the CORRECT use of spatial constraints should read:

query = new SeQuery(conn, columns, construct);
query.prepareQuery();
query.setSpatialConstraints(…);

And agreed, it seems like it’s unlikely that WFS alone uses a different call order. As you say, it may be that the query is being formulated in a different way (e.g. perhaps the WFS BBOX filter is not correctly converted to an ArcSDE spatial constraint - although it seems likely that WMS also uses a BBOX filter).

If a count is needed (yuck!) then I would hope that the same query is used as to retrieve the actual data - and then they should be be fast if the query uses the spatial constraint correctly. But there is a bunch of logic in the ArcSDE code around counts, so perhaps something else is going on.

Gabriel, if you have ideas it would be great to hear them. Also, I’m very close to having a debug GeoServer environment up and running, so I will be able to see what’s going on.

···

On Tue, Mar 31, 2015 at 11:12 PM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Tue, Mar 31, 2015 at 10:48 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

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();

Confused, where is the difference? :slight_smile:

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?

Hum… it may be, but a store is just a store, and featureSource.getFeatures(Query) is the same method.
Maybe the contents of Query are different and this triggers a different code path in the store?
One likely difference is that WFS (depending on your config and WFS version used) needs to perform a count before getting
the actual data (part of the response headers), WMS does not.

CC’ing Gabriel, he’s the last one to have worked on the SDE store.

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

14

Hmmm… in ArcSDEQuery.calculateResultCount() start at line 615 I see:

[1] 615: query.setSpatialConstraints(searchOrder, calcMasks, spatialFilters);

and then a few lines later in a code path that is executed only if the SDE instance is versioned and backed by Oracle:

[2] 631: query.prepareQueryInfo(queryInfo);

So that looks to me like the resultCount logic may be using the incorrect order of calls to the SDE API. Our setup definitely uses Oracle, and the SDE layer may be versioned, so perhaps we’re triggering this path. The good news is that the fix should be easy - just copy the call to setSpatialConstraints inside the path and put it after the prepareQueryInfo. I will try and verify this.

[1] https://github.com/geotools/geotools/blob/master/modules/plugin/arcsde/datastore/src/main/java/org/geotools/arcsde/data/ArcSDEQuery.java#L615
[2] https://github.com/geotools/geotools/blob/master/modules/plugin/arcsde/datastore/src/main/java/org/geotools/arcsde/data/ArcSDEQuery.java#L631

···

On Wed, Apr 1, 2015 at 8:47 AM, Martin Davis <mtnclimb@anonymised.com> wrote:

Argh… that code snippet for the CORRECT use of spatial constraints should read:

query = new SeQuery(conn, columns, construct);
query.prepareQuery();
query.setSpatialConstraints(…);

And agreed, it seems like it’s unlikely that WFS alone uses a different call order. As you say, it may be that the query is being formulated in a different way (e.g. perhaps the WFS BBOX filter is not correctly converted to an ArcSDE spatial constraint - although it seems likely that WMS also uses a BBOX filter).

If a count is needed (yuck!) then I would hope that the same query is used as to retrieve the actual data - and then they should be be fast if the query uses the spatial constraint correctly. But there is a bunch of logic in the ArcSDE code around counts, so perhaps something else is going on.

Gabriel, if you have ideas it would be great to hear them. Also, I’m very close to having a debug GeoServer environment up and running, so I will be able to see what’s going on.

On Tue, Mar 31, 2015 at 11:12 PM, Andrea Aime <andrea.aime@anonymised.com.> wrote:

On Tue, Mar 31, 2015 at 10:48 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

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();

Confused, where is the difference? :slight_smile:

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?

Hum… it may be, but a store is just a store, and featureSource.getFeatures(Query) is the same method.
Maybe the contents of Query are different and this triggers a different code path in the store?
One likely difference is that WFS (depending on your config and WFS version used) needs to perform a count before getting
the actual data (part of the response headers), WMS does not.

CC’ing Gabriel, he’s the last one to have worked on the SDE store.

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

15

I’ve now identified the problem causing the slow WFS performance. As Andrea suspected, it is in the ArcSDEQuery.calculateResultCount() method, called before the WFS query queries the data. The issue is that (on our Oracle SDE instance at least) the SeQuery.calculateTableStatistics() call is extremely slow (likely it is doing a full table scan rather than using the spatial index).

This shows up very obviously in our case, since we have a layer with 11M features in it. But it’s impacting performance for every WFS query (even scans of small tables seem to be much slower than the actual data retrieval). (This actually makes me wonder if the SDE API is pulling the data over the wire to count it!).

Here’s the actual stats from a test mockup:

Testing layer MTA_SPATIAL.MTA_MINERAL_PLACER_GRID_POLY
Fetching table stats…
Row count = 1560 ---- 955.04 s
Querying data…
Row count = 1560 ---- 2.052 s

We’re going to open a ticket with ESRI about this, but I don’t have much optimism they’ll do anything for us (given that the SDE API is sunsetting).

So what are the options on the GeoServer side? It might be always faster to simply run the query twice, once to count and once for the data. In fact, there is already code in the calculateResultCount to do this, for Oracle versioned layers. Perhaps this should be extended for all Oracle layers? (Note that I still think there may be a bug in this code to do with the order of API calls, but that can be fixed at the same time).

Thoughts?

In the meantime I am going to work on modifying the driver to test out this idea (and we may just use that in production if it works out anyway).

···

On Tue, Mar 31, 2015 at 11:12 PM, Andrea Aime <andrea.aime@anonymised.com> wrote

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?

Hum… it may be, but a store is just a store, and featureSource.getFeatures(Query) is the same method.
Maybe the contents of Query are different and this triggers a different code path in the store?
One likely difference is that WFS (depending on your config and WFS version used) needs to perform a count before getting
the actual data (part of the response headers), WMS does not.

16

On Thu, Apr 2, 2015 at 7:47 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

I've now identified the problem causing the slow WFS performance. As
Andrea suspected, it is in the ArcSDEQuery.calculateResultCount() method,
called before the WFS query queries the data. The issue is that (on our
Oracle SDE instance at least) the SeQuery.calculateTableStatistics() call
is extremely slow (likely it is doing a full table scan rather than using
the spatial index).

This shows up very obviously in our case, since we have a layer with 11M
features in it. But it's impacting performance for every WFS query (even
scans of small tables seem to be much slower than the actual data
retrieval). (This actually makes me wonder if the SDE API is pulling the
data over the wire to count it!).

Here's the actual stats from a test mockup:

Testing layer MTA_SPATIAL.MTA_MINERAL_PLACER_GRID_POLY
Fetching table stats...
Row count = 1560 ---- 955.04 s
Querying data...
Row count = 1560 ---- 2.052 s

Wow... gross :slight_smile:
At that point you can indeed fetch all the data, maybe telling SDE that you
only want one small column (to reduce data transfer)

We're going to open a ticket with ESRI about this, but I don't have much
optimism they'll do anything for us (given that the SDE API is sunsetting).

So what are the options on the GeoServer side? It might be always faster
to simply run the query twice, once to count and once for the data. In
fact, there is already code in the calculateResultCount to do this, for
Oracle versioned layers. Perhaps this should be extended for *all* Oracle
layers? (Note that I still think there may be a bug in this code to do with
the order of API calls, but that can be fixed at the same time).

Could be but.. I'm honestly blown away that this would be the way to go...
if it is, maybe we should add some flag to control it?
Or is it just going to always be slower to try get the stats?

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

17

On Thu, Apr 2, 2015 at 11:51 PM, Andrea Aime <andrea.aime@anonymised.com>
wrote:

On Thu, Apr 2, 2015 at 7:47 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

I've now identified the problem causing the slow WFS performance. As
Andrea suspected, it is in the ArcSDEQuery.calculateResultCount() method,
called before the WFS query queries the data. The issue is that (on our
Oracle SDE instance at least) the SeQuery.calculateTableStatistics() call
is extremely slow (likely it is doing a full table scan rather than using
the spatial index).

This shows up very obviously in our case, since we have a layer with 11M
features in it. But it's impacting performance for every WFS query (even
scans of small tables seem to be much slower than the actual data
retrieval). (This actually makes me wonder if the SDE API is pulling the
data over the wire to count it!).

Here's the actual stats from a test mockup:

Testing layer MTA_SPATIAL.MTA_MINERAL_PLACER_GRID_POLY
Fetching table stats...
Row count = 1560 ---- 955.04 s
Querying data...
Row count = 1560 ---- 2.052 s

Wow... gross :slight_smile:
At that point you can indeed fetch all the data, maybe telling SDE that
you only want one small column (to reduce data transfer)

Nope, have to include the geometry column, in order to be able to run the
spatial query (the SDE API requires the geometry column to be specified).

We're going to open a ticket with ESRI about this, but I don't have much
optimism they'll do anything for us (given that the SDE API is sunsetting).

So what are the options on the GeoServer side? It might be always faster
to simply run the query twice, once to count and once for the data. In
fact, there is already code in the calculateResultCount to do this, for
Oracle versioned layers. Perhaps this should be extended for *all* Oracle
layers? (Note that I still think there may be a bug in this code to do with
the order of API calls, but that can be fixed at the same time).

Could be but.. I'm honestly blown away that this would be the way to go...
if it is, maybe we should add some flag to control it?
Or is it just going to always be slower to try get the stats?

If the API is indeed doing a client-side evaluation of the spatial filter,
then the stats are always going to be no faster than the query (and a LOT
slower for a highly selective query). I tested against three tables with
very different datasets (with 10s, 1000s and Ms of records), and the stats
call was always slower than the actual query.

If this IS the case, then it doesn't seem like there's much point to a
flag, since every query with a spatial filter should use the "query twice"
strategy for best performance?

I don't know if this is the case on non-Oracle databases, so it might be
worth limiting this code path to only Oracle (for now - easy to extend to
other DBs if their drivers are found to have the same behaviour).

Also, we were orginally testing using the 9.3 JARs, against a 10.2 SDE.
Today have verified that the 10.1 JARs have the same issue.

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

18

In it’s useful for anyone, attached is a small test program that demonstrates the performance issue with computing statistics for SDE layers with spatial filters.

TableStatsTest.java (3.28 KB)

···

On Thu, Apr 2, 2015 at 10:47 AM, Martin Davis <mtnclimb@anonymised.com> wrote:

I’ve now identified the problem causing the slow WFS performance. As Andrea suspected, it is in the ArcSDEQuery.calculateResultCount() method, called before the WFS query queries the data. The issue is that (on our Oracle SDE instance at least) the SeQuery.calculateTableStatistics() call is extremely slow (likely it is doing a full table scan rather than using the spatial index).

This shows up very obviously in our case, since we have a layer with 11M features in it. But it’s impacting performance for every WFS query (even scans of small tables seem to be much slower than the actual data retrieval). (This actually makes me wonder if the SDE API is pulling the data over the wire to count it!).

Here’s the actual stats from a test mockup:

Testing layer MTA_SPATIAL.MTA_MINERAL_PLACER_GRID_POLY
Fetching table stats…
Row count = 1560 ---- 955.04 s
Querying data…
Row count = 1560 ---- 2.052 s

We’re going to open a ticket with ESRI about this, but I don’t have much optimism they’ll do anything for us (given that the SDE API is sunsetting).

So what are the options on the GeoServer side? It might be always faster to simply run the query twice, once to count and once for the data. In fact, there is already code in the calculateResultCount to do this, for Oracle versioned layers. Perhaps this should be extended for all Oracle layers? (Note that I still think there may be a bug in this code to do with the order of API calls, but that can be fixed at the same time).

Thoughts?

In the meantime I am going to work on modifying the driver to test out this idea (and we may just use that in production if it works out anyway).

On Tue, Mar 31, 2015 at 11:12 PM, Andrea Aime <andrea.aime@anonymised.com> wrote

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?

Hum… it may be, but a store is just a store, and featureSource.getFeatures(Query) is the same method.
Maybe the contents of Query are different and this triggers a different code path in the store?
One likely difference is that WFS (depending on your config and WFS version used) needs to perform a count before getting
the actual data (part of the response headers), WMS does not.

19

I have implemented a workaround for the performance issue with computing request size in SDE on Oracle. It’s here:

https://github.com/dr-jts/geotools/commit/eef7056e899660f34447f38e5b83c1c38666ff19

This has been tested in several environments and seems to work correctly in all of them.

(Note: the workaround is currently active for Oracle only, since that’s the only SDE environment I can test. The issue might exist on other DBs as well).

Will try and create issues for this when I can (Issue tracker seems to be in flux right now?). I can make a pull request as well if that’s acceptable now.

···

On Thu, Apr 2, 2015 at 10:47 AM, Martin Davis <mtnclimb@anonymised.com.> wrote:

I’ve now identified the problem causing the slow WFS performance. As Andrea suspected, it is in the ArcSDEQuery.calculateResultCount() method, called before the WFS query queries the data. The issue is that (on our Oracle SDE instance at least) the SeQuery.calculateTableStatistics() call is extremely slow (likely it is doing a full table scan rather than using the spatial index).

This shows up very obviously in our case, since we have a layer with 11M features in it. But it’s impacting performance for every WFS query (even scans of small tables seem to be much slower than the actual data retrieval). (This actually makes me wonder if the SDE API is pulling the data over the wire to count it!).

Here’s the actual stats from a test mockup:

Testing layer MTA_SPATIAL.MTA_MINERAL_PLACER_GRID_POLY
Fetching table stats…
Row count = 1560 ---- 955.04 s
Querying data…
Row count = 1560 ---- 2.052 s

We’re going to open a ticket with ESRI about this, but I don’t have much optimism they’ll do anything for us (given that the SDE API is sunsetting).

So what are the options on the GeoServer side? It might be always faster to simply run the query twice, once to count and once for the data. In fact, there is already code in the calculateResultCount to do this, for Oracle versioned layers. Perhaps this should be extended for all Oracle layers? (Note that I still think there may be a bug in this code to do with the order of API calls, but that can be fixed at the same time).

Thoughts?

In the meantime I am going to work on modifying the driver to test out this idea (and we may just use that in production if it works out anyway).

On Tue, Mar 31, 2015 at 11:12 PM, Andrea Aime <andrea.aime@anonymised.com> wrote

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?

Hum… it may be, but a store is just a store, and featureSource.getFeatures(Query) is the same method.
Maybe the contents of Query are different and this triggers a different code path in the store?
One likely difference is that WFS (depending on your config and WFS version used) needs to perform a count before getting
the actual data (part of the response headers), WMS does not.

20

Ciao Martin,
the JIRA instancehosetd by OSGEO is fully working.

Let's not miss the ooprtunity to merge this fix, and possibly backport
a little :P.

Regards,
Simone Giannecchini

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

Ing. Simone Giannecchini
@simogeo
Founder/Director

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 333 8128928

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------
AVVERTENZE AI SENSI DEL D.Lgs. 196/2003
Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate.
Il loro utilizzo è consentito esclusivamente al destinatario del
messaggio, per le finalità indicate nel messaggio stesso. Qualora
riceviate questo messaggio senza esserne il destinatario, Vi preghiamo
cortesemente di darcene notizia via e-mail e di procedere alla
distruzione del messaggio stesso, cancellandolo dal Vostro sistema.
Conservare il messaggio stesso, divulgarlo anche in parte,
distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità
diverse, costituisce comportamento contrario ai principi dettati dal
D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely
for the attention and use of the named addressee(s) and may be
confidential or proprietary in nature or covered by the provisions of
privacy act (Legislative Decree June, 30 2003, no.196 - Italy's New
Data Protection Code).Any use not in accord with its purpose, any
disclosure, reproduction, copying, distribution, or either
dissemination, either whole or partial, is strictly forbidden except
previous formal approval of the named addressee(s). If you are not the
intended recipient, please contact immediately the sender by
telephone, fax or e-mail and delete the information in this message
that has been received in error. The sender does not give any warranty
or accept liability as the content, accuracy or completeness of sent
messages and accepts no responsibility for changes made after they
were sent or for other risks which arise as a result of e-mail
transmission, viruses, etc.

On Wed, Apr 15, 2015 at 11:07 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

I have implemented a workaround for the performance issue with computing
request size in SDE on Oracle. It's here:

https://github.com/dr-jts/geotools/commit/eef7056e899660f34447f38e5b83c1c38666ff19

This has been tested in several environments and seems to work correctly in
all of them.

(Note: the workaround is currently active for Oracle only, since that's the
only SDE environment I can test. The issue might exist on other DBs as
well).

Will try and create issues for this when I can (Issue tracker seems to be in
flux right now?). I can make a pull request as well if that's acceptable
now.

On Thu, Apr 2, 2015 at 10:47 AM, Martin Davis <mtnclimb@anonymised.com> wrote:

I've now identified the problem causing the slow WFS performance. As
Andrea suspected, it is in the ArcSDEQuery.calculateResultCount() method,
called before the WFS query queries the data. The issue is that (on our
Oracle SDE instance at least) the SeQuery.calculateTableStatistics() call is
extremely slow (likely it is doing a full table scan rather than using the
spatial index).

This shows up very obviously in our case, since we have a layer with 11M
features in it. But it's impacting performance for every WFS query (even
scans of small tables seem to be much slower than the actual data
retrieval). (This actually makes me wonder if the SDE API is pulling the
data over the wire to count it!).

Here's the actual stats from a test mockup:

Testing layer MTA_SPATIAL.MTA_MINERAL_PLACER_GRID_POLY
Fetching table stats...
Row count = 1560 ---- 955.04 s
Querying data...
Row count = 1560 ---- 2.052 s

We're going to open a ticket with ESRI about this, but I don't have much
optimism they'll do anything for us (given that the SDE API is sunsetting).

So what are the options on the GeoServer side? It might be always faster
to simply run the query twice, once to count and once for the data. In
fact, there is already code in the calculateResultCount to do this, for
Oracle versioned layers. Perhaps this should be extended for *all* Oracle
layers? (Note that I still think there may be a bug in this code to do with
the order of API calls, but that can be fixed at the same time).

Thoughts?

In the meantime I am going to work on modifying the driver to test out
this idea (and we may just use that in production if it works out anyway).

On Tue, Mar 31, 2015 at 11:12 PM, Andrea Aime
<andrea.aime@anonymised.com> wrote

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?

Hum... it may be, but a store is just a store, and
featureSource.getFeatures(Query) is the same method.
Maybe the contents of Query are different and this triggers a different
code path in the store?
One likely difference is that WFS (depending on your config and WFS
version used) needs to perform a count before getting
the actual data (part of the response headers), WMS does not.

------------------------------------------------------------------------------
BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT
Develop your own process in accordance with the BPMN 2 standard
Learn Process modeling best practices with Bonita BPM through live exercises
http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual- event?utm_
source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users