在SQL开发中，循环处理是常见的需求，但While循环如果使用不当，会严重影响性能。今天我们来深入探讨如何正确使用While循环，以及如何优化循环操作。
一、While循环基础语法
DECLARE @counter INT = 1DECLARE @maxCount INT = 10
WHILE @counter <= @maxCountBEGIN PRINT '当前计数：' + CAST(@counter AS VARCHAR)
SET @counter = @counter + 1END
二、常见While循环场景及优化
场景1：批量插入数据（最常用）
CREATE TABLE #BadExample (ID INT, Name VARCHAR(50))DECLARE @i INT = 1
WHILE @i <= 100000BEGIN INSERT INTO #BadExample VALUES (@i, 'Name_' + CAST(@i AS VARCHAR)) SET @i = @i + 1END
CREATE TABLE #GoodExample (ID INT, Name VARCHAR(50))DECLARE @i INT = 1DECLARE @BatchSize INT = 1000DECLARE @MaxCount INT = 100000
WHILE @i <= @MaxCountBEGIN INSERT INTO #GoodExample (ID, Name) SELECT TOP (@BatchSize) @i + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS ID, 'Name_' + CAST(@i + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS VARCHAR) AS Name FROM sys.objects a, sys.objects b WHERE @i <= @MaxCount
SET @i = @i + @BatchSizeEND
CREATE TABLE #BestExample (ID INT, Name VARCHAR(50))
;WITH Numbers AS ( SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.object_id) AS n FROM sys.objects a CROSS JOIN sys.objects b)INSERT INTO #BestExample (ID, Name)SELECT n, 'Name_' + CAST(n AS VARCHAR)FROM Numbers
场景2：批量更新数据
DECLARE @id INTDECLARE cur CURSOR FOR SELECT ID FROM LargeTableOPEN curFETCH NEXT FROM cur INTO @id
WHILE @@FETCH_STATUS = 0BEGIN UPDATE LargeTable SET UpdateTime = GETDATE() WHERE ID = @id FETCH NEXT FROM cur INTO @idENDCLOSE curDEALLOCATE cur
DECLARE @BatchSize INT = 5000DECLARE @MinID INTDECLARE @MaxID INT
SELECT @MinID = MIN(ID), @MaxID = MAX(ID) FROM LargeTable
WHILE @MinID <= @MaxIDBEGIN UPDATE LargeTable SET UpdateTime = GETDATE() WHERE ID >= @MinID AND ID < @MinID + @BatchSize
SET @MinID = @MinID + @BatchSize
WAITFOR DELAY '00:00:00.100'END
UPDATE LargeTable SET UpdateTime = GETDATE()
场景3：分批删除大量数据
DELETE FROM LargeTable WHERE CreateDate < '2024-01-01'
DECLARE @BatchSize INT = 10000DECLARE @RowCount INT = 1
WHILE @RowCount > 0BEGIN DELETE TOP (@BatchSize) FROM LargeTable WHERE CreateDate < '2024-01-01'
SET @RowCount = @@ROWCOUNT
PRINT '已删除 ' + CAST(@@ROWCOUNT AS VARCHAR) + ' 条记录'
IF @RowCount > 0 AND @RowCount % 50000 = 0 CHECKPOINTEND
场景4：复杂业务逻辑处理
CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDate DATETIME, Status INT DEFAULT 0, ProcessTime DATETIME NULL)
INSERT INTO Orders (OrderID, OrderDate)SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.object_id), GETDATE()FROM sys.objects a, sys.objects b
CREATE PROCEDURE usp_ProcessOrdersASBEGIN DECLARE @BatchSize INT = 1000 DECLARE @ProcessedCount INT = 0
UPDATE Orders SET Status = 1 WHERE Status = 0 AND OrderDate < DATEADD(day, -7, GETDATE())
WHILE 1 = 1 BEGIN UPDATE TOP (@BatchSize) Orders SET Status = 2, ProcessTime = GETDATE() OUTPUT inserted.OrderID, inserted.OrderDate INTO #ProcessedLog (OrderID, OrderDate) WHERE Status = 1
SET @ProcessedCount = @ProcessedCount + @@ROWCOUNT
IF @@ROWCOUNT < @BatchSize BREAK
WAITFOR DELAY '00:00:00.500' END
INSERT INTO ProcessLog (ProcessDate, RecordCount) VALUES (GETDATE(), @ProcessedCount)
SELECT @ProcessedCount AS [处理记录数]END
三、While循环性能优化技巧
技巧1：避免循环内重复查询
DECLARE @i INT = 1WHILE @i <= 1000BEGIN IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'Table_' + CAST(@i AS VARCHAR)) BEGIN PRINT 'Table_' + CAST(@i AS VARCHAR) + ' 已存在' END SET @i = @i + 1END
DECLARE @i INT = 1DECLARE @Tables TABLE (TableName VARCHAR(100))INSERT INTO @Tables SELECT name FROM sys.objects WHERE name LIKE 'Table_%'
WHILE @i <= 1000BEGIN IF EXISTS (SELECT 1 FROM @Tables WHERE TableName = 'Table_' + CAST(@i AS VARCHAR)) BEGIN PRINT 'Table_' + CAST(@i AS VARCHAR) + ' 已存在' END SET @i = @i + 1END
技巧2：使用WHILE代替游标
DECLARE cur CURSOR FOR SELECT ID, Name FROM LargeTableOPEN curDECLARE @id INT, @name VARCHAR(100)FETCH NEXT FROM cur INTO @id, @nameWHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM cur INTO @id, @nameENDCLOSE curDEALLOCATE cur
CREATE TABLE #Data (ID INT PRIMARY KEY, Name VARCHAR(100))INSERT INTO #Data SELECT ID, Name FROM LargeTable
DECLARE @id INT, @name VARCHAR(100)
WHILE EXISTS (SELECT 1 FROM #Data)BEGIN SELECT TOP 1 @id = ID, @name = Name FROM #Data
DELETE FROM #Data WHERE ID = @idEND
技巧3：添加进度监控
CREATE PROCEDURE usp_BatchProcess @TotalCount INT, @BatchSize INT = 1000ASBEGIN DECLARE @CurrentCount INT = 0 DECLARE @BatchCount INT = 0 DECLARE @StartTime DATETIME = GETDATE()
WHILE @CurrentCount < @TotalCount BEGIN SET @BatchCount = @BatchCount + 1
SET @CurrentCount = @CurrentCount + @BatchSize
IF @BatchCount % 10 = 0 BEGIN DECLARE @Percent DECIMAL(5,2) = @CurrentCount * 100.0 / @TotalCount DECLARE @ElapsedSeconds INT = DATEDIFF(SECOND, @StartTime, GETDATE()) DECLARE @EstimateSeconds INT = @ElapsedSeconds * 100 / @Percent
PRINT CONVERT(VARCHAR, GETDATE(), 120) + ' - 进度：' + CAST(@Percent AS VARCHAR) + '%' + ' - 已处理：' + CAST(@CurrentCount AS VARCHAR) + ' - 预计剩余：' + CAST(@EstimateSeconds - @ElapsedSeconds AS VARCHAR) + '秒' END END
PRINT '处理完成，总耗时：' + CAST(DATEDIFF(SECOND, @StartTime, GETDATE()) AS VARCHAR) + '秒'END
四、While循环的适用场景与替代方案
场景决策树
需要循环处理？ ↓是否可以用集合操作？ → 是 → 使用UPDATE/INSERT/SELECT（最优） ↓ 否数据量是否<1000？ → 是 → While循环可接受 ↓ 否是否需要分批处理（防日志爆满）？ → 是 → While分批（推荐） ↓ 否是否需要复杂业务逻辑？ → 是 → While循环（可接受） ↓ 否→ 重新设计，用集合操作
替代方案对比
五、实战案例：订单状态批量更新
CREATE PROCEDURE usp_BatchUpdateOrderStatus @BatchSize INT = 5000, @MaxBatchCount INT = 100, @DelayMs INT = 100 ASBEGIN SET NOCOUNT ON
DECLARE @BatchCount INT = 0 DECLARE @ProcessedCount INT = 0 DECLARE @StartTime DATETIME = GETDATE()
CREATE TABLE #Progress ( BatchNo INT, ProcessedCount INT, BatchTime DATETIME, DurationMs INT )
WHILE @BatchCount < @MaxBatchCount BEGIN DECLARE @BatchStartTime DATETIME = GETDATE() SET @BatchCount = @BatchCount + 1
UPDATE TOP (@BatchSize) Orders SET Status = 1, ProcessDate = GETDATE() WHERE Status = 0 AND OrderDate < DATEADD(day, -30, GETDATE())
DECLARE @AffectedRows INT = @@ROWCOUNT SET @ProcessedCount = @ProcessedCount + @AffectedRows
INSERT INTO #Progress (BatchNo, ProcessedCount, BatchTime, DurationMs) VALUES ( @BatchCount, @AffectedRows, GETDATE(), DATEDIFF(ms, @BatchStartTime, GETDATE()) )
PRINT CONVERT(VARCHAR, GETDATE(), 120) + ' - 第' + CAST(@BatchCount AS VARCHAR) + '批' + ' - 处理：' + CAST(@AffectedRows AS VARCHAR) + '条' + ' - 累计：' + CAST(@ProcessedCount AS VARCHAR) + '条'
IF @AffectedRows < @BatchSize BREAK
IF @DelayMs > 0 AND @BatchCount < @MaxBatchCount WAITFOR DELAY '00:00:00.' + RIGHT('000' + CAST(@DelayMs AS VARCHAR), 3) END
DECLARE @TotalDuration INT = DATEDIFF(second, @StartTime, GETDATE())
PRINT '' PRINT '========== 处理完成 ==========' PRINT '总批次数：' + CAST(@BatchCount AS VARCHAR) PRINT '总处理数：' + CAST(@ProcessedCount AS VARCHAR) PRINT '总耗时：' + CAST(@TotalDuration AS VARCHAR) + '秒' PRINT '平均每批：' + CAST(@TotalDuration * 1000 / @BatchCount AS VARCHAR) + '毫秒'
SELECT BatchNo AS 批次, ProcessedCount AS 处理条数, BatchTime AS 处理时间, DurationMs AS 耗时毫秒 FROM #Progress ORDER BY BatchNo
DROP TABLE #ProgressEND
六、While循环性能对比测试
CREATE PROCEDURE usp_CompareWhilePerformanceASBEGIN SET NOCOUNT ON
IF OBJECT_ID('tempdb..#TestData') IS NOT NULL DROP TABLE #TestData CREATE TABLE #TestData (ID INT PRIMARY KEY, Value VARCHAR(100))
;WITH Numbers AS ( SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.object_id) AS n FROM sys.objects a, sys.objects b ) INSERT INTO #TestData (ID, Value) SELECT n, 'Value_' + CAST(n AS VARCHAR) FROM Numbers
PRINT '测试1：逐行更新' DECLARE @StartTime DATETIME = GETDATE() DECLARE @i INT = 1
WHILE @i <= 100000 BEGIN UPDATE #TestData SET Value = 'Updated_' + CAST(@i AS VARCHAR) WHERE ID = @i SET @i = @i + 1 END
PRINT '逐行更新耗时：' + CAST(DATEDIFF(ms, @StartTime, GETDATE()) AS VARCHAR) + 'ms'
UPDATE #TestData SET Value = 'Value_' + CAST(ID AS VARCHAR)
PRINT '测试2：分批更新（每批1000条）' SET @StartTime = GETDATE() SET @i = 1
WHILE @i <= 100000 BEGIN UPDATE #TestData SET Value = 'Batch_' + CAST(@i AS VARCHAR) WHERE ID BETWEEN @i AND @i + 999
SET @i = @i + 1000 END
PRINT '分批更新耗时：' + CAST(DATEDIFF(ms, @StartTime, GETDATE()) AS VARCHAR) + 'ms'
PRINT '测试3：一次性更新' SET @StartTime = GETDATE()
UPDATE #TestData SET Value = 'Set_Updated'
PRINT '一次性更新耗时：' + CAST(DATEDIFF(ms, @StartTime, GETDATE()) AS VARCHAR) + 'ms'END
EXEC usp_CompareWhilePerformance
七、最佳实践总结
✅ 推荐做法
优先考虑集合操作：能用一条SQL解决的，绝不用循环
分批处理大数据：使用WHILE + TOP/BETWEEN分批操作
添加进度监控：让运维人员了解执行进度
控制批次大小：根据数据量调整BatchSize（通常1000-10000）
添加延迟：WAITFOR避免长时间锁表
❌ 避免做法
循环内使用游标：游标 + WHILE = 性能灾难
循环内使用SELECT*FROM：重复查询浪费资源
无限制循环：忘记更新计数器导致死循环
循环内使用DISTINCT/ORDER BY：每次都排序
忽略事务日志：大事务不分批导致日志暴涨
总结
核心原则：能用集合不用循环，必须循环时控制批次和进度。
阅读原文：原文链接
该文章在 2026/4/15 18:19:39 编辑过
400 186 1886
