# DbGeography search query

I have a situation in which I need to get the closest road to a DbGeography point.

This takes 5 - 8 seconds to run in some cases.

I have a Spatial_Index on the column and I re-organised it, so it is not fragmented.

This query is quite heavy, sometimes the CPU usage raises to 100% because of this.

This is what the table looks like:

Here is the spatial result (in case it helps):

    public string GetRoad(DbGeography point)
{
var name = "";
try
{
using (var gisEntities = new GISSpatialEntities())
{
var query = String.Format(@"
DECLARE @point geography;
SET @point = geography::STPointFromText('POINT({0} {1})', 4326);

SELECT TOP 10 * FROM GeographyLine
WHERE GeoLine.STIsValid() =1 AND  @point.STBuffer(200).STIntersects(GeoLine) = 1
ORDER BY GeoLine.STDistance(@point)", point.Longitude, point.Latitude);
var firstOrDefault = gisEntities.GeographyLines.SqlQuery(query).FirstOrDefault();
if (firstOrDefault != null)
{
name = firstOrDefault.LineName;
}
}
}
catch (Exception ex)
{
var msg = string.Format("Exception in GetRoad() - {0} - Inner [{1}] \r\n", ex.Message, ex.InnerException);
_logger.LogMessage(MessageType.Error, msg);
}
return name;
}


• Instead of doing SELECT TOP 10 * FROM ... you should only retrieve the columns needed which in your case is LineName.
• var name = "" doesn't need to be there, if you just return firstOrDefault.LineName and for no result/exception return string.Empty.
• While we are at var firstOrDefault I would like to say that using var here is just wrong because it isn't clear from the right side of the assignment what the type is. The variable name firstOrDefault isn't descriptive enough so you should consider using maybe geographyLine or foundGeographyLine.
• You are selecting the TOP 10 but using FirstOrDefault. This just doesn't seem right. Using SELECT TOP 1 would be much better and faster.