位置: 编程技术 - 正文

SQLServer 参数化查询经验分享(sql参数化还是被注入了)

编辑:rootadmin
什么是参数化查询?   一个简单理解参数化查询的方式是把它看做只是一个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 = ;   GO在这里,你可以看到这个命令有两个特点。第一它简单,第二它在WHERE谓词中包含一个用于SalesOrderID值的指定值。查询优化器可以识别这个查询比较简单以及SalesOrderID有一个参数(“”)。因此,查询优化器可以自动地参数化这个查询。

  如果你使用下面的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 实例上运行这个命令时,我得到下面的输出,(注意,输出被重新格式化了,以便它更易读):

  cnt size plan_text

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

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

  WHERE [SalesOrderID]=@1

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

如果我在我的机器上运行下面的命令: DBCC FREEPROCCACHE;   GO   SELECT *   FROM AdventureWorks.Sales.SalesOrderHeader   WHERE SalesOrderID = ;   GO   SELECT *   FROM AdventureWorks.Sales.SalesOrderHeader   WHERE SalesOrderID = ;   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 (@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 = 这个查询类似于我前面的示例,除了在这里我添加了一个额外的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 =   GO   SELECT SUM(LineTotal) AS LineTotal   FROM AdventureWorks.Sales.SalesOrderHeader H   JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID   WHERE H.SalesOrderID =   GO   SELECT stats.execution_count AS cnt,   p.size_in_bytes AS [size],   LEFT([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在这里,我释放了过程缓存,然后运行这两个包含一个JOIN的、不同的非简单的T-SQL语句。然后我将检查缓存计划。这是这个使用DMV 的SELECT语句的输出(注意,输出被重新格式化了,以便它更易读): cnt size plan_text   --- ----------- -------------------------------------------------------------------------------   1 SELECT SUM(LineTotal) AS LineTotal   FROM AdventureWorks.Sales.SalesOrderHeader H   JOIN AdventureWorks.Sales.SalesOrderDetail D   ON D.SalesOrderID = H.SalesOrderID   WHERE H.SalesOrderID =   1 SELECT SUM(LineTotal) AS LineTotal   FROM AdventureWorks.Sales.SalesOrderHeader H   JOIN AdventureWorks.Sales.SalesOrderDetail D   ON D.SalesOrderID = H.SalesOrderID   WHERE H.SalesOrderID = 正如你从这个输出看到的,这两个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 = ;   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 = ;   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 (@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节省资源并优化你的查询的性能。

推荐整理分享SQLServer 参数化查询经验分享(sql参数化还是被注入了),希望有所帮助,仅作参考,欢迎阅读内容。

SQLServer 参数化查询经验分享(sql参数化还是被注入了)

文章相关热门搜索词:sql中如何实现参数的操作,sqlserver参数化查询慢,sql参数值,sql参数化还是被注入了,sql参数化是什么意思,sql参数化是什么意思,sqlserver参数化查询,sqlserver参数化查询慢,内容如对您有帮助,希望把文章链接给更多的朋友!

sqlserver 数据库同步 同步两个SQLServer数据库的内容 复制前要做好的准备工作:1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户我的电脑控制面

SQL中函数 replace 的参数1的数据类型ntext无效的解决方法 今天将一个ACC的数据库转换成ms-sql以后发现在使用replace替换语句的时候出现:SQL中函数replace的参数1的数据类型ntext无效。找了半天找到了解决办法:因

distinct 多列问题结合group by的解决方法 表table1idRegNamePostionSNPersonSN1山东齐鲁制药山东齐鲁制药北京城建公司科技公司我想获得结果是idRegNamePostionSNPersonSN1山东齐鲁制药北京

标签: sql参数化还是被注入了

本文链接地址:https://www.jiuchutong.com/biancheng/349545.html 转载请保留说明!

上一篇:sqlserver中查找所有包含了某个文本的存储过程(sqlserver 查询语句)

下一篇:sqlserver 2000数据库同步 同步两个SQLServer数据库的内容(sqlserver2000数据库连接不上)

  • 收到劳务发票还未付款
  • 一分钟了解货币
  • 印花税技术合同计税依据
  • 水电气费用属于固定成本
  • 红冲发票需要收回原发票政策
  • 开票金额大于付款金额怎么做分录
  • 购进货物的发票是外来凭证吗
  • 销售费用变动率税收政策
  • 季度利润表不包括什么
  • 滞纳金海关
  • 企业新增固定资产流程图
  • 房产企业赠送无形资产
  • 长期股权投资成本法核算
  • 委托贷款业务涉嫌诈骗吗
  • 税率是3%开成5%怎么办
  • 挂靠经营的纳税人
  • 高新技术企业软著数量
  • 债券借贷业务属于表外业务吗
  • 财务软件单机版下载免费
  • 代扣代缴个税手续费
  • 非盈利组织又称
  • 土地出让金土地使用税
  • 简述php操作mysql数据库的基本步骤
  • php公众号推送完整示例
  • 公司向股东借的钱怎么还
  • 营业外收入不计入所有者权益吗
  • thinkphp隐藏index.php
  • php获取地理位置
  • 销售折让销货方式有哪些
  • 【深度学习】Pytorch实现CIFAR10图像分类任务测试集准确率达95%
  • php截取字符串几种方式
  • flash中文版
  • hashmap resize源码
  • 税号一般多少位数字
  • 公司法人代表能考公务员吗
  • 所得税费用为什么不计入营业利润
  • python中如何创建一个对象
  • 删除sql server2019
  • mysql5.7.32安装
  • 织梦官网倒闭了吗
  • python 邮件服务
  • 一般纳税人零申报报税流程
  • 银行存款日记账填写样本图
  • 投资性房地产成本模式转公允模式差额
  • 增值税少企业所得税高吗
  • 科目在会计上是什么意思
  • 分公司是否具有独立承担民事责任的能力
  • 投入产出法如何申报
  • 暂估入库怎么处理
  • 加油充值卡能报销吗
  • 住宿费的进项税额
  • 一般纳税人主表中的25是怎么来的
  • 失业保险费返还怎么做账
  • 什么是中型企业
  • 销售酒怎么结转销售成本
  • 小规模纳税人怎么算税
  • 支付代理费计入什么科目
  • mysql如何修改数据库名
  • linux服务器查找文件命令
  • linux系统干嘛的
  • Win10预览版怎么变回正式版
  • win8磁盘占用率高怎么处理
  • linux重启命令网卡
  • js explode
  • js修改值
  • opengl入门视频教程
  • eclipse swt教程
  • 在微信支付宝借贷会影响征信吗
  • 衬线字体和无衬线字体各自的用途
  • unity unit
  • javascript点击按钮改变字体颜色
  • js中改变css文件中的样式
  • unity第三人称视角跟随物体移动
  • django批量上传图片
  • js添加一个div
  • python魔法方法有啥用
  • 银行税务代扣需要什么材料
  • 2023车船税收费价格表图片
  • 国税企业所得税网上申报
  • 烟台国家税务局王局长
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

    网站地图: 企业信息 工商信息 财税知识 网络常识 编程技术

    友情链接: 武汉网站建设