SQLSERVER SQL性能優化技巧

這篇文章主要介紹了SQLSERVER SQL性能優化技巧,需要的朋友可以參考下

1.選擇最有效率的表名順序(隻在基于規則的優化器中有效)       SQLSERVER的解析器按照從右到左的順序處理FROM子句中的表名，是以FROM子句中寫在最後的表（基礎表driving table）将被最先處理，在FROM子句中包含多個表的情況下，必須選擇記錄條數最少的表作為基礎表，當SQLSERVER處理多個表時，會運用排序及合并的方式連接配接它們， 

       首先，掃描第一個表（FROM子句中最後的那個表)并對記錄進行排序；然後掃描第二個表（FROM子句中最後第二個表)；最後将所有從第二個表中檢索出的記錄與第一個表中合适記錄進行合并        例如: 表 TAB1 16,384 條記錄表 TAB2 5 條記錄，選擇TAB2作為基礎表 (最好的方法) select count(*) from tab1,tab2 執行時間0.96秒，選擇TAB2作為基礎表 (不佳的方法) select count(*) from tab2,tab1 執行時間26.09秒；如果有3個以上的表連接配接查詢，那就需要選擇交叉表（intersection table）作為基礎表，交叉表是指那個被其他表所引用的表

例如:

EMP表描述了LOCATION表和CATEGORY表的交集

SELECT * 
FROM LOCATION L, 
CATEGORY C, 
EMP E 
WHERE E.EMP_NO BETWEEN 1000 AND 2000 
AND E.CAT_NO = C.CAT_NO 
AND E.LOCN = L.LOCN            

将比下列SQL更有效率

SELECT * 
FROM EMP E , 
LOCATION L , 
CATEGORY C 
WHERE E.CAT_NO = C.CAT_NO 
AND E.LOCN = L.LOCN 
AND E.EMP_NO BETWEEN 1000 AND 2000            

2.WHERE子句中的連接配接順序        SQLSERVER采用自下而上的順序解析WHERE子句，根據這個原理，表之間的連接配接必須寫在其他WHERE條件之前，那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾  

例如： 

(低效,執行時間156.3秒)

SELECT * 
FROM EMP E 
WHERE SAL > 50000 
AND JOB = 'MANAGER' 
AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); 
(高效,執行時間10.6秒) 
SELECT * 
FROM EMP E 
WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) 
AND SAL > 50000 
AND JOB = 'MANAGER';            

 3.SELECT子句中避免使用'*'       當你想在SELECT子句中列出所有的COLUMN時，使用動态SQL列引用'*'是一個友善的方法，不幸的是，這是一個非常低效的方法。實際上，SQLSERVER在解析的過程中，會将'*'依次轉換成所有的列名，這個工作是通過查詢資料字典完成的，這意味着将耗費更多的時間。

4.減少通路資料庫的次數       當執行每條SQL語句時，SQLSERVER在内部執行了許多工作：解析SQL語句，估算索引的使用率，綁定變量，讀資料塊等等

       由此可見，減少通路資料庫的次數，就能實際上減少SQLSERVER的工作量，例如： 以下有三種方法可以檢索出雇員号等于0342或0291的職員 

方法1 (最低效)

SELECT EMP_NAME, SALARY, GRADE 
FROM EMP 
WHERE EMP_NO = 342;  
SELECT EMP_NAME, SALARY, GRADE 
FROM EMP 
WHERE EMP_NO = 291;            

方法2 (次低效)

DECLARE 
CURSOR C1 (E_NO NUMBER) IS 
SELECT EMP_NAME,SALARY,GRADE 
FROM EMP 
WHERE EMP_NO = E_NO; 
BEGIN 
OPEN C1(342); 
FETCH C1 INTO …,…,…; 
… 
OPEN C1(291); 
FETCH C1 INTO …,…,…; 
… 
CLOSE C1; 
END;            

方法2 (高效)

SELECT A.EMP_NAME, A.SALARY, A.GRADE, 
B.EMP_NAME, B.SALARY, B.GRADE 
FROM EMP A, EMP B 
WHERE A.EMP_NO = 342 
AND B.EMP_NO = 291;            

5.使用DECODE函數來減少處理時間       使用DECODE函數可以避免重複掃描相同記錄或重複連接配接相同的表

例如：

