SQL Server表值函数因DATEDIFF导致性能异常下降问题排查

我来帮你拆解这个问题，这种性能突变通常和SQL Server的查询优化逻辑、函数执行模式以及数据分布有关，咱们一步步理清楚：

1. qryLMPDATE的执行模式是核心痛点 #

你提到qryLMPDATE返回单行数据，但如果它是多语句表值函数（MSUDF）而非内联表值函数（ITVF），问题就会凸显：SQL Server会把多语句函数当成独立的执行单元，无法将其逻辑与外层查询合并优化。当你把它和DATEDIFF结合到函数中时，意味着每处理一行数据，都会单独调用一次qryLMPDATE——如果你的查询返回几万甚至几十万行，成百上千次的函数调用叠加起来，开销会非常恐怖。

而当你注释掉那行代码后，SQL Server无需再逐行调用qryLMPDATE，自然能快速返回结果。

2. 确定性函数对查询计划的影响 #

SQL Server对确定性函数的优化力度远高于非确定性函数。如果CONVERT(INT, DATEDIFF(...))的计算依赖qryLMPDATE的结果，而qryLMPDATE本身是非确定性的（比如依赖系统时间、未明确排序的结果等），SQL Server无法提前计算这个表达式的值，只能逐行计算。这会导致它无法利用原本可用的索引（比如valdate上的索引），被迫走全表扫描或低效的嵌套循环。

你猜测的「2018-02-28时lmpdate与valdate相等」确实是关键影响点：当两者相等时，DATEDIFF(day, lmpdate, valdate)返回0，这个固定值会被SQL Server优化成常量，无需逐行计算；但2018-03-31的数据中，lmpdate和valdate大多不相等，表达式的值每行都变，SQL Server只能硬着头皮逐行计算，性能自然暴跌。

3. 数据分布放大了性能差异 #

2018-03-31的数据量可能远大于2018-02-28，或者这部分数据的索引碎片更多，再加上逐行计算表达式的开销，就会导致耗时从7秒飙升到1分钟以上——数据量越大，逐行计算的累积开销就越明显。

解决建议 #

• 把qryLMPDATE改成内联表值函数：内联函数的逻辑会被SQL Server展开成查询的一部分（类似视图），能和外层查询一起优化，避免重复调用。如果它现在是多语句函数，改成内联形式（用RETURN (SELECT ...)直接返回结果）会大幅降低开销。
• 提前固化qryLMPDATE的结果：在函数外部先把qryLMPDATE的结果存到变量里，再在函数中使用这个变量计算DATEDIFF。比如：

  DECLARE @targetLmpDate DATE;
  SELECT @targetLmpDate = lmpdate FROM qryLMPDATE;
  
  -- 然后在你的表值函数里用@targetLmpDate代替qryLMPDATE的调用
  SELECT 
      -- 其他列
      CONVERT(INT, DATEDIFF(day, @targetLmpDate, valdate)) AS DiffDays
  FROM 
      你的表
  WHERE 
      valdate = '2018-03-31';
  

  这样qryLMPDATE只被调用一次，而非每行都调用。
• 检查查询计划：用SSMS查看两种情况下的查询计划，重点关注是否存在全表扫描、高开销嵌套循环，或者「表值函数执行」的操作。如果是索引问题，可以考虑添加覆盖索引，或者把CONVERT(INT, DATEDIFF(...))做成持久化计算列并建立索引，让SQL Server直接利用索引获取结果。
• 验证函数的确定性：执行SELECT OBJECTPROPERTY(OBJECT_ID('你的表值函数名'), 'IsDeterministic')，如果返回0，说明函数是非确定性的，要排查哪些操作导致了非确定性（比如qryLMPDATE的逻辑、未明确的排序等），尽量改成确定性的，这样SQL Server能更好地优化。

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