How to optimize sql table containing geography column

Question

We saved the geography data in the "Location" table and based on this column we are looking for the closest place for this input. The query below is used to get the closest locations within 25 miles, and this query takes more than 4 seconds to retrieve 4000 records. We even created a spatial index in the location field.

DECLARE @Distance INT

SET @Distance =25

DECLARE @h sys.GEOGRAPHY

SET @h =CONVERT(sys.GEOGRAPHY, 0xE6100000010C92B06F27119D4140111AC1C6F53554C0)

SELECT CenterLocationId,
       [Location].Stdistance(@h) * Cast(0.000621371 AS FLOAT(53)) AS Distance
FROM   [dbo].[CenterLocation]
WHERE  [Location].Stdistance(@h) * Cast(0.000621371 AS FLOAT(53)) <= @Distance
       AND IsDeleted = 0
ORDER  BY [Location].Stdistance(@h) * Cast(0.000621371 AS FLOAT(53)) 

Can anyone suggest how to improve the performance of this query on sql server 2014?

Answer 1

Two things that are not guaranteed to fix the problem, but can certainly help. They are both interconnected:

SELECT CenterLocationId,
       [Location].Stdistance(@h) * Cast(0.000621371 AS FLOAT(53)) AS Distance
FROM   [dbo].[CenterLocation]
WHERE  [Location].Stdistance(@h) <= @Distance / Cast(0.000621371 AS FLOAT(53))
       AND IsDeleted = 0
ORDER  BY [Location].Stdistance(@h)

, WHERE , , - .

ORDER BY, ( ) .