樓主
No.18
版主
 發送站內信
|
最近處理一個舊客戶系統效能上的問題，這家客戶是個4~5千人的公司由於系統己經run了有4~5年之久了，想當然而資料量一定不少，面對大量的資料在處理的設計上，一定是當初無法實際想像或不見得會考量到的，那如何來調校效能呢，在此先不論當初在資料庫的設計上是否得當或是正規化是否合宜，畢竟現階段不可能去把資料表打散重新設計這是很大的工程，相對的客戶是否願意一個系統只用4~5年就再花大錢做一個新的系統又是一個大問號了，因此只能從程式面去找尋可能改善的方式囉。
話說應用系統一定離不開資料庫，有了資料庫那麼一定存在SQL Command(資料庫為MS SQL-SERVER)，所以就先由這個點來動刀做手術第一步想到的是Index，為什麼是index呢？想想看如果一個Table存在幾十萬筆甚至上百萬筆的資料，光是一個簡單的select * from xxx，就需要多久了，更何況現在的系統設計大部份都是關聯式的設計，一定會用到join其它table的做法，因此適當的為每一個Table建立index是有其必要性的，但很不幸的是一般Designer在設計初期大部份不會特別去Create index(預設只有Primary Key會是當然的Index)，隨著程式愈寫愈多隻、查詢的條件愈來愈多樣化，單單只有Primary Key的Index一定是不足的，但問題來了一個系統少說上百個Table，天啊！可別告訴我一個一個Table去查該建立什麼樣的Index吧！幸好MS SQL-Server提供了相當不錯的分析工具可以來利用，那就是SQL Profile + SQL-Server 2005 Tuning Advisor，做法是先利用Profile
設定好要監錄的條件，例如Command花費30秒以上才完成的語法等......，然後再把錄製好的Profile檔案利用SQL-Server 2005 Tuning Advisor做分析，SQL-Server 2005 Tuning Advisor會給予一份改善評估建議清單，只要一個簡單的動作就可以把建議的清單動作全部立即套用到資料庫裡，根據經驗大部份是Create Index囉
好啦接下來就再看看效能上是否有所提升囉，也許有但可能不會太顯著，看來可能之前己經有把大部份的Index建立上去了，所以這次再增加建上去的Index對整體效能提升效果並不明顯，所以只好針對所執行的SQL Command做分析囉，這部份就得自行下手囉也要加上一點經驗啦(呵.......尾巴翹了起來)，經過語法的拆解發現了幾點問題
(1)在select中包含了function，例如：
想查詢列出員工目前職稱及上一個職稱的資料，而fgettitle( )是自訂function用來回傳員工上一個職稱資料所以語法上我們可能會這樣寫
select empname,titlename,fgetoldname(id) oldtitlename from employee where ........
看出問題所在了嗎？迷錯，上面的語法在實際執行上fgetoldname()，是每一筆員工資料都會run一次的，所以如果員工資料有5千筆，這個function就要跑5千次很嚇人吧，於是乎我們可以想想看是不是每個員工都需要去跑這個function呢，針對沒有職稱異動過的員工可能不需要吧，所以我們可以改寫如下
select empname,titlename,case when b.id is null then a.titlename else fgetoldname(id) end oldtitlename from employee a left join empchg b on a.id=b.empid
利用left join empchg 員工異動檔，先過濾掉如果沒有異動過職稱的員工，只要直接找員工主檔的職稱即可，有異動過的再去call fgetoldname()就好，如此一次5千筆的員工可能只有2百筆員工有異動過職稱，當然只會call fgetoldname() 2百次而己，2百比5千少很多吧，效能上一定會有所提升的，當然這只是個舉例，實際情況可能更複雜點，這個例子只是要說明當select語法中有用到function的時候，必須考慮一下是否有必要每筆資料去call function一次，如果沒必要那麼請試著改變它吧
(2)Loop中不斷的open cursor，例如：
想寫一個function回傳最近一次異動職稱的職稱名，那麼語法上我們可能會這樣寫
Create function ( empid varchar(50))
returns varchar(50)
as
begin
declare
@paravalue varchar(50)
declare curs cursor for
select top 1 titlename from empchg where ........
open curs
fetch next from curs into @paravalue
if @@fetch_status=0
begin
..........
..........
end
close curs
deallocate curs
return (@paravalue)
end
這樣的一個寫法看起來似乎沒有什麼問題，但仔細再看看會發現寫法笨了點也有個缺點就是外部如果不斷的呼叫這個function，那麼cursor就不斷的被open及close效能上肯定受影響，所以可以把它改寫成如下
Create function ( empid varchar(50))
returns varchar(50)
as
begin
declare
@paravalue varchar(50)
declare curs cursor for
select top 1 @paravalue=titlename from empchg where ........
if @paravalue is null
...........................
return (@paravalue)
end
是不是程式碼變聰明了點，也少了cursor不斷的被open及close的缺點囉，相同的道理也可以應用到Procedure裡，這個例子只是要說明盡可能的減少在迴圈裡去open及close cursor，面對多層迴圈的情況這種寫法是會具有相乘效果的，效能的影響會是很可怕的囉
在程式設計的工作上我們其實都常常忘了考慮效能上的影響，由其面對專案時程的壓力往往都先做到功能先寫好，其它的就再說吧，但這樣一來就埋下了無數個地雷，差別在於何時引爆以及誰被炸到而己，到頭來可能受傷的還是自己，曾經看過一篇文章在談程式設計的藝術，它說程式設計的藝術不在於程式寫的多快，畫面多漂亮，而是在於它能禁得起多年的考驗而依然能提供良好的工作結果。
== 簽名檔 ==
多數情況下潛水，回文只是隨性來一筆
http://codeian.idv.tw
|
13859
43
6438
2195
