澳门金沙vip 6

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

 

 

 

本文出处:  

本文出处: 

本文出处: 

 

 

 

最近发现还有不少做开发的小伙伴,在写存储过程的时候,在参考已有的不同的写法时,往往很迷茫,
不知道各种写法孰优孰劣,该选用那种写法,以及各种写法优缺点,本文以一个简单的查询存储过程为例,简单说一下各种写法的区别,以及该用那种写法
专业DBA以及熟悉数据库的同学请无视。

 
  在考虑重编译T-SQL(或者存储过程)的时候,有两种方式可以实现强制重编译(前提是忽略导致重编译的其他因素的情况下,比如重建索引,更新统计信息等等),
  一是基于WITH
RECOMPILE的存储过程级别重编译,另外一种是基于OPTION(RECOMPILE)的语句级重编译。
  之前了解的比较浅,仅仅认为是前者就是编译整个存储过程中的所有的语句,后者是重编译存储过程中的某一个语句,也没有追究到底是不是仅仅只有这么一点区别。
  事实上在某些特定情况下,两者的区别并非仅仅是存储过程级重编译和语句级重编译的区别,
  从编译生成的执行计划来看,这两种强制编译的方式内在机制差异还是比较大的。
  这里同时引申出来另外一个问题:The Parameter Embedding
Optimization(怎么翻译?也没有中文资料中提到The Parameter Embedding
Optimization,勉强翻译为“参数植入优化”)

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

 

  本文通过一个简单的示例来说明这两者的区别(测试环境为SQL
Server2014)。这里首先感谢UEST同学提供的参考资料和指导建议。

 

废话不多,上代码说明,先造一个测试表待用,简单说明一下这个表的情况

 

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

类似订单表,订单表有订单ID,客户ID,订单创建时间等,查询条件是常用的订单ID,客户ID,以及订单创建时间

WITH RECOMPILE 和 OPTION(RECOMPILE)使用上的区别

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

create table SaleOrder
(
    id       int identity(1,1),
    OrderNumber  int         ,
    CustomerId   varchar(20)      ,
    OrderDate    datetime         ,
    Remark       varchar(200)
)
GO
declare @i int=0
while @i<100000
begin
    insert into SaleOrder values (@i,CONCAT('C',cast(RAND()*1000 as int)),GETDATE()-RAND()*100,NEWID())
    set @i=@i+1
end
create index idx_OrderNumber on SaleOrder(OrderNumber)
create index idx_CustomerId on SaleOrder(CustomerId)
create index idx_OrderDate on SaleOrder(OrderDate)

  关于存储过程级别的重编译,典型用法如下,在存储过程参数之后指定“WITH
RECOMPILE” 

 

 生成的测试数据大概就是这个样子的

CREATE PROCEDURE TestRecompile_WithRecompile
(
    @p_parameter int
)WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM TestRecompile WHERE Id = @p_parameter OR @p_parameter IS NULL
END
GO

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

 澳门金沙vip 1

 

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

 

  关于语句级重编译,典型用法如下,在某一条SQL语句的末尾指定OPTION(RECOMPILE)

 

 

CREATE PROCEDURE TestRecompile_OptionRecompile
(
    @p_parameter VARCHAR(50)
)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM TestRecompile WHERE Id = @p_parameter OR @p_parameter IS NULL OPTION(RECOMPILE)
END
GO

 

 

 

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

下面演示说明几种常见的写法以及每种写法潜在的问题

  按照惯例,先搭建一个测试环境
  创建一张TestRecompile的表,也即上面存储过程中用到的表,插入100W行数据,Id字段上建立一个名字为idx_id的索引

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

 

CREATE TABLE TestRecompile
(
    Id int,
    Value varchar(50)
)
GO

DECLARE @i int = 0
WHILE @i<=1000000
BEGIN
    INSERT INTO TestRecompile VALUES (@i,NEWID())
    SET @i = @i+1
END


CREATE INDEX idx_Id ON TestRecompile(Id)
GO

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

 

 

 

第一种常见的写法:拼凑字符串,用EXEC的方式执行这个拼凑出来的字符串,不推荐

WITH RECOMPILE 和 OPTION(RECOMPILE)使用时重编译生成的执行计划的异同

开始本文主题

