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

Reply via email to