I am redesigning a webmap application that loads vector grid data from a series of SQL Server tables using a SQL View in Geoserver. Variables are passed via the WMS call to display dynamic content (e.g. the count of species X for project Y from 2005 to 2010 will display counts on a UTM polygon grid). There are millions of records in the database, and the data are queried via a series of joins (flat tables are joined with geometry) and grouped by UTM grid cell. There are thousands of grid cells, 100s of species per grid, 26 possible project categories per species, and a date range from 1960s to 2018.
Using SQL Views in Geoserver, the calls are very slow using a Google Map JS, if they work at all. I am tasked with improving the overall performance of the application from the back end up. I wanted to ask this user group if anyone has experience dealing with similarly large datasets with Geoserver, especially data from SQL Server. I would tackle this very differently if the data were in PG/PostGIS, but in SQL Server everything seems rather slow (and painful). Here's what I'm thinking: 1) Use Leaflet over Google Maps API -- more a preference, but I also suspect a performance boost for WMS layers. 2) Maybe consider fetching the data as a single GeoJSON so that the database is not pinged on every pan/zoom. 3) Develop a "neater" table that can be queried using CQL to eliminate the rather complex joining and grouping query on every pan/zoom. Can anyone suggest a good strategy (or critique my suggestions above) to serve these data and improve overall responsiveness? Huge thanks, Cliff
_______________________________________________ Geoserver-users mailing list Please make sure you read the following two resources before posting to this list: - Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/ - The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer [email protected] https://lists.sourceforge.net/lists/listinfo/geoserver-users
