CODEWITHSUNDEEP
c#performancefor-loopforeach
Bulut Kartal
Bulut Kartal

Reputation: 103

For Loop in Foreach Loop Performance Improvement

I have a db table with 2M entries

My XPositions table structure is

Id - int
FID - int
CoordinateQue - int
Latitude - float
Longitude - float

Each row represents a marker position and I need to calculate distance between each coordinates and save to another table.

My xWeights table structure is;

Id - int
x_Id - int
Tox - int
Distance - decimal(18,8)

So far my working code is

var query = _xRepository.TableNoTracking;
var xNodes = query.ToList()
var n = new xWeights();

foreach (var x in xNodes)
{
    for (var i = 0; i < xNodes.Count; i++)
    {
        if(x.Id == xNodes[i].Id)
        {
            //Do nothing - Same Node
        }
        else
        { 
        var R = 6378137; 
        var φ1 = (Math.PI / 180) * x.Latitude;
        var φ2 = (Math.PI / 180) * xNodes[i].Latitude;
        var Δφ = (xNodes[i].Latitude - x.Latitude) * (Math.PI / 180);
        var Δλ = (xNodes[i].Longitude - x.Longitude) * (Math.PI / 180);
        var Δψ = Math.Log(Math.Tan(Math.PI / 4 + φ2 / 2) / Math.Tan(Math.PI / 4 + φ1 / 2));
        var q = Math.Abs(Δψ) > 10e-12 ? Δφ / Δψ : Math.Cos(φ1); // E-W course creates problem with 0/0
        // if Longitude over 180° take shorter rhumb line across the anti-meridian:
        if (Math.Abs(Δλ) > Math.PI) Δλ = Δλ > 0 ? -(2 * Math.PI - Δλ) : (2 * Math.PI + Δλ);
        var dist = (Math.Sqrt(Δφ * Δφ + q * q * Δλ * Δλ)) * R;

        n.x_Id = x.Id;
        n.Tox = xNodes[i].Id;
        n.Distance = dist;

            _xWeightsRepository.Insert(n);
        }
    }
}

My problem is; I am getting approximately 35k records per minute so will be 2.1M record per hour. This will take forever to finish this. Any ideas how to improve the performance?

Upvotes: 0

Views: 264

Answers (1)

Peter Aylett
Peter Aylett

Reputation: 780

The problem is not with this function, but with what you are trying to achieve.

You are trying to insert every from-to combination into _xWeightsRepository. If there are 2 million nodes, then that means 4 thousand billion weights.

If you could insert a weight per CPU clock cycle (which is several orders of magnitude faster than you could ever actually hope to achieve) then you'll still be waiting ten or twenty years.

Check out SQL spatial indexes. I'm going to take a guess that your answer lies in that direction: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-spatial-index-transact-sql

Upvotes: 2

Related Questions