I am running a spatial query inside a SQL procedure, and I am experiencing some inconsistent performance times. I've have tried to use various spatial indexes, however I cannot seem to get the query to run more consistently and at a faster pace.
The procedure pulls live GPS data (around 200 rows) and attempts to match each GPS coordinate to pre-defined polygons that are held in a sperate table to give each row a 'Current Location'.
Currently, the procedure takes on average around 13 seconds, however the median time is 7 seconds and the standard deviation is 14. Often the query can take beyond a minute to run, which is not ideal as this data is intended to influence live operations.
Here is the specific aspect of the code that is causing the issue:
CREATE SPATIAL INDEX spatial_lookup
ON #CurrentLoc(Area)
USING GEOGRAPHY_GRID
WITH (GRIDS = (LEVEL_1 = HIGH, LEVEL_2 = HIGH, LEVEL_3 = HIGH, LEVEL_4 = HIGH)
,CELLS_PER_OBJECT = 4
,PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,SORT_IN_TEMPDB = OFF
,DROP_EXISTING = OFF
,ONLINE = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
CREATE SPATIAL INDEX point_lookup
ON #LatestGPSPoints(Point)
USING GEOGRAPHY_GRID
WITH (GRIDS = (LEVEL_1 = HIGH, LEVEL_2 = HIGH, LEVEL_3 = HIGH, LEVEL_4 = HIGH)
,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)
ON [PRIMARY]
UPDATE P
SET P.CurrentLocation = (SELECT TOP 1 G.Blocks
FROM #CurrentLoc AS G WITH (INDEX(spatial_lookup))
WHERE G.Area.STDistance(P.Point) < 10
ORDER BY G.Area.STDistance(P.Point) ASC)
FROM #LatestGPSPoints P
Do you have any tips or ideas how I can make this query more consistent and faster?
How can I make this query faster and more consistent?
Thanks
question from:https://stackoverflow.com/questions/66062042/how-do-i-improve-sql-spatial-query-performance
