如何提升SQL Server中非空值向上填充空列的查询性能？

我有一张名为Values的表，结构包含ArtId、VarId、Sequence以及Val1至Val10（业务中最多可达100列）字段。需求是按VarId分组，将每组内非空列的值向上填充到空列中（比如VarId=1的两行，第一行Val3的值移到Val1，第二行Val6的值移到Val2）。

目前已实现的SQL能得到正确结果，但数据量超100万时性能很差，求优化方法。原实现代码如下：

DECLARE @start INT = 1, @end INT = 10

DECLARE @valueTable NVARCHAR(100) = N'[Values]'

DECLARE @valueColumns NVARCHAR(MAX) = N''

DECLARE @numColumns NVARCHAR(MAX)= N''
DECLARE @numColumnsWithoutNull NVARCHAR(MAX) = N''

DECLARE @attNumber NVARCHAR(3)
DECLARE @separator VARCHAR(1) = ','

WHILE @start <= @end
BEGIN
    
    SET @attNumber = CAST(@start AS NVARCHAR(3))  
    
    IF @start = @end
    BEGIN
        SET @separator = N''
    END
   
    SET @valueColumns += N'Val'+ @attNumber +''+@separator+''
            
    SET @numColumns += N'['+ @attNumber +']'+@separator+''
    SET @numColumnsWithoutNull += N'ISNULL(['+ @attNumber +'],'''') AS ['+ @attNumber +']'+@separator+''       

    SET @start = @start +1
END        

DECLARE @result NVARCHAR(MAX) =N'
DROP TABLE IF EXISTS #TempCache;
SELECT * INTO #TempCache
FROM
(
    SELECT  ArtId, VarId, Sequence, '+@numColumnsWithoutNull+'
    FROM
    (
        SELECT  VarId, ArtId, Sequence, Value, DENSE_RANK() OVER (PARTITION BY VariationGroupId ORDER BY [Cols]) AS RowId
        FROM
            (SELECT * FROM '+@valueTable+' ) AS T1
        UNPIVOT
            (value FOR [Cols] IN ('+@valueColumns+')) AS unpvt  
        WHERE Value <> ''''
    ) T
    PIVOT
    (
        MAX([value]) FOR RowId IN ('+@numColumns+')
    ) AS pvt
) Temp;
TRUNCATE TABLE '+@valueTable+';

INSERT INTO '+@valueTable+'
SELECT * FROM #TempCache 
ORDER BY Sequence
'
--SET STATISTICS XML ON;
exec sp_executesql @result
--SET STATISTICS XML OFF;

原方案性能瓶颈分析 #

• 行列转换开销大：UNPIVOT/PIVOT组合在百万级数据+多列场景下，会生成海量中间数据，内存和IO负载极高
• 临时表无索引：#TempCache是SELECT INTO生成的堆表，后续插入排序、查询都需要全表扫描
• 锁表时间过长：直接截断原表再插入，会长时间持有表锁，影响业务读写
• 排序成本高：DENSE_RANK()按VariationGroupId分区排序，若该字段无索引，会触发昂贵的排序操作

优化方案 #

1. 替换UNPIVOT/PIVOT，用窗口函数直接计算填充值 #

避免大宽表的行列转换，改用ROW_NUMBER()和MAX() OVER()按VarId分组，逐列计算需要填充的非空值：

DECLARE @start INT = 1, @end INT = 10;
DECLARE @valueTable NVARCHAR(100) = N'[Values]';
DECLARE @updateSql NVARCHAR(MAX) = N'';

