当前位置: 澳门新濠3559 > 数据库 > 正文

简单说一下各种写法的区别,RECOMPILE的存储过程

时间:2019-09-21 04:09来源:数据库
      0.参考文献 Table Scan, Index Scan, IndexSeek SQL SERVER – Index Seek vs. Index Scan – Diffefence and Usage – ASimpleNote oracle表访问方式 Index Seek和IndexScan的区别以及适用情况 本文出处:   本文出处

 

 

 

0.参考文献

Table Scan, Index Scan, Index Seek

SQL SERVER – Index Seek vs. Index Scan – Diffefence and Usage – A Simple Note

oracle表访问方式

Index Seek和Index Scan的区别以及适用情况

本文出处:  

本文出处: 

本文出处: 

1.oracle中的表访问方式

在oracle中有表访问方式的说法,访问表中的数据主要通过三种方式进行访问:

  1. 全表扫描(full table scan),直接访问数据页,查找满足条件的数据
  2. 通过rowid扫描(table access by rowid),如果知道数据的rowid,那么直接通过rowid进行查找
  3. 索引扫描(index scan),如果一个表创建了索引,那么可以通过索引来找出我们想要的数据在表中的存放位置,也就是rowid,通过返回rowid然后用rowid来进行访问具体数据。
  4. 而索引扫描中又可分为索引全扫描(index full scan)、索引范围扫描(index range scan)和索引唯一扫描(index unique scan)等。

 

 

 

2.sql server中clustered index scan,table scan,index scan

在sqlserver中也有类似的内容,这里就要将的是table scan,index scan以及index seek.

  1. table scan is where the table is processed row by row from beginning to end.
  2. An index scan is where the index is processed row by row from beginning to end.
  3. If the index is a clustered index then an index scan is really a table scan.
  4. 总结:在sql server中,对表中数据从头到尾一行一行的进行出来就是表扫描。这里的处理我们可以理解为sql中where子句的条件判断。我们需要遍历表中的每一行,判断是否满足where条件。最简单的table scan是select * from table。
  5. 索引扫描就是对索引中的每个节点从头到尾的访问。假设我们的索引是B树结构的,那么index scan就是访问B树中的每一个节点。
  6. 假如索引是聚集索引,那么B树索引的叶子节点保存的是数据页中的实际数据。假如索引是非聚集索引,那么B树叶子节点保存的是指向数据页的指针。

(ps:以下2.1-2.6于2012-9-4补充)

最近发现还有不少做开发的小伙伴,在写存储过程的时候,在参考已有的不同的写法时,往往很迷茫,
不知道各种写法孰优孰劣,该选用那种写法,以及各种写法优缺点,本文以一个简单的查询存储过程为例,简单说一下各种写法的区别,以及该用那种写法
专业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做编译优化的时候,跟普通的编译优化机制还是有很大差异的。

2.1实验数据准备

在介绍完clustered index scan,table scan和index scan以后,我们将通过实验来表述会在什么情况下使用这些表扫描方式。我们将使用AdventureWorks2008R2这个sample database进行实验,首先准备实验数据,TSQL如下所示:

澳门新濠3559 1

澳门新濠3559 2

--准备测试数据--------------------------------------------------
use adventureworks2008R2
go
--如果表已存在,删除
drop table dbo.SalesOrderHeader_test
go
drop table dbo.SalesOrderDetail_test
go
--创建表
select * into dbo.SalesOrderHeader_test
from Sales.SalesOrderHeader
go
select * into dbo.SalesOrderDetail_test
from Sales.SalesOrderDetail
go
--创建索引
create clustered index SalesOrderHeader_test_CL 
on dbo.SalesOrderHeader_test (SalesOrderID)
go
create index SalesOrderDetail_test_NCL
on dbo.SalesOrderDetail_test (SalesOrderID)
go

--select * from dbo.SalesOrderDetail_test
--select * from dbo.SalesOrderHeader_test 

declare @i int
set @i = 1
while @i<=9
begin
    insert into dbo.SalesOrderHeader_test
    (RevisionNumber, OrderDate, DueDate,
    ShipDate,Status, OnlineOrderFlag, SalesOrderNumber,PurchaseOrderNumber,
    AccountNumber, CustomerID, SalesPersonID, TerritoryID,
     BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID,
    CreditCardApprovalCode, CurrencyRateID, SubTotal,TaxAmt,
    Freight,TotalDue, Comment,rowguid,ModifiedDate)
    select RevisionNumber, OrderDate, DueDate,
    ShipDate,Status, OnlineOrderFlag, SalesOrderNumber,PurchaseOrderNumber,
    AccountNumber, CustomerID,SalesPersonID, TerritoryID,
     BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID,
    CreditCardApprovalCode, CurrencyRateID, SubTotal,TaxAmt,
    Freight,TotalDue, Comment,rowguid,ModifiedDate
    from dbo.SalesOrderHeader_test
    where SalesOrderID = 75123

    insert into dbo.SalesOrderDetail_test
    (SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID,
    SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,
    rowguid,ModifiedDate)
    select 75123 @i, CarrierTrackingNumber, OrderQty, ProductID,
    SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,
    rowguid, getdate()
    from Sales.SalesOrderDetail
    set @i = @i  1
