sql - 用sql最快更新

Question

我有以下查询需要几天才能执行

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

行数:Vente = 9200 万和促销 =419187执行计划:

如何让它更快？

Answer 1

为什么不使用 JOIN 而不是 IN 子句？

UPDATE v
    SET v.Promo = CASE 
                     WHEN p.[Code Article] IS NOT NULL THEN 1
                     ELSE 0
                  END
FROM [dbo].[Vente] v
LEFT JOIN [Promotion] p
    ON v.[Code Article] = p.[Code Article]
       AND (datecol between [Date Debut Promo] AND [Date Fin Promo])
WHERE v.Promo IS NULL;

你的表格有多少行？