SQL Server：地理搜索性能 - 查询最近的商店

Question

我有一个关于最近门店的性能查询：

我们有一张包含50,000个记录（商店/销售点位置）的表，位于一个国家。

每个记录都有一个类型为“地理”的location列。

[LOCATION_geo] [geography]

为了提高性能，我使用以下语法在位置列上创建了一个SPATIAL INDEX：

CREATE SPATIAL INDEX [LOCATION_geoIndex] 
ON [dbo].[StoreLocations] ([LOCATION_geo])
USING GEOGRAPHY_GRID 
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), 
CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

我有一个存储过程，可以返回用户当前位置附近的最近1000家商店。

USE [CompanyDB]
GO
SET STATISTICS TIME ON;  
GO  
declare @point geography;
    set @point = geography::Point(49.2471855, -123.1078987, 4326);

    SELECT top (1000) [id]
          ,[Location_Name]
          ,[LOCATION_geo]from [MYDB].[dbo].[StoreLocations]
        where [LOCATION_geo].STDistance(@point) <= 10000
        ORDER BY [LOCATION_geo].STDistance(@point)

问题在于查询总是需要花费656到800毫秒的时间。对于我们的网站来说，这样的性能是无法接受的，因为我们需要太多同步调用。

（已影响1000行）

SQL Server 执行时间：CPU时间=923毫秒，经过时间=1511毫秒。

请注意：大多数商店位于某些城市（约有10个城市）。

此外，我还注意到聚集索引搜索成本>= 45％的总查询成本。

所以我的问题是，是否有更好的方法来提高该查询的性能？

- Tarek El-Mallah

---

是的，它被使用了，成本为8％，估计行大小为17 B，排序：true，预计执行次数为109.8。 - Tarek El-Mallah

我猜如果你确定正在使用索引，那么这是你可以获得的最佳性能。你可以考虑另一种解决方案 - 例如为每个大城市创建10个存储位置的表，以及一个存储其余记录的表；然后，根据用户的坐标计算要查询哪个表；也许减少记录将提高性能； - gotqn

我很好奇返回1000行是否是导致速度变慢的原因。如果您将其降低到1作为测试，会发生什么？此外，您是否需要在结果集中返回location_geo列？ - Ben Thul

网格分辨率是空间索引中的 GRIDS =（LEVEL_1 = MEDIUM，LEVEL_2 = MEDIUM，LEVEL_3 = MEDIUM，LEVEL_4 = MEDIUM） 部分。文档比我能做的更好。https://technet.microsoft.com/zh-cn/library/bb964712(v=sql.105).aspx - Ben Thul

我想到的另一件事是，通过编写这些查询作为 where @g.STIntersects([LOCATION_geo]) = 1 并将 @g 定义为 declare @g geography = @point.STBuffer(10000);，我已经取得了一些成功。 - Ben Thul

Answer 1

我建议在表格中添加一个名为“distance”的列，其中“distance”是LOCATION_geo与Point（0，0，0）之间的距离。请参见下面的示例插入语句：

   INSERT INTO [GWDB].[dbo].[StoreLocations]
          ([id]
          ,[Location_Name]
          ,[LOCATION_geo]
          ,[Distance])
    Values(@id
          ,@Location_Name
          ,@LOCATION_geo
          ,@LOCATION_geo..STDistance(Point(0, 0, 0))

你还应该在新列distance上创建一个索引，并将存储过程更改如下：

USE [CompanyDB]
GO
SET STATISTICS TIME ON;  
GO  
declare @point geography;
declare @distance float;
    set @point = geography::Point(49.2471855, -123.1078987, 4326);
    set @distance = @point.STDistance(geography::Point(0, 0, 0);

    SELECT top (1000) [id]
          ,[Location_Name]
          ,[LOCATION_geo]from [GWDB].[dbo].[StoreLocations]
        where 
        distance < @distance AND
        [LOCATION_geo].STDistance(@point) <= 10000
        ORDER BY [LOCATION_geo].STDistance(@point)

- Dinesh Singh

---

值得一试。但这不会得到准确的结果，我认为如果搜索点在西部，这个查询只会得到东部商店（即搜索点东侧的商店）。 - Tarek El-Mallah

我尝试过了，但从科学角度来看并没有提高性能，因为我认为低性能的主要原因是order by语句。谢谢。 - Tarek El-Mallah

Answer 2

我不确定这个在你的应用中能否很好地工作，在某些情况下它更快，但在其他情况下则更慢。当你搜索的点靠近你的数据时，这个搜索过程会更快。当搜索点距离你的数据较远时，则会更慢。

在我的情况下，所有的点都相对较近（1600万条记录）。这是我看到的速度差异。

|--Search Location--|--STIntersects() time--|--Numbers time--|
--------------------------------------------------------------
|Close              |5 seconds              |700 ms          |
|Far                |90 ms                  |4 seconds       |

基本上，这个想法是使用数字表逐步扩大您的搜索区域。

DECLARE @point GEOGRAPHY = GEOGRAPHY::Point(49.2471855, -123.1078987, 4326)
DECLARE @MaximumRaidus INT = 10000

SELECT TOP 100
  ID,
  Location_Name,
  Location_geo
FROM
  GWDB.dbo.StoreLocations WITH(INDEX([LOCATION_geoIndex]))
CROSS JOIN
  GWDB.dbo.Numbers N
WHERE
  N.n BETWEEN 0 AND SQRT(@MaximumRadius)
  AND Location_geo.STIntersects(@Point.STBuffer(POWER(N.n,2))) = 1
ORDER BY
  N.n

- hcaelxxam

---

谢谢，但我不明白dbo.Numbers表是什么？我没有这样的表。 - Tarek El-Mallah

@Tarek_El-Mallah 数字表就是一个整数表。例如，数字表有一列(n INT)，其中的值从0到10000左右。它们非常有用，可以用来做像这样的事情，或者填充数据中的空缺，或者将数据拆分为直方图等。搜索这个术语，你应该能够找到一些关于它们的用途和如何创建它们的参考资料。 - hcaelxxam