end
go
--数据准备完毕--------------------------------

澳门新濠3559 3

 

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

 

2.2实验数据说明:

  1. dbo.SalesOrderHeader_test里存放的是每一张订单的头信息,包括订单创建日期、客户编号、合同编号、销售员编号等,每个订单都有一个单独的订单号。在订单号这个字段上,有一个聚集索引
  2. dbo.SalesOrderDetail_test里存放的是订单的详细内容。一张订单可以销售多个产品给同一个客户,所以dbo.SalesOrderHeader_test和dbo.SalesOrderDetail_test是一对多的关系。每条详细内容包括它所属的订单编号,它自己在表格里的唯一编号(SalesOrderDetailID)、产品编号、单价,以及销售数量等。在这里,先只在SalesOrderID上建立一个非聚集索引。create index默认创建的就是非聚集索引。
  3. 按照AdventureWorks里原先的数据,dbo.SalesOrderHeader_test里有3万多条订单信息,dbo.SalesOrderDetail里有12万多条订单详细记录,基本上一条订单有3~5条详细记录。这是一个正常的分布。为了使数据分布不均匀,我们再在dbo.SalesOrderHeader_test里加入9条订单记录,它们的编号是从75124到75132。这是9张特殊的订单,每张有12万多条详细记录。也就是说,dbo.SalesOrderDetail_test里会有90%的数据属于这9张订单。主要是使用“select 75123 @i...”来搜索出Sales.SalesOrderDetail中的所有记录插入到dbo.SalesOrderDetail。一共执行9次。

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

 

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

 2.3 table scan

sql server中表分为两种,一种是有聚集索引的聚集表,另外一种是没有聚集索引的对表。在聚集表中数据按照聚集索引有序存放,而对表则是无序存放在hash中的。以dbo.SalesOrderDetail_test为例,它的上面没有聚集索引,只有一个在SalesOrderID上的非聚集索引。所以表格的每一行记录,不会按照任何顺序,而是随意地存放在Hash里。此时我们找所有单价大于200的销售详细记录,要运行如下语句:

澳门新濠3559 4View Code

由于表格在UnitPrice上没有索引,所以SQL Server不得不对这个表格从头到尾扫描一遍,把所有UnitPrice的值大于200的记录一个一个挑出来,其过程如下图所示。

澳门新濠3559 5

从执行计划里可以清楚地看出来SQL Server这里做了一个表扫描,如下图所示:

澳门新濠3559 6

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

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

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

2.4 index scan 和 index seek

我们在SalesOrderID上创建了非聚集索引,加入查询条件是SalesOrderID,并且只SalesOrderID这一列的话,那么会以什么查询方式执行呢?首先我们查询SalesOrderID<43664的记录,执行如下TSQL语句:

select SalesOrderID from SalesOrderDetail_test where SalesOrderID< 43664

其执行计划如下图所示,我们发现执行的是index seek

澳门新濠3559 7

假如我们要查询所有SalesOrderID记录并且不加where条件,

select SalesOrderID from SalesOrderDetail_test

那么查询计划如下图所示,我们发现执行的是index scan

澳门新濠3559 8

那么假如我们要求查询所有SalesOrderID<80000的记录呢,是按照什么方式查询的。在执行查询之前晴空执行计划缓存

澳门新濠3559 9

DBCC DROPCLEANBUFFERS--清空执行计划缓存
DBCC FREEPROCCACHE--清空数据缓存
select SalesOrderID from SalesOrderDetail_test where SalesOrderID< 80000

其查询计划如下图所示,我们发现使用的是index seek

澳门新濠3559 10

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” 

 

2.5 clustered index scan

如果这个表格上有聚集索引,事情会怎样呢?还是以刚才那张表做例子,先给它在值是唯一的字段SalesOrderDetailID上建立一个聚集索引。这样所有的数据都会按照聚集索引的顺序存储。

澳门新濠3559 11View Code

可惜的是,查询条件UnitPrice上没有索引,所以SQL Server还是要把所有记录都扫描一遍。和刚才有区别的是,执行计划里的表扫描变成了聚集索引扫描(clustered index scan)。如下图所示:

澳门新濠3559 12

