SQL的奥秘~奇妙的SQL Optimzer-V1

1、經歷過去、邁向未來經歷過去、邁向未來叡揚與您共創競爭力叡揚與您共創競爭力1Sky Wu10/03/20032Revision HistoryNo.DateAuthorDescription1.2003/10/03SkyCreated2.2006/02/02SkyRevised format.3案例研討案例研討格式說明格式說明WhatWhyWhat If ?WhenHow背景/環境說明問題/需求描述現象/變異分析需求規格輸入/輸出驗證準則 變與不變 防患未然 他山之石 (Pattern) 舉一反三心路歷程方案比較驗證程序4案例研討案例研討格式說明格式說明需求描述 (Why?) (SA) 問題與需

2、求描述期望產出(What?) 輸入(Input): 範例腳本與測試資料 (What?) 原始資料庫設計: SQL Script (.sql/Visio) 原始測試資料 輸出(Output): 可驗證的結果 (.xls) (What?) Excel輸出範例 (select)解決方案 (How to?) (SD/Coding) 運算邏輯(Process): SQL/ Stored Procedure 的程式邏輯(.sql)測試與驗證 (How to?) (Test) 測試案例: 驗證程序: 分享開發與測試驗證的cycle.延伸應用 (When?) (Pattern)What If (Require

3、ment Change) 注意事項 可能的需求改變 建議設計調整說明本案例的需求內容，包括產出的格式、計算的業務規則等說明所參考的資料庫設計，包括實體關聯圖、重要欄位說明5Why.背景與問題說明背景與問題說明 環境: TSIB/ SQL 2000 + ASP. 問題/現象(TSIB) 查詢全公司部門訓練統計表， 時常無法執行， 經查有ASP Timeout的現象 上述問題，導致Global Temporary Table存在，使用者無法再次執行.6What.期望產出期望產出 功能需求 部門訓練統計表 查詢某個單位(含以下單位), 在某段區間的不同課程分類的受訓總人數, 總人次與總人時. 程式邏

4、輯(Source) 將查詢的單位轉換為暫存表 計算某段區間的不同課程分類的受訓總人數, 總人次與總人時 轉換為CrossTab 效能需求 No timeout，under 8 sec.7How.狀況分析狀況分析 變異現象變異現象 僅查詢某個單位僅查詢某個單位, 沒有問題沒有問題. 因擴大訓練的承認範圍，造成訓練資料大幅成長因擴大訓練的承認範圍，造成訓練資料大幅成長 (70萬萬) 可能原因可能原因 Index不好不好 Why? SQL不好不好 Why? 設計不好設計不好 Why? 對策對策 Divide and Conquer/ Partition 設計不好設計不好 利用利用Data Mart儲

5、存統計結果儲存統計結果, 降低線上直接運算降低線上直接運算8How.對策執行對策執行 了解需求了解需求(Why? What?) 為何需為何需filter掉離職人員掉離職人員/ 有很多有很多denormalization的欄位的欄位? 了解問題了解問題(What?) 解讀解讀Execution Plan 效能調校效能調校 (80/20) Index 調整調整 重寫重寫SQL 測試環境測試環境 Database: RADARNT01/TR_TSIB 在職教育訓練檔在職教育訓練檔: TR_081_M (58,218) 報名紀錄檔報名紀錄檔:TR_082_M (701,486)9前置準備前置準備系統設

6、定系統設定10前置準備前置準備資訊蒐集資訊蒐集Table TR_082_M. Scan count 1, logical reads 36234, physical reads 5, read-ahead reads 30791.SQL Server Execution Times: CPU time = 10925 ms, elapsed time = 29288 ms.11Original SQLINSERT INTO #TEMPCATGY(DEPCOD,CATGY_NAME,TTL_NUM) SELECT B.EMP_DEPT_COD,CATGY_NAME,COUNT(B.ID_NO)

