SQLServer 参数化查询经验分享

什么是参数化查询?
  一个简单理解参数化查询的方式是把它看做只是一个T-SQL查询,它接受控制这个查询返回什么的参数。通过使用不同的参数,一个参数化查询返回不同的结果。要获得一个参数化查询,你需要以一种特定的方式来编写你的代码,或它需要满足一组特定的标准。

  有两种不同的方式来创建参数化查询。第一个方式是让查询优化器自动地参数化你的查询。另一个方式是通过以一个特定方式来编写你的T-SQL代码,并将它传递给sp_executesql系统存储过程,从而编程一个参数化查询。这篇文章的后面部分将介绍这个方法。

  参数化查询的关键是查询优化器将创建一个可以重用的缓存计划。通过自动地或编程使用参数化查询,SQL Server可以优化类似T-SQL语句的处理。这个优化消除了对使用高贵资源为这些类似T-SQL语句的每一次执行创建一个缓存计划的需求。而且通过创建一个可重用计划,SQL Server还减少了存放过程缓存中类似的执行计划所需的内存使用。

  现在让我们看看使得SQL Server创建参数化查询的不同方式。

  参数化查询是怎样自动创建的?

  微软编写查询优化器代码的人竭尽全力地优化SQL Server处理你的T-SQL命令的方式。我想这是查询优化器名称的由来。这些尽量减少资源和最大限度地提高查询优化器执行性能的方法之一是查看一个T-SQL语句并确定它们是否可以被参数化。要了解这是如何工作的,让我们看看下面的T-SQL语句:





 SELECT *
  FROM AdventureWorks.Sales.SalesOrderHeader
  WHERE SalesOrderID = 56000;
  GO
在这里,你可以看到这个命令有两个特点。第一它简单,第二它在WHERE谓词中包含一个用于SalesOrderID值的指定值。查询优化器可以识别这个查询比较简单以及SalesOrderID有一个参数(“56000”)。因此,查询优化器可以自动地参数化这个查询。

  如果你使用下面的SELECT语句来查看一个只包含用于上面语句的缓存计划的、干净的缓冲池,那么你会看到查询优化器将T-SQL查询重写为一个参数化T-SQL语句:





SELECT stats.execution_count AS cnt,
  p.size_in_bytes AS [size],
  [sql].[text] AS [plan_text]
  FROM sys.dm_exec_cached_plans p
  OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
  JOIN sys.dm_exec_query_stats stats
  ON stats.plan_handle = p.plan_handle;
  GO
当我在一个SQL Server 2008实例上运行这个命令时,我得到下面的输出,(注意,输出被重新格式化了,以便它更易读):

  cnt size plan_text

  --- ------- --------------------------------------------------------------

  1 49152 (@1 int)SELECT * FROM [AdventureWorks].[Sales].[SalesOrderHeader]

  WHERE [SalesOrderID]=@1

  如果你看看上面输出中的plan_text字段,你会看到它不像原来的T-SQL文本。如前所述,查询优化器将这个查询重新编写为一个参数化T-SQL语句。在这里,你可以看到它现在有一个数据类型为(int)的变量(@1),它在之前的SELECT语句中被定义的。另外在plan_text的末尾, 值“56000”被替换为变量@1。既然这个T-SQL语句被重写了,而且被存储为一个缓存计划,那么如果未来一个T-SQL命令和它大致相同,只有SalesOrderID字段被赋的值不同的话,它就可以被用于重用。让我们在动作中看看它。

如果我在我的机器上运行下面的命令:





DBCC FREEPROCCACHE;
  GO
  SELECT *
  FROM AdventureWorks.Sales.SalesOrderHeader
  WHERE SalesOrderID = 56000;
  GO
  SELECT *
  FROM AdventureWorks.Sales.SalesOrderHeader
  WHERE SalesOrderID = 56001;
  GO
  SELECT stats.execution_count AS cnt,
  p.size_in_bytes AS [size],
  [sql].[text] AS [plan_text]
  FROM sys.dm_exec_cached_plans p
  OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
  JOIN sys.dm_exec_query_stats stats
  ON stats.plan_handle = p.plan_handle;
  GO
  我从最后的SELECT语句得到下面的输出,(注意,输出被重新格式化以便它更易读):
  cnt size plan_text
  --- -------- --------------------------------------------------------------
  2 49152 (@1 int)SELECT * FROM AdventureWorks].[Sales].[SalesOrderHeader]
  WHERE [SalesOrderID]=@1

