程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> SQL Server多表查詢優化計劃集錦

SQL Server多表查詢優化計劃集錦

編輯：MSSQL

SQL Server多表查詢優化計劃集錦。本站提示廣大學習愛好者：（SQL Server多表查詢優化計劃集錦）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL Server多表查詢優化計劃集錦正文

SQL Server多表查詢的優化計劃是本文我們重要要引見的內容，本文我們給出了優化計劃和詳細的優化實例，接上去就讓我們一路來懂得一下這部門內容。

1.履行途徑

ORACLE的這個功效年夜年夜地進步了SQL的履行機能並節儉了內存的應用:我們發明,單表數據的統計比多表統計的速度完整是兩個概念.單表統計能夠只需0.02秒,然則2張表結合統計就

能夠要幾十秒了.這是由於ORACLE只對簡略的表供給高速緩沖(cache buffering) ,這個功效其實不實用於多表銜接查詢..數據庫治理員必需在init.ora中為這個區域設置適合的參數,當這個內存區域越年夜,便可以保存更多的語句,固然被同享的能夠性也就越年夜了.

2.選擇最有用率的表名次序(記載少的放在前面)

ORACLE的解析器依照從右到左的次序處置FROM子句中的表名,是以FROM子句中寫在最初的表(基本表 driving table)將被最早處置. 在FROM子句中包括多個表的情形下,你必需選擇記載條數起碼的表作為基本表.當ORACLE處置多個表時, 會應用排序及歸並的方法銜接它們.起首,掃描第一個表(FROM子句中最初的誰人表)並對記載停止派序,然後掃描第二個表(FROM子句中最初第二個表),最初將一切從第二個表中檢索出的記載與第一個表中適合記載停止歸並.

例如:

表 TAB1 16,384 筆記錄

表 TAB2 1筆記錄

選擇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

3.WHERE子句中的銜接次序(前提細的放在前面)

ORACLE采取自下而上的次序解析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';

4.SELECT子句中防止應用'* '

當你想在SELECT子句中列出一切的COLUMN時,應用靜態SQL列援用 '*' 是一個便利的辦法.不幸的是,這是一個異常低效的辦法. 現實上,ORACLE在解析的進程中, 會將'*' 順次轉換成一切的列名, 這個任務是經由過程查詢數據字典完成的, 這意味著將消耗更多的時光.

5.削減拜訪數據庫的次數

當履行每條SQL語句時, ORACLE在外部履行了很多任務: 解析SQL語句, 預算索引的應用率, 綁定變量 , 讀數據塊等等. 因而可知, 削減拜訪數據庫的次數 , 就可以現實上削減ORACLE的任務量.

辦法1 (低效)

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

辦法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;

6.刪除反復記載

最高效的刪除反復記載辦法 ( 由於應用了ROWID)

DELETE FROM EMP E 
WHERE E.ROWID > (SELECT MIN(X.ROWID) 
          FROM EMP X 
          WHERE X.EMP_NO = E.EMP_NO);

7.用TRUNCATE替換DELETE

當刪除表中的記載時,在平日情形下, 回滾段(rollback segments ) 用來寄存可以被恢復的信息. 假如你沒有COMMIT事務,ORACLE會將數據恢復到刪除之前的狀況(精確地說是恢復到履行刪除敕令之前的狀態)，而當應用TRUNCATE時, 回滾段不再寄存任何可被恢復的信息.當敕令運轉後,數據不克不及被恢復.是以很少的資本被挪用,履行時光也會很短.

8.盡可能多應用COMMIT

只需有能夠,在法式中盡可能多應用COMMIT, 如許法式的機能獲得進步,需求也會由於COMMIT所釋放的資本而削減:

COMMIT所釋放的資本:

a. 回滾段上用於恢單數據的信息.

b. 被法式語句取得的鎖

c. redo log buffer 中的空間

d. ORACLE為治理上述3種資本中的外部消費(在應用COMMIT時必需要留意到事務的完全性,實際中效力和事務完全性常常是魚和熊掌弗成得兼)

9.削減對表的查詢

在含有子查詢的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;

10.用EXISTS替換IN,用NOT EXISTS替換NOT 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')

(絕對來講,用NOT EXISTS調換NOT IN 將更明顯地進步效力)

在子查詢中,NOT IN子句將履行一個外部的排序和歸並. 不管在哪一種情形下,NOT IN都是最低效的 (由於它對子查詢中的表履行了一個全表遍歷). 為了不應用NOT IN ,我們可以把它改寫成外銜接(Outer Joins)或NOT EXISTS.

例如:

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');

固然,最高效力的辦法是有表聯系關系.直接兩表關系春聯的速度是最快的!

11.辨認'低效履行'的SQL語句

用以下SQL對象找出低效SQL:

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, 
     ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, 
     ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, 
     SQL_TEXT 
FROM  V$SQLAREA 
WHERE  EXECUTIONS>0 
AND   BUFFER_GETS > 0 
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 
ORDER BY 4 DESC;

(固然今朝各類關於SQL優化的圖形化對象層見疊出,然則寫出本身的SQL對象來處理成績一直是一個最好的辦法)

以上就是SQL Server多表查詢優化計劃的相干常識，願望本次的引見可以或許對你有所收成！