SQL Server : Geography search performance - query nearest stores

Question

I have a performance query nearest stores:

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

Each record has location columns of type "geography"

[LOCATION_geo] [geography]

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

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)

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

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

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

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?

Answer 1

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)