在这里,我首先释放过程缓存,然后我执行两个不同、但却类似的非参数化查询来看看查询优化器是会创建两个不同的缓存计划还是创建用于这两个查询的一个缓存计划。在这里,你可以看到查询优化器事实上很聪明,它参数化第一个查询并缓存了计划。然后当第二个类似、但有一个不同的SalesOrderID值的查询发送到SQL Server时,优化器可以识别已经缓存了一个计划,然后重用它来处理第二个查询。你可以这么说是因为“cnt”字段现在表明这个计划被用了两次。

  数据库配置选项PARAMETERIZATION可以影响T-SQL语句怎样被自动地参数化。对于这个选项有两种不同的设置,SIMPLE和FORCED。当PARAMETERIZATION设置被设置为SIMPLE时,只有简单的T-SQL语句才会被参数化。要介绍这个,看下下面的命令:





SELECT SUM(LineTotal) AS LineTotal
  FROM AdventureWorks.Sales.SalesOrderHeader H
  JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
  WHERE H.SalesOrderID = 56000

这个查询类似于我前面的示例,除了在这里我添加了一个额外的JOIN标准。当数据库AdventureWorks的PARAMETERIZATION选项被设置为SIMPLE时,这个查询不会被自动地参数化。SIMPLE PARAMETERIZATION设置告诉查询优化器只参数化简单的查询。但是当选项PARAMETERIZATION被设置为FORCED时,这个查询将被自动地参数化。

  当你设置数据库选项为使用FORCE PARAMETERIZATION时,查询优化器试图参数化所有的查询,而不仅仅是简单的查询。你可能会认为这很好。但是在某些情况下,当数据库设置PARAMETERIZATION为FORCED时,查询优化器将选择不是很理想的查询计划。当数据库设置PARAMETER为FORCED时,它改变查询中的字面常量。这可能导致当查询中涉及计算字段时索引和索引视图不被选中参与到执行计划中,从而导致一个无效的计划。FORCED PARAMETERIZATION选项可能是改进具有大量类似的、传递过来的参数稍有不同的查询的数据库性能的一个很好的解决方案。一个在线销售应用程序,它的客户对你的产品执行大量的类似搜索, 产品值不同,这可能是一个能够受益于FORCED PARAMETERIZATION的很好的应用程序类型。

不是所有的查询从句都会被参数化。例如查询的TOP、TABLESAMPLE、 HAVING、GROUP BY、ORDER BY、OUTPUT...INTO或FOR XML从句不会被参数化。

  使用sp_execute_sql来参数化你的T-SQL

  你不需要依赖于数据库的PARAMETERIZATION选项来使得查询优化器参数化一个查询。你可以参数化你自己的查询。你通过重新编写你的T-SQL语句并使用“sp_executesql”系统存储过程执行重写的语句来实现。正如已经看到的,上面包括一个“JOIN”从句的SELECT语句在数据库的PARAMETERIZATION设置为SIMPLE时没有被自动参数化。让我重新编写这个查询以便查询优化器将创建一个可重用的参数化查询执行计划。

  为了说明,让我们看两个类似的、不会被自动参数化的T-SQL语句,并创建两个不同的缓存执行计划。然后我将重新编写这两个查询使得它们都使用相同的缓存参数化执行计划。

  让我们看看这个代码:





 DBCC FREEPROCCACHE
  GO
  SELECT SUM(LineTotal) AS LineTotal
  FROM AdventureWorks.Sales.SalesOrderHeader H
  JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
  WHERE H.SalesOrderID = 56000
  GO
  SELECT SUM(LineTotal) AS LineTotal
  FROM AdventureWorks.Sales.SalesOrderHeader H
  JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
  WHERE H.SalesOrderID = 56001
  GO
  SELECT stats.execution_count AS cnt,
  p.size_in_bytes AS [size],
  LEFT([sql].[text], 200) AS [plan_text]
  FROM sys.dm_exec_cached_plans p
  OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
  JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle;
  GO

在这里,我释放了过程缓存,然后运行这两个包含一个JOIN的、不同的非简单的T-SQL语句。然后我将检查缓存计划。这是这个使用DMV 的SELECT语句的输出(注意,输出被重新格式化了,以便它更易读):




cnt size plan_text
  --- ----------- -------------------------------------------------------------------------------
  1 49152 SELECT SUM(LineTotal) AS LineTotal
  FROM AdventureWorks.Sales.SalesOrderHeader H
  JOIN AdventureWorks.Sales.SalesOrderDetail D
  ON D.SalesOrderID = H.SalesOrderID
  WHERE H.SalesOrderID = 56001
  1 49152 SELECT SUM(LineTotal) AS LineTotal
  FROM AdventureWorks.Sales.SalesOrderHeader H
  JOIN AdventureWorks.Sales.SalesOrderDetail D
  ON D.SalesOrderID = H.SalesOrderID
  WHERE H.SalesOrderID = 56000
