在Web應(yīng)用程序中，對一個大數(shù)據(jù)庫結(jié)果集進行分頁已經(jīng)是一個家喻戶曉的問題了。簡單的說，你不希望所有的查詢數(shù)據(jù)顯示在一個單獨的頁面中，所以帶有分頁的顯示才是更合適的。雖然在傳統(tǒng)的asp里這并不是一個簡單的任務(wù)，但在asp.net中，DataGrid控件把這一過程簡化為只有幾行代碼。因此，在 asp.net中，分頁很簡單，但是默認(rèn)的DataGrid分頁事件會從數(shù)據(jù)庫中把所有的記錄全部讀出來放到asp.net web應(yīng)用程序中。當(dāng)你的數(shù)據(jù)在一百萬以上的時候，這將引起嚴(yán)重的性能問題（如果你不相信，你可以在你的應(yīng)用程序中執(zhí)行一個查詢，然后在任務(wù)管理器中查看 aspnet_wp.exe的內(nèi)存消耗情況）這也就是為什么需要自定義分頁行為，這樣可以保證僅獲得當(dāng)前頁需要的數(shù)據(jù)記錄。

　　在網(wǎng)上有很多關(guān)于這個問題的文章和帖子，還有一些成熟的解決方案。我寫這篇文章的目的不是向你展示一個可以解決一切問題的存儲過程，而是出于優(yōu)化已有方法，同時為你提供一個可供測試的應(yīng)用程序，這樣你就可以根據(jù)自己的需要進行開發(fā)。

　　但是我對目前網(wǎng)上介紹的方法不是很滿意。第一，使用了傳統(tǒng)的ADO，很明顯它們是為“古老”的asp而寫的。剩下的一些方法就是SQL Server存儲過程，并且其中的一些由于相應(yīng)時間過慢而無法使用，正如你在文章最后所看到的性能結(jié)果一樣，但是還是有一些引起了我的注意。

　　通用化

　　我要對對目前常用的三個方法進行仔細的分析，它們是臨時表(TempTable)，動態(tài)SQL（DynamicSQL）和行計數(shù) (Rowcount)。在下文中，我更愿意把第二個方法稱為（升序-降序）Asc-Desc方法。我不認(rèn)為動態(tài)SQL是一個好名字，因為你也可以把動態(tài) SQL邏輯應(yīng)用于另一個方法中。所有這些存儲過程的通病在于，你不得不估計哪些列是你即將要排序的，而不僅僅是估計主鍵列（PK Columns）而已，這可能導(dǎo)致一系列的問題——對于每個查詢來說，你需要通過分頁顯示，也就是說對于每不同的排序列你必須有許多不同的分頁查詢，這意味著你要么給每個排序列做不同的存儲過程（無論使用哪種分頁方法），也么你必須借助動態(tài)SQL的幫助把這個功能放在一個存儲過程中。這兩個方法對于性能有微小的影響，但是它增加了可維護性，特別是當(dāng)你需要使用這個方法顯示不同的查詢。因此，在本文中我會嘗試使用動態(tài)SQL對所有的存儲過程進行歸納，但是由于一些原因，我們只能對實現(xiàn)部分的通用性，因此你還是得為復(fù)雜查詢寫?yīng)毩⒌拇鎯^程。

　　允許包括主鍵列在內(nèi)的所有排序字段的第二個問題在于，如果那些列沒有作適當(dāng)?shù)乃饕敲催@些方法一個也幫不上忙。在所有這些方法中，對于一個分頁源必須先做排序，對于大數(shù)據(jù)表來說，使用非索引列排序的成本是可以忽略不計的。在這種情況下，由于相應(yīng)時間過長，所有的存儲過程都是無法在實際情況下使用的。（相應(yīng)的時間各有不同，從幾秒鐘到幾分鐘不等，這要根據(jù)表的大小和所要獲得的第一個記錄而定）。其他列的索引會帶來額外的不希望出現(xiàn)的性能問題，例如如果你每天的導(dǎo)入數(shù)據(jù)很多，它有可能變得很慢。

　　臨時表

　　首先，我準(zhǔn)備先來說一下臨時表方法，這是一個廣泛被建議使用的解決方案，我在項目中遇到過好幾次了。下面讓我們來看看這個方法的實質(zhì)：

CREATE TABLE #Temp(
　ID int IDENTITY PRIMARY KEY,
　PK /*heregoesPKtype*/
)

INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn

SELECT  FROM Table JOIN # Temp temp ON Table.PK = temp .PK ORDER BY temp .ID WHERE ID > @StartRow AND ID< @EndRow

　　通過把所有的行拷貝到臨時表中，我們可以對查詢進一步的優(yōu)化(SELECT TOP EndRow …)，但是關(guān)鍵在于最壞情況——一個包含100萬記錄的表就會產(chǎn)生一個100萬條記錄的臨時表。考慮到這樣的情況，再看看上面文章的結(jié)果，我決定在我的測試中放棄該方法

