SQL存储过程循环执行耗时过长问题排查求助

问题背景 #

单独查询单船对应的复杂视图仅需1分钟，但通过循环调用存储过程GetShipData批量插入数据时，执行3小时仍未完成。涉及的两个存储过程代码如下：

单船数据查询存储过程 #

CREATE PROCEDURE GetShipData
    @shipCode NVARCHAR(50)
AS
BEGIN 
   
    WITH voyages AS
    (
        SELECT [vslCode], [voyNum], ... -- andere kolommen
        FROM [IceBerg].[MRV].[voyage_copy]
        WHERE vslCode = @shipCode
    ),
    schedule AS (
        SELECT ...
        FROM [IceBerg].[mrv].[vsched_copy] AS vc
        LEFT JOIN voyages ON voyages.vslCode = vc.ves_code
        WHERE vc.ves_code = @shipCode -- of wellicht 'vesselcode' afhankelijk van uw schema
    ),
    -- Other CTEs or subqueries
   
    -- Final Select

    SELECT * FROM ...
    -- Every vslcode in the query has been replaced by @shipcode
END

批量执行存储过程 #

CREATE PROCEDURE PopulateNewEventsLogistic
AS
BEGIN
    DECLARE @vslCodes TABLE (vslCode NVARCHAR(50));
    INSERT INTO @vslCodes (vslCode) VALUES ('V018'), ('V019'), ('V020'); -- Voeg hier de gewenste vslCodes toe

    DECLARE @currentVslCode NVARCHAR(50);

    WHILE (SELECT COUNT(*) FROM @vslCodes) > 0
    BEGIN
        SELECT TOP 1 @currentVslCode = vslCode FROM @vslCodes;

        -- Execute GetShipData for current vslcode
        INSERT INTO MRV.new_events_logistic
        EXEC GetShipData @currentVslCode;

        DELETE FROM @vslCodes WHERE vslCode = @currentVslCode;
    END
END;

排查与优化方向 #

1. 循环逻辑效率低下 #

当前的WHILE循环存在两处低效点：

• 每次循环都执行SELECT COUNT(*) FROM @vslCodes，需要扫描整个表变量；
• 表变量@vslCodes无主键，SELECT TOP 1和DELETE操作均为全表扫描。

优化方案：
改用FAST_FORWARD游标遍历，同时用带主键的临时表替代表变量：

CREATE PROCEDURE PopulateNewEventsLogistic
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #vslCodes (vslCode NVARCHAR(50) PRIMARY KEY);
    INSERT INTO #vslCodes (vslCode) VALUES ('V018'), ('V019'), ('V020');

    DECLARE vslCursor CURSOR FAST_FORWARD FOR
        SELECT vslCode FROM #vslCodes;

    DECLARE @currentVslCode NVARCHAR(50);
    OPEN vslCursor;
    FETCH NEXT FROM vslCursor INTO @currentVslCode;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO MRV.new_events_logistic
        EXEC GetShipData @currentVslCode;

        FETCH NEXT FROM vslCursor INTO @currentVslCode;
    END

    CLOSE vslCursor;
    DEALLOCATE vslCursor;
    DROP TABLE #vslCodes;
END;

2. 参数嗅探导致执行计划失效 #

循环调用GetShipData时，SQL Server可能复用第一次执行的执行计划，若后续参数对应的数据集差异较大，会导致执行计划不匹配，效率骤降。

优化方案：
在GetShipData的最终SELECT语句末尾添加OPTION (RECOMPILE)，强制每次生成适配当前参数的执行计划：

SELECT * FROM ...
OPTION (RECOMPILE);

3. 目标表INSERT操作的累积开销 #

每次循环执行单条INSERT时，若目标表MRV.new_events_logistic存在大量索引、触发器或约束，会反复触发这些逻辑，累积开销极大。

优化方案：

• 先将所有数据存入临时表，最后一次性插入目标表；
• 临时禁用目标表的非聚集索引和触发器，完成插入后重建/启用；
• 通过sys.dm_tran_locks查看锁等待情况，排查是否存在锁竞争。

4. 视图逻辑的重复计算 #

原视图包含20个步骤，循环执行时每次都要重新计算整个视图逻辑，无中间结果复用。

优化方案：

• 将视图逻辑整合到GetShipData中，直接基于底层表查询，移除视图依赖；
• 提前按vslCode预计算视图数据到临时表，循环时直接读取临时表数据。

5. 事务与日志开销 #

默认情况下，每次INSERT都会开启隐式事务，导致大量日志写入和锁等待。

优化方案：
将整个循环包裹在显式事务中（注意数据量不宜过大，避免日志溢出）：

CREATE PROCEDURE PopulateNewEventsLogistic
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    -- 循环逻辑...

    COMMIT TRANSACTION;
END;

6. 索引缺失 #

检查voyage_copy表的vslCode字段、vsched_copy表的ves_code字段是否存在非聚集索引，缺失索引会导致每次过滤都全表扫描，大幅增加耗时。

优化方案：
创建对应字段的非聚集索引：

CREATE NONCLUSTERED INDEX IX_voyage_copy_vslCode ON [IceBerg].[MRV].[voyage_copy](vslCode);
CREATE NONCLUSTERED INDEX IX_vsched_copy_ves_code ON [IceBerg].[mrv].[vsched_copy](ves_code);

内容的提问来源于stack exchange，提问作者Önder Kandemir