使用ad hoc的利弊

所谓的ad hoc，中文译作"即席查询"。就是你使用 osql 或 sqlcmd 而不是作为远程过程调用引用作为语言事件提交的 Transact-SQL，通俗点讲就是你从SSMS命令窗口或是程序中拼接后直接发送的SQL语句就都是ad hoc,声明一下这不包括那些声明参数后，再通过参数执行的SQL语句。

使用ad hoc除了我们都很熟悉的SQL injection，带来额外的网络开销，不能很好的实现分层开发等不利之外。当你在程序中拼接的这些ad hoc中引用超过两个表时，还会带来额外的编译开销。下面我们通过示例演示一下：

--1.先清除缓存，以便于观察
DBCC FREEPROCCACHE;
GO

--2.当前缓存的对象
SELECT usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
GO

--3.分别执行引用一个表及两个表的ad hoc
SELECT *
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=44501
GO
--把表名称的第一个字母改为小写，只要与原有缓存
--存在任何的不一致都会被作为新的语句执行，因此
--会生成另一个参数化的执行计划
SELECT *
FROM Sales.salesOrderHeader
WHERE SalesOrderID=44501
GO
SELECT *
FROM Sales.SalesOrderHeader O
    JOIN Sales.SalesOrderDetail OD
        ON O.SalesOrderID=OD.SalesOrderID
WHERE O.SalesOrderID=44501

这时我们运行2所示的代码查看缓存得到如下结果：

大家看到第2行所示为联结两张表的查询，我们看到它没有对应的参数化的执行计划。而第4行和第6行分别对应了你只引用一张表的查询，因为对表的大小写不同，所以分别生成了一个执行计划。继续运行下面的代码:

--4.改变参数后查看跟踪结果
SELECT *
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=43860
GO
SELECT *
FROM Sales.SalesOrderHeader O
    JOIN Sales.SalesOrderDetail OD
        ON O.SalesOrderID=OD.SalesOrderID
WHERE O.SalesOrderID=43860

如果这时再运行代码2的查询缓存代码，得到如下结果：

我们看到第8行所示的第一个字段，得到本缓存计划被使用了两次。

跟踪结果显示如下，同样表示使用了原有的缓存计划：

    如果使用存储过程，不会因为参数化而造成不能使用计划缓存。但是，存储过程的计划缓存可能会因为没有执行重新编译而导致优化器选择了错误的执行计划，这个我在前面已经介绍过。也许有人觉得编译这么一个语句才占多点时间多点资源啊，但是随着用户数越来越多的话，这些时间开销和网络开销就不能被忽略了。

但是，使用ad hoc在有些情况下还是比较合理的。比如在对表进行更新操作时，如果这时使用存储过程，我们一般都是把所有的字段值都传进来，然后不管字段有没有更新，统统更新。如果客户只是更新了一个字段，那么这种方式，除了造成不必要的字节传输外，还带来了不必要的字段更新。特别是你要更新的表中有max类型的字段时，情况就更加明显了。如果你坚持使用存储过程，唯一的办法是使用instead of触发器，判断有哪些字段是更新的，然后只更新相应修改的字段。但这处理起来相当复杂，而且我们也不推荐使用触发器。这时，就是ad hoc的用武之地了。你在程序中很容易能判断哪些值发生了改变，然后只把更新的字段拼接出一个SQL语句发送回服务器，这样既避免了额外的网络传输开销（如果有max类型，但用户并没有修改这个字段时，更是如此）。同时你在update语句中还少了对没有更新的字段的操作，性能自然就比使用存储过程要高效的多。可能在程序中你有很多动态拼接的SQL语句，如果你要在过程中执行动态查询，这时过程一般都要使用with recomplie才能避免造成错误的选择执行计划。除此之外，我觉得在程序中就没有使用ad hoc的必要了。大概看了一下LINQ，统用和性能总是不能同时满足我们的要求。

alter database的定义中有个选项PARAMETERIZATION，你可以强制把所有的ad hoc强制参数化。但一般是不推荐这样做的，具体参见联机文档的 强制参数化。