冗长的SQL Server查询将消耗你的CPU

【 tulaoshi.com - 编程语言 】

　　问题

　　只要看看数据管理和关系型数据库管理系统规则，就发现关系型数据库是使用一个合理级别的并发来维护数据和当支持数据管理行为例如备份、成批清除、改变数据结构等等时的最合适的方法。

　　一个问题是在传统应用程序中编程语言的不同。SQL(结构化查询语言)语言是一个声明性语言，在大多数公司里，它成为了用于描述“我需要什么”和“从哪里获取”的“数据语言”。OOP(面向对象编程)语言成为了全世界R&D(研究和开发)公司的开发人员最普遍采用的语言。那么我们怎样弥补这个差距呢？

　　专家解答

　　这两个趋势使得需要一个弥补这个差距的“桥”，它是通过将请求从面向对象语言翻译成SQL来弥补的。在大多数情况下，DAL(数据访问层)是用来描述以一种集中方式管理所有这些“数据拼接任务”的机制的。

　　数据库供应商(Microsoft、Oracle、IBM等等)因其对SQL的特别喜爱而提供了众多私有命令，在DAL中的翻译就需要支持许多选项。而最后的结果是执行有时会失去内嵌到引擎中的性能优化。这使得许多这样的DAL以一种非常直接的方式被执行，它将这个请求分解成许多小段，它们各自被翻译成相应的SQL语句并建立将要象征性地进行这项工作的“SELECT…FROM…WHERE…”条件从句。

　　“机器编写的SQL语句”有时会是很长的文本语句。在32位和64位系统上，包含SQL语句的字符串长度是定义为65,536 *网络数据包大小。默认的网络数据包大小为4096，所以SQL语句文本限制为256MB。

　　我怀疑长文本查询(远小于256MB)将会对服务器的CPU造成负担。所以我在这篇文章中进行测试和公布。在这篇文章里，我将介绍介绍一下内容：

　　证明长文本查询将会消耗你的CPU。

　　给出关于在一个中等大小服务器上预计的实际消耗的理解。

　　具有2GB RAM和4 x 10,000 RPM磁盘的双核CPU。

　　测试表特征

　　为了测试，我将创建一个有200，000行记录的表(叫做t1000)。这个表有许多不同的数据类型，因为我认为这可以合理地表现生产环境中的一个普通表。这个表的特征包括：

　　一个单独的integer字段作为主键(默认为蔟索引)。

　　一个varchar字段。

　　一个模拟额外1KB数据的char字段。

　　五个用来创建WHERE条件从句中长文本查询的integer字段。

　　脚本：创建测试表

　　createtablet1000(　　c1intnotnullconstrainttest_pkprimarykey,　　c2varchar(10)notnull,　　c3char(1000),　　c4intnotnull,　　c5intnotnull,　　c6intnotnull,　　c7intnotnull,　　c8intnotnull　　)　　go

　　脚本：组装测试表

　　setnocounton　　declare@iasint　　set@i=0　　while@i200000　　begin　　set@i=@i+1　　insertintot1000(c1,c2,c3,c4,c5,c6,c7,c8)　　values(@i,　　cast(@iasvarchar(10)),　　'...simulatingadditional1kdata...',　　@i,@i,@i,@i,@i)　　end　　setnocountoff　　go

　　脚本：创建测试查询

　　因为我计划测试一些很长的查询，所以我将以自动的方式生成它们。我首先只是想将一些较长文本打印到屏幕上然后将它黏贴到一个新的SQL Server管理套件查询窗口中。但我发现较长的查询(几百KB)对于管理套件来说是个沉重的负担(特别是当自动换行打开的时候)，所以我转向另一个更好的东西——文件。

　　可以采用多种编程语言来写文本文件，但是因为我们要使用SQL Server，所以我将介绍一个T-SQL方法。我将使用下面的存储过程。

