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秒）。----测试环境------主机：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