SQL语句执行效率

整理文档很辛苦,赏杯茶钱您下走!

免费阅读已结束,点击下载阅读编辑剩下 ...

阅读已结束,您可以下载文档离线阅读编辑

资源描述

SQL语句执行效率SQL语句中,IN、EXISTS、NOTIN、NOTEXISTS的效率较低,尤其是后两种语句,当数据量较大时,更常给人一种死机般的感觉。本文提供一种使用连接的方法代替以上的四种语句,可大副提高SQL语句的运行效率。以NOTIN为例,当数据量达到一万时,效率可提高20倍,数据量越大,效率提高的幅度也就越大。1)SELECT语句中的中的效率提高方法SQL语句如下:CREATETABLETAB1(COL1VARCHAR(20)NOTNULL,COL2INTEGER,PRIMARYKEY(COL1));CREATETABLETAB2(COL1VARCHAR(20)NOTNULL,PRIMARYKEY(COL1));CREATETABLETAB3(COL1VARCHAR(20)NOTNULL,PRIMARYKEY(COL1));下面,我们将举2个例子来具体说明使用连接替换IN、NOTIN、EXISTS、NOTEXISTS的方法。读取表1中第2列(COL2)数据的总和,且其第1列数据存在于表2的第1列中。1.使用IN的SQL语句:SELECTSUM(COL2)FROMTAB1WHERECOL1IN(SELECTCOL1FROMTAB2)2.使用EXISTS的SQL语句:SELECTSUM(COL2)FROMTAB1WHEREEXISTS(SELECT*FROMTAB2WHERETAB1.COL1=TAB2.COL1)3.使用连接的SQL语句:SELECTSUM(A.COL2)FROMTAB1A,TAB2BWHEREA.COL1=B.COL1读取表1中第2列(COL2)数据的总和,且其第1列数据不存在于表2的第1列中。1.使用NOTIN的SQL语句:SELECTSUM(COL2)FROMTAB1WHERECOL1NOTIN(SELECTCOL1FROMTAB2)2.使用NOTEXISTS的SQL语句:SELECTSUM(COL2)FROMTAB1WHERENOTEXISTS(SELECT*FROMTAB2WHERETAB1.COL1=TAB2.COL1)3.使用外连接的SQL语句:SELECTSUM(A.COL2)FROMTAB1A,TAB2BWHEREA.COL1=B.COL1(+)ANDB.COL1ISNULL2)DELETE语句中的效率提高方法SQL语句如下:CREATETABLETA(CAINT)CREATETABLETB(CAINT)CREATETABLETC(CAINT)1.用IN的SQL语句:DELETEFROMTAWHERETA.CAIN(SELECTCAFROMTB)2.用EXISTS的SQL语句:DELETEFROMTAWHEREEXISTS(SELECT*FROMTBWHERETB.CA=TA.CA)3.使用连接的SQL语句:DELETETAFROMTA,TBWHERETA.CA=TB.CA删除表A中表A存在但表B中不存在的数据1.使用IN的SQL语句:DELETEFROMTAWHERETA.CANOTIN(SELECTCAFROMTB)2.使用EXISTS的SQL语句:DELETEFROMTAWHERENOTEXISTS(SELECTCAFROMTBWHERETB.CA=TA.CA)3.使用连接的SQL语句:DELETETAFROMTALEFTOUTERJOINTBONTA.CA=TB.CAWHERETB.CAISNULL3)UPDATE语句中的效率提高方法更新表A中表A和表B相同的数据1.使用IN的SQL语句:UPDATETASETCA=CA+10000WHERECAIN(SELECTCAFROMTB)2.使用EXISTS的SQL语句:UPDATETASETCA=CA+10000WHEREEXISTS(SELECTCAFROMTBWHERETB.CA=TA.CA)3.使用连接的SQL语句:UPDATETASETTA.CA=TA.CA+10000FROMTA,TBWHERETA.CA=TB.CA更新表A中表A存在但表B中不存在的数据1.使用IN的SQL语句:UPDATETASETCA=CA+10000WHERECANOTIN(SELECTCAFROMTB)2.使用EXISTS的SQL语句:UPDATETASETCA=CA+10000WHERENOTEXISTS(SELECTCAFROMTBWHERETB.CA=TA.CA)3.使用连接的SQL语句:UPDATETASETTA.CA=TA.CA+10000FROMTALEFTOUTERJOINTBONTA.CA=TB.CAWHERETB.CAISNULL================================方法一、尽量使用复杂的SQL来代替简单的一堆SQL.同样的事务,一个复杂的SQL完成的效率高于一堆简单SQL完成的效率。有多个查询时,要善于使用JOIN。oRs=oConn.Execute(SELECT*FROMBooks)whilenotoRs.EofstrSQL=SELECT*FROMAuthorsWHEREAuthorID=&oRs(AuthorID)oRs2=oConn.Execute(strSQL)Response.writeoRs(Title)&&oRs2(Name)&br"oRs.MoveNext()wend要比下面的代码慢:strSQL=SELECTBooks.Title,Authors.NameFROMBooksJOINAuthorsONAuthors.AuthorID=Books.AuthorIDoRs=oConn.Execute(strSQL)whilenotoRs.EofResponse.writeoRs(Title)&&oRs(Name)&br"oRs.MoveNext()wend方法二、尽量避免使用可更新RecordsetoRs=oConn.Execute(SELECT*FROMAuthorsWHEREAuthorID=17,3,3)oRs(Name)=DarkManoRs.Update()要比下面的代码慢:strSQL=UPDATEAuthorsSETName='DarkMan'WHEREAuthorID=17oConn.ExecutestrSQL方法三、更新数据库时,尽量采用批处理更新将所有的SQL组成一个大的批处理SQL,并一次运行;这比一个一个地更新数据要有效率得多。这样也更加满足你进行事务处理的需要:strSQL=strSQL=strSQL&SETXACT_ABORTON\n;strSQL=strSQL&BEGINTRANSACTION\n;strSQL=strSQL&INSERTINTOOrders(OrdID,CustID,OrdDat)VALUES('9999','1234',GETDATE())\n;strSQL=strSQL&INSERTINTOOrderRows(OrdID,OrdRow,Item,Qty)VALUES('9999','01','G4385',5)\n;strSQL=strSQL&INSERTINTOOrderRows(OrdID,OrdRow,Item,Qty)VALUES('9999','02','G4726',1)\n;strSQL=strSQL&COMM99vTRANSACTION\n;strSQL=strSQL&SETXACT_ABORTOFF\n;oConn.Execute(strSQL);其中,SETXACT_ABORTOFF语句告诉SQLServer,如果下面的事务处理过程中,如果遇到错误,就取消已经完成的事务。方法四、数据库索引那些将在Where子句中出现的字段,你应该首先考虑建立索引;那些需要排序的字段,也应该在考虑之列。在MSAccess中建立索引的方法:在Access里面选择需要索引的表,点击“设计”,然后设置相应字段的索引.在MSSQLServer中建立索引的方法:在SQLServer管理器中,选择相应的表,然后“设计表”,点击右键,选择“Properties”,选择“indexes/keys”方法五、避免使Text字段太大当字符串的值大小不固定时,用varchar比用char的效果要好些。我曾经看到一个例子程序,字段被定义为TEXT(255),但是他的取值经常只有20个字符。这个数据表有50k个记录,从而使这慢================================----人们在使用SQL时往往会陷入一个误区,即太关注于所得的结果是否正确,而忽略了不同的实现方法之间可能存在的性能差异,这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。笔者在工作实践中发现,不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后,其运行速度有了明显地提高!下面我将从这三个方面分别进行总结:----为了更直观地说明问题,所有实例中的SQL运行时间均经过测试,不超过1秒的均表示为(1秒)。----测试环境------主机:HPLHII----主频:330MHZ----内存:128兆----操作系统:Operserver5.0.4----数据库:Sybase11.0.3一、不合理的索引设计----例:表record有620000行,试看在不同的索引下,下面几个SQL的运行情况:----1.在date上建有一非个群集索引selectcount(*)fromrecordwheredate'19991201'anddate'19991214'andamount2000(25秒)selectdate,sum(amount)fromrecordgroupbydate(55秒)selectcount(*)fromrecordwheredate'19990901'andplacein('BJ','SH')(27秒)----分析:----date上有大量的重复值,在非群集索引下,数据在物理上随机存放在数据页上,在范围查找时,必须执行一次表扫描才能找到这一范围内的全部行。----2.在date上的一个群集索引selectcount(*)fromrecordwheredate'19991201'anddate'19991214'andamount2000(14秒)selectdate,sum(amount)fromrecordgroupbydate(28秒)selectcount(*)fromrecordwheredate'19990901'andplacein('BJ','SH')(14秒)----分析:----在群集索引下,数据在物理上按顺序在数据页上,重复值也排列在一起,因而在范围查找时,可以先找到这个范围的起末点,且只在这个范围内扫描数据页,避免了大范围扫描,提高了查询速度。----3.在place,date,amount上的组合索引selectcount(*)fromrecordwheredate'19991201'anddate'19991214'andamount2000(26秒)selectdate,sum(amount)fromrecordgroupbydate(27秒)selectcount(*)fromrecordwheredate'19990901'andplacein('BJ,'SH')(1秒)----分析:----这是一个不很合理的组合索引,因为它的前导列是place,第一和第二条SQL没有引用place,因此也没有利用上索引;第三个SQL使用了place,且引用的所有列都包含在组合索引中,形成了索引覆盖,所以它的速度是非常快的。----4.在date,place,amount上的组合索引selectcount(*)fromrecordwheredate'199912

1 / 8
下载文档,编辑使用

©2015-2020 m.777doc.com 三七文档.

备案号:鲁ICP备2024069028号-1 客服联系 QQ:2149211541

×
保存成功