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.

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.

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