WHILE @start <= @end
BEGIN
    DECLARE @currentCol NVARCHAR(10) = N'Val' + CAST(@start AS NVARCHAR(3));
    -- 为当前列生成更新逻辑：按VarId分组，将非空值向上填充
    SET @updateSql += N'
    WITH RankedData AS (
        SELECT 
            ArtId, VarId, Sequence, ' + @currentCol + ',
            -- 统计当前行及之前的非空值数量
            COUNT(' + @currentCol + ') OVER (PARTITION BY VarId ORDER BY Sequence ROWS UNBOUNDED PRECEDING) AS NonNullCnt
        FROM ' + @valueTable + '
    )
    UPDATE rd
    SET ' + @currentCol + ' = (
        SELECT TOP 1 ' + @currentCol + '
        FROM RankedData rd2
        WHERE rd2.VarId = rd.VarId AND rd2.NonNullCnt = rd.NonNullCnt
        ORDER BY rd2.Sequence
    )
    FROM RankedData rd
    WHERE rd.' + @currentCol + ' IS NULL OR rd.' + @currentCol + ' = '''';';

    SET @start += 1;
END

EXEC sp_executesql @updateSql;

优势：逐列处理，中间数据量小；窗口函数可利用索引大幅提升效率。

2. 创建覆盖索引，减少IO开销 #

在VarId上创建包含Sequence和所有ValN列的覆盖索引，让数据库无需回表即可获取所有所需数据：

CREATE NONCLUSTERED INDEX IX_Values_VarId_Sequence_AllCols ON [Values] (VarId, Sequence)
INCLUDE (ArtId, Val1, Val2, ..., Val100); -- 替换为实际存在的Val列

说明：覆盖索引能将查询所需数据全部存储在索引页中，避免昂贵的书签查找，大幅降低IO耗时。

3. 批量处理+元数据操作替换锁表逻辑 #

如果需要全表替换数据，不要直接截断插入，改用带索引的临时表+ALTER TABLE SWITCH操作，几乎无锁表时间：

-- 提前创建与原表结构一致的临时表并添加索引
CREATE TABLE #TempCache (
    ArtId INT, VarId INT, Sequence INT,
    Val1 VARCHAR(100), Val2 VARCHAR(100), ..., Val10 VARCHAR(100) -- 匹配原表字段类型
);
CREATE CLUSTERED INDEX IX_TempCache_Sequence ON #TempCache (Sequence);
CREATE NONCLUSTERED INDEX IX_TempCache_VarId ON #TempCache (VarId);

-- 用CROSS APPLY代替UNPIVOT，更高效地展开列
INSERT INTO #TempCache WITH (TABLOCK)
SELECT 
    ArtId, VarId, Sequence,
    MAX(CASE WHEN rn=1 THEN Value END) AS Val1,
    MAX(CASE WHEN rn=2 THEN Value END) AS Val2,
    ...,
    MAX(CASE WHEN rn=10 THEN Value END) AS Val10
FROM (
    SELECT 
        ArtId, VarId, Sequence, Value,
        -- 按VarId分组，给非空值分配序号
        ROW_NUMBER() OVER (PARTITION BY VarId ORDER BY ColOrder) AS rn
    FROM (
        SELECT 
            ArtId, VarId, Sequence,
            Value,
            -- 定义列的顺序，确保填充顺序正确
            CASE ColName
                WHEN 'Val1' THEN 1
                WHEN 'Val2' THEN 2
                ...
                WHEN 'Val10' THEN 10
            END AS ColOrder
        FROM [Values]
        CROSS APPLY (
            VALUES 
                ('Val1', Val1), ('Val2', Val2), ..., ('Val10', Val10)
        ) AS CA(ColName, Value)
        WHERE Value IS NOT NULL AND Value <> ''
    ) AS UnpivotedData
) AS RankedValues
GROUP BY ArtId, VarId, Sequence;

-- 用SWITCH替换原表（需确保原表无外键、结构完全一致）
ALTER TABLE #TempCache SWITCH TO [Values];

优势：SWITCH是元数据层面的操作，耗时极短，避免长时间锁表影响业务。

4. 其他细节优化 #

• 开启SET NOCOUNT ON，减少不必要的网络传输开销
• 批量操作时添加TABLOCK提示，提升插入性能
• 原代码中INSERT INTO ... ORDER BY Sequence可去掉，若临时表已按Sequence建索引，插入时会自动有序

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