转载

如何用参数化SQL语句污染你的计划缓存

你的SQL语句的参数化总是个好想法。使用参数化SQL语句你不会污染你的计划缓存—— 错！！！在这篇文章里我想向你展示下用参数化SQL语句就可以污染你的计划缓存，这是非常简单的！

ADO.NET-AddWithValue

ADO.NET是实现像SQL Server关系数据库数据访问的.NET框架的组成——有一些严重的副作用。不要误解我——只要你正确使用，ADO.NET一直很棒。你马上就会看到，它很容易被错误使用。我们来看下面实现SQL语句执行的C#代码。

 1 for (int i = 1; i <= 100; i++)  2 {  3    val += i.ToString();  4   5    cmd = new SqlCommand(  6       "SELECT * FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = @CarrierTrackingNumber",   7       cnn);  8    cmd.Parameters.AddWithValue("@CarrierTrackingNumber", val);  9    SqlDataReader reader = cmd.ExecuteReader(); 10    reader.Close(); 11 }

我们是聪明的开发者，因此SQL语句本身被参数化，因为ADO.NET框架是地球上最棒的框架，我们使用 System.Data.SqlClient.SqlParameterCollection 类的 AddWithValue 方法来提供实际的参数值。我在WHLIE循环里运行那个SQL语句100次，总用不同长度赋予参数值。在 Sales.SalesOrderDetail 表里 CarrierTrackingNumber 列定义为 NVARCHAR(25) 。因此我们可以在基于我们提供的不同字符长度上有上至25个不同数据类型的参数。现在让我们检查下我们SQL语句执行后的计划缓存。

1 SELECT 2     st.text, 3     cp.* 4 FROM sys.dm_exec_cached_plans cp 5 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st 6 GO

现在事情变得有点疯狂：在计划缓存里我们存储了100个不同的执行计划！

如何用参数化SQL语句污染你的计划缓存

对于每个可能的数据类型参数都有1个执行计划——即使当数据类型是 NVACHAR(25) 。 AddWithValue 方法非常，非常邪恶：基于你提供的参数值派生出数据类型。永远不要使用它！

ADO.NET – SqlDbType.VarChar

因为从我们的错误中我们学到了，现在我们知道ADO.NET的AddWithValue方法的副作用——我们不再用它。现在让我们重写我们的C#程序代码，如下所示定义一个显示的参数数据类型：

 1 for (int i = 1; i <= 100; i++)  2 {  3    val += i.ToString();  4   5    cmd = new SqlCommand(  6       "SELECT * FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = @CarrierTrackingNumber",  7       cnn);  8    cmd.Parameters.Add(new SqlParameter("@CarrierTrackingNumber", SqlDbType.VarChar));  9    cmd.Parameters["@CarrierTrackingNumber"].Value = val; 10    SqlDataReader reader = cmd.ExecuteReader(); 11    reader.Close(); 12 }

从代码里你可以看到，ADO.NET现在不能派生参数数据类型了，因为我们已经指定了SqlDbType.Varchar数据类型。让我们再次执行这个SQL语句100次并再次检查下计划缓存：

如何用参数化SQL语句污染你的计划缓存

没有啥改变。问题还是一样：在计划缓存里我们还有100个不一样的的执行计划。现在的问题是ADO.NET只强制数据类型（ SqlDbType.VarChar ），但不是数据类型的"长度"。有100个不同的长度在计划缓存里你就有100个不同的执行计划。

如果你在你的ADO.NET代码里显式指定参数数据类型，你也要指定它的长度！现在我们来看下一些修正的C#代码。

 1 for (int i = 1; i <= 100; i++)  2 {  3    val += i.ToString();  4   5    cmd = new SqlCommand(  6       "SELECT * FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = @CarrierTrackingNumber",  7       cnn);  8    cmd.Parameters.Add(new SqlParameter("@CarrierTrackingNumber", SqlDbType.VarChar, 100));  9    cmd.Parameters["@CarrierTrackingNumber"].Value = val; 10    SqlDataReader reader = cmd.ExecuteReader(); 11    reader.Close(); 12 }