因为在有聚集索引的表格上,数据是直接存放在索引的最底层的,所以要扫描整个表格里的数据,就要把整个聚集索引扫描一遍。在这里,聚集索引扫描就相当于一个表扫描。所要用的时间和资源与表扫描没有什么差别。并不是说这里有了“Index”这个字样,就说明执行计划比表扫描的有多大进步。当然反过来讲,如果看到“Table Scan”的字样,就说明这个表格上没有聚集索引。

现在在UnitPrice上面建一个非聚集索引,看看情况会有什么变化。

澳门新濠3559 13

--在UnitPrice上创建非聚集索引
create index SalesOrderDetail_test_NCL_Price
on dbo.SalesOrderDetail_test (UnitPrice)
go

 在非聚集索引里,会为每条记录存储一份非聚集索引索引键的值和一份聚集索引索引键的值(在没有聚集索引的表格里,是RID值)。所以在这里,每条记录都会有一份UnitPrice和SalesOrderDetailID记录,按照UnitPrice的顺序存放。

再跑刚才那个查询,

select SalesOrderDetailID, UnitPrice from dbo.SalesOrderDetail_test where UnitPrice > 200

你会看到这次SQL Server不用扫描整个表了,如下图所示。这次查询将根据索引直接找到UnitPrice > 200的记录。

澳门新濠3559 14

根据新建的索引,它直接找到了符合记录的值,查询计划如下图所示。我们可以看到是直接在nonclustered index上进行index seek操作。

澳门新濠3559 15

澳门新濠3559 16

但是光用建立在UnitPrice上的索引不能告诉我们其他字段的值。如果在刚才那个查询里再增加几个字段返回,如下TSQL查询:

澳门新濠3559 17View Code

SQL Server就要先在非聚集索引上找到所有UnitPrice大于200的记录,然后再根据SalesOrderDetailID的值找到存储在聚集索引上的详细数据。这个过程可以称为“Bookmark Lookup”,如下图所示。

澳门新濠3559 18

在SQL Server 2005以后,Bookmark Lookup的动作用一个嵌套循环来完成。所以在执行计划里,可以看到SQL Server先seek了非聚集索引SalesOrderDetail_test_NCL_Price,然后用Clustered Index Seek把需要的行找出来。这里的嵌套循环其实就是Bookmark Lookup,如下图所示:

澳门新濠3559 19

澳门新濠3559 20

上述Key Lookup就是Bookmark Lookup中的一种,这是因为我们的表中建有聚集索引,如果我们没有聚集索引,那么这里就是RID Lookup,如下图所示:

澳门新濠3559 21

上述key lookup其所消耗的时间如下所示:

SQL Server Execution Times:
CPU time = 2995 ms, elapsed time = 10694 ms.
SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.

在上述查询中,之所以要使用with (index (SalesOrderDetail_test_NCL_Price))这个语句,是为了强制其使用SalesOrderDetail_test_NCL_Price这个非聚集索引,通过非聚集索引找到了聚集索引键值以后再去聚集索引中查询。如果不使用的话,sql server有可能会使用clustered index scan,也可能使用bookmark lookup,这取决于查询返回的数据量。

(1)比如还是查询UnitPrice > 200的结果:

select SalesOrderID,SalesOrderDetailID,UnitPrice from dbo.SalesOrderDetail_test where UnitPrice > 200

其查询计划如下,我们可以发现使用的是clustered index scan,返回的记录数有481590条,非常大。

澳门新濠3559 22

更重要的是其cpu time,如下所示:

SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 10063 ms.
SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.

我们发现cpu time只有515ms,比我们之前看到的2995ms要小。这就表明:index seek 并不一定就比index scan要好。sql server会根据统计信息选择更有的方式执行操作。

(2)假如查询UnitPrice <2的结果:

select SalesOrderID,SalesOrderDetailID,UnitPrice from dbo.SalesOrderDetail_test where UnitPrice < 2

我们发现查询计划就不再使用cluster index scan了,而是使用了index seek clustered index seek,如下图所示,返回记录数只有1630条。相对来说记录数目比较小,所以不需要clustered index scan。

澳门新濠3559 23

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

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即为内联用户定义函数,当然后面要与表值用户定义函数作比较,就能看出来区别了

 2.6总结

总结一下,在SQL Server里根据数据找寻目标的不同和方法不同,有下面几种情况。

 结  构

Scan

Seek

堆(没有聚集索引的表格数据页)

Table Scan

聚集索引

Clustered Index Scan

Clustered Index Seek

非聚集索引

Index Scan

Index Seek

