CODEWITHSUNDEEP
sqlsql-serverperformancewhile-loopcursor
Indigo_heart
Indigo_heart

Reputation: 139

Very slow cursor SQL Server

I don't know why this is so slow

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

I make before one statement with cursor and it was fast and this is really really slow.

I thought at the beginning it was caused by DATEADD() and other function but no. After comment out it is as slow as before

Upvotes: 0

Views: 693

Answers (2)

digital.aaron
digital.aaron

Reputation: 5707

You just need an UPDATE, not a cursor:

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

Upvotes: 1

Lamak
Lamak

Reputation: 70638

This reaaaaaally doesn't need to be a cursor at all. Seems like you want a simple UPDATE:

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

Upvotes: 5

Related Questions