fastest update with sql

Viewed 73

I have the following query that it took days to be executed

DECLARE @cnt BIGINT
SET @cnt = 1

WHILE @cnt * 1000 < 92746339
BEGIN

    UPDATE TOP (1000) [dbo].[Vente]
    SET Promo =
        CASE
            WHEN [Code Article] IN (
                    SELECT [Code Article]
                    FROM [Promotion]
                    WHERE datecol BETWEEN [Date Debut Promo] AND [Date Fin Promo]
                )
                THEN 1
            ELSE 0
        END

    WHERE promo IS NULL
    PRINT '@cnt: ' + CONVERT(VARCHAR, @cnt)
    SET @cnt = @cnt + 1
END

Number of rows : Vente = 92 millions and promotion =419187 The execution plan :

enter image description here

How to make it faster ?

1 Answers
Related