title: How do I improve SQL spatial query performance?
body: <p>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.</p>
  <p>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'.</p>
  <p>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.</p>
  <p>Here is the specific aspect of the code that is causing the issue:</p>
  <pre><code>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) &lt; 10
                           ORDER BY G.Area.STDistance(P.Point) ASC)
  FROM #LatestGPSPoints P
  </code></pre>
  <p>Do you have any tips or ideas how I can make this query more consistent and faster?</p>
  <p>How can I make this query faster and more consistent?</p>
  <p>Thanks</p>