如果在执行计划里看到这些动作,就应该能够知道SQL Server正在对哪种对象在做什么样的操作。table scan(表扫描)表明正在处理的表格没有聚集索引,SQL Server正在扫描整张表。clustered index scan(聚集索引扫描)表明SQL Server正在扫描一张有聚集索引的表格,但是也是整表扫描。Index Scan表明SQL Server正在扫描一个非聚集索引。由于非聚集索引上一般只会有一小部分字段,所以这里虽然也是扫描,但是代价会比整表扫描要小很多。Clustered Index Seek和Index Seek说明SQL Server正在利用索引结果检索目标数据。如果结果集只占表格总数据量的一小部分,Seek会比Scan便宜很多,索引就起到了提高性能的作用。如果查询结果集很多,那么可能会更倾向使用table scan。

 澳门新濠3559 24

 

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

3.Index Scan, Index Seek的比较

        Index Seek就是SQL在查询的时候利用建立的索引进行扫描,先扫描索引节点,即遍历索引树。在查找到索引的叶子节点后,如果是聚簇索引就直接取叶子节点值的值,如果是非聚簇索引,则根据叶子节点中的rowid去查找相应的行(聚集索引的叶子节点是数据页,而非聚集索引的叶子节点是指向数据页的索引页,也就是数据页的rowid,这是在表没有聚集索引的情况下发生的;如果表本身含有聚集索引,那么非聚集索引的叶子结点中保存的是非聚集索引键值和聚集索引键值,在得到聚集索引键值以后会再去聚集索引中查找。)。而对于Index Scan是从头到位遍历整个索引页中的所有行,从头到尾,因此在数据量很大时效率并不是很高,在聚集索引的情况下,clustered index scan就是table scan。

        SQL有一个查询优化分析器 Query Optimizer,其在执行查询之前首先会进行分析,当查询中有可以利用的索引时,那么就优先分析使用Index Seek进行查询的效率,假如得出使用Index Seek的查询效率并不好,那么就使用Index Scan进行查询。那究竟是在什么情况下会造成Index Seek效率比Index Scan还低呢?可以分一下集中情况:

         1.在要查询的表中数据并不是很多的情况下,使用Index Seek效率不一定高,因为使用Index seek还要先从索引树开始,然后再利用叶子节点去查找相应的行。在行数比较少的情况下,还没有直接进行Index scan快。因此,表中存储的数据不能太少。

          2.在返回的数据量很大的情况下,比如返回的数据量占总数据量的50%或者超过50%,使用Index Seek效率不一定好,在返回的数据量占10%-15%时,利用Index Seek能获得最佳的性能。因此假如要使用index seek,返回的数据量既不能太多,也不能太少。

          3.在建立索引的列的取值很多是一致的情况下,建立索引不一定能获得很好的效率。比如不建议在“性别”列上建立索引。其实理由很简单,当建立索引的列取值的变化少的情况下,建立的索引二叉树应该是矮胖型的,树层次不高,很多行的信息都包含在叶子上,这样的查询显然是不能很好的利用到索引

          MSDN原话:不要总是将索引的使用等同于良好的性能,或者将良好的性能等同于索引的高效使用。如果只要使用索引就能获得最佳性能,那查询优化器的工作就简单了。但事实上,不正确的索引选择并不能获得最佳性能。因此,查询优化器的任务是只在索引或索引组合能提高性能时才选择它,而在索引检索有碍性能时则避免使用它。

 

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

 

4.Sql server中的I/O

The I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory.

在sqlserver中I/O可以分为逻辑IO和物理IO,从缓存(buffer cache)中读取一个页(page)是逻辑读,如果数据页不在当前的缓存中,那么必须从磁盘上读取数据页到缓存中,这样算是物理读。

转:

 

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
  如果这样的话,两者在输入同样参数的情况下执行计划也应该是一样的,那么到底一样不一样呢?

 

澳门新濠3559, 

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

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

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

  澳门新濠3559 25

  按照惯例,先造一个测试表,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

 

  澳门新濠3559 26

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

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

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

  

澳门新濠3559 27

 

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

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

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

 澳门新濠3559 28

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

  

 

