[數(shù)據(jù)庫] 通用分頁存儲過程第3/5頁

更新時間：2007年02月09日 00:00:00 作者：

升序-降序

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

DECLARE @temp TABLE (  
    PK  /* PK Type */ 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 /* default order – typically ASC */)   
ORDER BY SortColumn /* reversed default order – typically DESC */  

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

行計數(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)或者說自查詢方法，我找的一個比較透徹的方法在下面的文章中有描述

SQL Server服務器端分頁

雖然你需要訂閱，但是可以下載一個包含子查詢存儲過程定義的zip文件。列表4 SELECT_WITH_PAGINGStoredProcedure.txt文件包含一個完整的通用的動態(tài)SQL。在本文中，我也用一個類似的包含所有其他存儲過程的通用邏輯。這里的原理是連接到整個過程中，我對原始代碼做了一些縮減，因為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 

游標

在看google討論組的時候，我找到了最后一個方法，你可以點這里查看原始帖子。該方法是用了一個服務器端動態(tài)游標。許多人試圖避免使用游標，因為游標沒有關系可言，以及有序性導致其效率不高，但回過頭來看，分頁其實是一個有序的任務，無論你使用哪種方法，你都必須回到開始行記錄。在之前的方法中，先選擇所有在開始記錄之前的所有行，加上需要的行記錄，然后刪除所有之前的行。動態(tài)游標有一個FETCH RELATIVE選項可以完成魔法般的跳轉(zhuǎn)?；镜倪壿嬋缦拢?

復制代碼代碼如下:

DECLARE @PK /* PK Type */  
DECLARE @tblPK TABLE (  
    PK /* PK Type */ NOT NULL PRIMARY KEY  
)  

DECLARE PagingCursor CURSOR DYNAMIC READ_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 

復雜查詢的通用化

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

復制代碼代碼如下:

SELECT Customers.ContactName AS Customer,   
       Customers.Address + ', ' + Customers.City + ', ' +   
                                                Customers.Country AS Address,   
       SUM([Order Details].UnitPrice*[Order Details].Quantity) AS   
                                                          [Total money spent]   
FROM Customers  
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID  
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID  
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'  
GROUP BY Customers.ContactName, Customers.Address, Customers.City,   
         Customers.Country   
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000  
ORDER BY Customer DESC, Address DESC  
返回第二個頁面的分頁存儲調(diào)用如下：  
EXEC ProcedureName  
/* Tables */  
'Customers  
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID  
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID',  
/* PK */  
'Customers.CustomerID',  
/* ORDER BY */  
'Customers.ContactName DESC, Customers.Address DESC',  
/* PageNumber */  
2,  
/* Page Size */  
10,  
/* Fields */  
'Customers.ContactName AS Customer,  
Customers.Address + '', '' + Customers.City + '', '' + Customers.Country   
                                                                  AS Address,   
SUM([Order Details].UnitPrice*[Order Details].Quantity) AS [Total money spent]',  
/* Filter */  
'Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''',  
/*Group By*/  
'Customers.CustomerID, Customers.ContactName, Customers.Address,   
Customers.City, Customers.Country   
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000' 

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

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

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

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

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

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