Hi Ben, Thanks for the feedback. It turns out that a STRCAT() in the wrong place caused GeoServer to go slow. Now we have much quicker sub second times. Regards, Andrew
-----Original Message----- From: Ben Caradoc-Davies [mailto:[email protected]] Sent: Thursday, 22 March 2012 1:13 PM To: Andrew Betlehem Cc: [email protected]; Maurits van der Vlugt Subject: Re: [Geoserver-users] App schema - slow with large tables, doesn't use SQL WHERE clauses? Do you have joining enabled? Are you using trunk? http://docs.geoserver.org/latest/en/user/data/app-schema/joining.html The joining implementation can give massive (orders of magnitude) performance improvement, depending on your database schema and mapping style (normalised tables good, denormalised bad). It imposes some restrictions, such as source features having to come from the same JDBC database. (Without joining, app-schema can feature chain across different data sources, such as a database table and a shapefile, or different database servers. This is not supported with joining enabled.) For backwards compatibility, joining is not the default. Kind regards, Ben. On 22/03/12 07:24, Andrew Betlehem wrote: > An observation that we have found when using app schema, is that it can be > quite slow (5 second responses versus 200 millisecond for simple features). > We've looked at the database SQL queries that app schema produces, and it > appears that there are two queries being produced: > > 1. The first query extracts all the data from a given table (e.g. select * > from table) WITHOUT any SQL where clause. > 2. For each record found, another query is run select data from the database. > > It's the first query that is slow. It appears that filtering is done in > memory through Java rather than using SQL where clauses. This approach is > fine for small datasets, but when you get large datasets (we have 300,000 > records) you have a relatively slow WFS. > > Is there any way to trigger use of SQL where clauses in app schema? Maybe > we've just set up our workspace/mappings wrong. > > Cheers, > > Andrew Betlehem > > > ---------------------------------------------------------------------- > -------- > This SF email is sponsosred by: > Try Windows Azure free for 90 days Click Here > http://p.sf.net/sfu/sfd2d-msazure > _______________________________________________ > Geoserver-users mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/geoserver-users > -- Ben Caradoc-Davies <[email protected]> Software Engineer CSIRO Earth Science and Resource Engineering Australian Resources Research Centre ------------------------------------------------------------------------------ This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure _______________________________________________ Geoserver-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geoserver-users
