ASP.NET 2.0自訂分頁技巧

這篇文章主要目的是介紹ASP.NET 2.0中的自訂分頁功能,以提升預設的分頁功能效能低落的問題,資料庫方面主要是以SQL Server 2005為主,其他資料庫也可引用本文的相關技巧來實作

在撰寫網頁應用程式時,經常會使用分頁來讓使用者瀏灠他們所查詢的資料,在ASP.NET 1.1時,我們就可以利用DataGrid控制項,只要輕鬆設幾個屬性,然後在分頁事件中撰寫一些程式就可以達成分頁功能,到了ASP.NET 2.0所提供的GridView控制項,在結合SqlDataSource或ObjectDataSource控制項後,更是可以簡化到一行程式都不用寫就可以完成分頁功能,雖然大幅簡化了程式員的負擔,但是,由於ASP.NET的DataGrid及GridView控制項的分頁作法,在遇到底層資料表資料量大時,反而會造成效能不佳的問題,Why?原因就出在DataGrid及GridView的分頁作法,是將整個查詢結果傳回至Web Server後再進行分頁,而不是在資料庫端完成分頁僅傳回所需那一頁的資料,也就是說,如果你的查詢是要傳回1萬筆資料的第1頁,實際上,DataGrid及GridView每次都是將資料庫1萬筆資料撈回至Web Server中,然後再Binding所需的那1頁的資料回Browser端,而User每次按下一頁,又重覆回DB撈1萬筆到Web Server端分頁,所以效能當然不好!
要解決這個問題,最好的做法是,”僅從DB傳回所需的那一頁資料”,也就是說,分頁的動作應該在DB完成,由DB僅傳回查詢結果的該頁的資料,而不是整個查詢結果回傳至Web端再由Control去做分頁的動作,當然,由DB來做分頁,做法就會跟所使用的DB有關係,舉例來說,Oracle可以用RowNum函式傳回結果集中指定的第幾筆到第幾筆的記錄,MySql也有Limit 0,10的語法限制從第幾筆開始只傳回幾筆,而SQL Server 2005則是有Row_Number函式,跟Oracle的RowNum用法類似,所以,要用自訂分頁,可能就無法使用標準的ANSI SQL用法來達到應用程式與DB Independent的彈性,而且在實作上也比預設的分頁做法麻煩了點,但是,在效能優先的前提下,犧牲了平台的透通性及一點點的便利性,但是卻換來了效能的大幅改善,我想這個投資還是蠻划算的,文中我也會針對預設分頁與自訂分頁的效能做一比較,看到實際數據可能就比較容易勸服你花時間來改了.
ASP.NET 2.0預設分頁作法
先來看看ASP.NET 2.0的預設分頁做法好了,首先在網頁上放一個GridView控制項及一個SqlDataSource控制項, 然後點選SqlDataSource的智慧標籤,設定資料來源,設定好所要查的DB及查詢語法,為了能比較出兩種做法的差異,我在Northwind新增了一個LargeEmployees的Table,裏面有5萬筆資料,你也可以自己找一個筆數較多的Table來測試 ,設完SqlDataSource後再設定Gridview的資料來源為SqlDataSource1,然後點撰GridView的智慧標籤,勾選”啟用分頁”,完成後如下圖所示 

設好後就可以直接執行,然後按幾次分頁看看,如果你像我Table中有5萬筆,當你每次按分頁時都會有2-3秒左右的延遲,這就是因為預設分頁實際上是每次都從Table把5萬筆資料撈回至Web Server中分頁所致.
另外,你會發現一行程式都不用寫,程式就可以跑了,Why?在ASP.NET 1.1時代,像這樣的分頁程式,你必須自己寫程式將查回來的DataTable設定給DataGrid的DataSource屬性,然後在Page_Load去呼叫Page.DataBind或DataGrid.DataBind方法,而且還要在PageIndexChanged去設定DataGrid的CurrentPageIndex屬性並且重新呼叫資料繫結,才能完成DataGrid的分頁功能,而ASP.NET 2.0僅需幾個屬性設定就完成所有的功能就完成了,這都要歸功於ASP.NET 2.0新的SqlDataSource控制項,SqlDataSource控制項會幫你處理好跟資料繫結控制項(如GridView)的繫結動作及相關操作方法,你都不需再自行撰寫有關控制項的資料處理動作相關程式,例如分頁排序編輯等,當然你也可以不用SqlDataSource,不過這樣一來,即使你用GridView,我們仍需像1.1時代那樣自己去處理資料繫結及資料處理動作等相關細節就是了.當然有人可能會認為,使用SqlDataSource就必須把查詢之SQL語法寫在控制項的屬性中,或是ASPX網頁的程式碼中,這樣違反了3層式設計上資料處理與UI程式分離的原則,當然微軟也幫你想到這點,你可以用ASP.NET 2.0的ObjectDataSource控制項來達到一樣的功能,且又可以將資料處理動作寫在資料處理元件中,而且ObjectDataSource中有幾個屬性是可以直接搭配自訂分頁來使用,所以底下就以ObjectDataSource說明自訂分頁的作法.
ASP.NET 2.0自訂分頁作法
前面提到,自訂分頁最主要的技巧是”僅從DB傳回所需的那一頁資料”,所以資料庫必須有方法讓我們能將查詢結果中,指定第幾筆到第幾筆來回傳,而在SQL Server 2005中有一個新的函式Row_Number(),我們可以在查詢結果中增加一欄位回傳Row_Number,然後再用SubQuery的技巧從子查詢中得到每筆記錄的序號,再用此欄位來篩選出所要那一頁的筆數,舉例來講,如果每頁10筆,而你要回傳第2頁的資料,你可以用以下查詢語法

