sql - SQL最快的更新

我有以下查询，花了几天时间才能执行

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 = 92百万，促销= 419187
执行计划：



如何使其更快？

最佳答案

为什么不使用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;

您的表中有几行？