SQL Server: Geography Search Efficiency - Nearest Store Requests

Question

I have the nearest performance request store:

We have a table containing about 50,000 records (stores / points of sale) in one country.

Each record has location columns like "geography"

 [LOCATION_geo] [geography] 

Also for performance, I created a SPACE INDEX on this 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 

I have a stored procedure to return the nearest repository of 1000 for the user's 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) 

The problem is that the request always takes from 656 ms to 800 ms. And this is unacceptable performance for our website because we expect too many synchronous calls.

(1000 lines affected)

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

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

I also noticed that the Clustered Index Seek costs> = 45% of the total cost of the request.

So my question is: is there a better way to improve the performance of this request?

Answer 1

I would suggest adding another column called distance to table, where the distance would be the LOCATION_geo distance from Point (0, 0, 0). See an example of an 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 at a new column distance and modify your stored procedure as shown 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) 

Answer 2

I'm not sure how well this will work in your application, in certain scenarios it happens faster, but slower in others. When the point you are looking around is close to your data, this search process is faster. When the search point is further from your data, it is slower.

In my scenario, all of my points are relatively close (16 million records). These are the speed differences that I see.

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

Basically, the idea is to gradually expand your search using a table of numbers.

 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 Nn BETWEEN 0 AND SQRT(@MaximumRadius) AND Location_geo.STIntersects(@Point.STBuffer(POWER(Nn,2))) = 1 ORDER BY Nn