开发学院数据库 MSSQL Server 冗长的SQL Server查询将消耗你的CPU 阅读

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

　2008-09-27 10:05:30　来源：WEB开发网　　　

核心提示：问题只要看看数据管理和关系型数据库管理系统规则，就发现关系型数据库是使用一个合理级别的并发来维护数据和当支持数据管理行为例如备份、成批清除、改变数据结构等等时的最合适的方法，冗长的SQL Server查询将消耗你的CPU，一个问题是在传统应用程序中编程语言的不同，SQL(结构化查询语言)语言是一个声明性语言，包括所创

问题

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

一个问题是在传统应用程序中编程语言的不同。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@i<200000　　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@hr<>0　　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　　wherec1>0　　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

剩下的就是创建一个T-SQL块来创建一个类似于上面那个的查询：

　　/*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='selecttop1c1fromt1000wherec1>0'　　/*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