2
\$\begingroup\$

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:

enter image description here

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

enter image description here

    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;
    }
\$\endgroup\$
5
\$\begingroup\$
  • 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.

\$\endgroup\$
  • \$\begingroup\$ Do you think it will better to write this Query in LINQ and Entity Framework instead of getting EF to execute this Query. \$\endgroup\$ – Dawood Awan Nov 20 '15 at 7:03
  • \$\begingroup\$ I don't see any advantage of using Linq here. You already have (after you change the SELECT) a easy to read (and hopefully performant) query. \$\endgroup\$ – Heslacher Nov 20 '15 at 7:09
  • \$\begingroup\$ I checked the Database, and found that the condition GeoLine.STIsValid() =1 was not required. And I also changed Top 10 to Top 1. And Performance increased. Now it takes miliseconds to perform same query \$\endgroup\$ – Dawood Awan Nov 20 '15 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.