sql server 即席查询(ad hoc query)与预定义语句(prepared statement)

在SQL Server中预定义语句（Prepared Statement）与即席查询（Ad Hoc Query）是啥区别呢？

其实官方文档甚至没有一个非常明确的定义说明，像Oracle、MySQL等其它数据库，也没有即席查询这类概念。下面简单总结一下即席查询跟预定义语句。

 

即席查询（Ad Hoc Query）

 

（1）什么是即席查询（Ad Hoc Query）呢？

　　以单独的SQL语句的形式执行的查询就是即席查询，一般这样的SQL是硬编码形式，动态执行的。

　　例如，你在SSMS中查询一个表中数据的SQL，但是存储过程或函数中同样的这么一个SELECT语句，就不叫即席查询了，而是存储过程/函数中的语句。

　　即席查询有个特点就是SQL语句必须一模一样，才能重用缓存的执行计划。

关于即席查询的一些英文介绍（都是非官方资料）

An Ad-Hoc Query is a query that cannot be determined prior to the moment the query is issued. 
It is created in order to get information when need arises and it consists of dynamically constructed SQL which is usually constructed by desktop-resident query tools.

An Ad-Hoc Query is hard coded or the dynamically executed, and the cached plan can only be re-used for a near identical statement.

（2）即席查询包含的形式

1、 使用常量的查询

SELECT * FROM Person.Person WHERE BusinessEntityID=10;

2、 另外，你在程序里面，拼接而成的SQL也是即席查询。
3、 sql server中使用变量也是即席

DECLARE @BusinessEntityID INT;
SET @BusinessEntityID=12;
SELECT * FROM Person.Person WHERE BusinessEntityID=@BusinessEntityID;

预定义语句（Prepared Statement）

　　预定义语句指SQL语句中使用了占位符替换了实际值的SQL语句，例如，像参数化查询这类SQL等等。

　　这类语句可以重用，通过输入不同的值，完成不同的操作。这个有点类似Oracle中使用绑定变量的SQL

关于预定义语句的一些英文介绍（都是非官方资料）

A prepared query is paramaterized and can be reused for a range of different inputs.

Queries which substitute place holders in place of actual values are called Prepared statements

A prepared query is paramaterized and can be reused for a range of different inputs

（1）预定义语句包含的形式

1、预定义

command.CommandText = "SELECT * FROM dbo.Users WHERE UserID=@UserID AND Passowrd=@Passowrd";
 
command.Parameters.AddWithValue("@UserID", UserID);
command.Parameters.AddWithValue("@Passowrd", Passowrd);

2、使用 sp_executesql 且在内部使用变量

EXEC sp_executesql N'SELECT * FROM Person.Person WHERE BusinessEntityID=@BusinessEntityID', N'@BusinessEntityID INT', 10
GO

3、使用 sp_executesql 语句中有参数就算

EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3',N'@C1 INT',@C1=3

如上面代码，即使实际SQL没有参数执行，但在 sp_executesql 里面有 C1 参数，依然是 prepared 参数化（见测试5）

4、使用 sp_prepare，sp_execute，sp_unprepare

（2）验证查阅SQL即席查询情况

那么下面，在SSMS中执行的SQL是即席查询还是预定义语句呢？  

DECLARE @BusinessEntityID INT;
SET @BusinessEntityID=10;
SELECT * FROM Person.Person WHERE BusinessEntityID=@BusinessEntityID;

如下验证所示，你会发现这个SQL是即席查询 

SELECT  cp.[usecounts] ,
        cp.[refcounts] ,
        cp.[cacheobjtype] ,
        CASE cp.[objtype]
          WHEN 'Proc'       THEN 'Stored procedure'
          WHEN 'Prepared'   THEN 'Prepared statement'
          WHEN 'Adhoc'      THEN 'Ad hoc query'
          WHEN 'ReplProc'   THEN 'Replication-filter-procedure'
          WHEN 'UsrTab'     THEN 'User table'
          WHEN 'SysTab'     THEN 'System table'
          WHEN 'Check'      THEN 'Check constraint'
          ELSE cp.[objtype]
        END AS [object_type] ,
        cp.[size_in_bytes] ,
        ISNULL(DB_NAME(qt.[dbid]), 'resourcedb') AS [db_name] ,
        qp.[query_plan] ,
        qt.[text]
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(cp.[plan_handle]) qt
        CROSS APPLY sys.dm_exec_query_plan(cp.[plan_handle]) qp
