SQL的奥秘~奇妙的SQLOptimzerV分析.ppt

奇妙的SQL Optimizer Sky Wu 10/03/2003 Revision History 案例研討~格式說明 案例研討~格式說明 需求描述 (Why?) (SA) 問題與需求描述 期望產出(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 (Requirement Change) 注意事項 可能的需求改變 建議設計調整 Why.背景與問題說明 環境: TSIB/ SQL 2000 + ASP. 問題/現象(TSIB) 查詢全公司部門訓練統計表， 時常無法執行， 經查有ASP Timeout的現象 上述問題，導致Global Temporary Table存在，使用者無法再次執行. What.期望產出 功能需求 部門訓練統計表 查詢某個單位(含以下單位), 在某段區間的不同課程分類的受訓總人數, 總人次與總人時. 程式邏輯(Source) 將查詢的單位轉換為暫存表 計算‘某段區間的不同課程分類的受訓總人數, 總人次與總人時’ 轉換為CrossTab 效能需求 No timeout，under 8 sec. How.狀況分析 變異現象 僅查詢某個單位, 沒有問題. 因擴大訓練的承認範圍，造成訓練資料大幅成長 (70萬) 可能原因 Index不好 ? Why? SQL不好 ? Why? 設計不好 ? Why? 對策 Divide and Conquer/ Partition 設計不好 ? 利用Data Mart儲存統計結果, 降低線上直接運算 How.對策執行 了解需求(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) 前置準備~系統設定 前置準備~資訊蒐集 Original SQL INSERT INTO ##TEMPCATGY(DEPCOD,CATGY_NAME,TTL_NUM) SELECT B.EMP_DEPT_COD,CATGY_NAME,COUNT(B.ID_NO) 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.FRTDTE=920731 ) and (C.QUTDTE920731 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_NAME ORDER BY B.EMP_DEPT_COD Original SQL (Cont.) UPDATE ##TEMPCATGY SET TTL_HR=B.THR,TOT=B.TOT SELECT TTL_HR=B.THR,TOT=B.TOT FROM