测试环境SQL存储过程执行缓慢问题及WHILE循环性能优化咨询

问题背景 #

业务环境中菜单网格存在报错，创建副本流程无法完成。本地实现的解决方案运行正常，但部署至测试环境后页面加载异常缓慢，耗时约5分钟，核心问题出在新增的MapName唯一值生成逻辑上。

原存储过程中新增的循环逻辑如下：

DECLARE @UniqueMapName varchar(30) = @MapName  
DECLARE @Count int = 0   
--Check if MapName already exist ,if yes set to new names 
WHILE EXISTS(SELECT 1 FROM [KeyMap] WHERE MapName = @UniqueMapName)  
BEGIN  
    SET @Count = @Count + 1  
    SET @UniqueMapName = @MapName + '-' + CAST(@Count AS varchar(10))  
END  

WHILE循环的性能隐患 #

• 重复查询开销：每次循环都要执行全表（无索引时）扫描检查MapName是否存在，测试环境数据量远大于本地，循环次数越多，累计查询耗时呈线性增长。
• 并发冲突风险：高并发场景下，多个请求可能同时生成相同的@UniqueMapName，导致插入失败；极端情况下可能出现无限循环（多进程同时递增Count）。
• 字符串拼接额外消耗：循环中反复进行字符串拼接操作，虽单条开销小，但累计后会增加额外的CPU消耗。

优化方案 #

1. 给MapName字段添加非聚集索引 #

优先解决查询慢的核心问题，为KeyMap表的MapName字段创建非聚集索引，将每次存在性检查的时间复杂度从O(n)降至O(logn)：

CREATE NONCLUSTERED INDEX IX_KeyMap_MapName ON [dbo].[KeyMap] (MapName)

2. 一次性获取最大后缀，消除循环查询 #

无需逐次尝试递增，直接查询当前MapName对应的最大后缀值，一次性生成唯一名称，仅需一次查询即可完成：

DECLARE @UniqueMapName varchar(30) = @MapName
DECLARE @MaxCount int

-- 提取现有同名MapName的最大后缀数字
SELECT @MaxCount = ISNULL(MAX(
    CASE 
        WHEN MapName = @MapName THEN 0
        ELSE CAST(RIGHT(MapName, LEN(MapName) - LEN(@MapName) - 1) AS int)
    END
), 0)
FROM [KeyMap]
WHERE MapName LIKE @MapName + '%'

IF @MaxCount > 0
BEGIN
    SET @UniqueMapName = @MapName + '-' + CAST(@MaxCount + 1 AS varchar(10))
END

3. 事务+锁机制避免并发冲突 #

在高并发场景下，需保证查询后缀和插入操作的原子性，通过加锁防止多个请求生成重复名称：

BEGIN TRANSACTION

-- 加更新锁并保持到事务结束，避免并发冲突
SELECT @MaxCount = ISNULL(MAX(
    CASE 
        WHEN MapName = @MapName THEN 0
        ELSE CAST(RIGHT(MapName, LEN(MapName) - LEN(@MapName) - 1) AS int)
    END
), 0)
FROM [KeyMap] WITH (UPDLOCK, HOLDLOCK)
WHERE MapName LIKE @MapName + '%'

IF @MaxCount > 0
BEGIN
    SET @UniqueMapName = @MapName + '-' + CAST(@MaxCount + 1 AS varchar(10))
END

-- 执行插入操作
INSERT INTO [KeyMap]
(    
    [MealTypeId],    
    [MapName],    
    [MenuItemId],    
    [SubscriptionTerminalId],    
    [IsUsedForMealCombo],    
    [RegionId],    
    [DayofWeek],    
    [UserId],    
    [Rows],    
    [Columns],    
    [CreatedBy],    
    [CreatedOn]        
)    
VALUES    
(    
    @MealTypeId,    
    @UniqueMapName,    
    @MenuItemId,    
    @SubscriptionTerminalId,    
    @IsUsedForMealCombo,    
    @RegionId,    
    @DayofWeek,    
    @UserId,    
    @Rows,    
    @Columns,    
    @UserId,    
    GETUTCDATE()    
)    

SET @KeyMapId = SCOPE_IDENTITY()

COMMIT TRANSACTION

4. 改用全局唯一标识（可选） #

若业务逻辑允许，可直接使用GUID或结合自增ID生成唯一MapName，彻底避免重复检查逻辑：

-- 用GUID生成唯一名称
SET @UniqueMapName = @MapName + '-' + LEFT(CAST(NEWID() AS varchar(36)), 8)

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