快捷導(dǎo)航

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

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

升序-降序

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

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表達(dá)式，這樣可以跳過不必要的行并且獲得需要的行記錄

復(fù)制代碼代碼如下:

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服務(wù)器端分頁

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

復(fù)制代碼代碼如下:

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)?；镜倪壿嬋缦拢?

復(fù)制代碼代碼如下:

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 

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

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

復(fù)制代碼代碼如下:

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)，但原則是不要在一開始把所有的字段包括進(jìn)去，而僅僅是包括主鍵列（等同于RowCount方法中的排序列），這樣可以加快任務(wù)完成速度。只有在請求頁中，才獲得所有需要的字段。并且，在最終查詢中不存在字段別名，在跳行查詢中，必須提前使用索引列。

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

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

在測試中，我使用了一個自動生成得大數(shù)據(jù)表，大概插入了500000條數(shù)據(jù)。如果你沒有一張這樣的表來做實驗，你可以點擊這里下載一段用于生成數(shù)據(jù)的表設(shè)計和存儲過程腳本。我沒有使用一個自增的主鍵列，而是用一個唯一識別碼來識別記錄的。如果我使用上面提到的腳本，你可能會考慮在生成表之后添加一個自增列，這些自增數(shù)據(jù)會根據(jù)主鍵進(jìn)行數(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é)果集開始的位置的距離決定的，越是遠(yuǎn)離結(jié)果集的開始位置，就有越多的記錄要跳過，這也是我為什么不把前20也包括進(jìn)我的隨機序列的原因。作為替換，我會使用2的n次方個頁面，循環(huán)的大小是需要的不同頁的數(shù)量*1000，所以，每個頁面幾乎都被獲取了1000次（由于隨機原因，肯定會有所偏差）