SQL Server如何提升存储过程中临时表关联的查询性能

核心问题分析 #

你的代码运行慢的核心原因有3个：

• 表变量无索引、行数估计错误导致关联查询效率极低
• 旧版FOR XML PATH字符串拼接方式性能差，额外嵌套查询增加冗余开销
• 原始表查询条件用前导通配符LIKE、无合适索引导致全表扫描

具体优化措施 #

1. 替换表变量为带索引的临时表 #

表变量@installs默认无索引，且SQL Server优化器对其行数估计通常为1，很容易生成错误的执行计划。替换为临时表后增加UserName字段的非聚集索引，关联查询时可以直接走索引匹配。

2. 替换低效的字符串拼接逻辑 #

如果你使用的是SQL Server 2017及以上版本，直接使用内置的STRING_AGG聚合函数完成字符串拼接，比FOR XML PATH方式性能提升3~10倍，且不需要手动处理末尾逗号、不需要嵌套多层子查询。
如果是SQL Server 2016及更低版本，保留FOR XML PATH逻辑但去掉冗余的DISTINCT和多余嵌套，同时增加TYPE配置避免特殊字符转义，也能大幅减少开销。

3. 优化原始表查询性能 #

• 原查询中[Device Type] not like '%Server%'使用前导通配符，无法走普通索引，建议根据业务实际枚举的设备类型改为[Device Type] NOT IN ('服务器类型1','服务器类型2')的写法，可匹配普通索引
• 给原始表[DataCollection].[dbo].[iBoss_Installed_Users]建立覆盖索引，索引字段顺序为([Device Type], Username, [Device Name]) INCLUDE ([First Seen])，查询时直接走索引无需回表。

优化后代码示例（适用SQL Server 2017+） #

-- 改用临时表
CREATE TABLE #installs
(
    UserName varchar(200),
    DeviceName varchar(500),
    FirstSeenDate datetime
)
-- 给关联字段建索引
CREATE NONCLUSTERED INDEX IX_#installs_UserName ON #installs(UserName)

INSERT INTO #installs
SELECT  [Username] 
      ,[Device Name]
      ,min([First Seen]) as 'Install Date'
  FROM [DataCollection].[dbo].[iBoss_Installed_Users]
  -- 建议替换为NOT IN写法匹配索引
  where [Device Type] not like '%Server%'
  group by [Device Name], Username

-- 直接用STRING_AGG聚合，无需额外临时表
select 
    UserName,
    STRING_AGG(convert(varchar(200),FirstSeenDate), ', ') as InstalledDate
from #installs
group by UserName

DROP TABLE #installs

优化后代码示例（适用SQL Server 2016及更低版本） #

CREATE TABLE #installs
(
    UserName varchar(200),
    DeviceName varchar(500),
    FirstSeenDate datetime
)
CREATE NONCLUSTERED INDEX IX_#installs_UserName ON #installs(UserName)

INSERT INTO #installs
SELECT  [Username] 
      ,[Device Name]
      ,min([First Seen]) as 'Install Date'
  FROM [DataCollection].[dbo].[iBoss_Installed_Users]
  where [Device Type] not like '%Server%'
  group by [Device Name], Username

-- 去掉冗余嵌套和DISTINCT，增加TYPE处理特殊字符转义
select 
    ins2.UserName,
    left(
        (
            select convert(varchar(200),ins.FirstSeenDate)+', ' as [text()]
            from #installs ins
            where ins.UserName=ins2.UserName
            for XML PATH(''), TYPE
        ).value('.','varchar(max)'),
    len((select convert(varchar(200),ins.FirstSeenDate)+', ' as [text()] from #installs ins where ins.UserName=ins2.UserName for XML PATH(''), TYPE).value('.','varchar(max)'))-1) as InstalledDate
from #installs ins2
group by ins2.UserName

DROP TABLE #installs

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