Thanks for reporting back with your workaround, not a lot of active
developers have access to SQL Environment.
On the off chance we get funding and environment to work on this, is the
"WITH (INDEX (“MySpatialView”))" hint the correct approach?
Is there any specific query operator that can be used to check the bounding
boxes first (for example?)
--
Jody Garnett
On Thu, 1 Jul 2021 at 09:35, <mchar...@datascoutpro.com> wrote:
> 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:* mchar...@datascoutpro.com <mchar...@datascoutpro.com>
> *Sent:* Tuesday, June 29, 2021 4:49 PM
> *To:* 'GeoServer Users' <geoserver-users@lists.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 ((<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
> _______________________________________________
> 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
>
>
> Geoserver-users@lists.sourceforge.net
> 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
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
