T-SQL Fastforward Cursor vs. foreach

Question

i want to caluclate the distance between gps-point to get the complete distance between the first an last point.

My question is: What is faster?

• To load all rows into a DataTable and calculate it in c#.net via foreach or
• Caluclate it on Sql-Server using a StoredProcedure with a FastForward Cursor.

I talk of a amout of about 400,000 rows.

Answer 1

I would definitely try to do this on the server - try to avoid dragging down 400'000 rows just to calculate a single number (in the end).

Also: I would try to do it without a cursor , if ever possible. Cursors are a nightmare on SQL Server - they should be avoided at all costs.

In your case - not knowing your detailed table structure - you could definitely do e.g. a recursive CTE (Common Table Expression) that starts with the first element and a total distance of 0.0, and then recursively sums up all other way points, calculating the distance between point (x+1) and point x, and summing up to the previous total.

At the end, you should have a CTE that shows all way points, all distances between any two waypoints, and the total distance of the entire journey.

That CTE would be something like:

;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

If you are using SQL Server 2008 I would recommend trying to store them as the geography type and then

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

but to really know what is fastest you have to try both.

Answer 3

My understanding is that even using a Cursor in SQL, it is still orders of magnitude faster than iterating in front-side code. At the time, ADO and DAO were the technologies in question, so things may have changed a little with the advent of ADO.NET and DataSets.

However, I am betting that T-SQL, being designed specifically for this type of thing, is still more efficient.

The exception would might be if you need to apply special logic during your iteration, but I ssupect that a properly set up SQL Cursor, performing your calculation on the back side, will outperform the Dataset.

Best of all would be to do it without the cursor in SQL, if you are able . . .