sql-server - SQL Server : Geography search performance - query nearest stores

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 row(s) affected)

SQL Server Execution Times: CPU time = 923 ms, elapsed time = 1511 ms.

注:大部分门店分布在部分城市(约10个城市)。

我还注意到聚集索引查找成本 >= 总查询成本的 45%。

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

Answer 1

我建议在表中再添加一列名为 distance 的列，其中 distance 是 LOCATION_geo 距 P​​oint(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))

您还应该在新的列距离上创建索引并更改您的存储过程，如下所示:

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)