如何优化包含地理信息列的SQL表

Question

我们已经将地理数据存储在“位置”表中，并基于此列搜索给定输入的最近位置。下面的查询用于获取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中提高此查询性能吗？

- Sivakumar Mohanraj

---

尝试重新索引表格？看看这是否有助于解决问题？ - User2012384

我对地理数据不是很熟悉，但进行预选可能会有所帮助（例如作为公共表达式），选择距离范围内的所有位置（简单数学...），仅对该区域内的点进行大量计算。 - Shnugo

避免在查询中使用UDF。当然，有时候你必须这样做，但它们会异常地增加执行时间。所以，如果该列上有空间索引，那就没问题了。您需要摆脱像“cast”和乘数之类的函数。我的建议是创建一个将存储该FLOAT的列。 此外，如果您总是使用Stdistance * 0.000621371，可以考虑创建PERSISTED列。 - Amel

Answer 1

有两件事可以帮助解决问题，但并不保证一定有效。它们都是相关的：

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子句中执行乘法是没有意义的，因为乘法（乘以正数）不会改变排序。

- Damien_The_Unbeliever