Cómo acelerar las funciones espaciales de SQL Server 2014

Question

Tengo algunas tablas normalizadas con aproximadamente 1,2 millones de registros que recopilo en una vista en una base de datos de SQL Server 2014.

Tenga en cuenta que he simplificado los nombres de las tablas y columnas.

Para ilustrar, he creado esta consulta en las tablas de origen:

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')

Esta solicitud devuelve 54 filas en 1 segundo.

El problema es que tan pronto como amplío esta consulta con funciones espaciales, la consulta se vuelve lenta.

Ejemplo 1: filtro espacial agregado:

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

Esta solicitud devuelve las mismas 54 filas, pero ahora tarda 23 segundos.

El plan de ejecución muestra un 67% en la búsqueda de índices agrupados en el PK de la tabla de geometría.

https://www.brentozar.com/pastetheplan/?id=S1l1ivsBv

Ejemplo 2: incluso agregar el SRID requiere mucho tiempo extra:

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')

Esta solicitud devuelve las mismas 54 filas, pero ahora en 12 segundos.

https://www.brentozar.com/pastetheplan/?id=BJS8swsSw

La geometría en geomTable tiene un índice espacial:

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]

La pregunta es: ¿cómo puedo acelerar las funciones espaciales?

ACTUALIZAR

Simplifiqué un poco la pregunta para hacerla más comprensible. Las tablas reales tienen muchos índices que pueden estropear el optimizador de consultas. Aquí está la definición de tabla e índice:

https://pastebin.com/8GkSkN2h

Y la consulta:

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. parece que necesita agregar date_to column para indexar en date_from column (cree un índice de varias columnas en las fechas)
2. su índice de geometría no se usa en ambos planes de ejecución (probablemente porque los límites en el filtro están fuera de los límites en el índice). Pero parece que con el índice de múltiples columnas correcto no necesita este índice espacial en absoluto

Intentemos esto:

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