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?
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:
You should also create an index on the new column distance and change your Stored Procedure as below: