Re: [Geoserver-users] Slow Performance on MSSQL View (Believe Caused by SQL Filter Function)

[<mch...@da...>]

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: mch...@da... <mch...@da...> 
Sent: Tuesday, June 29, 2021 4:49 PM
To: 'GeoServer Users' <geo...@li...>
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 ((<ListOfPoints>))', 4326))
= 1 AND 

      "SHAPE".STIntersects(geometry::STGeomFromText('POLYGON
((<ListOfPoints>))', 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, 

1.	if I remove the "SHAPE".Filter() function in the where clause, it
returns results in less than a second.
2.	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