Como acelerar as funções espaciais do SQL Server 2014

Question

Eu tenho algumas tabelas normalizadas com cerca de 1,2 milhões de registros que coleto em uma exibição em um banco de dados SQL Server 2014.

Observe que simplifiquei os nomes das tabelas e colunas.

Para ilustrar, criei esta consulta nas tabelas de origem:

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

Essa solicitação retorna 54 linhas em 1 segundo.

O problema é que assim que eu estendo essa consulta com funções espaciais, a consulta fica lenta.

Exemplo 1: filtro espacial adicionado:

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

Essa solicitação retorna as mesmas 54 linhas, mas agora leva 23 segundos.

O plano de execução mostra 67% na busca de índice clusterizado no PK da tabela de geometria.

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

Exemplo 2: Mesmo apenas adicionar o SRID leva muito tempo 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')

Essa solicitação retorna as mesmas 54 linhas, mas agora em 12 segundos.

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

A geometria em geomTable tem um í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]

A questão é: como posso acelerar as funções espaciais?

ATUALIZAR

Simplifiquei um pouco a pergunta para torná-la mais compreensível. As tabelas reais têm muitos índices que podem confundir o otimizador de consulta. Aqui está a definição de tabela e índice:

https://pastebin.com/8GkSkN2h

E a 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 você precisa adicionar a coluna date_to para indexar na coluna date_from (criar índice de várias colunas nas datas)
2. seu índice de geometria não é usado em ambos os planos de execução (provavelmente porque os limites do filtro estão fora dos limites do índice). Mas parece que com o índice de várias colunas correto você não precisa deste índice espacial de forma alguma

Vamos tentar isso:

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