MS SQL Server JOIN中计算与CTE后计算的性能差异咨询

你提出的调整方案对性能会有明确的正向提升，针对1.74亿行的主表规模，这部分逻辑的耗时预计可以降低40%~60%，核心原因如下：

• 原逻辑存在两次全量数据扫描：第一次是关联table1和table2，写入临时表#temptable1，需要全量遍历1.74亿行主表数据；第二次是读取#temptable1全量数据计算datecalc字段，相当于再扫一次上亿行的临时表，额外产生了大量临时表IO和CPU计算开销。而且由于临时表是物化存储的结构，SQL Server优化器不会自动把临时表读写和后续CTE的计算逻辑合并，两次扫描是必然执行的。
• 调整后的逻辑仅需要一次全量扫描：关联表的同时直接完成datecalc的计算，写入临时表的步骤就把计算结果落地，后续仅需要读取需要的字段输出即可，省去了一次上亿行数据的读写和遍历开销。
• 额外补充：你贴出的原逻辑实际上存在语法错误，CTE中仅引用了临时表别名c1，但CASE判断中直接使用了原表别名t1，实际执行会报错，调整后的写法也同时修复了这个语法问题。

如果这部分是总耗时5小时的瓶颈，还可以通过以下方案进一步优化：

• 精简临时表字段：如果后续业务逻辑不需要用到column3，就不要把它写入#temptable1，减少临时表的写入数据量，降低IO开销。
• 优化datecalc计算逻辑：原有的闰年判断逻辑可以替换为更高效的系统函数判断，例如用ISDATE(DATEFROMPARTS(t1.CALENDAR_YEAR,2,29)) = 1来判断是否为闰年，执行效率比多次取模运算更高，也更易维护。
• 持久化计算列：如果这个datecalc的计算逻辑是业务高频使用的，直接在table1上创建持久化的计算列，把计算结果提前存储为物理列，查询时不需要实时计算，能省掉1.74亿行实时计算的CPU开销。
• 检查关联索引：确认table1和table2的关联列column1上存在合适的索引，1.74亿行表的关联如果走无索引的hash join，开销会非常高，加上覆盖索引可以大幅降低关联耗时。
• 去掉不必要的临时表：如果#temptable1不需要给后续其他业务逻辑复用，可以直接去掉INTO #temptable1的步骤，一次SELECT直接输出需要的字段，连临时表的读写开销都可以完全省略。

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