Slow WFS query against SDE?

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Slow WFS query against SDE?

Martin Davis-3
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
    query[0]:
        srsName = EPSG:3857
        typeName[0] = {<a href="http://gov.bc.ca/nrs}MTA_MINERAL_PLACER_GRID_POLY">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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

geowolf
On Tue, Mar 17, 2015 at 5:50 PM, Martin Davis <[hidden email]> 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


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.


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

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Stefano Iacovella

2015-03-17 18:03 GMT+01:00 Andrea Aime <[hidden email]>:
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

Cheers

Stefano

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


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

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

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Martin Davis-3
In reply to this post by geowolf
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.

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 <[hidden email]> wrote:
On Tue, Mar 17, 2015 at 5:50 PM, Martin Davis <[hidden email]> 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: <a href="tel:%2B39%200584%20962313" value="+390584962313" target="_blank">+39 0584 962313
fax: <a href="tel:%2B39%200584%201660272" value="+3905841660272" target="_blank">+39 0584 1660272
mob: <a href="tel:%2B39%20%C2%A0339%208844549" value="+393398844549" target="_blank">+39  339 8844549


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.


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


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Martin Davis-3
In reply to this post by Stefano Iacovella
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 <[hidden email]> wrote:

2015-03-17 18:03 GMT+01:00 Andrea Aime <[hidden email]>:
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



------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

geowolf
In reply to this post by Stefano Iacovella
On Tue, Mar 17, 2015 at 6:11 PM, Stefano Iacovella <[hidden email]> wrote:

2015-03-17 18:03 GMT+01:00 Andrea Aime <[hidden email]>:
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


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.


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

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Martin Davis-3
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 <[hidden email]> 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?



------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

geowolf
On Tue, Mar 17, 2015 at 6:25 PM, Martin Davis <[hidden email]> 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!" ;-)

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


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.


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

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Stefano Iacovella
In reply to this post by geowolf

2015-03-17 18:19 GMT+01:00 Andrea Aime <[hidden email]>:
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 :) , 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 :-D

Cheers

Stefano

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


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

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

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Stefano Iacovella
In reply to this post by Martin Davis-3

2015-03-17 18:25 GMT+01:00 Martin Davis <[hidden email]>:
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

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Martin Davis-3
In reply to this post by Martin Davis-3
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 <[hidden email]> 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
    query[0]:
        srsName = EPSG:3857
    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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

geowolf
On Tue, Mar 31, 2015 at 10:48 PM, Martin Davis <[hidden email]> 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? :-)
 

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


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.


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

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Martin Davis-3
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 <[hidden email]> wrote:
On Tue, Mar 31, 2015 at 10:48 PM, Martin Davis <[hidden email]> 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? :-)
 

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: <a href="tel:%2B39%200584%20962313" value="+390584962313" target="_blank">+39 0584 962313
fax: <a href="tel:%2B39%200584%201660272" value="+3905841660272" target="_blank">+39 0584 1660272
mob: <a href="tel:%2B39%20%C2%A0339%208844549" value="+393398844549" target="_blank">+39  339 8844549


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.


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


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Martin Davis-3
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.



On Wed, Apr 1, 2015 at 8:47 AM, Martin Davis <[hidden email]> 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 <[hidden email]> wrote:
On Tue, Mar 31, 2015 at 10:48 PM, Martin Davis <[hidden email]> 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? :-)
 

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: <a href="tel:%2B39%200584%20962313" value="+390584962313" target="_blank">+39 0584 962313
fax: <a href="tel:%2B39%200584%201660272" value="+3905841660272" target="_blank">+39 0584 1660272
mob: <a href="tel:%2B39%20%C2%A0339%208844549" value="+393398844549" target="_blank">+39  339 8844549


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.


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



------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Martin Davis-3
In reply to this post by geowolf
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 <[hidden email]> 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.



------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

geowolf
On Thu, Apr 2, 2015 at 7:47 PM, Martin Davis <[hidden email]> 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 :-)
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


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.


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

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Martin Davis-3


On Thu, Apr 2, 2015 at 11:51 PM, Andrea Aime <[hidden email]> wrote:
On Thu, Apr 2, 2015 at 7:47 PM, Martin Davis <[hidden email]> 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 :-)
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: <a href="tel:%2B39%200584%20962313" value="+390584962313" target="_blank">+39 0584 962313
fax: <a href="tel:%2B39%200584%201660272" value="+3905841660272" target="_blank">+39 0584 1660272
mob: <a href="tel:%2B39%20%C2%A0339%208844549" value="+393398844549" target="_blank">+39  339 8844549


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.


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


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Martin Davis-3
In reply to this post by Martin Davis-3
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.



On Thu, Apr 2, 2015 at 10:47 AM, Martin Davis <[hidden email]> 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 <[hidden email]> 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users

TableStatsTest.java (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Martin Davis-3
In reply to this post by Martin Davis-3
I have implemented a workaround for the performance issue with computing request size in SDE on Oracle.  It's here:


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 <[hidden email]> 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 <[hidden email]> 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow WFS query against SDE?

Simone Giannecchini
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 <[hidden email]> 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 <[hidden email]> 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
>> <[hidden email]> 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
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
12