澳门新濠3559 29

  那么这种存储过程的有什么问题,或者直接一点说,这种方式有什么不好的地方

  原文中是这么解释的:
  The Parameter Embedding Optimization takes this process a step further: query parameters are replaced with literal constant values during query parsing.
  The parser is capable of surprisingly complex simplifications, and subsequent query optimization may refine things even further.
     翻译过来大概意思就是:
  相比WITH RECOMPILE这种强制重编译的方式,OPTION(RECOMPILE)中的Parameter Embedding Optimization机制更进一步,解析查询的过程中,参数值被字面常量所替代
  解析器神奇地把复杂的问题简化。至于怎么简化了,还是强烈建议参考原文,演示的案例相当吊。

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

    其一,绕不过转移符(以及注入问题)

 

   澳门新濠3559 30

       在拼凑字符串时,把所有的参数都当成字符串处理,当查询条件本身包含特殊字符的时候,比如 ' 符号,
       或者其他需要转义的字符时,你拼凑的SQL就被打断了
       举个不恰当的例子,比如字符串中 @p_CustomerId中包含 ' 符号,直接就把你拼SQL的节凑给打乱了
       拼凑的SQL就变成了这个样子了,语法就不通过,更别提执行

  至于怎么简化,这里大概做一下解释,原文中的解释更加详细和有趣。
  首先,SQL语句是这么写的:SELECT * FROM TestRecompile WHERE Id = @p_parameter OR @p_parameter IS NULL
  当“植入参数”之后,也即上文中使用的@p_parameter = 123456,SQL语句变成了SELECT * FROM TestRecompile WHERE Id = 12345 OR 12345 IS NULL
  因为OR 12345 IS NULL是永远不成立的,甚至可以认为是将SQL语句直接简化成了SELECT * FROM TestRecompile WHERE Id = 12345 ,这样子的话,在当前情况下,肯定是可以用到索引的。
  因此,OPTION(RECOMPILE)强制重编译的SQL在编译并且简化之后,就变成了如下的SQL,这里解释还是感觉有点牵强的,没有原文有说服力。

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

          SELECT [id]
              ,[OrderNumber]
              ,[CustomerId]
              ,[OrderDate]
              ,[Remark]
          FROM [dbo].[SaleOrder] 
          where 1=1  and CustomerId  = 'C'88' 

   澳门新濠3559 31

   澳门新濠3559 32

       一方面需要处理转移符,另一方面需要要防止SQL注入

  那么再回头看WITH RECOMPILE强制重编译,WITH RECOMPILE强制重编译的时候,没有能够做到OPTION(RECOMPILE)强制重编译中的“解析器神奇地把复杂的问题简化”
  参考这个链接:
  对于类似WHERE Id = @p_parameter OR @p_parameter IS NULL这种查询方式,

 

   其二,参数不同就必须重新编译
        这种拼凑SQL的方式,如果每次查询的参数不同,拼凑出来的SQL字符串也不一样,
        如果熟悉SQL Server的同学一定知道,只要你执行的SQL文本不一样,
        比如
        第一次是执行查询 *** where CustomerId = 'C88' ,
                   第二次是执行查询 *** where CustomerId = 'C99' ,因为两次执行的SQL文本不同
        每次执行之前必然需要对其进行编译,编译的话就需要CPU,内存资源
        如果存在大批量的SQL编译,无疑要消耗更多的CPU资源(当然也需要一些内存资源)

  上述文章中是这么解释的:
  The problem with these types of queries is that there is no stable plan.
  The optimal plan differs completely depending on what paramters are passed.
  The optimiser can tell that and it plays safe. It creates plans that will always work. That’s (one of the reasons) why in the first example it was an index scan, not an index seek.
  翻译过来大概意思就是:
  这种类型的查询问题在于没有固定的执行计划,
  优化方案是基于具体传入进来的参数值的,
  优化器只能做到保证安全性(plays safe),他创建的执行计划确保总是可以正常工作的。

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

 

 

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

 

  我这里补充解释一下 it plays safe在我的理解:
  如果@p_parameter 参数非空,走索引Seek完全没有问题。
    如果@p_parameter 为null,此时and (Id= @p_parameter or @p_parameter is null )这个条件恒成立,如果再走索引Seek会出现什么结果?
    如果继续采用Index Seek的方式执行,语义上变成了是查找Id为null的值,这样的话逻辑上已经错误了。
  因此出现这种写法,为了安全起见(上文所谓的plays safe),优化器只能选择一个这种的索引的扫描的方案(所谓的always work的执行计划)

  澳门新濠3559 33

第二种常见的写法:对所有查询条件用OR的方式加在where条件中,非常不推荐

  关于OPTION(RECOMPILE)在SQL语句级重编译神奇的魔力,他会根据具体的参数做到真正的重编译,我们在做一个测试:
  这一次设置@p_parameter = null,看看是不是又重新编译了一个合理的执行计划,没错,这次它生成了一个全表扫描的执行计划,也是没有问题的。
  唯一有瑕疵的地方时,相对WITH RECOMPILE强制重编译的方式,他的执行计划没有用到并行。这也是WITH RECOMPILE和OPTION(RECOMPILE)两种强制重编译生成执行计划的区别
  但是不能否认OPTION(RECOMPILE)强制重编译中的Parameter Embedding Optimization这种优化机制的特性

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

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

    set nocount on;

    declare @strSql nvarchar(max);

    SELECT [id]
            ,[OrderNumber]
            ,[CustomerId]
            ,[OrderDate]
            ,[Remark]
    FROM [dbo].[SaleOrder] 
    where 1=1
        and (@p_OrderNumber is null  or OrderNumber  = @p_OrderNumber)
        and (@p_CustomerId  is null  or CustomerId   = @p_CustomerId)
        /*
        这是另外一种类似的奇葩的写法,下面会重点关注
        and  OrderNumber  = ISNULL( @p_OrderNumber,OrderNumber)
        and  CustomerId   = ISNULL( @p_CustomerId,CustomerId)
        */
        and (@p_OrderDateBegin is null or OrderDate  >= @p_OrderDateBegin)
        and (@p_OrderDateEnd is null   or OrderDate  <= @p_OrderDateEnd)

