优化SQL计算经纬度位置的距离
我正在创建一个托管在MS 2012服务器上的数据库。该数据库的主要功能是返回与原点一定距离内的结果。位置存储为纬度/经度。
通过阅读这里的堆栈溢出，我发现了一个非常好的方式来查询数据库的确切内容，我正在寻找，它的工作就像一个魅力！不过，我正在考虑一种可能的方法来优化这一点。
原始SQL查询
DECLARE @orig_lat DECIMAL(12, 9)
DECLARE @orig_lng DECIMAL(12, 9)
SET @orig_lat=56.xxxxxx
SET @orig_lng=14.xxxxxx
DECLARE @orig geography = geography::Point(@orig_lat, @orig_lng, 4326);
SELECT *
FROM foobar
WHERE @orig.STDistance(geography::Point(foobar.latitude, foobar.longitude, 4326)) < 2000我的猜测是，这个查询只对foobar表进行线性搜索，只返回匹配的列。但是，由于这个表包含了世界各地的位置，所以我想知道是否可以通过减少数据库运行距离计算所需的行数来帮助数据库。我的猜测是，这个计算对服务器来说是很重的。
我知道请求的起源，我也知道点之间的最大距离永远不会超过100公里。
假说
因为我知道，我不需要搜索整个世界，只有100公里的起点，我可以改进WHERE语句，如下所示。通过为纬度和经度创建一个最小和最大的界限，这是通过在每个方向上移动某个位置来完成的。
我解释：
- 原产地纬度56.xxxxxx
- 最低纬度55.xxxxxx
- 最大纬度57.xxxxxx
- 起源经度14.xxxxxx
- 最小经度13.xxxxxx
- 最大经度15.xxxxxx
通过这样做，我在原点周围创建了一个区域，约达126公里。通过将其添加到WHERE语句中，我首先确保所请求的位置在正确的范围内。在此之后，我进行距离计算，以获得准确的距离。距离计算现在只针对最小和最大界限内的行，而不是整个世界。
优化方案
DECLARE @orig_lat DECIMAL(12, 9)
DECLARE @orig_lng DECIMAL(12, 9)
DECLARE @orig_latMin DECIMAL(12, 9)
DECLARE @orig_latMax DECIMAL(12, 9)
DECLARE @orig_lngMin DECIMAL(12, 9)
DECLARE @orig_lngMax DECIMAL(12, 9)
SET @orig_lat=56.xxxxxx
SET @orig_lng=14.xxxxxx
SET @orig_latMin=55.xxxxxx
SET @orig_latMax=57.xxxxxx
SET @orig_lngMin=13.xxxxxx
SET @orig_lngMax=15.xxxxxx
DECLARE @orig geography = geography::Point(@orig_lat, @orig_lng, 4326);
SELECT *
FROM foobar
WHERE ([latitude] > @orig_latMin
AND [latitude] < @orig_latMax
AND [longitude] > @orig_lngMin
AND [longitude] < @orig_lngMax)
AND @orig.STDistance(geography::Point(foobar.latitude, foobar.longitude, 4326)) < 2000我不知道数据库实现的细节，但这是改善了查询，还是使查询变得更糟？我的猜测是，这取决于WHERE语句的实际工作方式，以及它按什么顺序处理事物。我希望在计算距离之前先进行边界检查，以减少计算距离的时间。
编辑
刚刚实现了建议的索引提案，结果如下。
没有索引：
- 优化语句的成本为0,025352
- 如果没有优化语句，则成本为0,025323
有索引：
- 优化语句的成本为0,0104057
- 如果没有优化语句，则成本为0,0253234
回答 2
Stack Overflow用户
发布于 2016-08-24 13:34:22
一个好的经验法则是，数据库查询的执行时间取决于必须读取的磁盘页的数量。CPU时间通常可以忽略。
根据这条规则，如果对磁盘页的数量有影响，那么所建议的优化将改进执行时间。如果纬度和经度上的索引允许跳过许多表行，从而跳过许多磁盘页，则会出现这种情况。如果是这样的话，优化器肯定会在距离之前评估WHERE子句的这一部分。
如果没有索引帮助这两列，我怀疑你会看到很大的不同。
Stack Overflow用户
发布于 2016-08-24 13:12:02
您可以使用Management分析查询时间，运行一个具有不同位置的大型查询，它甚至会显示查询的哪一部分需要多少时间。
您可以单击CTRL+L:显示估计执行计划或CTRL+M:显示实际执行计划(运行时)
先用“边界”运行一次，然后再用边界运行。您将能够看到哪个比较慢，然后再试一次，没有边界。
如果您没有足够的数据，差异可能是不可见的。
https://stackoverflow.com/questions/39122880
复制相似问题
腾讯云开发者
