SQL Timeout Geography Query

Question

I Get a random/intermittent SQL timeout on this query looks like it generates a lot of processing from a simple query. Is this correct behavior?

I have a simple stored procedure like this:

CREATE PROCEDURE [dbo].[FindClosestXNearCoordinates]
      @latitude decimal,
      @longitude decimal
   AS
BEGIN

SET NOCOUNT ON;
declare @emptyGUID uniqueidentifier
set @emptyGUID = cast(cast(0 as binary) as uniqueidentifier)
declare @radiusInMeters float
set @radiusInMeters = 3500 * 1609.344   
declare @coordinatePoint as Geography   
SET @coordinatePoint = geography::STGeomFromText('POINT(' + CAST(@longitude AS VARCHAR(20)) + ' ' + CAST(@latitude AS VARCHAR(20)) + ')', 4326)
declare @coordinateRadius as Geography
set @coordinateRadius = @coordinatePoint.STBuffer(@radiusInMeters);


select  top 1   [b].[BaseId], [b].[Code], [b].[Name], [b].[Location], [b].[TerritoryId], [b].[Latitude], [b].[Longitude]
from        XTable b
where       ( b.GeoLocation.STIntersects(@coordinateRadius) = 1 )
order by b.GeoLocation.STDistance(@coordinatePoint) asc

END

I capture it in SQL Profiler and it shows the query and over 188 statements in a row, which is really confusing because when i run this in SSMS it just shows 1 execution, but running in application generates 188 sub-statements.:

Answer 1

First the SQL Profiler. Because the Spatial types are somewhat special to SQL server, they incur additional processing. It registers SP:Starting and SP:Completed entries many times for the same TSQL statement, because it can only report the operation at the TSQL level. You will just have to live with this. It is the same in SQL Server 2012.

Regarding your query timeout, I would suggest replacing the STIntersect() with a more rudimentary test, making the condition

where (b.longitude between @longitude-@xdelta and @longitude+@xdelta)
  and (b.latitude between @latitude-@ydelta and @latitude-@ydelta)
order by b.GeoLocation.STDistance(@coordinatePoint) asc

And the key will be to figure out a suitable @xdelta by converting metres to a decimal value. The conversion escapes me at the moment, but Google is your friend. You can even add the STIntersect(buffer) if you really need it.