Christian Maul created GEOS-5086:
------------------------------------
Summary: getFeatureInfo causes full-table scans to return feature
information with ArcSDE/Oracle
Key: GEOS-5086
URL: https://jira.codehaus.org/browse/GEOS-5086
Project: GeoServer
Issue Type: Bug
Components: ArcSDE
Affects Versions: 2.1.3
Environment: Database
Oracle 10g ArcSDE jars version 9.2, 9.3, 9.3.1
Geoserver2.1.3/Tomcat6.0.35/Java 1.6.31/Apache2.2.24/Windows2008 64-bit
Reporter: Christian Maul
Assignee: Andrea Aime
Fix For: 2.1.4
According to database logs Oracle does a full table scan and does not use the
resultset or indices to find the feature for the getFeatureInfo request.
The spatial index is up-to-date and the tables are not versioned.
The SDE version is 9.2, we have a trial version of 9.3.1 both on top of Oracle
10g and both display the following behaviour:
tables with small amount of features (100 - 5000) return a getFeatureInfo in
less than 500msec.
tables with up to 50000 features need 10 seconds
tables with 200000 features return getFeatureInfo in 30 - 60 seconds.
with 3 mio features 9.2 does not return a response using the jsde-sdk92.jar,
using the jsde-sdk93.jar it returned a response after 70 minutes.
Answers when raised @ the geoserver-users group from Gabriel Roldan and Andrea
Aimee:
Andrea Aimee's answer
> Geoserver does two queries, one to determine how many features are in the
> pixel you queried and one to determine the actual features. Normally that is
> pretty fast since the area queried is so small, I guess either the SDE
> connector is missing some optimization or the underlying database is missing
> either a spatial index or has outdates statistics and it is not using the
> index. Hard to tellwithout access to the actual data.
Gabriel Roldans answer
The ArcSDE connector can't do optimized count on ArcSDE + Oracle
_only_ if the table is registered as versioned in ArcSDE. The
proprietary arcsde jar produces an error if you try to compute table
statistics on versioned tables. That said, by looking at the code [1]
it looks to me like there's actually a bug in there. I think the query
scan should only be done if that's the case, but the control statement
is not enclosing it. But I am not sure, would need to re-test it and
document what's the actual behavior with oracle. Can't do that right
now though due to other priorities. Chances are you create a jira
issue and assign it to me so I don't forget, or if you have some
coding skills you can send a patch.
link
[1]
<https://github.com/geotools/geotools/blob/2.7.x/modules/plugin/arcsde/datastore/src/main/java/org/geotools/arcsde/data/ArcSDEQuery.java#L613>
this is which Gabriel considers the offending bit:
[1]
<https://github.com/geotools/geotools/blob/2.7.x/modules/plugin/arcsde/datastore/src/main/java/org/geotools/arcsde/data/ArcSDEQuery.java#L613>
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://jira.codehaus.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Geoserver-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
