Improving Performance using SQL Server

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Improving Performance using SQL Server

cliff
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


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Improving Performance using SQL Server

GeoServer - User mailing list
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 <[hidden email]> 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


[hidden email]
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


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Improving Performance using SQL Server

cliff
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 <[hidden email]> 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 <[hidden email]> 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


[hidden email]
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


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Improving Performance using SQL Server

Mark Prins
On 17-12-18 16:06, Cliff Patterson wrote:
> 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.

No materialized views in ms sql server, you can however create indexed
views (it depends on the view defnitions, I agree it can be painful)

> 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.

FWIW My limited experience shows that on the same hardware/os sqlserver
gets about half of the perf that postgis does.
Compare eg. the integration test results of
https://travis-ci.org/B3Partners/brmo (PostgreSQL 10 vs. ms SQL Server
2017) note these testsuites are mostly doing inserts

-M


_______________________________________________
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


[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users