Kathi, I strongly recommend keeping all tables in the same database so that you can use joining. The performance improvement becomes very large once you have more than a small number features: you might need 1 SQL query for 10000 features with joining, but 10001 SQL queries without joining, because a new SQL query is required for each chained relationship. With more levels of nesting, the problem becomes even worse. I have seen queries that take hours without joining reduced to a few minutes with joining.
If the number of tables becomes unmanageable, you might be able to use different postgres schemas in the same way you were using different databases. These should work with joining (but I have not tested them myself). Other important performance considerations are to avoid CQL expressions in any property used for chaining or filter queries, and to index all these columns in the database. For example, I like to create gml_id columns in the database and index them to improve performance; postgres can do this efficiently but app-schema cannot convert a CQL expression with strconcat back to an SQL query, even with joining. Kind regards, Ben. On 26/08/16 02:50, Kathi Schleidt wrote: > Ben, > > MANY MANY THANKS!!!!! > > I've been trawling through the documentation, but hadn't found that bit > yet (still a lot to learn!) > > Now to get a feeling for my options based on performance, i.e. is it > better to pack the data from all themes (working on INSPIRE stuff here) > into 1 DB to enable joining across themes or saner to disaggregate and > forgo the joining benefits > > thanks for the prompt response! > > :) > > Kathi > > > On 24.08.2016 23:47, Ben Caradoc-Davies wrote: >> Kathi, >> >> thank you for your detailed report. I had a look through your mapping >> files and they look fine. Your testing proves that they work in a >> single database. >> >> For performance, app-schema converts queries against feature-chained >> types from database sources into efficient SQL, referred to as "joining": >> http://docs.geoserver.org/latest/en/user/data/app-schema/joining.html >> >> Joining cannot be used for feature chaining between databases because >> an SQL query can only be made against one database at a time. The >> evidence implicating joining is the mention in the stack trace of >> JoiningNestedAttributeMapping; this class is only used in app-schema >> joining. >> >> Joining is turned on by default. It is automatically turned off when >> using non-database sources, but I do not think that app-schema checks >> whether chaining is applied between database sources. As noted in the >> manual: >> >> "All feature mappings that are chained to each other must map to the >> same physical database." >> >> and >> >> "Failing to comply with any of these three restrictions when turning >> on Joining will result in exceptions thrown at run-time." >> >> To turn off app-schema chaining, set "app-schema.joining=false" in >> your app-schema.properties or as a JVM argument: >> http://docs.geoserver.org/latest/en/user/data/app-schema/joining.html#configuration >> >> >> Kind regards, >> Ben. >> >> On 24/08/16 22:06, Kathi Schleidt wrote: >>> Hi, >>> >>> In the process of trying to link features from multiple INSPIRE Themes >>> via Feature Chaining, I’ve run into difficulties using multiple (well 2) >>> databases (was trying to do one database per feature type/namespace). To >>> clarify that it’s not a problem with my configuration I put together a >>> simple example consisting of a main feature with two nested features, >>> one of which should stem from a different DB than the main parts. >>> (schema available at http://www.schleidt.org/schemas/test.xsd) >>> >>> Within the main DB (test2, DataStore idDataStoreTS), there are 2 tables: >>> >>> * testmain: the main part >>> >>> * nest: the part that should actually be in the other database >>> >>> Within the 2^nd DB (nest2, DataStore idDataStoreNS), there is only the >>> nest table (identical to the one in test, just different content to see >>> where the data is coming from) >>> >>> The DataStore configuration for both databases is identical except for >>> the database name (test2 vs nest2) >>> >>> As long as I’m only using the main database (test2), everything works >>> out beautifully. The moment I try to use the 2^nd database connection >>> (nest2), while there are no errors on startup, there are also no >>> features, just the following error (for ts:nestExt it should be going >>> for the table nest in the db nest2, but it’s complaining about the table >>> testmain from the db test2): >>> >>> java.lang.RuntimeException: Error applying mapping with targetAttribute >>> ts:nestExt >>> >>> Error applying mapping with targetAttribute ts:nestExt >>> >>> java.io.IOExceptionSchema 'testmain' does not exist. >>> >>> An interesting aside – when I set up this test, I first forgot to set up >>> my db as postgis (I’m an old geek, and spatial is still new to me). This >>> worked fine as long as I was only using one DB (there are no geometries >>> in my simple example), when I added the 2^nd one I got an interesting >>> PostGis error (might help in debugging): >>> >>> Function postgis_lib_version() doesn’t exist >>> >>> >>> Configuration and logfiles are available at: >>> http://www.schleidt.org/kathi/DBProblem.zip >>> >>> Main problem in the subdirectory PostGisDB, the 2 variants with just >>> PostGres are PostGres1 & 2 (for 1 or 2 dbs) >>> >>> Can somebody tell me if I'm going crazy or if there's a problem here? >>> >>> :) >>> >>> Kathi >>> >>> >>> >>> ------------------------------------------------------------------------------ >>> >>> >>> >>> >>> _______________________________________________ >>> Geoserver-users mailing list >>> Geoserver-users@lists.sourceforge.net >>> https://lists.sourceforge.net/lists/listinfo/geoserver-users >>> >> > -- Ben Caradoc-Davies <b...@transient.nz> Director Transient Software Limited <http://transient.nz/> New Zealand ------------------------------------------------------------------------------ _______________________________________________ Geoserver-users mailing list Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
