Nearest neighbor search query performance

Question

I am using Microsoft SQL Server 2012, and I have a simple query that selects the top 50 users ordered by the nearest to a specific point, example:

DECLARE @Location geography = geography::Point(30.9384016, 29.9582148, 4326)

SELECT TOP 50 * 
FROM Users  
ORDER BY LastLocation.STDistance(@Location)

Looking at the execution plan, I can see that it didn't use my spatial index

The table contains around 40,000 records and the query takes over 1 minute in execution, my spatial index is created like this:

create spatial index IX_Location ON Users (LastLocation) using GEOGRAPHY_AUTO_GRID

I have tried using hinting in my query and specifying the index like the following:

DECLARE @Location geography = geography::Point(30.9384016, 29.9582148, 4326)

SELECT TOP 50 * 
FROM Users WITH (INDEX(IX_Location)) 
WHERE LastLocation.STDistance(@Location) IS NOT NULL 
ORDER BY LastLocation.STDistance(@Location)

But actually it take much more time in execution, could someone tell me how can I improve the performance of this query ?

Thanks

Answer 1

Add a WHERE clause to restrict the distance.