大資料量時提高分頁的效率

如我們在之前的教程裡討論的那樣,分頁可以通過兩種方法來實現:

• 預設分頁– 你僅僅只用選中data Web control的 智能標籤的Enable Paging ; 然而,當你瀏覽頁面的時候,雖然你看到的只是一小部分資料,ObjectDataSource 還是會每次都讀取所有資料
• 自訂分頁– 通過只從資料庫讀取使用者需要瀏覽的那部分資料,提高了效能. 顯然這種方法需要你做更多的工作.

預設的分頁功能非常吸引人,因為你只需要選中一個checkbox就可以完成了.但是它每次都讀取所有的資料,這種方式在大資料量或者並發使用者多的情況下就不合適.在這樣的情況下,我們必須通過自訂分頁來使系統達到更好的效能.

自訂分頁的一個重點是要寫一個返回僅僅需要的資料的查詢語句.幸運的,Microsoft SQL Server 2005 提供了一個新的keyword,通過它我們可以寫出讀取需要的資料的查詢.在本教程裡,我們將學習在GridView裡如何使用Microsoft SQL Server 2005 的這個新的keyword來實現自訂分頁.自訂分頁和預設分頁的介面看起來一樣,但是當你從一頁轉到另一頁時,在效率上差了幾個數量級.

注意:自訂分頁帶來的效能提升程式取決於資料的總量和資料庫的負載.在本教程的最後我們會用資料來說明自訂分頁帶來的效能方面的好處.

第一步: 理解自訂分頁的過程

給資料分頁的時候,頁面顯示的資料取決於請求的是哪一頁和每頁顯示多少條.比如,想象以下我們給81個product分頁,每頁顯示10條.當我們瀏覽第一頁時,我們需要的是product 1 到 product 10.當瀏覽第二頁時,我們需要的是product 11 到 product 20,以次類推.

對於需要讀取什麼資料和分頁的頁面怎麼顯示,有三個相關的變數:

• Start Row Index – 頁面裡顯示資料的第一行的索引; 這個值可以通過頁的索引乘每頁顯示的記錄的條數加1得到. 例如, 如果一頁顯示10條資料, 那麼對第一頁來說(第一頁的索引為0), 第一行的索引為0 * 10 + 1, or 1; 對第二頁來說(索引為1), 第一行的索引為1 * 10 + 1, 即 11.
• Maximum Rows – 每頁顯示的最多記錄的條數. 之所以稱為“maximum” rows 是由於最後一頁顯示的資料可能會比page size要小. 比如, 當以每頁10條記錄來顯示81條時, 最後一頁也就是第九頁只包含一條記錄. 沒有頁面顯示的記錄條數會大於Maximum Rows 的值.
• Total Record Count – 顯示資料的總條數. 不需要知道頁面顯示什麼資料,但是記錄總數會影響到分頁. 比如, 如果對81條product記錄分頁,每頁10條,那麼總頁數為9.

對預設分頁來說,Start Row Index是由頁索引和每頁的記錄數加1得到,Maximum Rows 就是每頁的記錄數.使用預設分頁時,不管是呈現哪頁的資料,都是要讀取全部的資料,所有每行的索引都是已知的,這樣擷取Start Row Index變的沒有價值.而且,記錄的總條數是可以通過DataTable的總條數來擷取的.

自訂分頁只返回從Start Row Index 開始的Maximum Rows條記錄.在這裡有兩個要注意的地方:

• 我們必須把整個要分頁的資料和一個row index關聯起來,這樣才能從指定的Start Row Index 開始返回需要的資料.
• 我們需要提供用來分頁的資料的總條數.

在後面的兩步裡我們將寫出和上面兩點相關的SQL.除此之外，我們還將在DAL和BLL裡完成相應的方法.

第二步: 返回需要分頁的記錄的總條數

在我們學習如何返回顯示頁面需要的資料之前,我們先來看看怎麼擷取資料的總條數.因為在配置介面的時候需要用到這個資訊.我們使用SQL的COUNT aggregate function來實現這個.比如,返回Products表的總記錄條數,我們可以用如下的語句:

SQL
1 2	SELECT COUNT(*) FROM Products

我們在DAL裡添加一個方法來返回這個資訊.這個方法名為TotalNumberOfProducts() ,它會執行上面的SQL語句.

開啟App_Code/DAL 檔案夾裡的 Northwind.xsd .然後在設計器裡右鍵點ProductsTableAdapter ,選擇Add Query.和我們在以前的教程裡學習的那樣,這樣會允許我們添加一個新的DAL方法,這個方法被調用時會執行指定的SQL或預存程序.和前面的 TableAdapter 方法一樣,為這個添加一個SQL statement.

圖 1: 使用 SQL Statement

在下一個表單我們可以指定建立哪種SQL .由於查詢只返回一個值–Products表的總記錄條數–我們選擇“SELECT which returns a singe value”.