　　升序-降序

　　這個方法在子查詢中使用默認(rèn)排序，在主查詢中使用反向排序，原理是這樣的：

DECLARE @temp TABLE(
　PK /* PKType */
　NOT NULL PRIMARY
)

INSERT INTO @temp SELECT TOP @PageSize PK FROM
(
　SELECT TOP(@StartRow + @PageSize )
　PK,
　SortColumn /* If sorting column is defferent from the PK,SortColumn must
　be fetched as well,otherwise just the PK is necessary
　*/

　ORDER BY SortColumn
　/*
　　defaultorder–typicallyASC
　*/
)

ORDER BY SortColumn
/*
reversed　default　order–typicallyDESC
*/

　SELECT  FROM Table JOIN @Temp temp ON Table　.PK= temp .PK
　ORDER BY SortColumn
　/*
　　defaultorder
　*/

　　行計數(shù)

　　這個方法的基本邏輯依賴于SQL中的SET ROWCOUNT表達式，這樣可以跳過不必要的行并且獲得需要的行記錄：

DECLARE @Sort /* the type of the sorting column */

SET ROWCOUNT @StartRow

SELECT @Sort=SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize

SELECT  FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

　　子查詢

　　還有兩個方法也是我考慮過的，他們的來源不同。第一個是眾所周知的三角查詢(Triple Query)或者說自查詢方法，在本文中，我也用一個類似的包含所有其他存儲過程的通用邏輯。這里的原理是連接到整個過程中，我對原始代碼做了一些縮減，因為recordcount在我的測試中不需要）

SELECT  FROM Table WHERE PK IN(
SELECT TOP @PageSize PK FROM Table WHERE PK NOT IN
(
SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn)
ORDER BY SortColumn)
ORDER BY SortColumn

　　游標(biāo)

　　在看google討論組的時候，我找到了最后一個方法。該方法是用了一個服務(wù)器端動態(tài)游標(biāo)。許多人試圖避免使用游標(biāo)，因為游標(biāo)沒有關(guān)系可言，以及有序性導(dǎo)致其效率不高，但回過頭來看，分頁其實是一個有序的任務(wù)，無論你使用哪種方法，你都必須回到開始行記錄。在之前的方法中，先選擇所有在開始記錄之前的所有行，加上需要的行記錄，然后刪除所有之前的行。動態(tài)游標(biāo)有一個FETCH RELATIVE選項可以完成魔法般的跳轉(zhuǎn)。基本的邏輯如下：

DECLARE @PK /* PKType */

DECLARE @tblPK

TABLE(
　PK　/*PKType*/ NOT NULL PRIMARY KEY
)

DECLARE PagingCursor CURSOR DYNAMICREAD_ONLY FOR
SELECT @PK FROM Table ORDER BY SortColumn

OPEN PagingCursor
FETCH RELATIVE @StartRow FROM PagingCursor INTO @PK

WHILE @PageSize>0 AND @@FETCH_STATUS =0
BEGIN
　INSERT @tblPK(PK) VALUES(@PK)
　FETCH NEXT FROM PagingCursor INTO @PK
　SET @PageSize = @PageSize - 1
END

CLOSE
　PagingCursor
DEALLOCATE
　PagingCursor

SELECT  FROM Table JOIN @tblPK temp ON Table .PK= temp .PK
ORDER BY SortColumn

　　復(fù)雜查詢的通用化

　　我在之前指出，所有的存儲過程都是用動態(tài)SQL實現(xiàn)通用性的，因此，理論上它們可以用任何種類的復(fù)雜查詢。下面有一個基于Northwind數(shù)據(jù)庫的復(fù)雜查詢例子。

SELECT Customers.ContactName AS Customer, Customers.Address + ' , ' + Customers.City + ', '+ Customers.Country
AS Address, SUM([OrderDetails].UnitPrice*[ OrderDetails ] .Quantity)
AS [Totalmoneyspent]

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

INNER JOIN [ OrderDetails ] ON Orders.OrderID = [ OrderDetails].OrderID

WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico '

GROUP BY Customers.ContactName,Customers.Address,Customers.City, Customers.Country
HAVING(SUM([OrderDetails].UnitPrice * [ OrderDetails ] .Quantity)) > 1000

ORDER BY Customer DESC ,Address DESC

　　返回第二個頁面的分頁存儲調(diào)用如下：

EXEC ProcedureName

/*Tables */

'
Customers
INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
INNER JOIN [OrderDetails] ON Orders.OrderID=[OrderDetails].OrderID
'
,
/* PK */

'
Customers.CustomerID
'
,
/* ORDERBY */

'
Customers.ContactName DESC,Customers.AddressDESC
'
,

/*PageNumber */

2
,

/*PageSize */

10
,

/*Fields */

'
Customers.Contact Name AS Customer,
Customers.Address+'' , '' +Customers.City+ '' , '' +Customers.Country ASAddress, SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity)AS[Totalmoneyspent]
'
,

