減少網路傳輸的資料量,分頁從資料庫就做好
日前為了解決公司系統效能的問題,真的是挖了不少解決方案出來,其中一個比較有意思的就是分頁處理.
分頁方式其實有非常的多種,例如把資料全部丟到Web,再分頁顯示,或是直接在DataAdapter.Fill時,下筆數,或是用Selete top 加上一些過濾及排序等動作.
但這些做法,都不能解決我現在的頻寬問題,因為分店連回總公司只有2M/256K的頻寬,如果一次倒回大量的資料,一定會打爆網路,而總公司的架構部份,WebService與DataBase也是不同台的伺服器,兩台伺服器也是需透過網路來傳輸,所以思考方向會是在如何在DataBase那,就做好分頁的動作. 不要有大量的資料在網路上傳輸.
為了這個問題,找了不少方法,像是Select top的方式,但因為程式查詢彈性的關係,造成這部份的邏輯相當的複雜,效能也不好. 最後找到SQL 2005才有的Row_number()這個Function可以用. 從一些參考網站所做的效能評比中,也算是數一數二的做法,所以這做法也是最後被公司採用的解決方案.
以下就是說明Row_number()的基本做法.
select a.UserName,a.UserID from (select row_number() over (order by UserID) as UID,UserName,UserID from UserDataInfo where bmi>20) as a where a.UID between @SP and @EP
這段語法就是關鍵所在,正常來說,我的語法只有紅字部份,帶出所有bmi>20的人員,一次把資料全部回傳,但這裡卻多了一段藍字在裡面,Row_Number()就是將符合條件的結果,再從1開始,依序給予編號,所以回傳的結果就會是
UID UserName UserID
1 Jeff A0001
2 Jerry C0014
3 Judy C0096
4 Mark D0002
5 Jason D0010
6 Rober D0011
7 Martin D0022
所以黑色部份的語法,就依這個的查詢結果,取出UID介於幾號到幾號之間. 所以就可以在DataAccess端,依據設定的PageSize,來算頁次,再依目前的頁次與PageSize去帶回所需顯示的資料範圍.
如果不分頁的話,一次載入上萬筆資料是很可怕的,尤其是透過2M/256K的頻寬,這是ERP系統,不是P2P下載平台,使用者不可能等,如果用分頁方式,就只會一次傳回指定的筆數,大幅的減少網路的負擔,而且這是在DB就做好的動作,所以從DataBase到WebService這段的網路問題也解決了.
這做法也不是沒有缺點,例如在換頁過程中,突然又有幾筆資料符合或不符合了,其順序就會異動,就有可能剛好在換頁時,某筆資料會沒看到,可能跳到前頁或下頁去了. 這部份就看各家使用者了.
另一個問題就是筆數一多時,頁次愈換到後面,速度會愈慢,但這也是百萬筆資料的事,當有這麼多資料時,應該也沒有什麼人會一頁一頁的去看完它.
Row_number()更多的說明,參考MSDN 或是GOOGLE大神
2008/10/17補充 :
Oracle可以用Rownum的方式取代Row_number()
.jpg)