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:

Execution Plan - Paste The Plan