正如你从这个输出看到的,这两个SELECT语句没有被查询优化器参数化。优化器创建了两个不同的缓存执行计划,每一个都只被执行了一次。我们可以通过使用sp_executesql系统存储过程来帮助优化器为这两个不同的SELECT语句创建一个参数化执行计划。
下面是上面的代码被重新编写来使用sp_executesql 系统存储过程:




 DBCC FREEPROCCACHE;
  GO
  EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotal
  FROM AdventureWorks.Sales.SalesOrderHeader H
  JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
  WHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', @SalesOrderID = 56000;
  GO
  EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotal
  FROM AdventureWorks.Sales.SalesOrderHeader H
  JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
  WHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', @SalesOrderID = 56001;
  GO
  SELECT stats.execution_count AS exec_count,
  p.size_in_bytes AS [size],
  [sql].[text] AS [plan_text]
  FROM sys.dm_exec_cached_plans p
  OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
  JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle;
  GO
如同你所看到的,我重新编写了这两个SELECT语句,使它们通过使用“EXEC sp_executesql”语句来执行。对这些EXEC语句中的每一个,我都传递三个不同的参数。第一个参数是基本的SELECT语句,但是我将SalesOrderID的值用一个变量(@SalesOrderID)替代。在第二个参数中,我确定了@SalesOrderID的数据类型,在这个例子中它是一个integer。然后在最后一个参数中,我传递了SalesOrderID的值。这个参数将控制我的SELECT根据SalesOrderID值所生成的结果。sp_executesql的每次执行中前两个参数都是一样的。但是第三个参数不同,因为每个都有不同的SalesOrderID值。

  现在当我运行上面的代码时,我从DMV SELECT语句得到下面的输出(注意,输出被重新格式化了,以便它更易读):





cnt size plan_text
  --- ----------- -----------------------------------------------------------------------------------------
  2 49152 (@SalesOrderID INT)SELECT SUM(LineTotal) AS LineTotal
  FROM AdventureWorks.Sales.SalesOrderHeader H
  JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
  WHERE H.SalesOrderID = @SalesOrderID

从这个输出,你可以看出,我有一个参数化缓存计划,它被执行了两次,为每个EXEC语句各执行了一次。

  使用参数化查询来节省资源和优化性能

  在语句可以被执行之前,每个T-SQL语句都需要被评估,而且需要建立一个执行计划。创建执行计划会占用宝贵的CPU资源。当执行计划被创建后,它使用内存空间将它存储在过程缓存中。降低CPU和内存使用的一个方法是利用参数化查询。尽管数据库可以被设置为对所有查询FORCE参数化,但是这不总是最好的选择。通过了解你的哪些T-SQL语句可以被参数化然后使用sp_executesql存储过程,你可以帮助SQL Server节省资源并优化你的查询的性能。

(0)

