On Tue, May 15, 2012 at 9:05 PM, cheesybiscuits <[email protected]> wrote: > I have a layer defined in GeoServer which uses a parametric view to Oracle > (11.2.0.1, with Locator capabilities). The query I use to define the view is > not particularly complex and when I run it directly against the DB it > executes in well under 1 second. > > If I request a WMS map from this layer (providing the same parameters as > when I execute the query directly) it takes around 10 seconds to render. I > am using extremely simple styles at the moment (red squares for all points). > > My first thoughts are that this is something to do with my query being used > as a subquery by GeoServer, and that this might impact on the use of the > spatial index of the underlying table. > > Has anybody had similar experiences, and maybe can offer some advice? I am > using a JNDI connection but previously saw similar behaviour under the > regular ng connection type. My next step is to start profiling the queries > but I'm hoping somebody might have good advice first.
It's not the first time I get a similar report, but never had the opportunity to actually explore it against the real installation (just second hand information). My guess is that the slowdown is due to a combination of subquery usage and prepared statement usage, with the Oracle query planner making a poor choice of data access plan when confronted with the ? value placeholders in the prepared statement. Things might be better if we had a non prepared statement path where the query planner can see the actual values instead of the ? for each request before making a data access plan. Postgis store has the option to use both prepared and non prepared statements, Oracle one does not (not a technical limitation, just lack of developer time/funding to get there). Cheers Andrea -- Ing. Andrea Aime GeoSolutions S.A.S. Tech lead Via Poggio alle Viti 1187 55054 Massarosa (LU) Italy phone: +39 0584 962313 fax: +39 0584 962313 mob: +39 339 8844549 http://www.geo-solutions.it http://geo-solutions.blogspot.com/ http://www.youtube.com/user/GeoSolutionsIT http://www.linkedin.com/in/andreaaime http://twitter.com/geowolf ------------------------------------------------------------------------------ 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-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geoserver-users