圖 2: 使用 SELECT Statement that Returns a Single Value來配置SQL

下一步是寫SQL語句.

圖 3: 使用SELECT COUNT(*) FROM Products 語句

最後給這個方法命名為TotalNumberOfProducts.

圖 4: 將方法命名為 TotalNumberOfProducts

點擊結束後,DAL裡添加了一個TotalNumberOfProducts方法.這個方法返回的值可為空白,而Count語句總是返回一個非空的值.

我們還需要在BLL中加一個方法.開啟ProductsBLL類檔案,添加一個TotalNumberOfProducts方法,這個方法要做的只是調用DAL的TotalNumberOfProducts方法.

C#
1 2 3 4	public int TotalNumberOfProducts() { return Adapter.TotalNumberOfProducts().GetValueOrDefault(); }

DAL的TotalNumberOfProducts方法返回一個可空的整型,而需要ProductsBLL類的TotalNumberOfProducts方法返回一個標準的整型.調用GetValueOrDefault方法,如果可為空白的整型為空白,則返回預設值,0.

第三步: 返回需要的資料記錄

下一步我們要在DAL和BLL裡建立接受Start Row Index 和Maximum Rows 的方法,然後返回合適的記錄.我們首先看看需要的SQL語句.我們面臨的挑戰是需要為整個分頁的記錄分配索引,用來返回從Start Row Index 開始的Maximum Records number of records條記錄.

如果在資料庫表裡已經有一個列作為索引,那麼一切會變的很簡單.我們首先會想到Products表的ProductID欄位可以滿足這個條件,第一個 Product的ProductID為1,第二個為2,以此類推.然而當一個product被刪除後,這個序列會留下間隔來,所以這個方法不行.

有兩種可以把整個要分頁的資料和一個row index關聯起來的方法.

• 使用SQL Server 2005的ROW_NUMBER() Keyword – SQL Server 2005的新特性,它可以將記錄根據一定的順序排列,每條記錄和一個等級相關 這個等級可以用來作為每條記錄的row index.
• 使用SET ROWCOUNT – SQL Server的 SET ROWCOUNT statement 可以用來指定有多少記錄需要處理; table variables 是可以存放表格式的T-SQL 變數, 和temporary tables類似. 這個方法在Microsoft SQL Server 2005 和SQL Server 2000都可以用 (ROW_NUMBER() 方法只能在SQL Server 2005裡用).
  
  
  這個思路是,為要分頁的資料建立一個table變數,這個table變數裡有一個作為主健的IDENTITY列.這樣需要分頁的每條記錄在table變數裡就和一個row index(通過IDENTITY列)關聯起來了.一旦table變數產生,串連資料庫表的SELECT語句就被執行,擷取需要的記錄.SET ROWCOUNT用來限制放到table變數裡的記錄的數量.
  當SET ROWCOUNT的值指定為Start Row Index 加上Maximum Rows時,這個方法的效率取決於被請求的頁數.對於比較前面的頁來說– 比如開始幾頁的資料– 這種方法非常有效. 但是對接近尾部的頁來說,這種方法的效率和預設分頁時差不多.

本教程用ROW_NUMBER()來實現自訂分頁.如果需要知道更多的關於table變數和SET ROWCOUNT的技術,請看 A More Efficient Method for Paging Through Large Result Sets.

以下語句用來使用ROW_NUMBER()將一個等級和返回的每條記錄關聯:

SQL
1 2 3	SELECT columnList, ROW_NUMBER() OVER(orderByClause) FROM TableName

ROW_NUMBER()返回一個根據指定排序的表示每條記錄的等級的值.比如,我們可以用以下居於查看根據價格來排序(降序)的每個product的等級:

SQL
1 2 3	SELECT ProductName, UnitPrice, ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank FROM Products

圖5 是在Visual Studio裡運行以上代碼的結果. 注意product根據價格排序,每行有一個等級.

圖 5: 返回的記錄裡每行有一個Price Rank

注意: ROW_NUMBER() 只是 SQL Server 2005裡很多排級的功能中的一種. 想瞭解更多的ROW_NUMBER()的討論,包括其它的排級功能,請看 Returning Ranked Results with Microsoft SQL Server 2005.

當使用OVER從句裡的ORDER BY 列名(UnitPrice)來排級時,SQL Server會對結果排序.為了提升大資料量查詢時的效能,可以為用來排序的列加上非叢集索引.更多的效能考慮參考Ranking Functions and Performance in SQL Server 2005.

ROW_NUMBER()返回的等級資訊無法直接在WHERE從句中使用.而在From後面的Select裡可以返回ROW_NUMBER(),並在 WHERE從句裡使用.比如,下面的語句使用一個From後的Select返回ProductName,UnitPrice,和ROW_NUMBER() 的結果,然後使用一個WHERE從句來返回price rank在11到20之間的product.