end

  澳门新濠3559 34

  澳门新濠3559 35

首先看这种方式的执行结果,带入同样的参数,跟上面的结果一样,查询(结果)本身是没有任何问题的

  而此时WITH RECOMPILE强制重编译方式的执行计划,在传入参数值为null的时候,生成的是并行的执行计划

  到这里就有意思了,既然是一样的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编译优化机制基本上是吻合的

澳门新濠3559 36

  澳门新濠3559 37

  澳门新濠3559 38

  这种写法写起来避免了拼凑字符串的处理,看起来很简洁,写起来也很快,稀里哗啦一个存储过程就写好了,
  发布到生产环境之后就相当于埋了一颗雷,随时引爆。
  因为一条低效而又频繁执行的SQL,拖垮一台服务器也是司空见惯
  但是呢,问题非常多,也非常非常不推荐,甚至比第一种方式更糟糕。

 

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

  分析一下这种处理方式的逻辑:
  这种处理方式,因为不确定查询的时候到底有没有传入参数,也就数说不能确定某一个查询条件是否生效,
  于是就采用类似 and (@p_OrderNumber is null or OrderNumber = @p_OrderNumber)这种方式,来处理参数,
  这样的话
  如果@p_OrderNumber为null,or的前者(@p_OrderNumber is null)成立,后者不成立,查询条件不生效
  如果@p_OrderNumber为非null,or的后者(OrderNumber = @p_OrderNumber)成立而前者不成立,查询条件生效
  总之来说,不管参数是否为空,都可以有效地拼凑到查询条件中去。
  避免了拼SQL字符串,既做到让参数非空的时候生效,有做到参数为空的时候不生效,看起来不错,是真的吗?

现在来解释为什么非常强烈不建议写这种SQL:SELECT * FROM TestRecompile WHERE Id = @p_parameter OR @p_parameter IS NULL

澳门新濠3559 39

  那么这种存储过程的有什么问题?

  我在之前也写过,感觉没有彻底解释清楚索引抑制问题的原因。

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

    1,会抑制索引的情况

  开发中常见的一个潜在的多个条件的查询SQL,具体的查询条件是依赖于用户输入的,
  比如提供给用户三个查询条件可选的查询条件,用户可以输入一个,两个或者三个,这个太常见了,也不用再解释了
  那么我们就要构造出适应这种查询的一种方案
  面对这种catch-all-queries的查询方式,其中方案之一就是类似于这种写法
  SELECT * FROM TestRecompile
  WHERE (parameter1 = @p_parameter1 OR @p_parameter1 IS NULL)
    and (parameter2 = @p_parameter2 OR @p_parameter2 IS NULL)
    and (parameter3 = @p_parameter3 OR @p_parameter3 IS NULL)
  这种最大的问题就是在查询列上有索引,且查询列上接收到的输入参数非空的时候,是会抑制到索引的使用的
  上文中演示了,虽然用到了Id 列上的索引,采用的是INDEX SCAN,比全表扫描(TABLE SCAN)强一点点,他跟真正用到INDEX SEEK在效率上讲,完全是两码事,
  所以我们在开发的过程中强烈不建议使用 Id = @p_parameter OR @p_parameter IS NULL这种写法,
  当然,在不考虑parameter sinffing问题的时候,我们首选参数化动态SQL,即便是非参数化动态SQL(EXEC的方式执行一个拼凑出来的字符串),也比Id = @p_parameter OR @p_parameter IS NULL这种方式好
  如果有人进一步问:为什么查询条件中Id = @p_parameter OR @p_parameter IS NULL这种写法会抑制到索引的使用,真的是一个很难解释清楚的问题,解释不清楚也是一件很尴尬的事。
  这种逻辑之所以抑制到索引的最优化使用,真如上文分析的,优化器没有真正的用到INDEX SEEK,是为了安全起见(上文所谓的plays safe)考虑
  说道到这里我又开始凌乱了,也就是WITH RECOMPILE和OPTION(RECOMPILE)这两种方式的造强制,有一种只可意会不可言传的感觉。
  这就是即便是编译的过程中知道具体的参数值,也做到编译出来INDEX SEEK的执行计划的原因
  总是我在这里找到了跟我对该问题理解的相似的解释,也算释怀了

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

      如图,带入参数值执行存储过程,先忽略另外三个查询字段,只传入@p_CustomerId参数,
      相关查询列上(CustomerId)有索引,但是这里走的是CustomerId列上的Index Scan而非预期的Index Seek

 

