Nearest Neighbor Search Results

Question

I am using Microsoft SQL Server 2012, and I have a simple query that selects the 50 best users ordered closest to a specific point, for 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 see that it did not use my spatial index

The table contains about 40,000 records, and the query takes 1 minute to execute, my spatial index is created as follows:

 create spatial index IX_Location ON Users (LastLocation) using GEOGRAPHY_AUTO_GRID 

I tried using hints in my query and specifying the index as follows:

 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 takes much longer to execute, can someone tell me how I can improve the performance of this request?

thanks

Answer 1

Add a WHERE to limit the distance.