SELECT COUNT(*), SUM(SAL) 
FROM EMP 
WHERE DEPT_NO = '0020' 
AND ENAME LIKE 'SMITH%'; 
 
SELECT COUNT(*), SUM(SAL) 
FROM EMP 
WHERE DEPT_NO = '0030' 
AND ENAME LIKE 'SMITH%';            

你可以用DECODE函數高效地得到相同結果

SELECT COUNT(DECODE(DEPT_NO, '0020', 'X', NULL)) D0020_COUNT, 
COUNT(DECODE(DEPT_NO, '0030', 'X', NULL)) D0030_COUNT, 
SUM(DECODE(DEPT_NO, '0020', SAL, NULL)) D0020_SAL, 
SUM(DECODE(DEPT_NO, 0030, SAL, NULL)) D0030_SAL 
FROM EMP 
WHERE ENAME LIKE 'SMITH%';            

'X'表示任何一個字段 

類似的，DECODE函數也可以運用于GROUP BY和ORDER BY子句中 

6.用Where子句替換HAVING子句        避免使用HAVING子句，HAVING隻會在檢索出所有記錄之後才對結果集進行過濾，這個處理需要排序、統計等操作 。如果能通過WHERE子句限制記錄的數目，那就能減少這方面的開銷

低效

SELECT REGION, AVG(LOG_SIZE) 
FROM LOCATION 
GROUP BY REGION 
HAVING REGION REGION != 'SYDNEY' 
AND REGION != 'PERTH' 
 
高效 
SELECT REGION, AVG(LOG_SIZE) 
FROM LOCATION 
WHERE REGION REGION != 'SYDNEY' 
AND REGION != 'PERTH' 
GROUP BY REGION            

7.減少對表的查詢       在含有子查詢的SQL語句中，要特别注意減少對表的查詢 

低效 
SELECT TAB_NAME 
FROM TABLES 
WHERE TAB_NAME = (SELECT TAB_NAME 
FROM TAB_COLUMNS 
WHERE VERSION = 604) 
AND DB_VER = (SELECT DB_VER 
FROM TAB_COLUMNS 
WHERE VERSION = 604) 
 
高效 
SELECT TAB_NAME 
FROM TABLES 
WHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VER 
FROM TAB_COLUMNS 
WHERE VERSION = 604)            

Update多個Column例子：

低效 
UPDATE EMP 
SET EMP_CAT = (SELECT MAX(CATEGORY) 
FROM EMP_CATEGORIES), 
SAL_RANGE = (SELECT MAX(SAL_RANGE) 
FROM EMP_CATEGORIES) 
WHERE EMP_DEPT = 0020; 
 
高效 
UPDATE EMP 
SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY), MAX(SAL_RANGE) 
FROM EMP_CATEGORIES) 
WHERE EMP_DEPT = 0020;            

8.使用表的别名(Alias)       當在SQL語句中連接配接多個表時，請使用表的别名并把别名字首于每個Column上，這樣可以減少解析的時間并減少那些由Column歧義引起的文法錯誤 

9.用EXISTS替代IN        在許多基于基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯接。在這種情況下，使用EXISTS(或NOT EXISTS)通常将提高查詢的效率

低效 
SELECT * 
FROM EMP (基礎表) 
WHERE EMPNO > 0 
AND DEPTNO IN (SELECT DEPTNO 
FROM DEPT 
WHERE LOC = 'MELB') 
 
高效 
SELECT * 
FROM EMP (基礎表) 
WHERE EMPNO > 0 
AND EXISTS (SELECT 'X' 
FROM DEPT 
WHERE DEPT.DEPTNO = EMP.DEPTNO 
AND LOC = 'MELB')            

SELECT … 
FROM EMP 
WHERE DEPT_NO NOT IN (SELECT DEPT_NO 
FROM DEPT 
WHERE DEPT_CAT = 'A');            

高效 
SELECT … 
FROM EMP A, DEPT B 
WHERE A.DEPT_NO = B.DEPT(+) 
AND B.DEPT_NO IS NULL 
AND B.DEPT_CAT(+) = 'A' 
 
最高效 
SELECT … 
FROM EMP E 
WHERE NOT EXISTS (SELECT 'X' 
FROM DEPT D 
WHERE D.DEPT_NO = E.DEPT_NO 
AND DEPT_CAT = 'A');