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

问题描述：

我在离商店最近的地方进行了性能查询:

I have a performance query nearest stores:

我们有一个表格，其中包含一个国家/地区约50,000条记录(商店/销售点的位置).

We have a table that contains around 50,000 records (stores/point of sale locations) in one country.

每条记录都有location个地理位置"类型的列

Each record has location columns of type "geography"

[LOCATION_geo] [geography]

为了提高性能，我使用此语法在该location列上创建了一个空间索引

Also for performance I created a SPATIAL INDEX over that location column using this syntax

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家商店.

I have a stored procedure to return the nearest 1000 store for the user current location.

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毫秒.对于我们的网站来说，这是不可接受的性能，因为我们期望太多的同步调用.

The problem is that query always takes 656 ms to 800 ms. And this is not acceptable performance for our web site, because we expect too many synchronous calls.

(受影响的1000行)

(1000 row(s) affected)

SQL Server执行时间:CPU时间= 923毫秒，经过的时间= 1511毫秒.

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

注意:大多数商店都位于某些城市(约10个城市).

Note: that most of stores located in some cities (about 10 cities).

我还注意到聚簇索引的搜索成本> =总查询成本的45％.

Also I noticed that Clustered Index Seek cost >= 45% of total query cost.

所以我的问题是，有没有更好的方法来改善该查询的性能?

So my question is are there a better way to improve the performance of that query?

答

我建议在表中再添加一个名为distance的列，其中distance将是LOCATION_geo与Point(0，0，0)的距离.请参阅下面的示例插入语句:

I would suggest to add one more column named distance to the table where distance would be distance of LOCATION_geo from Point(0, 0, 0). See sample insert statement below:

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

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

You should also create an index on the new column distance and change your Stored Procedure as below:

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)

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

相关推荐