4

I'm trying to test the performance of spatial indexes on Latitude/Longitude values in a table of 1.7 million postcodes. I've created a geography column and added an index to it, however the query using the spatial index is dramatically (at least 100x) slower than the one using the 'normal' index on the Lat/Long columns in the same table, yet the query plan shows the index is being used.

Here is my query:

DECLARE @point GEOGRAPHY
SET @point = geography::STGeomFromText('POINT(-1.31548 51.06390)', 4326)

SELECT postcode , location.STDistance(@point) AS dist
FROM dbo.Postcodes WITH(INDEX(IX_Postcodes_SpatialIndex))
WHERE location.STIntersects(@point.STBuffer(200)) = 1   
ORDER BY location.STDistance(@point)

How can I go about debugging what's happening here?

EDIT: I accepted most of the defaults when creating the index:

CREATE SPATIAL INDEX [IX_Postcodes_SpatialIndex] ON [dbo].[Postcodes] (Location) USING GEOGRAPHY_AUTO_GRID ON [PRIMARY]

Query plan: (you may need to zoom in ;)

enter image description here

NickG
  • 9,315
  • 16
  • 75
  • 115
  • What is your other query? – iamdave Sep 07 '16 at 15:36
  • And can you please post the script to create your indexes? – iamdave Sep 07 '16 at 15:38
  • 1
    Do you have query plans for both? At first blush, I'd guess that pulling back the entire table is the culprit (i.e. key lookups), but I'd like to see actual plans before making that assessment. – Ben Thul Sep 07 '16 at 16:52
  • Which SQL Server version are you using? SQL Server 2014 is able to use spatial indexes in more scenarios that 2012. On the other hand, there is *NO* guarantee that a spatial index will be faster than a B-Tree index. The rule of thumb is to limit the results as much as possible using other criteria first. Please post the execution plans. – Panagiotis Kanavos Sep 07 '16 at 16:55
  • And finally, a distance calculation is always slow, because you have to calculate the distance to your point for every row. The optimizer will generate a square envelope and check against it *first* to limit results, then calculate the distance for all matches. If you used a square shape and checked for overlaps, the optimizer would simply perform ranges queries, ie is y between top and bottom? Is x between left and right? – Panagiotis Kanavos Sep 07 '16 at 17:01
  • @PanagiotisKanavos How can I check a square? That's actually what I'm really trying to do... There seem to be very few examples online for SQL geography queries. – NickG Sep 08 '16 at 09:54
  • I've added the index creation SQL and the query plan. It seems that sorting takes up 90% of the time but I can't see how I can avoid that as the results of TOP will be invalid otherwise. – NickG Sep 08 '16 at 10:00
  • To check against a square use STIntersects and check [this question](http://gis.stackexchange.com/questions/145019/optimizing-geometry-stintersect-query) for optimizations. Your particular query though looks for the nearest neighbours which requires a special syntax as shown in [the docs](https://msdn.microsoft.com/en-us/library/bb895265.aspx#Queries-that-use-Spatial-Indexes). Perhaps you can combine a square with the nearest neighbour syntax to get the nearest neighbours inside a square only – Panagiotis Kanavos Sep 08 '16 at 13:09
  • OK I've updated my query (and execution plan) to use STIntersect. Seems most time is spent joining back to the main table. It's still nowhere near the performance of using the normal index on the float columns. The actual spatial index seek itself is only 2.5% of the query time. – NickG Sep 09 '16 at 09:19
  • It seems that part of the problem is that spatial indexes can't have INCLUDES on them and don't even seem to store the actual value from the geography column - just an approximation of it. – NickG Sep 09 '16 at 09:22

0 Answers0