缓存计划这把双刃剑

    不知道大家有没有看过这篇文章（http://book.csdn.net/bookfiles/738/index.html），有两个地方可能会误导大家，在此说明一下的看法：

1. 关于在程序中使用SqlParameter指定查询参数后会被自动参数化，那我们可以放心拼接SQL吗？
2. 关于在存储过程中使用sp_executesql生成一个缓存计划会始终有效吗？
   其实如果使用SqlParameter后发送的SQL指令就是以sp_executesql的形式执行的。使用这两者都会因为产生Prepared类型的缓存而造成选择度不同时导致优化器选择错误的执行计划，除非我们显示指定重新编译。 为了能得到试验比较结果，请大家还原一个新的AdventureWorks数据库（如果你对记录做过修改）然后运行下面的语句：

UPDATE TOP(4000) Sales.SalesOrderHeader SET CustomerID=11091
WHERE CustomerID<>11091 

接着手工更新一下Sales.SalesOrderHeader表的统计信息。目的是为了模拟表中数据选择度不同时，缓存对查询性能的影响。

1. 什么时候会被自动参数化？我极力反对在程序中拼接SQL，不管是否使用SqlParameter的方式。如果你的查询不会因为查询条件值的变化而返回不同的记录数时（比如你对一个唯一索引列进行查询），SQLSERVER会自动参数化你的查询，后续的查询就会利用此缓存计划，书中的例子SELECT * FROM HumanResources.Employee WHERE EmployeeId IN (@EmpId1, @EmpId2)因为不管参数如何变化，都只会返回固定的两行或是一行数据，因此被自动参数化了。对于这个查询，自动参数化对后续的查询是有利而无害的。

   下面我们再看看下面的语句也被自动参数化了，这会对后继的查询造成影响，如果是直接使用某个具体值就不会被自动参数化，而是ad hoc。这会为每个不同的查询值保存一个缓存计划，造成大量的内存浪费。在程序中按照如下的方式调用：

   Code
   
                   SqlCommand cmd = cnn.CreateCommand();
   
                   SqlParameter CustomerID = new SqlParameter("@CustomerID", SqlDbType.Int);
   
                   CustomerID.Value = 11091;
   
                   cmd.Parameters.Add(CustomerID);
   
                   cmd.CommandText = "SELECT C.CustomerID,C.CustomerType,O.SalesOrderID,O.OrderDate,O.Status,O.TotalDue "
   
                   + " FROM Sales.Customer C JOIN Sales.SalesOrderHeader O ON C.CustomerID=O.CustomerID WHERE O.CustomerID=@CustomerID";
   
                   cmd.ExecuteNonQuery();

   查看缓存中结果如下： 因为CustomerID字段的选择度这时存在很大的差异，因此如果我们第一次使用11091，这个客户有4028个订单，查询会选择对表进行扫描，按道理来讲应该是合理的。不过我们应不应该对Sales.SalesOrderHeader这种不断有记录被添加或修改的表进行表扫描呢？我在调整SQLSERVER非最优执行计划里已详述了我的观点：不应该对这种经常被修改的大表做扫描，所以在存储过程里面先取了最终结果的交集之后，再去查询所要的其它数据。但像本例中的查询只有一个查询条件，没有办法来进一步组合过滤条件，后继的查询只要有缓存计划存在，不管参数值如何变化都会使用表扫描的方式进行查询。比如21038的客户只有1个订单，这时用索引查找是高效的。

        书中建议我们要使用存储过程，但对于上面的语句即便你封装到存储过程中，如果不要求重新编译的话，都会存在上述的问题（如果先用21038执行一次查询，则只要还有缓存计划，则后续任何值都使用索引查找，不过我认为如果表中只有极少数像11091这样有很多订单的客户的话，始终使用索引查找也未必一定是坏事，我们可以使用提示强制始终进行索引查找）。我们可以像下面一样在语句中加入索引提示：

   
    cmd.CommandText = "SELECT C.CustomerID,C.CustomerType,O.SalesOrderID,O.OrderDate,O.Status,O.TotalDue "
               + " FROM Sales.Customer C JOIN Sales.SalesOrderHeader O WITH(INDEX(IX_SalesOrderHeader_CustomerID)) ON C.CustomerID=O.CustomerID WHERE O.CustomerID=@CustomerID";

   为了简单，我没有使用StringBuilder来拼接字符串，.NET中像这样直接拼接字符串会占用额外的内存。不过我认为直接在程序中使用这种提示会给以后系统的升级和维护工作带来很大麻烦，索引提示的写法在SQL2000和SQL2005中的写法已经有不同，幸好2008与2005的语法是一样的。
       如果表中有很多像11091这样有很多订单的客户，使用上面的方式会适得其反。这时可以在语句中使用OPTION(RECOMPILE)指定不保存缓存计划，每次都重新编译，由SQLSERVER决定执行表扫描还是索引查找。为了防止出现多次对表进行扫描，只有在IX_SalesOrderHeader_CustomerID索引中使用Include把OrderDate,Status,TotalDue一并加进去了。

2. 书中最后的存储过程改进版本是GetEmployeeData_sp_executesql，它在指定参数和不指定参数时都正确的选择了使用索引查找还是表扫描。但这里有个前提条件就是如果指定了参数后，此过程都只会返回0或1行记录。也就是说这个查询满足自动参数化的要求，所以这时产生的缓存计划是正确的。但是如果我们把过程改成如下形式,使用ManagerID来代替EmployeeID，因为这时不同的ManagerID选择度不同，所以同样造成错误的：

   
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   CREATE PROCEDURE GetEmployeeData_sp_executesql
   @ManagerID INT = NULL,
   @NationalIdNumber NVARCHAR(15) = NULL
   AS
   BEGIN
       SET NOCOUNT ON
       DECLARE @sql NVARCHAR(MAX)
       SET @sql = '' +
       'SELECT ' +
       'ContactId, ' +
       'LoginId, ' +
       'Title ' +
       'FROM HumanResources.Employee ' +
       'WHERE 1=1 ' +
       CASE
           WHEN @ManagerID IS NULL THEN ''
           ELSE 'AND ManagerID = @ManagerID '
       END +
       CASE
           WHEN @NationalIdNumber IS NULL THEN ''
           ELSE 'AND NationalIdNumber = @NationalIdNumber '
       END
       EXEC sp_executesql
               @sql,
               N'@ManagerID INT, @NationalIdNumber NVARCHAR(60)',
               @ManagerID,
               @NationalIdNumber
   END
   
   exec GetEmployeeData_sp_executesql @ManagerID=21--22行记录
   GO
   exec GetEmployeeData_sp_executesql @ManagerID=150--2行记录

   第一个查询使用表扫描，第二个也跟着使用表扫描了。从上面的结果来看，只有你动态执行的SQL语句不因查询条件而返回不同记录数时，使用sp_executesql才是最高效的。EXEC书中已经演示过，它会为不同的参数都保留一份缓存计划，因此会造成大量的内存浪费。

   以上是我的一点看法，如有不同意见，请更正！