7、FROM TR_081_M A ,TR_082_M B ,HR_TSIB.HRM201 C ,TR_010_C D ,TR_011_C E WHERE A.DEPT_COD=B.DEPT_COD AND A.OJT_COD=B.OJT_COD AND B.ID_NO=C.IDNO AND A.CATGY_KND*=D.CATGY_KND AND A.SUBCATGY_KND*=E.SUBCATGY_KND AND START_DATE=920701 AND START_DATE=920731 and (ISNULL(C.FRTDTE,0)0 and C.FRTDTE920731 or ISNU

8、LL(C.QUTDTE,0)=0) and (C.RSVSDT920731 or ISNULL(C.RSVSDT,0)=0) or (ISNULL(C.RSVSDT,0)0 and C.RTNDTE=920731 and ISNULL(C.RTNDTE,0)0 and ISNULL(C.RTNDTE,0)ISNULL(C.RSVSDT,0) GROUP BY B.EMP_DEPT_COD,CATGY_NAME ORDER BY B.EMP_DEPT_COD 12Original SQL (Cont.)UPDATE #TEMPCATGY SET TTL_HR=B.THR,TOT=B.TOT SE

9、LECT TTL_HR=B.THR,TOT=B.TOT FROM #TEMPCATGY A, (SELECT B.EMP_DEPT_COD ,SUM(B.ID_NO * TTL_HR ) AS TOT ,SUM(B.ID_NO) AS THR,CATGY_NAME FROM TR_081_M A ,(SELECT A.DEPT_COD ,A.EMP_DEPT_COD ,A.OJT_COD, COUNT(A.ID_NO) AS ID_NO FROM TR_082_M A, TR_081_M B ,HR_TSIB.HRM201 C WHERE START_DATE=920701 AND START

10、_DATE=920731 AND A.DEPT_COD=B.DEPT_COD AND A.OJT_COD=B.OJT_COD AND A.ID_NO=C.IDNO and (ISNULL(C.FRTDTE,0)0 and C.FRTDTE920731 or ISNULL(C.QUTDTE,0)=0) and (C.RSVSDT920731 or ISNULL(C.RSVSDT,0)=0) or (ISNULL(C.RSVSDT,0)0 and C.RTNDTE=920731 and ISNULL(C.RTNDTE,0)0 and ISNULL(C.RTNDTE,0)ISNULL(C.RSVSD

11、T,0) GROUP BY A.EMP_DEPT_COD,A.OJT_COD,A.DEPT_COD ) B , TR_010_C C WHERE A.OJT_COD=B.OJT_COD AND A.DEPT_COD=B.DEPT_COD AND A.START_DATE=920701 AND A.START_DATE=920701 AND START_DATE=920731 and (ISNULL(C.FRTDTE,0)0 and C.FRTDTE920731 or ISNULL(C.QUTDTE,0)=0) and (C.RSVSDT920731 or ISNULL(C.RSVSDT,0)=

12、0) or (ISNULL(C.RSVSDT,0)0 and C.RTNDTE=920731 and ISNULL(C.RTNDTE,0)0 and ISNULL(C.RTNDTE,0)ISNULL(C.RSVSDT,0) GROUP BY B.EMP_DEPT_COD, CATGY_NAME資料多直接做DISTINCT影響很大21Execution Plans人是誰殺的.22Execution Plan V1- Optimizer doesnt use new indexTable TR_082_M. Scan count 1, logical reads 36234, physical r

13、eads 5, read-ahead reads 30791.Table HRM201. Scan count 1, logical reads 2602, physical reads 72, read-ahead reads 288.Table TR_081_M. Scan count 1, logical reads 3217, physical reads 0, read-ahead reads 0.Table TR_010_C. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.SQL Server

14、 Execution Times: CPU time = 10925 ms, elapsed time = 29288 ms.易讀,但效能改變不大!23Version 2.- 強迫改變強迫改變Join的順序的順序SELECT B.EMP_DEPT_COD , CATGY_NAME , TTL_NUM=COUNT(DISTINCT ID_NO) - 多了多了TABLE SPOOL , TOT=SUM(TTL_HR) , THR=SUM(HEAD) FROM (SELECT A.EMP_DEPT_COD, A.OJT_COD ,CATGY_KND, A.ID_NO, HEAD=1 ,TTL_HR

15、FROM TR_082_M A JOIN TR_081_M B ON (START_DATE=920701 AND START_DATE=920701 AND START_DATE=920731 AND A.DEPT_COD=B.DEPT_COD AND A.OJT_COD=B.OJT_COD) GROUP BY A.EMP_DEPT_COD, CATGY_KND, A.ID_NO) B JOIN HR_TSIB.HRM201 C ON (B.ID_NO=C.IDNO) LEFT JOIN TR_010_C D ON (B.CATGY_KND=D.CATGY_KND) WHERE (ISNUL

16、L(C.FRTDTE,0)0 and C.FRTDTE920731 or ISNULL(C.QUTDTE,0)=0) and (C.RSVSDT920731 or ISNULL(C.RSVSDT,0)=0) or (ISNULL(C.RSVSDT,0)0 and C.RTNDTE=920731 and ISNULL(C.RTNDTE,0)0 and ISNULL(C.RTNDTE,0)ISNULL(C.RSVSDT,0) GROUP BY B.EMP_DEPT_COD, CATGY_NAME27Execution Plan-V3 ? - Indexing just IDX_HRM201Tabl

17、e TR_082_M. Scan count 1, logical reads 36234, physical reads 1, read-ahead reads 30791.Table TR_081_M. Scan count 1, logical reads 3217, physical reads 0, read-ahead reads 0.Table HRM201. Scan count 1, logical reads 2602, physical reads 0, read-ahead reads 2312.Table TR_010_C. Scan count 1, logical

18、 reads 1, physical reads 0, read-ahead reads 1.SQL Server Execution Times: CPU time = 3485 ms, elapsed time = 30041 ms.28Execution Plan-V3- Good Indexing(1) + GROUP BY firstCREATE INDEX IDX_START_DATE ON TR_081_M(START_DATE, OJT_COD, DEPT_COD)CREATE INDEX IDX_EMP_DEPT_COD ON TR_082_M(OJT_COD, DEPT_C

19、OD, EMP_DEPT_COD)Table TR_081_M. Scan count 1, logical reads 3217, physical reads 0, read-ahead reads 0.Table TR_082_M. Scan count 1, logical reads 6700, physical reads 0, read-ahead reads 0.SQL Server Execution Times: CPU time = 2593 ms, elapsed time = 2798 ms.29Execution Plan-V3- Good Indexing(2)

20、+ GROUP BY firstCREATE INDEX IDX_START_DATE ON TR_081_M(START_DATE, OJT_COD, DEPT_COD)CREATE INDEX IDX_EMP_DEPT_COD ON TR_082_M(DEPT_COD, OJT_COD, EMP_DEPT_COD)Table TR_081_M. Scan count 1, logical reads 3217, physical reads 1, read-ahead reads 2860.Table TR_082_M. Scan count 1, logical reads 2695,

21、physical reads 2, read-ahead reads 2481.SQL Server Execution Times: CPU time = 2163 ms, elapsed time = 5561 ms.因為SWAP的原因30Execution Plan-V3- Good Indexing(3) + GROUP BY firstCREATE INDEX IDX_START_DATE ON TR_081_M(START_DATE, DEPT_COD, OJT_COD)CREATE INDEX IDX_EMP_DEPT_COD ON TR_082_M(DEPT_COD, OJT_

22、COD, EMP_DEPT_COD)Table TR_081_M. Scan count 1, logical reads 3217, physical reads 0, read-ahead reads 0.Table TR_082_M. Scan count 1, logical reads 2695, physical reads 0, read-ahead reads 0.SQL Server Execution Times: CPU time = 2304 ms, elapsed time = 2197 ms.沒有改善31SummaryBadIndexesSuitableIndexesMemoOriginal5516Revise SQL With DISTINCT30X沒有用到期望的沒有用到期望的IndexReorder JOIN