Spatial index is slow when trying to find all the points within a range of a geocode.

You might need to use an Index hint (i.e. WITH(INDEX( [INDEX_NAME] ))

 

Select top 100 
ci.Geocode.STDistance(@g), ci.CIOI  
from CustomerInformation WITH(INDEX(IX_CI_Geocode))
ci where ci.Geocode.Filter(@region) = 1 
order by ci.Geocode.STDistance(@g) asc 

 

http://stackoverflow.com/questions/6461496/spatial-index-is-slow-when-trying-to-find-all-the-points-within-a-range-of-a-geo

 

my example

DECLARE
@search_longitude FLOAT='121.78364585795',
@search_latitude FLOAT='31.2351032520358',
@radius Float=100
DECLARE @search_position geography = geography::Point(@search_latitude, @search_longitude, 4326)

--DECLARE @search_position geography = geography::Point(@search_latitude, @search_longitude, 4326).STBuffer(@radius)
SELECT TOP 50 *
FROM Locations l  WITH(INDEX(IX_Stores_Location))
WHERE l.Location is not null and l.Location.STDistance(@search_position) <= @radius

--l.Location.Filter(@search_position)=1