中国领先的IT技术网站
|
|

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

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

作者:佚名来源:博客园|2015-08-26 16:26

Tech Neo技术沙龙 | 11月25号,九州云/ZStack与您一起探讨云时代网络边界管理实践


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

ADO.NET-AddWithValue

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

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

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

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

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

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

ADO.NET – SqlDbType.VarChar

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

 

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

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

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

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

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

这次我也指定了数据类型的长度——这里是100,现在当我们再次执行SQL语句100次时,最后我们在计划缓存里以1个执行计划且重用了100次来完美收工。这是从SQL Server角度的最终目标。

小结

寓意:ADO.NET是个很棒的数据访问框架,它提供你有用的功能(例如AddWithValue方法),当从SQL Server角度来说你真的要考虑下你在做什么。当你使用参数化SQL语句时,你要尽量显式:你必须地冠以参数值的实际数据类型,还有你想要的获得数据类型长度。

感谢关注!

注:此文章为WoodyTu学习MS SQL技术,收集整理相关文档撰写,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出此文链接!

【责任编辑:honglu TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

网管员必读——超级网管经验谈

本书是一本以示例形式直接面向应用的网络管理图书。书中以大量示例和大量实用网络管理与故障排除经验介绍了当前网络管理工作的各主要方面。...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊
× Phthon,最神奇好玩的编程语言