MYSQL网站制作之SQLServer 参数化查询履历分享微软,文章,而且,计划,资源

变相怪杰 发表于 2015-1-16 22:15:34

MYSQL网站制作之SQLServer 参数化查询履历分享

由于MySQL数据库已经如此普及，对企业来说它无疑是一个更好的选择。甚么是参数化查询?
　　一个复杂了解参数化查询的体例是把它看作只是一个T-SQL查询，它承受把持这个查询前往甚么的参数。经由过程利用分歧的参数，一个参数化查询前往分歧的了局。要取得一个参数化查询，你必要以一种特定的体例来编写你的代码，或它必要满意一组特定的尺度。　　有两种分歧的体例来创立参数化查询。第一个体例是让查询优化器主动地参数化你的查询。另外一个体例是经由过程以一个特定体例来编写你的T-SQL代码，并将它传送给sp_executesql体系存储历程，从而编程一个参数化查询。这篇文章的前面部分将先容这个办法。
　　参数化查询的关头是查询优化器将创立一个能够重用的缓存企图。经由过程主动地或编程利用参数化查询，SQLServer能够优化相似T-SQL语句的处置。这个优化打消了对利用尊贵资本为这些相似T-SQL语句的每次实行创立一个缓存企图的需求。并且经由过程创立一个可重用企图，SQLServer还削减了寄存历程缓存中相似的实行企图所需的内存利用。
　　如今让我们看看使得SQLServer创立参数化查询的分歧体例。
　　参数化查询是如何主动创立的?
　　微软编写查询优化器代码的人不遗余力地优化SQLServer处置你的T-SQL命令的体例。我想这是查询优化器称号的由来。这些只管削减资本和最年夜限制地进步查询优化器实行功能的办法之一是检察一个T-SQL语句并断定它们是不是能够被参数化。要懂得这是怎样事情的，让我们看看上面的T-SQL语句：

　SELECT*
　　FROMAdventureWorks.Sales.SalesOrderHeader
　　WHERESalesOrderID=56000;
　　GO在这里，你能够看到这个命令有两个特性。第一它复杂，第二它在WHERE谓词中包括一个用于SalesOrderID值的指定值。查询优化器能够辨认这个查询对照复杂和SalesOrderID有一个参数(“56000”)。因而，查询优化器能够主动地参数化这个查询。　　假如你利用上面的SELECT语句来检察一个只包括用于下面语句的缓存企图的、洁净的缓冲池，那末你会看到查询优化器将T-SQL查询重写为一个参数化T-SQL语句：

SELECTstats.execution_countAScnt,
　　p.size_in_bytesAS,
　　.AS
　　FROMsys.dm_exec_cached_plansp
　　OUTERAPPLYsys.dm_exec_sql_text(p.plan_handle)sql
　　JOINsys.dm_exec_query_statsstats
　　ONstats.plan_handle=p.plan_handle;
　　GO当我在一个SQLServer2008实例上运转这个命令时，我失掉上面的输入，(注重，输入被从头格局化了，以便它更容易读)：　　cntsizeplan_text
　　------------------------------------------------------------------------
　　149152(@1int)SELECT*FROM..
　　WHERE=@1
　　假如你看看下面输入中的plan_text字段，你会看到它不像本来的T-SQL文本。如前所述，查询优化器将这个查询从头编写为一个参数化T-SQL语句。在这里，你能够看到它如今有一个数据范例为(int)的变量(@1)，它在之前的SELECT语句中被界说的。别的在plan_text的开端，值“56000”被交换为变量@1。既然这个T-SQL语句被重写了，并且被存储为一个缓存企图，那末假如将来一个T-SQL命令和它大抵不异，只要SalesOrderID字段被赋的值分歧的话，它就能够被用于重用。让我们在举措中看看它。
假如我在我的呆板上运转上面的命令：

DBCCFREEPROCCACHE;
　　GO
　　SELECT*
　　FROMAdventureWorks.Sales.SalesOrderHeader
　　WHERESalesOrderID=56000;
　　GO
　　SELECT*
　　FROMAdventureWorks.Sales.SalesOrderHeader
　　WHERESalesOrderID=56001;
　　GO
　　SELECTstats.execution_countAScnt,
　　p.size_in_bytesAS,
　　.AS
　　FROMsys.dm_exec_cached_plansp
　　OUTERAPPLYsys.dm_exec_sql_text(p.plan_handle)sql
　　JOINsys.dm_exec_query_statsstats
　　ONstats.plan_handle=p.plan_handle;
　　GO
　　我从最初的SELECT语句失掉上面的输入，(注重，输入被从头格局化以便它更容易读)：
　　cntsizeplan_text
　　-------------------------------------------------------------------------
　　249152(@1int)SELECT*FROMAdventureWorks]..
　　WHERE=@1
在这里，我起首开释历程缓存，然后我实行两个分歧、但却相似的非参数化查询来看看查询优化器是会创立两个分歧的缓存企图仍是创立用于这两个查询的一个缓存企图。在这里，你能够看到查询优化器现实上很伶俐，它参数化第一个查询并缓存了企图。然后当第二个相似、但有一个分歧的SalesOrderID值的查询发送到SQLServer时，优化器能够辨认已缓存了一个企图，然后重用它来处置第二个查询。你能够这么说是由于“cnt”字段如今标明这个企图被用了两次。　　数据库设置选项PARAMETERIZATION能够影响T-SQL语句如何被主动地参数化。关于这个选项有两种分歧的设置，SIMPLE和FORCED。当PARAMETERIZATION设置被设置为SIMPLE时，只要复杂的T-SQL语句才会被参数化。要先容这个，看下上面的命令：

