I have a table of 4,000 schools - each having their lat & long coordinates. Each shop has a level (3 options).
I want to pass current lat, long coordinates, and find the 3 closest for each category of shop.
DECLARE @current_lat DECIMAL(12, 9)
DECLARE @current_long DECIMAL(12, 9)
SET @current_lat=55.151025 set @current_long=-7.455171
DECLARE @orig geography = geography::Point(@current_lat, @current_long, 4326);
SELECT * FROM
(
(
SELECT top 3 *, @orig.STDistance(geography::Point(s.Lat, s.Long, 4326)) AS distance
FROM School s
WHERE Level = 1
order by distance asc
)
UNION
(
SELECT top 3 *, @orig.STDistance(geography::Point(s.Lat, s.Long, 4326)) AS distance
FROM School s
WHERE Level = 2
order by distance asc
)
UNION
(
SELECT top 3 *, @orig.STDistance(geography::Point(s.Lat, s.Long, 4326)) AS distance
FROM School s
WHERE Level = 3
order by distance asc
)
) s
The code works fine, but it takes about 1 second to execute. I have added non-clustered indexes to the lat, long & level columns.
Can someone suggest an improvement to make this a little snappier? Adding 1 second to an API call doesn't seem feasable.
Thanks
Execution Plan:
1条答案
按热度按时间56lgkhnf1#
Rewrited as :
Is it quicker ?
Do you have a geo index ?