I have a setup where I have a SQL Server database with two tables: one table storing the geometry of the shapes I want to display, another table with some attribute value that is used to color and label the shapes. Then I use this data to generate WMS tiles that are displayed in a web front-end to the end user. In my data, the geometry does not change, but the attributes change very often.
In SQL Server, I configured a view joining the two tables, then used this view as a data source for Geoserver. Then I configured a SLD using these attributes to get the style I want.
It works. However, I have some performance issues, which seems to be linked to the necessity of reloading the geometry from the database for each query. My first idea was tile caching, but I can't use it because there are so many possible values for the attributes, and they change very often.
Ideally, what I would like to do is to have Geoserver cache the geometry data and for each new WMS query only look up the attributes to use and generate a style based on that, instead of retrieving both geometries and attributes at the same time. Is it possible to achieve this?
I am currently using Geoserver, but if it is possible to do it with a different WMS server, I would be interested as well.