How to speed up SQL Server 2014 Spatial functions

Question

I have some normalized tables with about 1,2M records that I collect in a view in a SQL Server 2014 database.

Note that I have simplified the names of the tables and columns.

To illustrate I have created this query on the source tables:

SELECT geom, dateFrom, dateTo 
FROM requestsTable 
INNER JOIN geomTable ON requestsTable.requestId = geomTable.requestId 
WHERE (dateTo >= '2020-09-10 00:00:00' AND dateFrom <= '2020-10-09 00:00:00')

This requests returns 54 rows within 1 second.

The problem is that as soon as I extent this query with spatial functions, the query gets slow.

Example 1: Added spatial filter:

SELECT geom, dateFrom, dateTo 
FROM requestsTable 
INNER JOIN geomTable ON requestsTable.requestId = geomTable.requestId 
WHERE (dateTo >= '2020-09-10 00:00:00' AND dateFrom <= '2020-10-09 00:00:00')
AND geom.Filter(geometry::STGeomFromText('POLYGON ((-791308.53524129 5350657.6892069, -791308.53524129 6762958.489206901, 2357696.2647587 6762958.489206901, 2357696.2647587 5350657.6892069, -791308.53524129 5350657.6892069))', 25832)) = 1

This requests returns the same 54 rows, but now it takes 23 seconds.

The execution plan shows 67% at the clustered index seek on the PK of the geometry table.

enter image description here https://www.brentozar.com/pastetheplan/?id=S1l1ivsBv

Example 2: Even just adding the SRID takes a lot of extra time:

SELECT geom, dateFrom, dateTo, geom.STSrid 
FROM requestsTable 
INNER JOIN geomTable ON requestsTable.requestId = geomTable.requestId 
WHERE (dateTo >= '2020-09-10 00:00:00' AND dateFrom <= '2020-10-09 00:00:00')

This requests returns the same 54 rows, but now in 12 seconds.

enter image description here https://www.brentozar.com/pastetheplan/?id=BJS8swsSw

The geometry in geomTable does have a spatial index:

CREATE SPATIAL INDEX [spatial_idx] 
ON [geomTable] ([geom]) 
USING GEOMETRY_GRID 
WITH (BOUNDING_BOX =(440000, 6040000, 900000, 6410000), GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), 
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]

The question is: how do I speed up the spatial functions?

UPDATE

I simplified the question a bit to make it more understandable. The real tables have a lot of indices that might mess up the query optimizer. Here is the table and index definition:

https://pastebin.com/8GkSkN2h

And the query:

SELECT the_geom, graveperiode_fra, graveperiode_til FROM graveforespoergsel
INNER JOIN graveforespoergsel_polygon ON graveforespoergsel_polygon.graveforespoergsel_id = graveforespoergsel.graveforespoergsel_id 
WHERE 
 ("graveperiode_til" >= '2020-01-10 00:00:00' AND "graveperiode_fra" <= '2020-11-05 00:00:00') 
 -- Adding the filter below adds a lot of time to the query
 AND "the_geom".Filter(geometry::STGeomFromText('POLYGON ((434527.96559412003 5928957.9586343, 434527.96559412003 6282033.158634299, 1214815.9655941 6282033.158634299, 1214815.9655941 5928957.9586343, 434527.96559412003 5928957.9586343))', 25832)) = 1

Answer 1

1. it seems you need to add date_to column to index on date_from column (create multicolumn index on dates)
2. your geometry index is not used in both execution plans (probably because the boundaries in filter are outside of the boundaries in index). But it seems with correct multicolumn index you don't need this spatial index at all

Let's try this:

SELECT graveforespoergsel_id, graveperiode_fra, graveperiode_til 
INTO #t
FROM graveforespoergsel
WHERE "graveperiode_til" >= '2019-01-10 00:00:00' AND "graveperiode_fra" <= '2022-10-10 00:00:00'
SELECT "the_geom", graveperiode_fra, graveperiode_til 
FROM #t
INNER JOIN graveforespoergsel_polygon ON graveforespoergsel_polygon.graveforespoergsel_id = #t.graveforespoergsel_id 
WHERE "the_geom".Filter(geometry::STGeomFromText('POLYGON ((434527 5928957, 434527 6282033, 1214815 6282033, 1214815 5928957, 434527 5928957))', 25832)) = 1
DROP TABLE #t

• carens: Let us continue this discussion in chat.
• carens: It worked to drop the spatial index. To be sure not to break anything I went to add an index hint instead.