Hi Cliff, I would first look into desktop performance and average response times from the database. If the DB views are un-indexed or the index is woefully out of date, I am expecting to see similarly slow performance on the desktop side. (Suggest using QGIS which is lightning fast even with very large datasets). Not very sure about DB performance if you are using SQL geometry, and not really sure what to recommend to build spatial indexes in the DB (try QGIS, see how it works).
Once the appropriate DB-tuning is done in the DB/Desktop environment I would then look to the Geoserver WMS/WFS services and look at individual response times for a "full" WMS GetMap request. If low response times and if you envisage little traffice on the application side, you could just use the WMS. If the KVP/Get reponse is slow then it might be a Geoserver issue that is worth investigating the production configurations you have. This might be a good place to start: https://www.slideshare.net/geosolutions/geoserver-in-production-we-do-it-here-is-how-foss4g-2016 While SQL Server should work fine, most "big" db implementations I've seen usually relly on PostgreSQL + Postgis and QGIS Desktop for data management. Leaflet is light-weight, and should let the map run a lot faster, just keep the number of plugins in check. Geojson is a good way of reducing the number of requests being sent to GS, and should help with overall performance and there should be native support in GS for GeoJSON output. Would recommend you look into materialized views if your data is not constantly being updated, but maybe updated once a day, instead of relying on simple DB views. Sorin R. Pe luni, 17 decembrie 2018, 16:37:48 EET, Cliff Patterson <[email protected]> a scris: 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
_______________________________________________ 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