相关推荐

  • SQLServer 参数化查询经验分享

    什么是参数化查询? 一个简单理解参数化查询的方式是把它看做只是一个T-SQL查询,它接受控制这个查询返回什么的参数.通过使用不同的参数,一个参数化查询返回不同的结果.要获得一个参数化查询,你需要以一种特定的方式来编写你的代码,或它需要满足一组特定的标准. 有两种不同的方式来创建参数化查询.第一个方式是让查询优化器自动地参数化你的查询.另一个方式是通过以一个特定方式来编写你的T-SQL代码,并将它传递给sp_executesql系统存储过程,从而编程一个参数化查询.这篇文章的后面部分将介绍这个方法

  • SqlServer参数化查询之where in和like实现详解

    身为一名小小的程序猿,在日常开发中不可以避免的要和where in和like打交道,在大多数情况下我们传的参数不多简单做下单引号.敏感字符转义之后就直接拼进了SQL,执行查询,搞定.若有一天你不可避免的需要提高SQL的查询性能,需要一次性where in 几百.上千.甚至上万条数据时,参数化查询将是必然进行的选择.然而如何实现where in和like的参数化查询,是个让不少人头疼的问题. where in 的参数化查询实现 首先说一下我们常用的办法,直接拼SQL实现,一般情况下都能满足需要 复

  • SqlServer参数化查询之where in和like实现之xml和DataTable传参介绍

    方案5 使用xml参数 对sql server xml类型参数不熟悉的童鞋需要先了解下XQuery概念,这里简单提下XQuery 是用来从 XML 文档查找和提取元素及属性的语言,简单说就是用于查询xml的语言说到这就会牵着到XPath,其实XPath是XQuery的一个子集,XQuery 1.0 和 XPath 2.0 共享相同的数据模型,并支持相同的函数和运算符,XPath的方法均适用于XQuery,假如您已经学习了 XPath,那么学习 XQuery 也不会有问题.详见http://www

  • ACCESS的参数化查询,附VBSCRIPT(ASP)和C#(ASP.NET)函数第1/2页

    最近因项目需要用ACCESS做数据库开发WEB项目 看论坛上还许多人问及ACCESS被注入的安全问题 许多人解决的方法仍然是用Replace替换特殊字符,然而这样做也并没有起到太大做用 今天我就把我用ACCESS参数化查询的一些方法和经验和大家分享 希望对大家有所启发,有写的不对的地方希望高手们多多指教 ASP.NET 用OleDbCommand的new OleDbParameter创建参数货查询 ASP用Command的CreateParameter 方法创建参数化查询 (SQL储存过程查询也

  • 日常收集整理SqlServer数据库优化经验和注意事项

    网上关于SQL优化的教程很多,但是比较杂乱.近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充. 优化数据库的注意事项: 1.关键字段建立索引. 2.使用存储过程,它使SQL变得更加灵活和高效. 3.备份数据库和清除垃圾数据. 4.SQL语句语法的优化.(可以用Sybase的SQL Expert,可惜我没找到unexpired的序列号) 5.清理删除日志. SQL语句优化的基本原则: 1.使用索引来更快地遍历表. 缺省情况下建立的索引是非群集索引,但有时它并不是最

  • asp.net和asp下ACCESS的参数化查询

    今天我就把我用ACCESS参数化查询的一些方法和经验和大家分享 希望对大家有所启发,有写的不对的地方希望高手们多多指教 ASP.NET 用OleDbCommand的new OleDbParameter创建参数货查询 ASP用Command的CreateParameter 方法创建参数化查询 (SQL储存过程查询也是用这个方法建立的) ASP.NET C#语法 OleDbParameter parm = new OleDbParameter(Name, Type, Direction, Size,

  • 巧妙解决Oracle NClob读写问题(经验分享)

    最近一个新项目中,尝试在 Oracle 数据库中使用 NCLOB 来保存大的 xml 字符串. 在代码自动生成工具(通过 JDBC 驱动程序,读数据库表结构,自动生成对应的 java 代码,包含增加.删除.修改.分页查询.根据主键查找等前台 html/js.后台代码 java),将 NCLOB 字段映射到 String 类型. 运行代码,无报错.使用 SQuirreL SQL 客户端查看数据,觉察数据未保存成功. 网上搜一通,有提到用 SetBigStringTryClob  的数据库连接额外属

  • SQLSERVER分页查询关于使用Top方式和row_number()解析函数的不同

    临近春节,心早已飞了不在工作上了,下面小编给大家整理些数据库的几种分页查询. Sql Sever 2005之前版本: select top 页大小 * from 表名 where id not in ( select top 页大小*(查询第几页-1) id from 表名 order by id ) order by id 例如: select top 10 * --10 为页大小 from [TCCLine].[dbo].[CLine_CommonImage] where id not in

  • MYSQL跨服务器同步数据经验分享

    项目需要,自己找了些资料和亲手配置过后:得出的经验分享. (1)主服务器 修改配置文件/etc/my.cnf(my.ini) [mysqld] # mysql-bin是log文件的前缀,也可以使用其它的名字,比如服务器名 # 如果不带路径,会把log文件写到`/var/lib/mysql`下 log-bin=mysql-bin # serverid在一个同步体系中必须是唯一的,大于等于1且小于2^32-1的整数 server-id=1 binlog-do-db = 数据库名 (你要备份的数据库)

  • Yii框架参数化查询中IN查询只能查询一个的解决方法

    本文实例讲述了Yii框架参数化查询中IN查询只能查询一个的解决方法.分享给大家供大家参考,具体如下: 在yii框架中使用参数化进行IN查询时,结果不如所愿 $sql =<<<SQL SELECT id FROM tb WHERE id IN(:ids) SQL; $db = GeneralService::getSlaveDB(); $result = $db->createCommand($sql)->query([':ids' => '1013,1015,1017'

随机推荐