Hi Sorin, All very good suggestions! Big thanks! I do like your final idea about "materialized views". I was thinking the same thing since a simple crontab job (at least in my Linux/PG world) would do it nicely.
I might also try to export the data and import them into PG/PostGIS to see if there are any obvious gains in performance. But first, I am going to tinker more with SQL Server and try your suggestions. Cheers, Cliff On Mon, Dec 17, 2018 at 9:52 AM Rusu Sorin <[email protected]> wrote: > 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