SELECT RowNum, EmployeeID, LastName, FirstName

FROM

(SELECT EmployeeID, LastName, FirstName, 

ROW_NUMBER() OVER(ORDER BY EmployeeID) as RowNum

FROM Employees e

) as EmployeeInfo

WHERE RowNum BETWEEN 11 AND 20

了解如何利用SQL Server 2005來取出所要的那一頁的資料後,接下來我們就要來寫個資料存取元件,用來提供給ObjectDataSource使用,請在App_Code中新增一類別,然後撰寫以下程式碼:

   1: <System.ComponentModel.DataObject()> _

   2: Public Class MyData

   3:     Dim cn As New SqlConnection( _

   4:         ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)

   5:     <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, False)> _

   6:     Public Function GetEmployees(ByVal startRowIndex As Integer, _

   7:                                  ByVal maximumRows As Integer) As DataTable

   8:         Dim ds As New DataSet

   9:         Dim sql As String = "SELECT RowNum, *" _

  10:                         & " FROM (SELECT * ,ROW_NUMBER() OVER(ORDER BY EmployeeID) as RowNum " _

  11:                         & " FROM Employees e) as EmployeeData " _

  12:                         & " WHERE RowNum > @startRowIndex AND RowNum <= (@startRowIndex + @maximumRows)"

  13:         Dim cmd As New SqlDataAdapter(sql, cn)

  14:         cmd.SelectCommand.Parameters.Add(New SqlParameter("@startRowIndex", startRowIndex))

  15:         cmd.SelectCommand.Parameters.Add(New SqlParameter("@maximumRows", maximumRows))

  16:         cmd.Fill(ds, " Employees")

  17:         Return ds.Tables(0)

  18:     End Function

  19:  

  20:     Public Function GetEmployeesCount(ByVal startRowIndex As Integer, _

  21:                                       ByVal maximumRows As Integer) As Integer

  22:         Dim sql As String = "SELECT Count(*) FROM Employees"

  23:         Dim cmd As New SqlCommand(sql, cn)

  24:         Using cn

  25:             cn.Open()

  26:             Return cmd.ExecuteScalar

  27:         End Using

  28:     End Function

  29: End Class

在這個類別中GetEmployees是執行查詢的方法,注意方法中加入了兩個參數startRowIndex及maxiumRows,這是必須的,而且參數名稱最好都照抄不變,至於為什麼稍候會說明,ObjectDataSource在呼叫這個方法時,會自動將查詢該頁的第1筆索引值及每頁筆數傳入,而方法中就可以用Row_Number函數配合RowNum > @startRowIndex AND RowNum <= (@startRowIndex + @maximumRows)條件來讓SQL傳回所要的那一頁資料了.而GetEmployeesCount方法則是查詢所傳回的總筆數,為何還要寫一方法回傳查詢的筆數呢?那是因為我們從DB只傳回所要的那一頁筆數,但是ObjectDataSource必須要知道你這個查詢總共有幾筆才知道要分幾頁,所以除了原來的查詢方法之外,還必需多寫一個方法回傳查詢之總筆數,另外;在類別前有宣告了一個System.ComponentModel.DataObject屬性,還有查詢方法前宣告了System.ComponentModel.DataObjectMethodAttribute屬性,這兩個Attribute是用來讓ObjectDataSource資料來源精靈能夠篩選出我們這個元件及方法.

接下來另外新增一網頁來測試自訂分頁,一樣先將GridView接到網頁中,只不過這次拉的是ObjectDataSource而不是SqlDataSource,然後按下智慧標籤設定ObjectDataSource的資料來源,此時會出現設定資料來源精靈來幫你設定,第一個步驟是選擇你的資料來源物件,當你勾選僅顯示資料元件時,左邊只會列出你有標示System.ComponentModel.DataObject的類別,如下所示:



接下來選查詢的方法,在Select頁籤中選取GetCustomers方法,同樣的,精靈只會列出我們有標示System.ComponentModel.DataObjectMethodAttribute的方法,選好後如下圖所示:



接下來的定義參數是指定你Select方法中所用的參數值要從何取得,目前我們的方法只有startRowIndex及maxiumRows兩個參數,由於這兩個參數是ObjectDataSource會自行取得傳給Select Method,所以我們可略過此步驟直接按完成即可,當然,如果你的方法有其他參數,比如Where的條件等,就必須設定該參數的值從何取得



