MS-SQL查詢效能改善經驗分享

作者：張達人 / 計算機及資訊網路中心程式組幹事

資料庫系統的主要功能為提供應用系統儲存重要的資料，目前市面上的資料庫系統均為關聯式資料庫，如本校校務資訊系統所使用的MS-SQL也是一關聯式資料庫系統。本文以臺灣大學帳務系統為例，與各位讀者分享如何使用查詢分析工具改善資料庫查詢效能，文中使用的Microsoft SQL Server Management Studio分析工具提供了圖形化的分析介面，可協助資料庫開發人員了解資料庫查詢時耗用了多少系統資源，並可進一步對系統使用的T-SQL做比較、評估。

查詢執行計畫
預估計資料庫查訊的所需計算資源，首先需在T-SQL編輯視窗上方的工具列點選「包括實際執行計畫」（如圖一所示），在執行查詢時就會顯示「執行計畫」。(注意：若要使用 Management Studio 的圖形執行計畫功能，使用者必須要有足夠的權限才能執行 T-SQL查詢。對於包含參考物件的所有資料庫，SHOWPLAN 權限也必須已經授與給使用者。)

圖一點選包括實際執行計畫
　

「執行計畫視窗」中顯示了相對於批次的查詢成本(圖二I.)、查詢所需的流程(圖二II.)、及各流程所占的成本，將滑鼠移到任一流程上可進一步顯示該流程的詳細資料(圖二III.)。這些資訊提供我們了解各流程所占的比重、索引使用的狀況、比較不同T-SQL所耗的成本等，我們可以根據這些資訊來對耗用成本比較大的流程進行改善、判斷是否該建立或重整索引、及挑選使用效率較佳的SQL語法。

圖二顯示估計執行計畫
　

用戶端統計資料
另一個工具是用戶端統計資料，如圖三所示，我們可選取包括用戶端統計資料圖示。

圖三點選包括用戶端統計資料
　

點選後每在T-SQL執行完成時，「用戶端統計資料」視窗會顯示執行查詢時的時間(圖四I.)及網路流量統計(圖四II.)，對每次的執行結果以鍵號表示數據之增加或減少，並算出各次各項數據的平均值。我們可以根據這些資料來評估修改T-SQL後對網路流量及執行時間的減少是否有幫助。

圖四顯示包括用戶端統計資料
　

案例分享
由於系統的資料量會隨著時間累積，剛開始設計的資料庫往往需要進行改進以因應大資料量與眾多用戶的考驗。

以臺灣大學帳務系統為例，當資料量越來越多，我們一般會考慮在需要頻繁查詢的條件上建立索引以增進查詢效率。而建立後可以利用「執行計畫」察看索引是否有被該查詢所使用(如圖五)。

圖五查看索引建立後是否有被使用
　

而前端應用程式的設計決定了查詢指令，也會具體地影響效能。大部分的情況都沒有必要用到大結果集供使用者瀏覽，我們應該盡量使用小結果集以減少CPU與網路I/O的負擔。原本帳務系統計畫(部門)經費報帳列表均會列出該帳號所有的報帳歷史資料，但這樣的設計會增長報過非常多帳的使用者查詢，亦會增加資料庫負擔，對於這個問題，我們考慮改以列出各年份報帳資料因應(如圖六)。

圖六帳務系統改以年度呈現報帳歷史資料

圖七為使用顯示執行計畫來比較改善前後的查詢成本，由圖表可發現改以年度呈現所使用的T-SQL查詢成本整整少了原來的三分之二以上，再觀察用戶端統計資料，發現傳回資料列從2316降到193(圖八I.)，網路傳輸量(圖八II.)與執行時間(圖八III.)也大幅降低，這些資訊證明調整有助於效能提升。

圖七查看改以年度呈現前後的查詢成本

圖八查看改以年度呈現前後的用戶端統計資料
　

另一個耗費的資料庫查詢是搜尋功能。該功能的原先設計並沒有針對欄位去進行搜尋，也就是在T-SQL中使用「OR」串聯多項欄位做為條件。由於使用「OR」會在兩個索引取得聯集，搜尋範圍將相當大，故該功能的計算成本相當的高。而使用者通常多以搜尋功能尋找特定「報帳條碼」或其他單一欄位，起不需要同時搜尋如此多欄位的資料。故針對這項功能，我們考慮在使用者介面增加一搜尋欄位選單(圖九)，改為搜尋單一欄位以提升效能。

圖九帳務系統搜尋功能增加查詢欄位限制條件
　

比較原先搜尋了11個欄位的T-SQL與修改後不使用「OR」只搜尋單一欄位的T-SQL，雖然測試用的查詢傳回資料列只由205減至171，但我們發現搜尋成本整整減少了7成以上(如圖十)。
比較原先搜尋了11個欄位的T-SQL與修改後不使用「OR」只搜尋單一欄位的T-SQL，雖然測試用的查詢傳回資料列只由205減至171，但我們發現搜尋成本整整減少了7成以上(如圖十)。

圖十查看增加搜尋欄位選單前後的查詢成本
　

總結
本文提到了提升效能的幾個要點：1.依查詢條件適當建立索引、2.使用小結果集、3.避免多餘的網路流量，加上兩樣查詢分析工具的使用，相信大家讀過本文後可進一步應用這些技巧在系統的開發與調整上，在往後撰寫程式時也能掌握住這幾個簡單要注意的原則。
　

參考文獻
[1] 桂思強，SQL SERVER 2008 R2資料庫設計實務，2011。
[2] Microsoft SQL SERVER文件庫，顯示圖形執行計畫(SQL Server Management Studio)。
　http://technet.microsoft.com/zh-tw/library/ms178071(v=sql.105).aspx