使用declare表变量执行聚合查询性能优于CTE及子查询的原因与优化建议

性能差异原因 #

SQL Server的查询优化器对CTE和子查询的默认处理逻辑是直接将其展开嵌入主查询的执行计划，这时候优化器需要一次性生成整个复杂查询的执行策略，很容易出现行数估算偏差：你这里同时存在聚合、多表关联、TOP N排序多个操作，优化器很可能错误判断了符合条件的用户量级，选错了关联顺序或者索引使用策略，导致AccessHistory的非聚集索引查找效率极低。

而表变量方案相当于把整个逻辑拆成了两个独立执行的步骤：

1. 第一步先将符合条件的用户及其最近更新日期预计算存入表变量，执行逻辑简单，效率很高
2. 第二步做表关联的时候，SQL Server会选择嵌套循环关联方式，用表变量里的每一行UserId作为键，直接到IX_AccessHistory_UserId_TimeStamp索引里精准查找大于对应日期的记录，该索引已经覆盖了查询需要的UserId、TimeStamp、Value三个字段，不需要回表查主数据，查找效率拉满。

是否可以直接使用表变量方案 #

完全可以。你的场景里UpdateHistory总数据量不到5000行，就算Param=1的用户量级达到几千条，表变量的存储和处理开销都可以忽略，且你实测性能比另外两种方案高5倍以上，直接用没有问题。

额外优化方案 #

如果你不想用表变量，也可以对CTE/子查询方案做优化，性能可以达到和表变量一致的水平：

• 在CTE方案的查询末尾加上OPTION (RECOMPILE)提示，让优化器执行时先拿到子查询的实际行数再生成执行计划，避免估算偏差
• 给UpdateHistory表新增非聚集索引IX_UpdateHistory_Param_UserId，键列为Param、UserId，包含UpdateDate字段，进一步提升预计算用户最近更新日期的效率，尤其是后续UpdateHistory数据量上涨后收益更明显
• 注意避免隐式类型转换：如果后续修改TimeStamp或TmpDate的字段类型，要确保两者比较时不会发生类型转换，否则会导致IX_AccessHistory_UserId_TimeStamp索引失效

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