create proc pr_getOrederInfo_1
(
    @p_OrderNumber       int      ,
    @p_CustomerId        varchar(20) ,
    @p_OrderDateBegin    datetime   ,
    @p_OrderDateEnd      datetime
)
as
begin

    set nocount on;
    declare @strSql nvarchar(max);
    set @strSql= 'SELECT [id]
               ,[OrderNumber]
               ,[CustomerId]
               ,[OrderDate]
               ,[Remark]
            FROM [dbo].[SaleOrder] where 1=1 ';
    /*
        这种写法的特点在于将查询SQL拼凑成一个字符串,最后以EXEC的方式执行这个SQL字符串
    */

    if(@p_OrderNumber is not null)
        set @strSql = @strSql + ' and OrderNumber = ' + @p_OrderNumber
    if(@p_CustomerId is not null)
        set @strSql = @strSql + ' and CustomerId  = '+ ''''+ @p_CustomerId + ''''
    if(@p_OrderDateBegin is not null)
        set @strSql = @strSql + ' and OrderDate >= ' + '''' + cast(@p_OrderDateBegin as varchar(10)) + ''''
    if(@p_OrderDateEnd is not null)
        set @strSql = @strSql + ' and OrderDate <= ' + '''' + cast(@p_OrderDateEnd as varchar(10)) + ''''

    print @strSql
    exec(@strSql);

end

  如果说With Recompile存储过程级的重编译和Option
Recompile的SQL语句级的重编译效果是一样的话,
  由上面的存储过程可知,存储过程中仅仅只有一句SQL代码,那么存储过程级别的重编译和SQL语句级别的重编译都是编译这一句SQL
  如果这样的话,两者在输入同样参数的情况下执行计划也应该是一样的,那么到底一样不一样呢?

 

 

  首先来看TestRecompile_WithRecompile这个存储过程的执行计划,可以看到是一个索引扫描(INDEX
SCAN)

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

  假如我们查询CustomerId为88,在2016-10-1至2016-10-3这段时间内的订单信息,如下,带入参数执行

  澳门金沙vip 2

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

exec pr_getOrederInfo_1
    @p_OrderNumber      = null      ,
    @p_CustomerId       = 'C88'     ,
    @p_OrderDateBegin   = '2016-10-1' ,
    @p_OrderDateEnd     = '2016-10-3'

  然后再来看TestRecompile_OptionRecompile的执行计划,带入同样的参数

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

 

  澳门金沙vip 3

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

  首先说明,这种方式执行查询是完全没有问题的如下截图,结果也查出来了(当然结果也是没问题的)

  至此,可以看出,虽然都用到索引,很明显第一个语句是索引扫描(INDEX
SCAN),第二个语句是索引查找(INDEX SEEK)
  可以证明:在存储过程级指定 WITH RECOMPILE 强制重编译
和SQL语句级指定的OPTION(RECOMPILE)强制重编译,相同条件下生成的执行计划是不一样的。

  

澳门金沙vip 4

 

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

我们把执行的SQL打印出来,执行的SQL语句本身就是就是存储过程中拼凑出来的字符串,这么一个查询SQL字符串

为什么WITH RECOMPILE强制重编译 和
OPTION(RECOMPILE)强制重编译得到的执行计划是不一样的?

 澳门金沙vip 5

SELECT [id]
    ,[OrderNumber]
    ,[CustomerId]
    ,[OrderDate]
    ,[Remark]
FROM [dbo].[SaleOrder] 
where 1=1  
    and CustomerId  = 'C88' 
    and OrderDate >= '2016-10-1' 
    and OrderDate <= '2016-10-3'

  WITH
RECOMPILE强制重编译是每次运行存储过程,都根据当前的参数情况做一次重编译,
  首先我们暂时先不纠结为什么第一种方法用不到索引查找(INDEX的SEEK)。
  事实上正式因为使用了Id = @p_parameter OR @p_parameter IS
NULL这种写法导致的,具体我后面做解释。
  那么对于OPTION(RECOMPILE)强制重编译存储过程中同样写法的SQL语句,为什么有能用到索引了呢?
    因为在用OPTION(RECOMPILE)强制重编译的时候,这里涉及到一个“Parameter
Embedding Optimization”编译问题,
  事实上我之前也没有听说过这个名词,直译过来就是“参数植入编译”(不知道恰不恰当)
    OPTION(RECOMPILE)强制重编译在一定程度上增强和优化重编译的效果,
  参考这里:,文章中分析的极度牛逼,案例也非常精彩

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

  

 

澳门金沙vip 6