WHERE qt.text LIKE '%@BusinessEntityID%'
ORDER BY cp.[usecounts] DESC;
GO

这个是否有点颠覆了我们的认知？ 照理说，这里的SQL使用了参数化查询，应该是预定义语句（Prepared Statement）吧，但是在SSMS中执行这个SQL，就变成了即席查询。

　　但是在C#等语言中这样运用的话，它就是预定义语句（Prepared Statement），很是纠结吧。

　　原因也很简单，如下所示，你修改一下参数@BusinessEntityID的值，运行一次，你会发现，SQL Server并没有重用原先的执行计划，而是重新编译了。所以它符合即席查询的特征。 

DECLARE @BusinessEntityID INT;
SET @BusinessEntityID=12;
SELECT * FROM Person.Person WHERE BusinessEntityID=@BusinessEntityID;

在SSMS中执行下面SQL，继续用上面SQL验证，你会发现这个SQL语句的类型为预定义语句（Prepared Statement）

EXEC sp_executesql N'SELECT * FROM Person.Person WHERE BusinessEntityID=@BusinessEntityID', N'@BusinessEntityID INT', 10
GO

 

缓存的具体SQL如下：

(@BusinessEntityID INT)SELECT * FROM Person.Person WHERE BusinessEntityID=@BusinessEntityID

总结

　　预定义语句（Prepared Statement）与即席查询（Ad Hoc Query）有一个区别就是它是否有占位符（参数化查询），而且能否在输入不同的参数时，能否重用缓存的执行计划。

 

【测试使用即席查询与预定义】

（1.1）adhoc 与 prepared 的概念与区别

　　在和SQLPass讨论adhoc和Prepare时，有各自不同的观点，我来发表下我的理解，不对之处，敬请指出！

　　Adhoc(即席查询):没有参数化的查询计划会被标记为adhoc,adhoc不能理解为该执行计划不会被重用。

　　Prepared(预定义):查询中使用到参数的执行计划会被标记为Prepared.

 

（1.2）如何测试、查看SQL执行是即席还是预定义

在后续测试中，每次测试之前需要清除执行计划：

--清理执行计划
DBCC FREEPROCCACHE

测试语句执行结束后需要使用以下语句来查看执行计划：