完成精靈後,在ObjectDataSource的屬性視窗,記得設定EnableCaching=True,還有在SelectCountMethod屬性中,填入我們用來查詢總筆數的方法名稱,例如本例的GetEmployeesCount,另外請注意MaximumRowsParameterName和StartRowIndexParameterName這兩個屬性要填入我們查詢方法中用來代表該頁記錄第一筆索引值和每頁筆數這兩個參數名稱,由於預設這兩個屬性是maximumRows和startRowIndex,所以我們剛才在寫查詢方法時,才會故意用這兩個參數名稱,這樣ObjectDataSource就不用改這兩個屬性的值了.設好後如下圖所示:



接下來就可以瀏灠此網頁,然後按幾次分頁看看,你會發現比剛才的結果明顯變快了,當然,數字會說話,我們可以啟動ASP.NET的Trace功能看看兩者的執行來做一比較,請在這兩個網頁的第一行Page宣告中加入Trace=”true”屬性啟動ASP.NET Trace功能,然後瀏灠預設分頁的結果,你會看到網頁下半部會出現Trace的資訊,如下圖所示



請看到Begin PreRender及EndPreRender這兩行,ObjectDataSource的資料繫結動作會在Begin PreRender及End PreRender事件中發生,所以我們可以觀察End PreRener這行最後一欄的數據,從最末項開始是指跟上一個事件的時間差,就可以知道整個資料從抓取到繫結完成總共花的時間,當然你可以多跑幾次取一個最快的來比較,例如本例中的0.12717,另外我們看一下預設分頁的結果,如下圖所示:



你可以看到預設分頁要花上1.653981秒,自訂分頁比預設分頁足足要快上92%,如果資料量愈大,所增加的效能將會愈明顯,另外,我們可以開啟SQL Server Profiler，來看看在DB端所帶來的效能差距,首先我們來看預設分頁的結果,如下圖所示:



請看SQL:BatchCompleted事件,可以看到Reads數380,意思是SQL Server讀了380個Page的資料出來,且Duration為726毫秒.

另外看一下自訂分頁的Profiler結果如下:



請看到RPC:Completed那一行,我們分頁的SQL指令,SQL Server只讀了3個Page就得到我們要的資料,且Duration是0!所以可以看的出來,自訂分頁的確只從DB中讀出真正所需的那一頁資料至Web Server端,如此也大幅減輕了SQL Server的負擔,不過你可能會發現,自訂分頁中,ObjectDataSource多送了一個Select count(*)的指令到SQL Server,即使是每次按下一頁也不例外,那是因為ObjectDataSource在每次做分頁時,都會去呼叫SelectCountMethod屬性中所定義的方法來取得總筆數,當然,如果你想要再更快一點的話,你可以修改資料存取元件中回傳資料筆數的Method,利用快取機制將讀出來的筆數放至快取中以便隨後做用,如此就連Select Count(*)的動作都省下來了,修改過的GetEmployeesCount方法如下:

   1: Public Function GetEmployeesCount(ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As Integer

   2:     Dim i As Integer

   3:     If HttpContext.Current.Cache.Item("EmployeesCount") Is Nothing Then

   4:         Dim sql As String = "SELECT Count(*) FROM [Employees]"

   5:         Dim cmd As New SqlCommand(sql, cn)

   6:         Using cn

   7:             cn.Open()

   8:             i = cmd.ExecuteScalar   '將查到的筆數記錄到Cache中,並設定5分鐘後失效

   9:             HttpContext.Current.Cache.Insert("EmployeesCount", i, _Nothing, _DateTime.Now.AddMinutes(5), _TimeSpan.Zero)

  10:         End Using

  11:     Else

  12:         i = CInt(HttpContext.Current.Cache("EmployeesCount"))

  13:     End If

  14:     Return i

  15: End Function

  16:  

修改過後我們再看一次執行結果的Trace,除了第一次的結果以外,第二次以後每次按分頁都只剩0.001611秒,又更快了點!

如果你的資料庫是SQL Server 2000,由於SQL Server 2000不支援Row_Number函數,所以你必須用其他方式來做分頁,比如寫一Store Procedure,建立一跟原查詢結構一樣的Table Variable,並增加一Identity欄位,然後將你的資料倒至Table Variable中,如此可模擬出Row_Number的效果,然後就一樣可以用此Identity欄位做條件篩選出你要的那一頁的資料出來,當然ASP.NET這邊的實作是不變的,你可以參考文末的參考資料,至MSDN網站上看實作的說明及範例.

結論

以下將自訂分頁做法,摘要整理如下:

1. 撰寫一資料存取元件,實作查詢方法及回傳總筆數的方法
2. 網頁上放置ObjectDataSource,設定SelectMethod,SelectCountMethod分別對應到資料存取元件中的兩個方法,並注意MaximumRowsParameterName及StartRowIndexParameterName是否與資料存取方法中的兩個參數名稱一致,然後再設定EnableCaching屬性為True
3. 放置GridView,設定啟用分頁(AllowPaging=True),資料來源為ObjectDataSource1

參考資料:

1. Efficiently Paging Through Large Amounts of Data
2. How To: Page Records in .NET Applications