　　createPROCEDUREspWriteStringToFile　　(@StringVarchar(max),--8000inSQLServer2000　　@PathVARCHAR(255),　　@FilenameVARCHAR(100)　　)　　AS　　DECLARE@objFileSystemint　　,@objTextStreamint,　　@objErrorObjectint,　　@strErrorMessageVarchar(1000),　　@Commandvarchar(1000),　　@hrint,　　@fileAndPathvarchar(80)　　setnocounton　　select@strErrorMessage='openingtheFileSystemObject'　　EXECUTE@hr=sp_OACreate'Scripting.FileSystemObject',@objFileSystemOUT　　Select@FileAndPath=@path+''+@filename　　if@HR=0Select@objErrorObject=@objFileSystem,@strErrorMessage='Creatingfile"'+@FileAndPath+'"'　　if@HR=0execute@hr=sp_OAMethod@objFileSystem,'CreateTextFile'　　,@objTextStreamOUT,@FileAndPath,2,True　　if@HR=0Select@objErrorObject=@objTextStream,　　@strErrorMessage='writingtothefile"'+@FileAndPath+'"'　　if@HR=0execute@hr=sp_OAMethod@objTextStream,'Write',Null,@String　　if@HR=0Select@objErrorObject=@objTextStream,@strErrorMessage='closingthefile"'+@FileAndPath+'"'　　if@HR=0execute@hr=sp_OAMethod@objTextStream,'Close'　　if@hr0　　begin　　Declare　　@Sourcevarchar(255),　　@DescriptionVarchar(255),　　@HelpfileVarchar(255),　　@HelpIDint　　EXECUTEsp_OAGetErrorInfo@objErrorObject,　　@sourceoutput,@Descriptionoutput,@Helpfileoutput,@HelpIDoutput　　Select@strErrorMessage='Errorwhilst'　　+coalesce(@strErrorMessage,'doingsomething')　　+','+coalesce(@Description,'')　　raiserror(@strErrorMessage,16,1)　　end　　EXECUTEsp_OADestroy@objTextStream　　EXECUTEsp_OADestroy@objTextStream　　GO

　　脚本：激活OLE自动化

　　因为上面的存储过程使用了OLE自动化，所以你需要在你的SQL Server上激活它，因为这个选项基于安全考虑默认情况下是关闭的。使用下面的命令来打开OLE自动化：

　　EXECsp_configure'OleAutomationProcedures',1　　RECONFIGUREWITHOVERRIDE　　GO

　　如果你的服务器没有激活OLE自动化，那么运行上面的存储过程将产生下面的错误：

　　Msg15281,Level16,State1,Proceduresp_OACreate,Line1　　SQLServerblockedaccesstoprocedure'sys.sp_OACreate'ofcomponent'OleAutomationProcedures'becausethiscomponentisturnedoffaspartofthesecurityconfigurationforthisserver.Asystemadministratorcanenabletheuseof'OleAutomationProcedures'byusingsp_configure.Formoreinformationaboutenabling'OleAutomationProcedures',see"SurfaceAreaConfiguration"inSQLServerBooksOnline.

　　脚本：我应该在我的测试表上采用什么SQL语句？

　　我想写一个简单的查询，它只返回一条记录但具有很长的WHERE条件从句。下面是一个示例查询：

　　selecttop1c1　　fromt1000　　wherec10　　or(c5=1)　　or(c6=2)　　or(c7=3)　　or(c8=4)　　or(c4=5)　　or(c5=6)　　or(c6=7)　　or(c7=8)　　or(c8=9)　　or(c4=10)　　GO

　　/*01*/setnocounton　　/*02*/declare@iasint　　/*03*/declare@sql_stmtasvarchar(max)　　/*04*/declare@num_of_orsasint　　/*05*/set@num_of_ors=5000　　/*06*/set@i=0　　/*07*/set@sql_stmt='selecttop1c1fromt1000wherec10'　　/*08*/while@i@num_of_ors　　/*09*/begin　　/*10*/set@i=@i+1　　/*11*/set@sql_stmt=@sql_stmt+　　/*12*/'or(c'　　/*13*/+　　/*14*/cast　　/*15*/(　　/*16*/@i%5+4　　/*17*/asvarchar(10)　　/*18*/)　　/*19*/+　　/*20*/'='　　/*21*/+　　/*22*/cast　　/*23*/(　　/*24*/@iasvarchar(10)　　/*25*/)　　/*26*/+　　/*27*/')'　　/*28*/end　　/*29*/setnocountoff　　/*30*/executespWriteStringToFile@sql_stmt,'c:temp','query.sql'　　/*31*/print'Done.'　　/*32*/go