/*Filter */

'
Customers.Country<>'' USA '' ANDCustomers.Country<> '' Mexico ''' ,

/*GroupBy */

'
Customers.CustomerID,Customers.ContactName,Customers.Address,
Customers.City,Customers.Country
HAVING(SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity))>1000
'

　　值得注意的是，在原始查詢中在ORDER BY語句中使用了別名，但你最好不要在分頁存儲過程中這么做，因為這樣的話跳過開始記錄之前的行是很消耗時間的。其實有很多種方法可以用于實現(xiàn)，但原則是不要在一開始把所有的字段包括進去，而僅僅是包括主鍵列（等同于RowCount方法中的排序列），這樣可以加快任務(wù)完成速度。只有在請求頁中，才獲得所有需要的字段。并且，在最終查詢中不存在字段別名，在跳行查詢中，必須提前使用索引列。

　　行計數(shù)(RowCount)存儲過程有一個另外的問題，要實現(xiàn)通用化，在ORDER BY語句中只允許有一個列，這也是升序-降序方法和游標(biāo)方法的問題，雖然他們可以對幾個列進行排序，但是必須保證主鍵中只有一個字段。我猜如果用更多的動態(tài)SQL是可以解決這個問題的，但是在我看來這不是很值得。雖然這樣的情況很有可能發(fā)生，但他們發(fā)生的頻率不是很高。通常你可以用上面的原理也獨立的分頁存儲過程。

　　性能測試

　　在測試中，我使用了四種方法，如果你有更好的方法的話，我很有興趣知道。不管如何，我需要對這些方法進行比較，并且評估它們的性能。首先我的第一個想法就是寫一個asp.net包含分頁DataGrid的測試應(yīng)用程序，然后測試頁面結(jié)果。當(dāng)然，這無法反映存儲過程的真實響應(yīng)時間，所以控制臺應(yīng)用程序顯得更加適合。我還加入了一個Web應(yīng)用程序，但不是為了性能測試，而是一個關(guān)于DataGrid自定義分頁和存儲過程一起工作的例子。

　　在測試中，我使用了一個自動生成得大數(shù)據(jù)表，大概插入了500000條數(shù)據(jù)。如果你沒有一張這樣的表來做實驗，你可以點擊這里下載一段用于生成數(shù)據(jù)的表設(shè)計和存儲過程腳本。我沒有使用一個自增的主鍵列，而是用一個唯一識別碼來識別記錄的。如果我使用上面提到的腳本，你可能會考慮在生成表之后添加一個自增列，這些自增數(shù)據(jù)會根據(jù)主鍵進行數(shù)字排序，這也意味著你打算用一個帶有主鍵排序的分頁存儲過程來獲得當(dāng)前頁的數(shù)據(jù)。

　　為了實現(xiàn)性能測試，我是通過一個循環(huán)多次調(diào)用一個特定的存儲過程，然后計算平均相應(yīng)時間來實現(xiàn)的。考慮到緩存的原因，為了更準(zhǔn)確地建模實際情況——同一頁面對于一個存儲過程的多次調(diào)用獲得數(shù)據(jù)的時間通常是不適合用來做評估的，因此，我們在調(diào)用同一個存儲過程時，每一次調(diào)用所請求的頁碼應(yīng)該是隨機的。當(dāng)然，我們必須假設(shè)頁的數(shù)量是固定的，10-20頁，不同頁碼的數(shù)據(jù)可能被獲取很多次，但是是隨機獲取的。

　　有一點我們很容易注意到，相應(yīng)時間是由要獲取的頁數(shù)據(jù)相對于結(jié)果集開始的位置的距離決定的，越是遠離結(jié)果集的開始位置，就有越多的記錄要跳過，這也是我為什么不把前20也包括進我的隨機序列的原因。作為替換，我會使用2的n次方個頁面，循環(huán)的大小是需要的不同頁的數(shù)量*1000，所以，每個頁面幾乎都被獲取了1000次（由于隨機原因，肯定會有所偏差）

　　結(jié)果

　　這里有我的測試結(jié)果：






　　結(jié)論

　　測試是按照從性能最好到最差的順序進行的——行計數(shù)、游標(biāo)、升序-降序、子查詢。有一件事很有趣，通常人們很少會訪問前五頁之后的頁面，因此子查詢方法可能在這種情況下滿足你的需要，這得看你的結(jié)果集的大小和對于遠距離（distant）頁面的發(fā)生頻率預(yù)測，你也很有可能使用這些方法的組合模式。如果是我，在任何情況下，我都更喜歡用行計數(shù)方法，它運行起來十分不錯，即使對于第一頁也是如此，這里的“任何情況”代表了一些很難實現(xiàn)通用化的情況，在這種情況下，我會使用游標(biāo)。（對于前兩種我可能使用子查詢方法，之后再用游標(biāo)方法）