Gavin, are you using app-schema joining? http://docs.geoserver.org/latest/en/user/data/app-schema/joining.html
For backwards compatibility, app-schema joining support is disabled by default. Enabling joining for performance is strongly recommended, and is particularly important for feature types with multivalued properties. With joining enabled, more efficient SQL queries are generated and performance typically improves for large data sets, sometimes by several orders of magnitude. Kind regards, Ben. On 06/02/13 09:09, Gavin wrote: > We (Dept. Primary Industry, Vic, Aus > Earth Resources Division) > have deployed GeoServer 2.2 with app-schema module on Tomcat 6 serving > GeoSciML 2 & 3 into UAT and Production Servers. > Our main obstacle to taking these services beyond just experimenting & > trialling is performance issues. > Currently no server instance has done better than a 4 second response > time for a single feature GetFeature request where the ID of the Feature > was specified in the request. > And for more general GetFeature requests (such as using maxFeatures or > bounding boxes) the performance is considerably worse. > I have been analyzing the GeoServer instances in a profiler and run > several debug sessions while profiling. > One particular concern arose when I followed the execution in the > FilteringFeatureReader class. > To try and put this into context, this class was called/used when doing > a request for a GeologicalUnit feature which had a GeologicalEvent > linked to it using a foreign key relationship. It's a many to one > relationship - many GeologicalEvents can be associated with a > GeologicalUnit feature and hence the foreign key is in the > GeologicalEvent table. > (I can put the relevant app-schema configuration that has the > FEATURE_LINK relationship in later.) > > NOW THE PERFORMANCE ISSUE IS, that GeoTools does an SQL fetch without a > where clause on the GeologicalEvent table, thus fetching the entire table, > and then iterates through the entire result set (using the > FilteringFeatureReader class) comparing whether the row is the related > GeologicalEvent for the requested GeologicalUnit. > The GeologicalEvent table has tens of thousands of records in it and > this part of the request processing is a major bottleneck contributing > (I think) more than anything else to the blow-out in the response times. > > I was expecting that if all the foreign key relationships were specified > in the app-schema configuration, then I would see only SQL statements in > the profiler that have WHERE clauses for matching related rows in our > Oracle database. > There is no attribute filtering/searching in the request but even if > there were, I would have thought the associated features (we're dealing > here with complex features and feature chaining) could still be > extracted using appropriate where clauses. > > No doubt there is a reason for the FilteringFeatureReader class doing > what it's doing. > Can you help with finding a way to eliminate it being called in the > request/response processing code. > Is there any general advice on this. > Could having a more optimal app-schema configuration help. > I can post the app-schema configuration files or relevant portions of > them if you think it could illuminate a problem. -- Ben Caradoc-Davies <[email protected]> Software Engineer CSIRO Earth Science and Resource Engineering Australian Resources Research Centre ------------------------------------------------------------------------------ Free Next-Gen Firewall Hardware Offer Buy your Sophos next-gen firewall before the end March 2013 and get the hardware for free! Learn more. http://p.sf.net/sfu/sophos-d2d-feb _______________________________________________ Geoserver-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geoserver-users
