关于T-SQL重编译那点事，内联函数和表值函数在编译生成执行计划的区别

 

本文出处：http://www.cnblogs.com/wy123/p/6266724.html 

 

最近在学习 WITH RECOMPILE和OPTION(RECOMPILE)在重编译上的区别的时候，无意中发现表值函数和内联表值函数编译生成执行计划的区别
下文中将会对此问题展开讨论。
简单地说就是：同样一句SQL，分别写成内联函数和表值函数，然后执行对Function的查询，发现其执行计划和执行计划缓存是不一样的，
根据某些测试的一些共同规律发现，内联函数的编译很有可能与Parameter Embedding Optimization 有关
关于Parameter Embedding Optimization，我在http://www.cnblogs.com/wy123/p/6262800.html写了一个案例
在发生Parameter Embedding Optimization做编译优化的时候，跟普通的编译优化机制还是有很大差异的。

 

概念解释:内联用户定义函数和表值用户定义函数

　　SQL Server中的表值函数分为“内联用户定义函数”和“表值用户定义函数”。

 

内联用户定义函数(Inline User-Defined Functions)：
　　不上MDSN上搬概念了，简单地说，内联函数的特点就是就是返回类型为table，返回的结果是一个查询语句
　　如下，dbo.fn_InlineFunction即为内联用户定义函数，当然后面要与表值用户定义函数作比较，就能看出来区别了

create function dbo.fn_InlineFunction
(
    @p_parameter     varchar(500)
)
returns table
as
return
(
    SELECT id,col2
    FROM [dbo].[TestTableValueFunction] 
    where ( col2  = @p_id or @p_id is null)      
)
GO

 

 

表值用户定义函数(Table-Valued User-Defined Functions),
　　与内联函数区别在于，表值用户定义函数返回的是一个表变量，在函数体中，通过赋值给这个表变量，然后返回表变量
　　如下dbo.fn_TableValuedFunction即为内联用户定义函数，

create function fn_TableValuedFunction
(
    @p_paramter      varchar(500)
)
RETURNS @Result TABLE
(
    id int ,
    value char(5000)
)
as
begin
    
    insert into @Result
    select id,col2
    from [dbo].[TestTableValueFunction] 
    where ( col2  = @p_id or @p_id is null)   
      
    return
end

　　熟悉sqlserver的同学可能已经知道这两者的区别了，关于内联用户定义函数和表值用户定义函数就先这么简单说一下区别
　　虽然内联函数和表值函数在功能上和使用上是有一些差异的，但是有一部分查询，用两种方式都可以实现，也就说两者在功能上有差异也有交集。

 

开始本文主题

 

同样的SQL语句，使用内联函数和使用表值函数查询生成执行计划的区别

　　按照惯例，先造一个测试表,char(500)的字段可以是的表以及索引占用空间变大，后面对比测试的效果变得更加明显。

create table TestTableValueFunction
(
    id int IDENTITY(1,1),
    col2 char(500)
)
GO

INSERT INTO TestTableValueFunction VALUES (NEWID())
GO 1000000

CREATE INDEX idx_col2 on TestTableValueFunction(col2)
GO

　　同样的查询条件下，分别用内联函数和表值函数查询，查看其性能

　　

　　首先使用内联函数的方式查询，用插入数据中的一条值做查询，最直观的方式去看SSMS的执行时间，显示为0秒，本机测试几乎是瞬间就出来结果了
　　可以看到执行计划走的是原始表TestTableValueFunction上idx_col2索引查找Index Seek

 

观察IO，发现发生了8次IO

　　使用表值函数的方式查询，使用上面同样的条件做查询，SSMS显式耗时4秒（本机测试的，可以忽略测试环境的外界影响因素）
　　但是使用表值函数无法直接观察查询的执行计划和IO信息，这两个信息后面从计划缓存中查询

　　 

　　其显示的IO信息应该也不是原始的SQL的IO，应该是表变量的IO，原始SQL语句的IO和执行计划信息暂时看不到，后面再说

　　 

 

为什么同样的查询，使用表值函数，性能差异居然有这么大？

　　对于表值函数，由于无法直接观察到其实际执行计划和IO信息，那么我们去查询其缓存的执行计划和IO信息
　　如下，sys.dm_exec_query_stats系统表中查询到其最近一次执行的IO信息，76997，远远大于上面的8次IO
　　查看缓存的执行计划

　　

　　从缓存中的执行计划可以看到，其执行计划为全表扫描

　　

　　到这里就有意思了，既然是一样的SQL，写成内联函数和表值函数，两者的执行计划不一样，
　　那么就可以推断出，SQL Server对内联函数和表值函数的编译处理方式是不一样的。
　　同时，上面的内联函数是可以知道看到实际执行计划的，显示为Index Seek，
　　但是在观察缓存计划的时候，是没有查到的，如下截图，也就是说内联函数dbo.fn_InlineFunction对应的SQL的执行计划是没有被缓存起来的
　　种种迹象不由的使我想到上一篇关于T-SQL重编译那点事中，OPTION(RECOMPILE)的The Parameter Embedding Optimization编译优化机制
　　从内联函数的SQL的执行计划发现，编译过程中是对SQL语句做植入参数优化+简化，又因为没有缓存执行计划，那么很有可能是发生了重编译
　　从个这两点来看，跟OPTION(RECOMPILE)强制重编译中的The Parameter Embedding Optimization编译优化机制基本上是吻合的

　　

　　回头再说表值函数为什么是全表扫描？参考下图，正常情况下这种查询逻辑就是走的全表扫描
　　只不过是内联函数里面，编译优化机制对这种写法做了专门的优化，才能走一个索引查找的方式。
　　这也正是内联函数和表值函数在编译上最大的区别之一。
　　对于为什么表值函数里面这种逻辑会在造成全表扫描在上一篇也解释了，这里就不啰嗦了。

如上，同样的T-SQL查询，在末尾加上OPTION(RECOMPILE)，执行计划也变成了Index Seek，跟内联函数的执行计划一致（都是index Seek），当然内联函数中是没有加OPTION(RECOMPILE)的

因此这里有理由怀疑，内联函数的编译，是类似等价于加了OPTION(RECOMPILE)的

　　之前只是了解过内联函数和表值函数在预估方面的区别（不过记得好像是SQL Server2012之后对表值函数的预估计算方式也做了更新），
　　 除此之外，从来没有注意到也没有考虑过两者在编译以及计划缓存方面的区别
　　 工作中见到过有人使用内联函数做复杂的查询，并且是查询条件是(col2 =@p_parameter or @p_parameter is null)这种方式
　　 如果是在存储过程中，这种方式是会抑制到索引的使用的，之前“理所当然地”认为，写成内联函数，肯定也会抑制索引的使用
　　不过从这个测试case来看，内联函数这种写法，确实可以正常使用索引

 

总结
　　本文通过一个简单的case，来演示了内联函数和表值函数在编译上的一些差别，优化器对内联函数进行专门的优化处理，而不会去对表值函数做特别的优化。
　　在对内联函数做特殊优化的时候，虽然没有明确执行强制重编译，但等效于存在类似于option(recompile)的基于sql语句级的强制重编译优化机制。