　　下面是对这个代码的解释：

　　01行到06行是初始化变量，4行保存where条件从句将要包含的“OR”数量。

　　07行是将要生成的SQL语句的开始。

　　08行-28行是一个while循环，它创建了这个where条件从句。30行使用先前创建的存储过程(spWriteStringToFile)将这个查询写到一个文件中。

　　对于每一个文件，我还将添加会使用正确数据库、dbcc命令来刷出缓存和SET STATISTICS来显示IO、CPU和这个查询的执行计划的代码：

　　usetotal_long_text_queries　　go　　dbccdropcleanbuffers　　dbccfreeproccache　　go　　SETSTATISTICSIOON　　go　　SETSTATISTICSTIMEON　　go　　SETSTATISTICSPROFILEON　　go

　　将上面查询代码中的@num_of_ors分别设置为5000、10000、15000和20000并运行它，生成下面的文件：

　　query05000.txt，大小为145KB。

　　query10000.txt，大小为292KB。

　　query15000.txt，大小为448KB。

　　query20000.txt，大小为604KB。

　　运行查询

　　如同我之前提到的，加载这么长的查询对于管理套件来说是任务过重了些。所以在我的测试过程中，我使用Sqlcmd Utility(osql的新版本，得到了很大改进)运行这些查询，它可以在命令行里或从一个参数文件中运行这些查询。

　　使用sqlcmd utility来运行测试查询是非常简单的。为了测试，我使用下面的命令来连接到SQL Server实例，从一个输入文件读入并将输出写到一个文件中。

　　Sqlcmd –S -I c:tempquery000.txt –o c:tempqueryresults000.txt

　　分析结果

　　既然我们已经运行了上面的查询，那么让我们看看结果：

　　queryresults05000.txt

　　queryresults10000.txt

　　queryresults15000.txt

　　queryresults20000.txt

　　根据分析，我想关注于这些结果的两个方面：

　　首先，看看描述了这个查询执行了多少I/O的部分。

　　在queryresults20000.txt文件中的一个例子：

　　表“t1000”，扫描数1，逻辑读3，物理读2，预读0，lob逻辑读0，lob物理读0，lob预读0。

　　其次，查看显示了解析这个查询花费了多少时间的部分。

　　在queryresults20000.txt文件中的一个例子：

　　SQL Server解析和编译时间：CPU time = 83829 ms，elapsed time = 83893 ms

　　这些结果显示了下面的趋势：

　　运行这个查询几乎不需要I/O资源。

　　大多数时间是“解析和编译时间”。

　　“解析和编译时间”主要是CPU时间。

　　随着查询文本变得更长，消耗的CPU时间也随之增加。

　　我将这些结果总结为以下的Excel;

　　查询的大小(KB)　　　　CPU时间 (ms)　　　　　占用时间(ms)
　　　　145　　　　　　　　　5053　　　　　　　　　5053
　　　　292　　　　　　　　　19875　　　　　　　　　19944
　　　　448　　　　　　　　　45625　　　　　　　　　45657
　　　　604　　　　　　　　　83829　　　　　　　　　83893

　　下面的图表显示了这三个测试查询的大小和以毫秒为单位用逗号分隔的CPU时间。根据这个信息，我相信我们得出了这个结论：冗长的SQL Server查询会消耗你的CPU。

　　当在你的应用程序中设计一个数据访问层或使用一个数据访问层时，你应该将下面的注意事项考虑进去：

　　较长的SQL语句需要使用大量的CPU，无论将要获取的实际记录数目是多少。

　　DAL架构除了性能挑战外，还应该解决功能需求，包括所创建的SQL查询语句长度。一个好的方法是确保所有大于50KB的查询都完整地测试过以确保它们不会产生性能问题。

　　DAL执行者可能考虑要包含对查询大小的限制。