SQL
1 2 3 4 5 6 7	SELECT PriceRank, ProductName, UnitPrice FROM (SELECT ProductName, UnitPrice, ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank FROM Products ) AS ProductsWithRowNumber WHERE PriceRank BETWEEN 11 AND 20

更進一步,我們可以根據這個方法返回給定Start Row Index 和Maximum Rows 的頁的資料.

SQL
1 2 3 4 5 6 7	SELECT PriceRank, ProductName, UnitPrice FROM (SELECT ProductName, UnitPrice, ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank FROM Products ) AS ProductsWithRowNumber WHERE PriceRank > <i>StartRowIndex</i> AND PriceRank <= (<i>StartRowIndex</i> + <i>MaximumRows</i>)

注意:我們在本教程的後面會看到, ObjectDataSource 提供的StartRowIndex是從0開始的,而ROW_NUMBER()的值從1開始.因此,WHERE從句返回會嚴格返回PriceRank大於 StartRowIndex而小於StartRowIndex+MaximumRows的那些記錄.

我們已經知道如何根據給定的Start Row Index 和Maximum Rows 用ROW_NUMBER()返回特定頁的資料.現在我們需要在DAL和BLL裡實現它.

我們首先要決定根據什麼排序來分級.我們這裡用product名字的字母順序.這意味著我們還不能同時實現排序的功能.在後面的教程裡,我們將學習如何?這樣的功能.

在前面我們使用SQL statement建立DAL方法.但是TableAdapter wizard 使用的Visual Stuido裡的T-SQL 解析器不能識別帶OVER文法的ROW_NUMBER()方法.因此我們要以預存程序來建立這個DAL方法.從view menu裡選擇server explorer(Ctrl+Alt+S),展開NORTHWND.MDF 的節點.右鍵點擊預存程序,選擇增加一個新的預存程序(見圖6).

圖 6: 為Products分頁增加一個預存程序

這個預存程序帶兩個整型的輸入參數- @startRowIndex和@maximumRows- 並用ROW_NUMBER()以ProductName欄位排序,返回那些大於@startRowIndex並小於等於 @startRowIndex+@maximumRows的記錄.將以下代碼加到預存程序裡,然後儲存.

SQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

CREATE PROCEDURE dbo.GetProductsPaged ( @startRowIndex int, @maximumRows int ) AS SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, CategoryName, SupplierName FROM ( SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, (SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID) AS CategoryName, (SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) AS SupplierName, ROW_NUMBER() OVER (ORDER BY ProductName) AS RowRank FROM Products ) AS ProductsWithRowNumbers WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

建立完預存程序後,花點時間測試一下.右鍵在Server Explorer 點名為GetProductsPaged的預存程序,選擇執行.Visual Studio 會讓你輸入參數, @startRowIndex和@maximumRows(見圖7).輸入不同的值查看一下結果是什麼.

圖 7: 為 @startRowIndex 和@maximumRows Parameters輸入值

輸入參數的值後,你會看到結果.圖8的結果為兩個參數的值都為10的結果.

圖 8: 將在第二頁裡顯示的資料

完成預存程序後,我們可以建立ProductsTableAdapter 方法了.開啟Northwind.xsd ,右鍵點ProductsTableAdapter,選擇Add Query.選擇使用已經存在的預存程序.

圖 9: 使用已經存在的預存程序建立DAL Method

下一步會要我們選擇要調用的預存程序.從下拉式清單裡選擇GetProductsPaged .

圖10: 選擇GetProductsPaged

下一步要選擇預存程序返回的資料類型:表值,單一值,無值.由於GetProductsPaged 返回多條記錄,所以選擇表值.

圖 11: 為預存程序指定返回表值

最後給方法命名.象前面的方法一樣,選擇Fill a DataTable 和Return a DataTable,為第一個命名為FillPaged ,第二個為GetProductsPaged.

圖 12: 命名方法為FillPaged 和GetProductsPaged

除了建立一個DAL方法返回特定頁的products外,我們需要在BLL裡也這樣做.和DAL方法一樣,BLL的GetProductsPaged 方法帶兩個整型的輸入參數,分別為Start Row Index 和Maximum Rows,並返回在指定範圍內的記錄.在ProductsBLL 建立這個方法,僅僅調用DAL的GetProductsPaged 就可以了.

C#
1 2 3 4 5	[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)] public Northwind.ProductsDataTable GetProductsPaged(int startRowIndex, int maximumRows) { return Adapter.GetProductsPaged(startRowIndex, maximumRows); }

你可以為BLL方法的參數取任何名字.但是我們馬上會看到,選擇用startRowIndex 和maximumRows 會讓我們在配置ObjectDataSource 時方便很多.