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

《SQL的奥秘~奇妙的SQL Optimzer-V1》由会员分享，可在线阅读，更多相关《SQL的奥秘~奇妙的SQL Optimzer-V1（35页珍藏版）》请在文档大全上搜索。

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)=