循环内与循环前声明变量的SQL性能差异探究

测试#1 #

我编写了如下SQL语句，通过游标遍历374条记录。经过十多次测试，该查询的执行时间稳定在3分40秒左右（误差几秒）。

测试#2 #

我修改了查询语句，将所有declare语句移至while循环之前，此时查询的完成时间始终快15-20秒。

我原本认为两者不会有明显差异，难道循环内的declare语句会带来如此大的开销，还是另有原因？

两次测试中游标处理的记录完全相同，我按测试1、测试2、测试1、测试2的顺序交替执行，测试2的执行速度至少比测试1快8秒。

我确信并非declare语句导致该问题，但无法解释测试2始终更快的原因。

另外需要说明的是，测试期间数据库无其他连接。

测试#1的SQL代码 #

declare @fillfactor int = 100


declare tableCursor CURSOR for
select  table_catalog, table_schema, table_name
from    information_schema.tables
where   table_type = 'BASE TABLE'
order   by table_schema, table_name

open tableCursor

declare @db varchar(128), @schema varchar(128), @table varchar(128)

fetch next from tableCursor into @db, @schema, @table

declare @cmd nvarchar(2000)
declare @lastLogged datetime = getdate()
declare @msg as varchar(500)
declare @startDate datetime = getdate()
declare @processed int = 0

while @@FETCH_STATUS = 0
begin
    declare @tableQualified varchar(200) = concat('[', @db, '].[', @schema, '].[', @table, ']')

    set @cmd = 'ALTER INDEX ALL ON ' + @tableQualified + ' REBUILD WITH (FILLFACTOR = 100)'
    exec (@cmd)

    fetch next from tableCursor into @db, @schema, @table

    set @processed += 1

    declare @now datetime = getdate()

    if datediff(second, @lastLogged, @now) > 30
    begin
        declare @elapsedSec int = datediff(second, @startDate, @now)
        declare @mins int = @elapsedSec / 60
        declare @seconds int = @elapsedSec % 60

        set @msg = concat(@processed, ' tables processed in ', @mins, 'm ', @seconds, 's')
        raiserror (@msg, 10, 1) with nowait
        
        set @lastLogged = getdate()
    end
END

核心原因：循环内DECLARE的累积隐性开销 #

你觉得单条DECLARE开销不大是对的，但在循环内执行时，SQL Server并非只做变量声明——每次循环都会重新初始化变量，还会触发执行上下文的频繁重建，甚至可能导致语句级的重复编译。单次操作开销微乎其微，但374次循环累积后，就会形成可感知的性能差距。

其他隐性影响因素 #

1. 缓存复用差异：测试2中提前声明所有变量，SQL Server能稳定复用执行上下文和缓存计划；测试1循环内的DECLARE会打破缓存连续性，每次循环都要重新处理变量的内存绑定与分配。
2. 编译稳定性：循环内的变量声明可能干扰优化器的计划生成，尤其是搭配exec(@cmd)这类动态SQL时，提前声明变量能让优化器生成更稳定的最优计划，避免反复重编译。
3. 内存分配效率：一次性声明所有变量，SQL Server在执行初期就能完成内存分配；循环内声明则会反复申请、释放小块内存，累积的内存操作开销不可忽视。

验证建议 #

如果想彻底确认，可以做个空跑测试：保留循环内的DECLARE，但注释掉ALTER INDEX的执行逻辑，只运行循环体。如果此时测试1依然比测试2慢，就能实锤是循环内DECLARE的累积开销导致的差异。

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