SQL 语言执行效率经验谈

774530750

http://www.croot.com/imges/t_gsdt_b2.gif　　SQL语句中，IN、EXISTS、NOT IN、NOT EXISTS的效率较低，尤其是后两种语句，当数据量较大时，更常给人一种死机般的感觉。本文提供一种使用连接的方法代替以上的四种语句，可大副提高SQL语句的运行效率。以NOT                IN为例，当数据量达到一万时，效率可提高20倍，数据量越大，效率提高的幅度也就越大。

               本文所举的例子在Oracle 7.0下运行通过，但本文所推荐的方法在各种大型数据库上皆适用。
               为了能够更好的说明问题，我们采用示例的方式来说明问题。下面，我们将创建一些数据库表和数据，用于在举例时使用。

               下面的语句将创建我们示例中将使用的表，并分别在表1（TAB1）中存入10000条数据，表2（TAB2）中存入5000条数据。

               SQL语句如下：
　　CREATE TABLE TAB1
               (
               COL1 VARCHAR(20) NOT NULL,
               COL2 INTEGER，
               PRIMARY KEY(COL1)
               );
               CREATE TABLE TAB2
               (
               COL1 VARCHAR(20) NOT NULL,
               PRIMARY KEY(COL1)
               );
               CREATE TABLE TAB3
               (
               COL1 VARCHAR(20) NOT NULL,
               PRIMARY KEY(COL1)
               );
               CREATE OR REPLACE TRIGGER T_TAB3 BEFORE INSERT ON TAB3 FOR EACH                ROW
               DECLARE
               NUM1 NUMBER;
               BEGIN
               NUM1:=1;
               LOOP
               EXIT WHEN NUM1>10000;
               INSERT INTO TAB1 VALUES (NUM1,NUM1);
               IF NUM1<=5000 THEN INSERT INTO TAB2 VALUES (NUM1);
               END IF;
               NUM1:=NUM1+1;
               END LOOP;
               END;
               INSERT INTO TAB3 VALUES('1');
　　下面，我们将举2个例子来具体说明使用连接替换IN、NOT IN、EXISTS、NOT EXISTS的方法。

http://www.croot.com/imges/jt.gif　读取表1中第2列（COL2）数据的总和，且其第1列数据存在于表2的第1列中。

               1. 使用IN的SQL语句：

               SELECT SUM(COL2) FROM TAB1 WHERE COL1 IN(SELECT COL1 FROM TAB2)

               2. 使用EXISTS的SQL语句：

               SELECT SUM(COL2) FROM TAB1 WHERE EXISTS(SELECT * FROM TAB2 WHERE                TAB1.COL1=TAB2.COL1)

               3. 使用连接的SQL语句：

               SELECT SUM(A.COL2) FROM TAB1 A,TAB2 B

               WHERE A.COL1=B.COL1

http://www.croot.com/imges/jt.gif　读取表1中第2列（COL2）数据的总和，且其第1列数据不存在于表2的第1列中。


               1. 使用NOT IN的SQL语句：

               SELECT SUM(COL2) FROM TAB1 WHERE COL1 NOT IN(SELECT COL1 FROM TAB2)

               2. 使用NOT EXISTS的SQL语句：

               SELECT SUM(COL2) FROM TAB1 WHERE NOT EXISTS(SELECT * FROM TAB2 WHERE               
               TAB1.COL1=TAB2.COL1)

               3. 使用外连接的SQL语句：

               SELECT SUM(A.COL2) FROM TAB1 A,TAB2 B WHERE A.COL1=B.COL1(+) AND                B.COL1 IS NULL

　　下面介绍IN、NOT IN、EXIST、NOT EXIST在DELETE和UPDATE语句中的效率提高方法。

下面所举的例子在Microsoft SQL Server 7.0下运行通过，但所推荐的方法在各种大型数据库上皆适用。下面，我们将创建一些数据库表和数据，用于举例说明。我们将分别在表A（TA）中存入 10000条数据，表B（TB）中存入5000条数据。

               SQL语句如下：
　　CREATE TABLE TA
               (
               CA INT
               )
               CREATE TABLE TB
               (
               CA INT
               )
               CREATE TABLE TC
               (
               CA INT
               )
               CREATE TRIGGER TRA ON TC
               FOR INSERT
               AS
               DECLARE @MINT INT
               BEGIN
               SELECT @MINT=1
               WHILE (@MINT<=5000)
               BEGIN
               INSERT INTO TA VALUES(@MINT)
               INSERT INTO TB VALUES(@MINT)
               SELECT @MINT=@MINT+1
               END
               WHILE (@MINT<=10000)
               BEGIN
               INSERT INTO TA VALUES(@MINT)
               SELECT @MINT=@MINT+1
               END
               END
               GO
               INSERT INTO TC VALUES(1)
               GO
http://www.croot.com/imges/jt.gif　删除表A中表A和表B相同的数据

               1． 用IN的SQL语句：
               DELETE FROM TA WHERE TA.CA IN (SELECT CA FROM TB)

               2． 用EXISTS的SQL语句：
               DELETE FROM TA WHERE EXISTS (SELECT * FROM TB WHERE TB.CA=TA.CA)

               3． 使用连接的SQL语句：
               DELETE TA FROM TA,TB WHERE TA.CA=TB.CA

http://www.croot.com/imges/jt.gif　删除表A中表A存在但表B中不存在的数据

               1. 使用IN的SQL语句：
               DELETE FROM TA WHERE TA.CA NOT IN (SELECT CA FROM TB)

               2. 使用EXISTS的SQL语句：
               DELETE FROM TA WHERE NOT EXISTS (SELECT CA FROM TB WHERE TB.CA=TA.CA)

               3. 使用连接的SQL语句：
               DELETE TA FROM TA LEFT OUTER JOIN TB ON TA.CA=TB.CA WHERE TB.CA                IS NULL


http://www.croot.com/imges/jt.gif　更新表A中表A和表B相同的数据
               1. 使用IN的SQL语句：
               UPDATE TA SET CA=CA+10000 WHERE CA IN (SELECT CA FROM TB)

               2. 使用EXISTS的SQL语句：
               UPDATE TA SET CA=CA+10000 WHERE EXISTS (SELECT CA FROM TB WHERE                TB.CA=TA.CA)

               3. 使用连接的SQL语句：
               UPDATE TA SET TA.CA=TA.CA+10000 FROM TA,TB WHERE TA.CA=TB.CA


http://www.croot.com/imges/jt.gif　更新表A中表A存在但表B中不存在的数据

               1. 使用IN的SQL语句：
               UPDATE TA SET CA=CA+10000 WHERE CA NOT IN (SELECT CA FROM TB)

               2. 使用EXISTS的SQL语句：
               UPDATE TA SET CA=CA+10000 WHERE NOT EXISTS (SELECT CA FROM TB WHERE                TB.CA=TA.CA)

               3. 使用连接的SQL语句：
               UPDATE TA SET TA.CA=TA.CA+10000 FROM TA LEFT OUTER JOIN TB ON TA.CA=TB.CA                WHERE TB.CA IS NULL