非常慢的光标SQL Server

Question

我不知道为什么这么慢

DECLARE kursor CURSOR FOR
    SELECT mediatime, ShortDate, MediaDateID, TimeID 
    FROM DateTimeMCross
DECLARE @mediatime INT, @ShortDate DATE, @MediaDateID INT, @TimeID INT, @tmpDate DATE
SET @mediatime = 0;
OPEN kursor
FETCH NEXT FROM kursor INTO @mediatime, @ShortDate, @MediaDateID, @TimeID
WHILE @@FETCH_STATUS = 0
BEGIN
    /*SET @tmpDate = (select DATEADD(day,-1,@shortDate))*/
    IF @mediatime >= 2400
    BEGIN
        UPDATE DateTimeMCross
        SET DateTimeMCross.newMediaDateID = 2/*(SELECT     YEAR(@tmpDate) * 10000 + MONTH(@tmpDate) * 100 + DAY(@tmpDate))*/
        WHERE DateTimeMCross.MediaDateID = @MediaDateID 
          AND DateTimeMCross.TimeID = @TimeID
    END
    ELSE IF @mediatime < 2400 AND @mediatime >= 0
    BEGIN
        UPDATE DateTimeMCross
        SET DateTimeMCross.newMediaDateID = @MediaDateID
        WHERE DateTimeMCross.MediaDateID = @MediaDateID 
          AND DateTimeMCross.TimeID = @TimeID
    END
    FETCH NEXT FROM kursor INTO @mediatime, @ShortDate, @MediaDateID, @TimeID
END
CLOSE kursor
DEALLOCATE kursor

我在使用光标之前发表一句话，这很快，这真的很慢。

我一开始就以为是由DATEADD()和其他功能引起的，但没有。评论后，它和以前一样慢

Answer 1

这根本不需要成为光标。似乎您想要一个简单的UPDATE：

UPDATE DateTimeMCross
SET newMediaDateID = CASE 
                        WHEN mediatime >= 2400 THEN 2
                        ELSE MediaDateID
                     END
;

Answer 2

您只需要UPDATE，而不是光标：

UPDATE d
SET d.newMediaDateID = CASE 
                        WHEN mediatime>=2400 THEN 2 
                        WHEN mediatime <2400 AND mediatime >=0 THEN MediaDateID
                        END
FROM DateTimeMCross d