SQL SERVER性能優化綜述——編碼階段（二）

5、

子查詢的用法

子查詢是一個 SELECT 查詢，它嵌套在 SELECT、INSERT、UPDATE、DELETE 語句或其它子查詢中。任何允許使用表達式的地方都可以使用子查詢。

子查詢可以使我們的編程靈活多樣，可以用來實現一些特殊的功能。但是在性能上，往往一個不合適的子查詢用法會形成一個性能瓶頸。

如果子查詢的條件中使用了其外層的表的欄位，這種子查詢就叫作相關子查詢。相關子查詢可以用IN、NOT IN、EXISTS、NOT EXISTS引入。

關於相關子查詢，應該注意：

A、NOT IN、NOT EXISTS的相關子查詢可以改用LEFT JOIN代替寫法。比如：

SELECT PUB_NAMEFROM PUBLISHERS

WHERE PUB_ID NOT IN (SELECT PUB_ID

FROM TITLES

WHERE TYPE = BUSINESS)

可以改寫成：

SELECT A.PUB_NAMEFROM PUBLISHERS A LEFT JOIN TITLES B

ON B.TYPE = BUSINESS AND A.PUB_ID=B. PUB_ID

WHERE B.PUB_ID IS NULL

SELECT TITLEFROM TITLES

WHERE NOT EXISTS (SELECT TITLE_ID

FROM SALES

WHERE TITLE_ID = TITLES.TITLE_ID)

可以改寫成：

SELECT TITLEFROM TITLES LEFT JOIN SALES

ON SALES.TITLE_ID = TITLES.TITLE_ID

WHERE SALES.TITLE_ID IS NULL

B、

如果保證子查詢沒有重複 ，IN、EXISTS的相關子查詢可以用INNER JOIN 代替。比如：

SELECT PUB_NAMEFROM PUBLISHERS

WHERE PUB_ID IN (SELECT PUB_ID

FROM TITLES

WHERE TYPE = BUSINESS)

可以改寫成：

SELECT DISTINCT A.PUB_NAMEFROM PUBLISHERS A INNER JOIN TITLES B

ON B.TYPE = BUSINESS AND

A.PUB_ID=B. PUB_ID

C、

IN的相關子查詢用EXISTS代替，比如

SELECT PUB_NAMEFROM PUBLISHERS

WHERE PUB_ID IN (SELECT PUB_ID

FROM TITLES

WHERE TYPE = BUSINESS)

可以用下面語句代替：

SELECT PUB_NAMEFROM PUBLISHERS

WHERE EXISTS (SELECT 1

FROM TITLES WHERE TYPE = BUSINESS AND

PUB_ID= PUBLISHERS.PUB_ID)

D、不要用COUNT(*)的子查詢判斷是否存在記錄，最好用LEFT JOIN或者EXISTS，比如有人寫這樣的語句：

SELECT JOB_DESC FROM JOBS

WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0

應該改成：

SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE ON EMPLOYEE.JOB_ID=JOBS.JOB_ID

WHERE EMPLOYEE.EMP_ID IS NULL

SELECT JOB_DESC FROM JOBS

WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0

應該改成：

SELECT JOB_DESC FROM JOBS

WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)

6、

慎用游標

資料庫一般的操作是集合操作，也就是對由WHERE子句和選擇列確定的結果集作集合操作，游標是提供的一個非集合操作的途徑。一般情況下，游標實現的功能往往相當於客戶端的一個循環實現的功能，所以，大部分情況下，我們把游標功能搬到客戶端。

游標是把結果集放在伺服器內存，並通過循環一條一條處理記錄，對資料庫資源（特別是內存和鎖資源）的消耗是非常大的，所以，我們應該只有在沒有其他方法的情況下才使用游標。

另外，我們可以用SQL SERVER的一些特性來代替游標，達到提高速度的目的。

A、字元串連接的例子

這是論壇經常有的例子，就是把一個表符合條件的記錄的某個字元串欄位連接成一個變數。比如需要把JOB_ID=10的EMPLOYEE的FNAME連接在一起，用逗號連接，可能最容易想到的是用游標：

DECLARE @NAME VARCHAR(20) DECLARE @NAME VARCHAR(1000)

DECLARE NAME_CURSOR CURSOR FOR SELECT FNAME FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID

