创建存储过程spVerhoogPrijzen1执行耗时过长求助

嘿，我来帮你拆解这个存储过程跑慢的原因，再给你落地的优化方案：

可能导致执行缓慢的原因 #

• 缺少关键索引：你的查询涉及三张表的连接和过滤，但如果连接字段（artikelnr、catcode）和过滤字段（catomschrijving、begindatum）没有合适的索引，数据库会被迫做全表扫描，这是慢查询的头号元凶。
• 事务范围过大：当前整个更新操作都包裹在一个事务里，如果要更新的行数很多，事务会长时间持有锁，不仅自身执行慢，还可能阻塞其他数据库操作。
• LIKE操作的潜在问题：如果传入的@artikelcategorie参数是以%开头的模糊匹配（比如'%Elektronica'），那么catomschrijving字段的索引会直接失效，触发全表扫描。
• 一次性更新大量数据：如果目标更新行数成千上万，一次性更新会瞬间占用大量CPU、内存和日志资源，导致执行时间拉长。

针对性优化方案 #

1. 添加合适的索引 #

给涉及连接和过滤的字段创建复合索引，让数据库能快速定位到需要更新的数据：

-- 给artikelprijs表创建过滤+连接的复合索引，包含要更新的prijs字段
CREATE NONCLUSTERED INDEX IX_ArtikelPrijs_Begindatum_ArtikelNr 
ON artikelprijs(begindatum, artikelnr)
INCLUDE(prijs);

-- 给artikel表创建连接字段的索引
CREATE NONCLUSTERED INDEX IX_Artikel_CatCode_ArtikelNr 
ON artikel(catcode, artikelnr);

-- 给artikelcategorie表创建分类描述的索引（仅当前缀匹配或精确匹配时生效）
CREATE NONCLUSTERED INDEX IX_ArtikelCategorie_CatOmschrijving 
ON artikelcategorie(catomschrijving)
INCLUDE(catcode);

2. 优化事务与分批更新 #

把大更新拆分成小批次执行，每个批次用独立的小事务，减少锁持有时间和资源占用：

Create Procedure spVerhoogPrijzen1 
    @artikelcategorie varchar(128), 
    @ingangsdatum date 
as
BEGIN
    SET NOCOUNT ON; -- 关闭计数消息输出，提升执行效率
    DECLARE @RowCount INT = 1;
    DECLARE @BatchSize INT = 1000; -- 可根据服务器性能调整批次大小

    WHILE @RowCount > 0
    BEGIN
        BEGIN TRANSACTION;

        -- 每次只更新指定行数的数据
        UPDATE TOP(@BatchSize) p 
        SET prijs = prijs * 1.1 
        FROM artikelprijs p 
        JOIN artikel a ON a.artikelnr = p.artikelnr 
        JOIN artikelcategorie c ON c.catcode = a.catcode 
        WHERE c.catomschrijving LIKE @artikelcategorie 
          AND p.begindatum >= @ingangsdatum
          AND p.prijs * 1.1 <> p.prijs; -- 避免重复更新已修改的行

        SET @RowCount = @@ROWCOUNT;

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION;
            RAISERROR('Je hebt iets fouts ingevuld', 16, 1);
            RETURN;
        END

        COMMIT TRANSACTION;
        WAITFOR DELAY '00:00:01'; -- 可选：给其他操作留缓冲时间，减少锁竞争
    END
END

3. 优化LIKE查询逻辑 #

• 如果@artikelcategorie是精确匹配，把LIKE改成=，这样索引能完全生效：WHERE c.catomschrijving = @artikelcategorie
• 如果必须用模糊匹配，尽量使用前缀匹配（比如'Elektronica%'），避免用%开头的模式
• 若需要任意位置的模糊匹配，考虑给catomschrijving字段创建全文索引，用全文搜索替代LIKE

4. 额外排查建议 #

• 查看执行计划：在SSMS里执行存储过程时，开启“包含实际执行计划”，看看是否有全表扫描、键查找等低效操作
• 更新统计信息：过时的统计信息会让数据库生成糟糕的执行计划，执行以下命令更新：

UPDATE STATISTICS artikelprijs;
UPDATE STATISTICS artikel;
UPDATE STATISTICS artikelcategorie;

• 检查阻塞情况：用sp_who2或活动监视器查看是否有其他会话持有锁导致当前操作等待

内容的提问来源于stack exchange，提问作者Moi