SELECTSUM(LineTotal)ASLineTotal
　　FROMAdventureWorks.Sales.SalesOrderHeaderH
　　JOINAdventureWorks.Sales.SalesOrderDetailDOND.SalesOrderID=H.SalesOrderID
　　WHEREH.SalesOrderID=56000
这个查询相似于我后面的示例，除在这里我增加了一个分外的JOIN尺度。当数据库AdventureWorks的PARAMETERIZATION选项被设置为SIMPLE时，这个查询不会被主动地参数化。SIMPLEPARAMETERIZATION设置告知查询优化器只参数化复杂的查询。可是中选项PARAMETERIZATION被设置为FORCED时，这个查询将被主动地参数化。　　当你设置数据库选项为利用FORCEPARAMETERIZATION时，查询优化器试图参数化一切的查询，而不单单是复杂的查询。你大概会以为这很好。可是在某些情形下，当数据库设置PARAMETERIZATION为FORCED时，查询优化器将选择不是很幻想的查询企图。当数据库设置PARAMETER为FORCED时，它改动查询中的字面常量。这大概招致当查询中触及盘算字段时索引和索引视图不被选中介入到实行企图中，从而招致一个有效的企图。FORCEDPARAMETERIZATION选项多是改善具有大批相似的、传送过去的参数稍有分歧的查询的数据库功能的一个很好的办理计划。一个在线发卖使用程序，它的客户对你的产物实行大批的相似搜刮，产物值分歧，这多是一个可以受害于FORCEDPARAMETERIZATION的很好的使用程序范例。
不是一切的查询从句城市被参数化。比方查询的TOP、TABLESAMPLE、HAVING、GROUPBY、ORDERBY、OUTPUT...INTO或FORXML从句不会被参数化。　　利用sp_execute_sql来参数化你的T-SQL
　　你不必要依附于数据库的PARAMETERIZATION选项来使得查询优化器参数化一个查询。你能够参数化你本人的查询。你经由过程从头编写你的T-SQL语句并利用“sp_executesql”体系存储历程实行重写的语句来完成。正如已看到的，下面包含一个“JOIN”从句的SELECT语句在数据库的PARAMETERIZATION设置为SIMPLE时没有被主动参数化。让我从头编写这个查询以便查询优化器将创立一个可重用的参数化查询实行企图。
　　为了申明，让我们看两个相似的、不会被主动参数化的T-SQL语句，并创立两个分歧的缓存实行企图。然后我将从头编写这两个查询使得它们都利用不异的缓存参数化实行企图。
　　让我们看看这个代码：