OPEN NAME_CURSOR FETCH NEXT FROM RNAME_CURSOR INTO @NAME

WHILE @@FETCH_STATUS = 0

BEGIN

SET @NAMES = ISNULL(@NAMES+,,)+@NAME FETCH NEXT FROM NAME_CURSOR INTO @NAME

END CLOSE NAME_CURSOR

DEALLOCATE NAME_CURSOR

可以如下修改，功能相同：

DECLARE @NAME VARCHAR(1000) SELECT @NAMES = ISNULL(@NAMES+,,)+FNAME

FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID

B、

用CASE WHEN 實現轉換的例子

很多使用游標的原因是因為有些處理需要根據記錄的各種情況需要作不同的處理，實際上這種情況，我們可以用CASE WHEN語句進行必要的判斷處理，而且CASE WHEN是可以嵌套的。比如:

表結構:

CREATE TABLE 料件表(料號 VARCHAR(30),

名稱 VARCHAR(100),主單位 VARCHAR(20),

單位1 VARCHAR(20),單位1參數 NUMERIC(18,4),

單位2 VARCHAR(20),單位2參數 NUMERIC(18,4)

)

GO

CREATE TABLE 入庫表(時間 DATETIME,

料號 VARCHAR(30),單位 INT,

入庫數量 NUMERIC(18,4),損壞數量 NUMERIC(18,4)

)

GO

其中，單位欄位可以是0，1，2，分別代表主單位、單位1、單位2，很多計算需要統一單位，統一單位可以用游標實現：

DECLARE @料號 VARCHAR(30), @單位 INT,

@參數 NUMERIC(18,4),

DECLARE CUR CURSOR FOR SELECT 料號,單位 FROM 入庫表 WHERE 單位 <>0

OPEN CURFETCH NEXT FROM CUR INTO @料號,@單位

WHILE @@FETCH_STATUS<>-1

BEGIN

IF @單位=1

BEGIN

SET @參數=(SELECT 單位1參數 FROM 料件表 WHERE 料號 =@料號) UPDATE 入庫表 SET 數量=數量*@參數,損壞數量=損壞數量*@參數,單位=1 WHERE CURRENT OF CUR

END IF @單位=2

BEGIN SET @參數=(SELECT 單位1參數 FROM 料件表 WHERE 料號 =@料號)

UPDATE 入庫表 SET 數量=數量*@參數,損壞數量=損壞數量*@參數,單位=1 WHERE CURRENT OF CUR END

FETCH NEXT FROM CUR INTO @料號,@單位END

CLOSE CUR

DEALLOCATE CUR

可以改寫成：

UPDATE A SET 數量=CASE A.單位 WHEN 1 THEN A.數量*B. 單位1參數

WHEN 2 THEN A.數量*B. 單位2參數 ELSE A.數量

END, 損壞數量= CASE A.單位 WHEN 1 THEN A. 損壞數量*B. 單位1參數

WHEN 2 THEN A. 損壞數量*B. 單位2參數 ELSE A. 損壞數量

END,單位=1

FROM入庫表 A, 料件表 BWHERE A.單位<>1 AND

A.料號=B.料號

C、

變數參與的UPDATE語句的例子

SQL ERVER的語句比較靈活，變數參與的UPDATE語句可以實現一些游標一樣的功能，比如：

在

SELECT A,B,C,CAST(NULL AS INT) AS 序號INTO #T

FROM 表

ORDER BY A ,NEWID()

產生臨時表後，已經按照A欄位排序，但是在A相同的情況下是亂序的，這時如果需要更改序號欄位為按照A欄位分組的記錄序號，就只有游標和變數參與的UPDATE語句可以實現了，這個變數參與的UPDATE語句如下：

DECLARE @A INTDECLARE @序號 INT

UPDATE #T SET @序號=CASE WHEN A=@A THEN @序號+1 ELSE 1 END,

@A=A,

序號=@序號

D、如果必須使用游標，注意選擇游標的類型，如果只是循環取數據，那就應該用只進游標（選項FAST_FORWARD），一般只需要靜態游標（選項STATIC）。

E、

注意動態游標的不確定性，動態游標查詢的記錄集數據如果被修改，會自動刷新游標，這樣使得動態游標有了不確定性，因為在多用戶環境下，如果其他進程或者本身更改了紀錄，就可能刷新游標的記錄集。

以上技術由重慶思庄提供