澳门新濠3559 40

      澳门新濠3559 41

如果准确地预估表变量

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

 

  与with recompile相比,option(recompile)选项可以地预估表变量的行数,再次说明option(recompile)是基于参数置入(Parameter Embedding Optimization)编译的特性

 

      为什么说可能会抑制到索引的时候?上面提到过,SQL在执行之前是需要编译的,
      因为在编译的时候并不知道查询条件是否传入了值,有可能为null,有可能是一个具体的值

  直接看例子吧,应该是很清楚的

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

       纠错:上面的一句话,使用参数做编译的时候,是知道参数的值的(只有使用本地变量的时候才不知道具体的参数值,直接使用参数确实是知道的),
          编译也是根据具体的参数值来生成执行计划的,但是为什么即使知道具体的参数值的情况下,依然生成一个Index Scan的方式,而不是期望的Index Seek?
          即便是存储过程在编译的时候知道了参数的值,为什么仍旧用不到索引?
          还要从and (@p_CustomerId  is null  or CustomerId   = @p_CustomerId)这种写法入手分析。

  with recompile情况下对表变量的预估,

 

         即便是CustomerId列上有索引,
           如果@p_CustomerId  参数非空,走索引Seek完全没有问题。
                      如果@p_CustomerId  为null,此时and (@p_CustomerId  is null  or CustomerId   = @p_CustomerId)这个条件恒成立,如果再走索引Seek会出现什么结果?
                      语义上变成了是查找CustomerId  为null的值,如果采用Index Seek的方式执行,这样的话逻辑上已经错误了。
                    因此出现这种写法,为了安全起见,优化器只能选择一个索引的扫描(即便是字段上有索引的情况下)

  简单解释一下,就是在存储过程中,with recompile强制存储过程重编译的情况下,表变量参数join的时候,对表变量的预估情况如下

 

         可以认为是这种写法在语义支持不了相关索引的Seek,而索引的Scan是处理这种写法的一种安全的方式

  澳门新濠3559 42

   

         The optimiser can tell that and it plays safe. It creates plans that will always work.
         That’s (one of the reasons) why in the first example it was an index scan, not an index seek.

  option(recompile)强制存储过程重编译的情况下,表变量参数join的时候,对表变量的预估情况如下

        参考这里,可以简单地理解成这种写法,语义上支持不了索引的Seek,最多支持到index scan

  可见,option(recompile)强制重编译,不但可以获取与with recompile重编译不一样的执行计划,也可以非常准确地预估到表变量的行数

      至于(@p_CustomerId  is null or CustomerId  = @p_CustomerId  )这种写法遇到本地变量的时候,
      为什么抑制到到索引的使用,我之前也是没有弄清楚的,评论中10楼Uest 给出了解释,这里非常感谢Uest

  可以解决默认情况下,表变量总是预估为1行的情况(应该是sqlserver 2012之后有改善,sqlserver 2012之前默认预估为1行)

      如下

  澳门新濠3559 43

      澳门新濠3559 44

 

      如果我直接带入CustomerId=‘C88’,再来看执行计划,结果跟上面一样,但是执行计划是完全不一样的,这就是所谓的抑制到索引的使用。

 

      澳门新濠3559 45

 

   

 

   2,非常非常致命的逻辑错误

总结:本文通过一个简单的案例,解释了WITH RECOMPILE和OPTION(RECOMPILE)这种强制重编译方式的区别,以及引申出来的The Parameter Embedding Optimization(第一次听说)优化机制。
   很多时候,自己对一些知识只是想当然地去理解和使用,比如随意使用WITH RECOMPILE和OPTION(RECOMPILE),
   粗暴地认为这两种强制重编译的方式区别仅仅在于一个是存储过程级的重编译,一个是SQL语句级的重编译。真正拿着case测试的时候,才发现,还真不一样。

        /*
            这是另外一种类似的奇葩的写法,需要重点关注,真的就能满足“不管参数是否为空都满足”
            and  OrderNumber = ISNULL( @p_OrderNumber,OrderNumber)
            and  CustomerId  = ISNULL( @p_CustomerId,CustomerId)
            */

 

    对于如下这种写法:OrderNumber = ISNULL( @p_OrderNumber,OrderNumber),
    一部分人非常推崇,认为这种方式简单、清晰,我也是醉了,有可能产生非常严重的逻辑错误
    如果参数为null,就转换成这种语义 where 1=1 and OrderNumber = OrderNumber
    目的是查询参数为null,查询条件不生效,让这个查询条件恒成立,恒成立吗,不一定,某些情况下就会有严重的语义错误 

