0

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

french
  • 41
  • 4
  • 2
    Tag your question with the database you are using. – Gordon Linoff Feb 05 '21 at 11:51
  • Why would I need to tag the database I am using? – french Feb 17 '21 at 11:16
  • . . SQL is a standardized language supported fully by **no** databases that I'm aware of. Each database has its own peculiarities. Your code would fail in most databases at the second token, `SPATIAL`.k – Gordon Linoff Feb 17 '21 at 13:33
  • @GordonLinoff -- is # used to denote a session table on anything beside MSSQL? – Hogan Feb 17 '21 at 14:35
  • Why are you using a temporary table instead of a pre-made one. Since you are creating an index it has to be on disk anyway -- I'd say make the table on the disc and set up the index before this query -- also what does the query plan say -- does that give hints? – Hogan Feb 17 '21 at 14:39
  • 1
    @Hogan . . . That the database can be imputed is separate from the fact that OP's should be correctly tagging questions. I should note that `#` is also used for Sybase. – Gordon Linoff Feb 17 '21 at 14:41
  • @GordonLinoff -- Sybase! Wow, I don't want to say how long ago it was that I last used Sybase. :/ – Hogan Feb 17 '21 at 14:45

0 Answers0