T-SQL Fastforward Cursor vs Foreach

Question

I want to calculate the distance between the GPS point to get the total distance between the first last point.

My question is: which is faster?

• To load all rows in a DataTable and compute it in C # .net via foreach or
• Configure it on the Sql server using the Saved Procedure with the FastForward Cursor.

I am talking about a distance of about 400,000 lines.

Answer 1

I would definitely try to do this on the server - try not to drag 400'000 lines to calculate one number (at the end).

: , . - SQL Server - .

- - , . CTE (Common Table Expression), 0.0, , (x + 1) x .

CTE, , .

CTE - :

;WITH Waypoints AS
(
    -- anchor your query
    SELECT
       WaypointID, PrevWaypointID, Long, Lat, 0.0 as Distance, 0.0 as SumOfDistance
    FROM
       dbo.Waypoint
    WHERE
       PrevWaypointID IS NULL  --  or some other condition

    UNION  -- recurse

    SELECT
       WaypointID, Long, Lat, 
       dbo.GetDistanceBetween(wp.WaypointID, pts.WaypointID),   -- distance
       pts.SumOfDistance + dbo.GetDistanceBetween(wp.WaypointID, pts.WaypointID)  -- sum
    FROM
       dbo.Waypoint wp
    INNER JOIN
       Waypoints pts ON wp.PrevWaypointID = pts.WaypointID          
    WHERE
       (some condition; ID = 1 or PreviousWaypointID IS NULL or something)
)
SELECT * FROM Waypoints

Answer 2

SQL Server 2008, geography,

declare @point1 geography = 'POINT (-42 84)';
declare @point2 geography = 'POINT (-3 10)';
select @point1.STDistance (@point2)

, , , .

Answer 3

, SQL , . ADO DAO , , ADO.NET DataSets.

, , T-SQL, , .

, , , SQL-, , .

SQL, .,.

Answer 4

Sql Server 2008 ( ), , . :

SELECT geography::Point(lat1, lon1, 4326).STDistance(geography::Point(lat2, lon2, 4326))

, , .

SQL Server, .

, , .