--查看执行计划
select cp.usecounts as '使用次数',cp.cacheobjtype as '缓存类型',
cp.objtype as [对象类型],
st.text as 'TSQL',
--cp.plan_handle AS '执行计划',
qp.query_plan as '执行计划',
cp.size_in_bytes as '执行计划占用空间(Byte)'           
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(plan_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
ORDER BY[对象类型]

（1.3）实际测试

测试1:简单查询

--执行两遍
SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3

执行结果：

可以看到,生成了一个Adhoc执行计划和一个Prepared执行计划,其中Adhoc执行计划被执行两次，证明Adhoc执行计划也是可以被重用的，而Prepared执行计划是由于“简单参数化”的原因生成的。

（PS:在该场景中，Adhoc执行计划最终使用的是Prepared执行计划来执行的，因此可以发现Prepared的执行计划占用的空间更多一些）

 

测试2：使用sp_executesql来实现参数化查询

--执行两遍
EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=@ID',N'@ID INT',@ID=2

执行结果：

可以看到在TSQL列里有明显的参数，因此该执行计划被标记为Prepared，同时该计划被执行两遍

 

测试3：使用sp_executesql来实现非参数化查询

--执行两遍
EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3'

 

执行结果：

可以看到，即使使用sp_executesql，但由于TSQL里没有使用参数，因此执行计划仍然被标记为Adhoc。

 

测试4：使用sp_executesql来实现混合查询

--执行两遍
EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3 AND C1=@C1',N'@C1 INT',@C1=3

执行结果：

可以发现，只有含有一部分的参数，执行计划就会被标记为Prepared

 

测试5：使用 sp_executesql 来实现混合查询2

 

--执行两遍
EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3',N'@C1 INT',@C1=3

 

执行结果

在上面的测试中，查询根本没有使用到参数C1，但是由于整个查询里有参数，所以仍被标记为Prepared。

综上所述，只有查询计划里有参数，执行计划就标记为Prepared，如果没有参数，就会标记为Adhoc.

SQL SERVER 会在两个环节考虑是否有可重用执行计划

1>在解析SQL语句之前，对SQL语句进行hash的到一个key，使用这个key去查找是否存在现成的执行计划；

2>将SQL解析成语法树后，再使用语法树的hash key去寻找是否存在现成的执行计划。

 

为证明上述观点，我们做以下测试：

SELECT *FROM [TestDB].[dbo].[TB1]  WHERE ID=3
SELECT * FROM [TestDB].[dbo].[TB1]  WHERE ID=3

 

测试结果：

　　

两条语句中有一个空格的差别，因此会生成两个adhoc执行计划，但是只会生成一个Prepared执行计划，表明这两个Adhoc执行计划最终都使用该Prepeared的执行计划。
Adhoc执行计划会调用Prepared执行计划，但Prepared执行计划不会调用Adhoc执行计划，这是两者的另一区别。

 

（1.4）关于性能的误区

 

误区：Adhoc会导致重编译，Adhoc就是影响性能，就是需要把Adhoc查询改成Prepared查询

 

　　这个是初学者很容易犯的误区，容易把问题一刀切，由于我们需要在查询里使用到不同的变量，如"WHERE ID=1"和"WHERE ID=2"这样的语句，会生成不同的adhoc的执行计划，每个执行计划生成会消耗CPU资源，并需要占用buffer pool里的内存，当频繁执行这些类似但又不相同的SQL语句时，就会浪费大量的资源，因此需要将之参数化，共用一个执行计划，尤其在执行复杂SQL(如四五个表做连接查询)时，查询优化器需要分析生成很多执行计划并选择一种比较合理的执行计划来执行，消耗很多CPU资源并延长总的SQL执行时间，共用一个执行计划会大大提升系统性能。

 

　　当然，参数化也有其切点，在数据分布不均或参数变动对查询影响巨大的情况下，参数化反而会导致系统异常，如果“WHERE ID>@ID”语句，当ID=10000000时返回一条数据，而当ID=1是返回10000000条数据，前者适合索引查找，后者适合全表扫描，如果两者使用同一个执行计划，并会导致系统性能严重下降，此时Adhoc反而更适合。

　　此外，还有一种情况，当查询语句特别简单，简单到编译几乎不消耗资源时，SQL SERVER会选择不保存这些语句的执行计划。

 

 

（1.5）adhoc 与 prepared 使用的区别

在分析执行计划问题时，需要考虑以下问题：

1>系统是否有过多的adhoc执行计划占用大量内存

2>这些adhoc的执行频率和相似度

3>是否可以改写这些adhoc执行计划的SQL

4>是否可以使用'optimize for ad hoc workloads'来优化

5>是否可以使用'强制参数化'

 

 

推荐阅读：http://www.cnblogs.com/TeyGao/p/3526804.html

 

 

 

【参考资料】

部门转自：https://www.cnblogs.com/kerrycode/p/14604862.html

剩下部分转自：https://www.cnblogs.com/TeyGao/p/3531911.html

参考：sp_prepare，sp_execute，sp_unprepare

https://stackoverflow.com/questions/38072550/what-is-the-difference-between-ad-hoc-and-prepared-query-in-sql-server-plan-cach