　DBCCFREEPROCCACHE
　　GO
　　SELECTSUM(LineTotal)ASLineTotal
　　FROMAdventureWorks.Sales.SalesOrderHeaderH
　　JOINAdventureWorks.Sales.SalesOrderDetailDOND.SalesOrderID=H.SalesOrderID
　　WHEREH.SalesOrderID=56000
　　GO
　　SELECTSUM(LineTotal)ASLineTotal
　　FROMAdventureWorks.Sales.SalesOrderHeaderH
　　JOINAdventureWorks.Sales.SalesOrderDetailDOND.SalesOrderID=H.SalesOrderID
　　WHEREH.SalesOrderID=56001
　　GO
　　SELECTstats.execution_countAScnt,
　　p.size_in_bytesAS,
　　LEFT(.,200)AS
　　FROMsys.dm_exec_cached_plansp
　　OUTERAPPLYsys.dm_exec_sql_text(p.plan_handle)sql
　　JOINsys.dm_exec_query_statsstatsONstats.plan_handle=p.plan_handle;
　　GO
在这里，我开释了历程缓存，然后运转这两个包括一个JOIN的、分歧的非复杂的T-SQL语句。然后我将反省缓存企图。这是这个利用DMV的SELECT语句的输入(注重，输入被从头格局化了，以便它更容易读)：
cntsizeplan_text
　　---------------------------------------------------------------------------------------------
　　149152SELECTSUM(LineTotal)ASLineTotal
　　FROMAdventureWorks.Sales.SalesOrderHeaderH
　　JOINAdventureWorks.Sales.SalesOrderDetailD
　　OND.SalesOrderID=H.SalesOrderID
　　WHEREH.SalesOrderID=56001
　　149152SELECTSUM(LineTotal)ASLineTotal
　　FROMAdventureWorks.Sales.SalesOrderHeaderH
　　JOINAdventureWorks.Sales.SalesOrderDetailD
　　OND.SalesOrderID=H.SalesOrderID
　　WHEREH.SalesOrderID=56000正如你从这个输入看到的，这两个SELECT语句没有被查询优化器参数化。优化器创立了两个分歧的缓存实行企图，每个都只被实行了一次。我们能够经由过程利用sp_executesql体系存储历程来匡助优化器为这两个分歧的SELECT语句创立一个参数化实行企图。
上面是下面的代码被从头编写来利用sp_executesql体系存储历程：
　DBCCFREEPROCCACHE;
　　GO
　　EXECsp_executesqlNSELECTSUM(LineTotal)ASLineTotal
　　FROMAdventureWorks.Sales.SalesOrderHeaderH
　　JOINAdventureWorks.Sales.SalesOrderDetailDOND.SalesOrderID=H.SalesOrderID
　　WHEREH.SalesOrderID=@SalesOrderID,N@SalesOrderIDINT,@SalesOrderID=56000;
　　GO
　　EXECsp_executesqlNSELECTSUM(LineTotal)ASLineTotal
　　FROMAdventureWorks.Sales.SalesOrderHeaderH
　　JOINAdventureWorks.Sales.SalesOrderDetailDOND.SalesOrderID=H.SalesOrderID
　　WHEREH.SalesOrderID=@SalesOrderID,N@SalesOrderIDINT,@SalesOrderID=56001;
　　GO
　　SELECTstats.execution_countASexec_count,
　　p.size_in_bytesAS,
　　.AS
　　FROMsys.dm_exec_cached_plansp
　　OUTERAPPLYsys.dm_exec_sql_text(p.plan_handle)sql
　　JOINsys.dm_exec_query_statsstatsONstats.plan_handle=p.plan_handle;
　　GO好像你所看到的，我从头编写了这两个SELECT语句，使它们经由过程利用“EXECsp_executesql”语句来实行。对这些EXEC语句中的每个，我都传送三个分歧的参数。第一个参数是基础的SELECT语句，可是我将SalesOrderID的值用一个变量(@SalesOrderID)替换。在第二个参数中，我断定了@SalesOrderID的数据范例，在这个例子中它是一个integer。然后在最初一个参数中，我传送了SalesOrderID的值。这个参数将把持我的SELECT依据SalesOrderID值所天生的了局。sp_executesql的每次实行中前两个参数都是一样的。可是第三个参数分歧，由于每一个都有分歧的SalesOrderID值。　　如今当我运转下面的代码时，我从DMVSELECT语句失掉上面的输入(注重，输入被从头格局化了，以便它更容易读)：

cntsizeplan_text
　　-------------------------------------------------------------------------------------------------------
　　249152(@SalesOrderIDINT)SELECTSUM(LineTotal)ASLineTotal
　　FROMAdventureWorks.Sales.SalesOrderHeaderH
　　JOINAdventureWorks.Sales.SalesOrderDetailDOND.SalesOrderID=H.SalesOrderID
　　WHEREH.SalesOrderID=@SalesOrderID
从这个输入，你能够看出，我有一个参数化缓存企图，它被实行了两次，为每一个EXEC语句各实行了一次。　　利用参数化查询来节俭资本和优化功能
　　在语句能够被实行之前，每一个T-SQL语句都必要被评价，并且必要创建一个实行企图。创立实行企图会占用可贵的CPU资本。当实行企图被创立后，它利用内存空间将它存储在历程缓存中。下降CPU和内存利用的一个办法是使用参数化查询。只管数据库能够被设置为对一切查询FORCE参数化，可是这不老是最好的选择。经由过程懂得你的哪些T-SQL语句能够被参数化然后利用sp_executesql存储历程，你能够匡助SQLServer节俭资本并优化你的查询的功能。
解决方案提供商开始推动DBaaS浪潮之前，他们应该深入了解究竟什么是DBaaS。

若天明 发表于 2015-1-19 06:17:35

原来公司用过MYSQL自己也只是建个表写个SQL

变相怪杰 发表于 2015-1-24 14:48:38

作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！

分手快乐 发表于 2015-2-1 17:14:32

很多书籍啊，不过个人认为看书太慢，还不如自己学。多做实际的东西，就会遇到很多问题，网上搜下解决问题。不断重复这个过程，在配合sql的F1功能。

飘飘悠悠 发表于 2015-2-7 12:36:42

你可以简单地认为适合的就是好，不适合就是不好。

因胸联盟 发表于 2015-2-21 21:22:28

而写到本地,我又考虑到效率问题.大家来讨论讨论吧,分数不打紧,就给10分,十全十美,没啥对错,各抒己见,但是要有说服力的哦~

冷月葬花魂 发表于 2015-3-13 22:07:36

原来公司用过MYSQL自己也只是建个表写个SQL

金色的骷髅 发表于 2015-3-20 20:33:41

语句级快照和事务级快照终于为SQLServer的并发性能带来了突破。个人感觉语句级快照大家应该应用。事务级快照，如果是高并发系统还要慎用。如果一个用户总是被提示修改不成功要求重试时，会杀人的！

页: [1]

仓酷云's Archiver

MYSQL网站制作之SQLServer 参数化查询履历分享