为什么使用变量调用SQL函数时执行耗时大幅增加？

• 基数估计偏差是核心原因
  直接传入字面量常量调用函数时，SQL查询优化器在编译执行计划阶段可以直接获取到参数的准确值，结合数据表的统计信息做精准的基数预估（比如预测查询返回行数、选择匹配的索引、选用最优的连接算法等），生成的执行计划完全适配当前参数的查询场景，执行效率更高。
  而使用局部变量传参时，SQL的批处理编译步骤发生在变量赋值操作之前，优化器编译时无法预知变量最终的赋值内容，只能基于统计信息的平均密度做模糊预估，很容易出现预估行数和实际行数偏差极大的问题：比如实际仅返回几十行数据，预估却返回上万行，优化器误选表扫描替代索引查找，或者选用了不适合当前数据量的哈希连接替代嵌套循环，最终导致执行效率大幅下降。
• 表值函数的特性放大了性能差异
  如果你使用的是多语句表值函数（MSTVF），这类函数本身的返回行数基数估计默认是固定值（旧版SQL Server默认预估为1行，2014及之后版本默认预估为100行），和实际返回行数偏差更大，结合变量传参的预估偏差问题，性能衰减会更明显。

• 给查询增加重编译提示，让执行计划在变量赋值完成后再生成，优化器可以拿到变量的实际值做精准优化，修改后的查询语句如下：

  SELECT * 
  FROM MyFunction(@PARAM1, @PARAM2, @PARAM3, @PARAM4, @PARAM5)
  OPTION (RECOMPILE)
  

• 如果参数取值相对固定，可以使用参数优化提示指定预估用的参数值，避免每次重编译的开销：

  SELECT * 
  FROM MyFunction(@PARAM1, @PARAM2, @PARAM3, @PARAM4, @PARAM5)
  OPTION (OPTIMIZE FOR (@PARAM1 = 'param1', @PARAM2 = 'param2', @PARAM3 = 'param3', @PARAM4 = 'param4', @PARAM5 = 'param5'))
  

• 条件允许的情况下，把多语句表值函数改写为内联表值函数，内联函数会被展开到主查询的执行计划中统一优化，基数估计准确性和执行效率都会有明显提升。

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