位置: 编程技术 - 正文

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数据库连接不上)

  • 企业所得税广告费30%扣除
  • 利润表里面的所得税
  • 基本税种有哪些税种
  • 车船税保险公司会不会多收
  • 金税四期主要监管什么
  • 买手机手续费怎么算
  • 如何在金蝶算季度利润表发生额
  • 扣缴义务人怎么删除
  • 自产自销农产品企业所得税
  • 职工差旅费现金流出属于
  • 微信公众号认证费用
  • 软件公司的销售
  • 公司分红是按股权比例分配吗
  • 盈余公积多计提对报表的影响
  • 发工资时计提个人所得税
  • 小规模红字发票怎么做账务处理
  • 不动产证上宗地图名字错一个字
  • 建筑业异地预缴税款怎么抵扣
  • 长期股权投资的初始计量
  • 分公司不独立核算怎么报税
  • 个体户可以不领税控盘吗
  • 土地使用税每个月都要计提吗
  • 公司名下没车能报油费吗
  • 开电子发票是填不含税单价吗
  • 发票认证平台登陆不了,IE不成功
  • 企业所得税取得股息红利
  • 培训机构的收入有哪几方面
  • 劳务费的现金流量是什么
  • 出租房屋如何确认收入
  • 差额征收单位所需材料
  • wordpress常用api
  • 世界十大销量书
  • 暂估入库的税务分录
  • call for conference paper
  • 预付工程施工款会计分录
  • 当月缴纳上月未缴纳社保
  • 小规模超500万升一般纳税人规定
  • 个体户个人所得税税率表
  • 报建费减免
  • SQL server 2008安装程序遇到以下错误 sku
  • 个体户利润分配会计分录怎么做
  • sqldbmon
  • 围挡属于市政还是建筑
  • 企业所得税研发费用加计扣除例题
  • 现金日记账余额自动生成公式
  • 会计 借方 贷方
  • 兼职费计入什么科目
  • 五金配件生意好做吗
  • 开票金额大于付款金额 多余部分为返点
  • 企业必须设立的账薄
  • 工业企业无形资产有哪些
  • 商业零售企业商品进销差价
  • 用mysql的小技巧
  • sql server数据库恢复
  • mysql中的rand函数
  • vs2010安装失败的解决办法
  • mysql的zip包怎么安装
  • win2003和xp一样吗
  • window8开机
  • win8.1怎么升级成win7
  • msoobe.exe是什么
  • winxp怎么改壁纸
  • win7系统如何提升开关机速度
  • 电脑操作系统7
  • 一岁的宝宝可以喝枸杞水吗
  • win7组合键失效
  • linux扩容inode
  • win8系统怎么关闭屏保
  • win7找不到启动
  • jquery的实现原理
  • shell脚本连接服务器
  • 置顶句子怎么弄
  • shell动态显示
  • Android IntentService解析
  • 如何证明发货前货物完好
  • 按期申报和按次申报的区别
  • 重庆个人所得税是多少
  • 浙江摩托车上牌条件
  • 湖北注册税务师协会
  • 中税网讲师
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

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

    友情链接: 武汉网站建设