下一篇写一个跟这个机制类似的同样有意思的文章。

    博主发现这个问题也是因为某些实际系统中的bug,折腾了好久才发现这个严重的逻辑错误 

 

    对于这种写法,
    不管是第一点说的抑制索引的问题,数据量大的时候是非常严重的,上述写法会造成全表(索引)扫描,有索引也用不上,至于全表(索引)扫描的坏处就不说了
    还是第二点说的造成的逻辑错误,都是非常致命的
    所以这种方式是最最不推荐的。

参考资料:

 

     

 

       

第三种常见的写法:参数化SQL,推荐

     同时,再次感谢Uest同学提供的参考资料和指导建议。

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

       set nocount on;

      DECLARE @Parm         NVARCHAR(MAX) = N'',
              @sqlcommand   NVARCHAR(MAX) = N''

        SET @sqlcommand = 'SELECT [id]
                                  ,[OrderNumber]
                                  ,[CustomerId]
                                  ,[OrderDate]
                                  ,[Remark]
                            FROM [dbo].[SaleOrder] 
                            where 1=1 '

        IF(@p_OrderNumber IS NOT NULL)
            SET @sqlcommand = CONCAT(@sqlcommand,' AND OrderNumber= @p_OrderNumber')

        IF(@p_CustomerId IS NOT NULL)
            SET @sqlcommand = CONCAT(@sqlcommand,' AND CustomerId= @p_CustomerId')

        IF(@p_OrderDateBegin IS NOT NULL)
            SET @sqlcommand = CONCAT(@sqlcommand,' AND OrderDate>=@p_OrderDateBegin ')

        IF(@p_OrderDateEnd IS NOT NULL)
            SET @sqlcommand = CONCAT(@sqlcommand,' AND OrderDate<=@p_OrderDateEnd ')

        SET @Parm= '@p_OrderNumber        int,
                    @p_CustomerId        varchar(20),
                    @p_OrderDateBegin    datetime,
                    @p_OrderDateEnd        datetime '


        PRINT @sqlcommand
        EXEC sp_executesql @sqlcommand,@Parm,
                            @p_OrderNumber       =    @p_OrderNumber,
                            @p_CustomerId        =    @p_CustomerId,
                            @p_OrderDateBegin    =    @p_OrderDateBegin,
                            @p_OrderDateEnd      =    @p_OrderDateEnd 

end

 

首先我们用同样的参数来执行一下查询,当然没问题,结果跟上面是一样的。

  澳门新濠3559 46

所谓的参数化SQL,就是用变量当做占位符,通过 EXEC sp_executesql执行的时候将参数传递进去SQL中,在需要填入数值或数据的地方,使用参数 (Parameter) 来给值,
这样的话,

第一,既能避免第一种写法中的SQL注入问题(包括转移符的处理),
   因为参数是运行时传递进去SQL的,而不是编译时传递进去的,传递的参数是什么就按照什么执行,参数本身不参与编译
第二,保证执行计划的重用,因为使用占位符来拼凑SQL的,SQL参数的值不同并导致最终执行的SQL文本不同
   同上面,参数本身不参与编译,如果查询条件一样(SQL语句就一样),而参数不一样,并不会影响要编译的SQL文本信息
第三,还有就是避免了第二种情况(and (@p_CustomerId is null or CustomerId = @p_CustomerId)
   或者 and OrderNumber = ISNULL( @p_OrderNumber,OrderNumber))
    这种写法,查询条件有就是有,没有就是没有,不会丢给SQL查询引擎一个模棱两个的结果,
    避免了对索引的抑制行为,是一种比较好的处理查询条件的方式。

缺点,1,对于这种方式,也有一点不好的地方,就是拼凑的字符串处理过程中,
      调试具体的SQL语句的时候,参数是直接拼凑在SQL文本中的,不能直接执行,要手动将占位参数替换成具体的参数值

      2,可能存在parameter sniff问题,但是对于parameter sniff问题,不是否定参数化SQL的重点,当然解决parameter sniff问题的办法还是有的,

       参考:

 

总结:

  以上总结了三种在开发中比较常见的存储过程的写法,每种存储过程的写法可能在不同的公司都用应用,
  是不是有人挑个最简单最快捷(第二种)写法,写完不是完事了,而是埋雷了。
  不是太熟悉SQL Server的同学可能会有点迷茫,有很多种写法,究竟要用哪种写法这些写法之间有什么区别。
  本文通过一个简单的示例,说了常见的几种写法之间的区别,每种方式存在的问题,以及孰优孰劣,请小伙伴们明辨。
  数据库大神请无视,谢谢。

 

编辑:数据库 本文来源:简单说一下各种写法的区别,RECOMPILE的存储过程

关键词: 澳门新濠3559