Comment accélérer les fonctions spatiales de SQL Server 2014

Question

J'ai des tables normalisées avec environ 1,2M d'enregistrements que je collecte dans une vue dans une base de données SQL Server 2014.

Notez que j'ai simplifié les noms des tables et des colonnes.

Pour illustrer, j'ai créé cette requête sur les tables source:

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

Cette demande renvoie 54 lignes en 1 seconde.

Le problème est que dès que j'étend cette requête avec des fonctions spatiales, la requête devient lente.

Exemple 1: Filtre spatial ajouté:

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

Cette requête renvoie les mêmes 54 lignes, mais cela prend maintenant 23 secondes.

Le plan d'exécution affiche 67% à la recherche d'index clusterisé sur le PK de la table de géométrie.

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

Exemple 2: même l'ajout du SRID prend beaucoup de temps supplémentaire:

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

Cette demande renvoie les mêmes 54 lignes, mais maintenant en 12 secondes.

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

La géométrie dans geomTable a un index spatial:

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 question est: comment accélérer les fonctions spatiales?

METTRE À JOUR

J'ai simplifié un peu la question pour la rendre plus compréhensible. Les vraies tables ont beaucoup d'indices qui pourraient gâcher l'optimiseur de requêtes. Voici la définition de la table et de l'index:

https://pastebin.com/8GkSkN2h

Et la requête:

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. il semble que vous deviez ajouter la colonne date_to pour indexer la colonne date_from (créer un index multicolonne sur les dates)
2. votre index de géométrie n'est pas utilisé dans les deux plans d'exécution (probablement parce que les limites du filtre sont en dehors des limites de l'index). Mais il semble qu'avec un index multicolonne correct, vous n'avez pas du tout besoin de cet index spatial

Essayons ça:

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