locked
SELECT "TOP" without "ORDER BY" takes forever RRS feed

  • Question

  • I have a table called Bubbles with 14 million Geography points.

    I want to get ANY point that falls within a specified distance of a query point....so I have a query that looks like this:

    DECLARE @Point geography;
    DECLARE @Radius int;

    SELECT TOP 1 *
    FROM Bubbles
    WHERE @Point.STBuffer(@Radius).STIntersects(GeoPoint) = 1

    However......that query runs seemingly forever (almost 7 minutes to finish!!!).

    When I add an "ORDER BY" clause....then the query returns quickly (usually less than a second):

    SELECT TOP 1 *
    FROM Bubbles
    WHERE @Point.STBuffer(@Radius).STIntersects(GeoPoint) = 1
    ORDER BY @Point.STDistance(GeoPoint)

    I'd like my query NOT to have to go through all the calculations of STDistance to find the closest distance.  Is there any way to make it return ANY point that matches my query at least as fast as using the ORDER BY clause (but ideally even faster!)?

    Thanks

    Monday, March 7, 2011 6:30 PM

Answers

  • Try creating the buffered point outside the SELECT statement. i.e.:
    DECLARE @Point geography;
    DECLARE @Radius int;
    DECLARE @BufferedPoint = @Point.STBuffer(@Radius);
    
    SELECT TOP 1 *
    FROM Bubbles
    WHERE GeoPoint.STIntersects(@BufferedPoint) = 1;
    Also, if necessary, add a WITH(INDEX(nameofyourspatialindex)) hint to the query.

    twitter: @alastaira blog: http://alastaira.wordpress.com/
    • Marked as answer by GavinLaz Monday, March 7, 2011 9:15 PM
    • Unmarked as answer by GavinLaz Wednesday, March 16, 2011 9:43 PM
    • Marked as answer by tanoshimiEditor Tuesday, April 12, 2011 10:05 AM
    Monday, March 7, 2011 7:30 PM
    Answerer

All replies

  • Try creating the buffered point outside the SELECT statement. i.e.:
    DECLARE @Point geography;
    DECLARE @Radius int;
    DECLARE @BufferedPoint = @Point.STBuffer(@Radius);
    
    SELECT TOP 1 *
    FROM Bubbles
    WHERE GeoPoint.STIntersects(@BufferedPoint) = 1;
    Also, if necessary, add a WITH(INDEX(nameofyourspatialindex)) hint to the query.

    twitter: @alastaira blog: http://alastaira.wordpress.com/
    • Marked as answer by GavinLaz Monday, March 7, 2011 9:15 PM
    • Unmarked as answer by GavinLaz Wednesday, March 16, 2011 9:43 PM
    • Marked as answer by tanoshimiEditor Tuesday, April 12, 2011 10:05 AM
    Monday, March 7, 2011 7:30 PM
    Answerer
  • Thanks!  Moving the buffered point outside of the SELECT made no difference.  However, adding the explicit index name worked.  I hadn't tried that because in the ORDER BY case I could see via the query plan that the index was being used.  I didn't realize that it then might not automatically be used if I didn't have the ORDER BY clause.

    Thanks again.

    Monday, March 7, 2011 9:15 PM
  • I have a related question to what I had asked originally.  In my table of 14 million points, I want to write a query that tells me if ANY point in my data intersects with a specified rectangle.

    The query looks like this:

    -- This represents the continental US
    DECLARE @CoverageArea Geography
    SET @CoverageArea = Geography::STGeomFromText('POLYGON((-153.525351901877 57.3906816586939, -153.525351901877 6.78274180059447, -31.7968362768773 6.78274180059447, -31.7968362768773 57.3906816586939, -153.525351901877 57.3906816586939))', 4326);

    SELECT TOP 1  GeoPoint.Lat, GeoPoint.Long
    FROM Bubbles WITH(NOLOCK, INDEX([Bubbles.GeoPoint.IX]))
    WHERE GeoPoint.STIntersects(@CoverageArea) = 1

    This query takes 23 seconds to return.....but I feel like it should return almost instantly.  Is there a better way to form the query so that I can get a fast result?

    Thanks

    Wednesday, March 16, 2011 9:42 PM