# SELECT "TOP" without "ORDER BY" takes forever

• ### 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;

SELECT TOP 1 *
FROM Bubbles

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
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

• Try creating the buffered point outside the SELECT statement. i.e.:
```DECLARE @Point geography;

SELECT TOP 1 *
FROM Bubbles
WHERE GeoPoint.STIntersects(@BufferedPoint) = 1;```
Also, if necessary, add a WITH(INDEX(nameofyourspatialindex)) hint to the query.

• Marked as answer by Monday, March 7, 2011 9:15 PM
• Unmarked as answer by Wednesday, March 16, 2011 9:43 PM
• Marked as answer by Tuesday, April 12, 2011 10:05 AM
Monday, March 7, 2011 7:30 PM

### All replies

• Try creating the buffered point outside the SELECT statement. i.e.:
```DECLARE @Point geography;

SELECT TOP 1 *
FROM Bubbles
WHERE GeoPoint.STIntersects(@BufferedPoint) = 1;```
Also, if necessary, add a WITH(INDEX(nameofyourspatialindex)) hint to the query.

• Marked as answer by Monday, March 7, 2011 9:15 PM
• Unmarked as answer by Wednesday, March 16, 2011 9:43 PM
• Marked as answer by Tuesday, April 12, 2011 10:05 AM
Monday, March 7, 2011 7:30 PM
• 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