After a lot of head scratching and trial and error, I believe I found a solution (or at least workaround) for this.
The most obvious solution seemed to be to use a query hint to force the spatial index in the SQL view (ie., WITH (INDEX (“MySpatialView”))). This seemed to work great with the SQL queries coming from the GetMap and GetFeature requests. However, if there were no spatial queries in the where statement, then SQL would throw a “Query processor could not produce a query plan because of the hints defined in this query.” But since all my queries for my end use would involved a spatial element, I hoped it would work.
It did not work because Geoserver kicks off a couple queries each time the layer’s configs are reread to select the top 1 record in order to get the dimension and srid. I’m really not sure why it does this since this data is already stored in the configs for the layer… but regardless, it failed in SQL because there was no spatial query in the where statement. I also suspect I would have not been able to create a layer from that view for the same reason (in this case I was modifying a view that already existed which was already tied to an existing Geoserver layer).
IN THE END, I created the sql view directly through Geoserver instead of in SQL and included the table hint there. It looks like since both the dimension and SRID are manually entered when creating the view in Geoserver, those two “select top 1” queries are never fired off when the layer’s configs are reread. I suspect that if I tried a GetFeature request without a spatial filter, then it would error out… but that is not a need I have for this layer.
Maybe this will help someone else. I apologize if it was unclear.
Thanks,
Matt
From: mcharton@anonymised.com mcharton@anonymised.com
Sent: Tuesday, June 29, 2021 4:49 PM
To: ‘GeoServer Users’ geoserver-users@anonymised.comsts.sourceforge.net
Subject: Slow Performance on MSSQL View (Believe Caused by SQL Filter Function)
We have a MSSQL 2012 View added as a layer in Geoserver 2.17.2 that performs well with GetMap requests but times out with GetFeature requests.
We have tracked it down to the following Query sent from Geoserver to MSSQL:
SELECT top 1000 * FROM “schema”.“MyTable_view”
WHERE (
“SHAPE”.Filter(geometry::STGeomFromText(‘POLYGON (())’, 4326)) = 1 AND
“SHAPE”.STIntersects(geometry::STGeomFromText(‘POLYGON (())’, 4326)) = 1 AND
“Field1” = 1234 AND
“Field1” IS NOT NULL
)
The view is just an inner join between two tables, both of which have a variety of indexes including a proper spatial index on the “shape” field.
If I run this query manually against SQL, it basically runs indefinitely. HOWEVER,
- if I remove the “SHAPE”.Filter() function in the where clause, it returns results in less than a second.
- If I remove the subsequent “Field1” query criteria from the where clause, it returns results in less than a second. I assume this points to the culprit here being that it is using the Field1 index instead of the spatial index on the tables.
Is there a good solution to this problem? We are somewhat new to Geoserver, so we may very well be doing something wrong. I have tried to find a way to force the spatial query in the view with no luck. Any help would be much appreciated!
Thanks,
Matt