Update Row by Row - Cursor

Transact-SQL
1

Hi,
I've written a cursor to update a data set using a cursor. The problem is that it takes a long time to run and it needs to be done regularly. There must be a better way to do it, but I've taught myself and can't identify another way.

I have 2 tables:

  1. Master Table - Approx 160,000 rows with a yardage start and end point. Each row is no more than 220 yards
  2. Plan table - approx 80,000 rows with a yardage start and end point. each row could be any number of yards (usually upwards of 10,000 yards)

I'm basically trying to return the next date each 220 yard section will be checked.

[[DECLARE @strRowsAffected NVARCHAR(400), @TRKELR NVARCHAR(20), @TRKTrackID INT, @TRVVehicle Nvarchar(50), @ShiftCode nvarchar(50)
DECLARE @TRVTotalENDYards BIGINT, @TRVTotalSTARTYards BIGINT, @ShiftDate DATE, @UXSwap BIGINT

		DECLARE TRKCURSOR CURSOR
			FOR SELECT DISTINCT ELR, TrackID FROM tbl_TRVTrainPlanData
			WHERE ELR IS NOT NULL
			ORDER BY ELR, TrackID

		OPEN TRKCURSOR		
		
		FETCH NEXT FROM TRKCURSOR
		INTO @TRKELR, @TRKTrackID	
		
			
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			DECLARE TRVCURSOR CURSOR
			FOR SELECT ShiftDate, TotalStartYards, TotalFinishYards, Vehicle, ShiftCode  FROM tbl_TRVTrainPlanData
						WHERE ELR = @TRKELR AND TrackID = @TRKTrackID	
						ORDER BY ShiftDate ASC
						
			OPEN TRVCURSOR
			
			-- FETCH THE FIRST DATA ROW FOR LOADED ELR AND TRACK ID
			FETCH NEXT FROM TRVCURSOR
			INTO @ShiftDate, @TRVTotalStartYards, @TRVTotalENDYards, @TRVVehicle, @ShiftCode
			
			WHILE @@FETCH_STATUS = 0
			BEGIN
			
				IF (@TRVTotalEndYards < @TRVTotalSTARTYards)
				BEGIN
					set @UXSwap = @TRVTotalEndYards
					set @TRVTotalEndYards = @TRVTotalSTARTYards
					set @TRVTotalSTARTYards = @UXSwap
				END 
			
					
				UPDATE tbl_MasterTrackInfo_TrackGeo SET NextRecordingDate = @ShiftDate, NextDateVehicle = @TRVVehicle
				WHERE ELR = @TRKELR 
					AND TrackID = @TRKTrackID 
					AND ((TotalStartyards >= @TRVTotalStartYards AND TotalStartYards < @TRVTotalEndYards)
							OR	(TotalFinishYards > @TRVTotalStartYards AND TotalFinishYards <= @TRVTotalEndYards)
							OR ((@TRVTotalStartYards >= TotalStartyards AND @TRVTotalSTARTYards < TotalFinishYards)
									AND (@TRVTotalEndYards > TotalStartyards AND @TRVTotalENDYards <= TotalFinishYards)))
					AND (NextRecordingDate IS NULL OR NextRecordingDate > @ShiftDate)
					and (@ShiftDate >= GETDATE()) 
			
				FETCH NEXT FROM TRVCURSOR
				INTO @ShiftDate, @TRVTotalStartYards, @TRVTotalENDYards, @TRVVehicle, @ShiftCode
				
			END
			
			CLOSE TRVCURSOR
			DEALLOCATE TRVCURSOR
			
			FETCH NEXT FROM TRKCURSOR
			INTO @TRKELR, @TRKTrackID
		END
	
	CLOSE TRKCURSOR
	DEALLOCATE TRKCURSOR]](http://txt)
2

rewrite your query using set-based logic and eliminate the cursor

3

I think this is the same logic, although of course it's extremely difficult to know with no data to test it. At any rate, (hopefully!) it's close enough for you to adjust as needed:

    UPDATE mtitg
    SET NextRecordingDate = ttpd.ShiftDate, NextDateVehicle = ttpd.Vehicle
    FROM tbl_MasterTrackInfo_TrackGeo mtitg
    CROSS APPLY (
            SELECT TOP (1) ttpd2.ShiftDate, ttpd2.TotalStartYards, ttpd2.TotalFinishYards, 
                    ttpd2.Vehicle, ttpd2.ShiftCode  
            FROM tbl_TRVTrainPlanData ttpd2
            CROSS APPLY (
                SELECT CASE WHEN ttpd2.TotalFinishYards < ttpd2.TotalStartYards 
                            THEN ttpd2.TotalFinishYards 
                            ELSE ttpd2.TotalStartYards END AS StartYards,
                       CASE WHEN ttpd2.TotalFinishYards < ttpd2.TotalStartYards 
                            THEN ttpd2.TotalStartYards 
                            ELSE ttpd2.TotalFinishYards END AS EndYards
            ) AS ca1
            WHERE ttpd2.ELR = mtitg.ELR
                AND ttpd2.TrackID = mtitg.TrackID
				AND ((mtitg.TotalStartyards >= ca1.StartYards AND mtitg.TotalStartYards < ca1.EndYards)
					OR (mtitg.TotalFinishYards > ca1.StartYards AND mtitg.TotalFinishYards <= ca1.EndYards)
					OR ((ca1.StartYards >= mtitg.TotalStartyards AND ca1.StartYards < mtitg.TotalFinishYards)
						AND (ca1.EndYards > mtitg.TotalStartyards AND ca1.EndYards <= mtitg.TotalFinishYards)))
				AND (mtitg.NextRecordingDate IS NULL OR mtitg.NextRecordingDate > ttpd2.ShiftDate)
				AND (ttpd2.ShiftDate >= GETDATE())
			ORDER BY ttpd2.ShiftDate ASC                      
    ) AS ttpd
2 Likes
4

This worked perfectly!

Thanks for your help!

5

This query selects the top result to return. How would I go about selecting the nth result?

6

Your original code would have updated with the first result -- the lowest ShiftDate -- found.

Notice the first cursor has:
ORDER BY ShiftDate ASC

Then the UPDATE sets the NextRecordingDate value. The WHERE clause in it contains:
AND (NextRecordingDate IS NULL OR NextRecordingDate > @ShiftDate)

Which means, in combination, that only the earliest matching ShiftDate gets updated into the table. I believe my code does that.

If you need something other than the lowest date, then instead of "TOP (1)" you'd need to do "TOP (n)" and then add another subquery above that to select "TOP (1)" with the reverse sort order.

7

It's not for this code, it's for a similar but different code that needs to identify the 1st, 2nd, 3rd, 4th, 5th etc. shiftcode over a 220 yard section.

I was going to try the Top(n) and reverse the sort order but didn't know if there was a better way.

Thanks