In an earlier post we looked in the United Kingdom and how we might use postcodes together with the geography data type in SQL Server 2012.
Now as our next step we will optimise our query to make use of the spatial index we created earlier.
Firstly we’ll parametrise our input variable and take the point of our chosen location, the Loch Ness Centre Exhibition Experience at IV63 6TU, which works our to be the latitude and longitude below.
DECLARE @CurrentLocation geography
SET @CurrentLocation = geography::STGeomFromText(‘POINT(-4.478758 57.337857)’, 4326)
Now we’ll also change our query to show us something more likely to be useful, the closest postcodes to our chosen location or our nearest neighbour.
SELECT TOP 10
dbo.PostcodeUKAll.GeoLocation,
dbo.PostcodeUKAll.Postcode,
geolocation.STDistance(@CurrentLocation)/1000 AS Kilometres
FROM [dbo].[PostcodeUKAll]
ORDER BY geolocation.STDistance(@CurrentLocation)
When we look at the actual execution plan we can see a parallel execution plan with a clustered index scan, showing us, that in the significant time we waited, SQL Server was going through the entire table row by row.
Sometimes the query optimiser needs a little help and also a couple of clues to use the nearest neighbour so we re-write the query as below.
SELECT TOP 10
dbo.PostcodeUKAll.GeoLocation,
dbo.PostcodeUKAll.Postcode,
geolocation.STDistance(@CurrentLocation)/1000 AS Kilometres
FROM [dbo].[PostcodeUKAll]
WHERE geolocation.STDistance(@CurrentLocation) IS NOT NULL
ORDER BY geolocation.STDistance(@CurrentLocation) ASC
Running this query the query optimiser doesn’t choose the spatial index either so we’ll have to help it with an index hint. Usually index hints are a bad idea, the query optimiser is very clever and will usually choose the right plan and more importantly choose the right plan under different circumstances.
In this instance as we’re clear what we want to do and know the other choices available we can use the hint below but whenever you remove the optimisers choices remember to revisit this, like any query, especially one with a hint, when they run slower than expected or have a significant change in performance.
WITH (INDEX ([ix_GeoLocation]))
When we now run the query and look at the actual execution plan, we can see the tree is much bigger and at the start of the query on the right we can see the spatial index being used as the primary filter.
