SQL的奧秘~奇妙的SQLOptimzerV

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

2、望產(chǎn)出(What?) 輸入(Input): 範(fàn)例腳本與測試資料 (What?) 原始資料庫設(shè)計: SQL Script (.sql/Visio) 原始測試資料 輸出(Output): 可驗證的結(jié)果 (.xls) (What?) Excel輸出範(fàn)例 (select)解決方案 (How to?) (SD/Coding) 運算邏輯(Process): SQL/ Stored Procedure 的程式邏輯(.sql)測試與驗證 (How to?) (Test) 測試案例: 驗證程序: 分享開發(fā)與測試驗證的cycle.延伸應(yīng)用 (When?) (Pattern)What If (Requirement

3、 Change) 注意事項 可能的需求改變 建議設(shè)計調(diào)整說明本案例的需求內(nèi)容，包括產(chǎn)出的格式、計算的業(yè)務(wù)規(guī)則等說明所參考的資料庫設(shè)計，包括實體關(guān)聯(lián)圖、重要欄位說明5Why.背景與問題說明背景與問題說明 環(huán)境: TSIB/ SQL 2000 + ASP. 問題/現(xiàn)象(TSIB) 查詢?nèi)静块T訓(xùn)練統(tǒng)計表， 時常無法執(zhí)行， 經(jīng)查有ASP Timeout的現(xiàn)象 上述問題，導(dǎo)致Global Temporary Table存在，使用者無法再次執(zhí)行.6What.期望產(chǎn)出期望產(chǎn)出 功能需求 部門訓(xùn)練統(tǒng)計表 查詢某個單位(含以下單位), 在某段區(qū)間的不同課程分類的受訓(xùn)總?cè)藬?shù), 總?cè)舜闻c總?cè)藭r. 程式邏輯(So

4、urce) 將查詢的單位轉(zhuǎn)換為暫存表 計算某段區(qū)間的不同課程分類的受訓(xùn)總?cè)藬?shù), 總?cè)舜闻c總?cè)藭r 轉(zhuǎn)換為CrossTab 效能需求 No timeout，under 8 sec.7How.狀況分析狀況分析 變異現(xiàn)象變異現(xiàn)象 僅查詢某個單位僅查詢某個單位, 沒有問題沒有問題. 因擴大訓(xùn)練的承認(rèn)範(fàn)圍因擴大訓(xùn)練的承認(rèn)範(fàn)圍，造成訓(xùn)練資料大幅成長造成訓(xùn)練資料大幅成長 (70萬萬) 可能原因可能原因 Index不好不好 Why? SQL不好不好 Why? 設(shè)計不好設(shè)計不好 Why? 對策對策 Divide and Conquer/ Partition 設(shè)計不好設(shè)計不好 利用利用Data Mart儲存統(tǒng)計結(jié)果

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

6、10前置準(zhǔn)備前置準(zhǔn)備資訊蒐集資訊蒐集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) FROM

7、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 ISNULL(C.

8、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 SELECT

9、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_DATE

10、=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.RSVSDT,0)

11、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)=0) or

12、 (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 reads

13、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 Exec

14、ution 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 FROM

15、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 (ISNULL(C.F

16、RTDTE,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_HRM201Table TR_

17、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 read

18、s 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_COD, E

19、MP_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) + GRO

20、UP 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, physi

21、cal 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_COD,

22、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 JO