我们已经将地理数据存储在“位置”表中,并基于此列搜索给定输入的最近位置。下面的查询用于获取25英里内的最近位置,该查询需要4秒以上才能检索4000条记录。我们甚至在位置字段上创建了空间索引。
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))
有人能建议如何在SQL Server 2014中提高此查询性能吗?