Executing parameterized query with spatial functions

Question

We want to use SET PARAMETERIZATION FORCED on a DB due to unstable Plan Cache caused by third party applications like https://geoserver.org/ and https://mapserver.org/

We have tables with spatial data and when querying them with spatial functions then we experience problems with parameterization.

As an example, this query runs fine with an acceptable actual execution plan.

SELECT DISTINCT egm.matrikel_id

FROM #temp egm INNER JOIN e_gis_overfladevand v

ON (egm.geom.STBuffer(250).STIntersects(v.geom)=1)

https://www.brentozar.com/pastetheplan/?id=B1vEPH2CC
It runs in about 30 seconds.

When the query is parameterized, it runs bad with an unacceptable estimated execution plan, even though DBCC FREEPROCCACHE is used before executing the query. On our server it will probably take a few days for the query to finish.

DECLARE @buffer int = 250;

DECLARE @1 int = 1;

SELECT DISTINCT egm.matrikel_id

FROM #temp egm INNER JOIN e_gis_overfladevand v

ON egm.geom.STBuffer(@buffer).STIntersects(v.geom)=@1
https://www.brentozar.com/pastetheplan/?id=rJYyFH3CC

However, the parameterized query runs fine with an acceptable actual execution plan, when using Option Recompile or dynamic SQL.

DECLARE @buffer int = 250;

DECLARE @1 int = 1;

SELECT DISTINCT egm.matrikel_id

FROM #temp egm INNER JOIN e_gis_overfladevand v

ON egm.geom.STBuffer(@buffer).STIntersects(v.geom)=@1

OPTION (RECOMPILE)
https://www.brentozar.com/pastetheplan/?id=SJOvcBn00

DECLARE @execquery nvarchar(1000);

DECLARE @buffer int = 250;

DECLARE @1 int = 1;

SET @execquery =

N'select distinct egm.matrikel_id

from #temp egm

inner join ' + 'e_gis_overfladevand' + ' v

ON (egm.geom.STBuffer('+CAST(@buffer AS char(3))+').STIntersects(v.' + 'geom' + ')='+CAST(@1 AS char(1))+')'

EXECUTE sp_executesql @execquery
https://www.brentozar.com/pastetheplan/?id=SJFdsShA0 Both queries take about 30 seconds to finish.

Can anyone explain why it’s not possible to run the parameterized spatial query, without Option Recompile or dynamic SQL?

Does anyone have experiences with PARAMETERIZATION FORCED and spatial data?

Answer

What happens if you try:

EXEC sp_executesql N'SELECT DISTINCT egm.matrikel_id
                     FROM  #temp egm 
                     INNER JOIN e_gis_overfladevand v 
                         ON egm.geom.STBuffer(@buffer).STIntersects(v.geom)=@1',
                N'@buffer int, @1 int', 250, 1

Your slow query is not really a parameterised query; there are no parameters, only local variables. And when compiling the batch, SQL Server does not know the values of these variables, but makes standard assumptions. What those assumptions are for spatial queries, I don't know, but they don't seem to be very good, let's put it at that.To resolve this, you need to make the values known, and OPTION (RECOMPILE) is one solution. My suggestion above is another. The difference is that the above results in a cached plan that can be re-executed with other values.

It seems that you have a misconception about forced parameterisation. With forced parameterisation, the values of constants in a query are replaced with parameters. Thus, a query like

SELECT DISTINCT egm.matrikel_id
FROM #temp egm INNER JOIN e_gis_overfladevand v
ON (egm.geom.STBuffer(250).STIntersects(v.geom)=1)

would become

SELECT DISTINCT egm.matrikel_id
FROM  #temp egm INNER JOIN e_gis_overfladevand vON (egm.geom.STBuffer(@1).STIntersects(v.geom)=@2)

However, my reading of the execution plan suggest that this does not happen, so it may be that forced parameterisation does not apply to spatial queries. But that is not relevant to your problem because it is due to the unknown values of the variables.

Share via

Executing parameterized query with spatial functions

1 answer

Your answer