從存儲過程分頁談起
為什麼要選擇用存儲過程分頁呢？其實原因很簡單，數據庫查詢功能的性能終究是有限的。即使我們對數據庫進行了最優配置，對數據表設計再三斟酌，然而一旦面臨海量數據，且返回結果集較大的時候，常規的查詢語句就無能為力了。一般說來，當返回的結果集超過總數量的40%時，數據庫層面上的優化就顯得束手無策了。此時，我相信大多數同行首先想到的便是分頁。當我們指定好每頁的記錄總數（PageSize）和當前頁的索引（CurrentPage）時，理想的狀況便發生了，首先我們不再從一個海量數據（百萬級）中檢索出超過40%的數據量，我們可以做個估算如果每頁顯示50條記錄，那麼也就是從100萬條記錄中查詢50條記錄，這個比例我相信大家都比較清楚。其次，網絡中的數據通信量將大大縮減，我想這筆帳就不用我再做過多解釋。同時，查詢數量的減少對內存開銷、頁面的刷新、用戶的等待時間都會得到相應的減少。
好處頗多，如何實現呢？我大致總結了以下幾種實現方式。下面，我將一一介紹：
我將表分成兩類
1.數據表中有唯一的自增索引，並且這個字段沒有出現斷號現象。在此我姑且稱之為連續表，後面文章中出現的連續表就是指此類表。
2.數據表中不存在唯一的自增索引，或者存在唯一自增索引，但是由於刪除記錄等操作讓該索引不連續，對於這類表我稱之為不聯系表。
分頁之前我們模擬一張產品表，其結構如下圖：
插入1000000條記錄
DECLARE @I INT
SET @I=0
WHILE(@I<1000000)
BEGIN
INSERT INTO PRODUCT(ProductName,ProductAddDate) VALUES('產品名',GETDATE())
SET @I=@I+1
同時給出存儲過程的結構，由於今天只討論分頁，所以查詢條件、排序分組方法等請讀者自行補充。
CREATE PROCEDURE SelectProduct
@PageSize int,
@CurrentPage int,
@TotalPage int output
BEGIN
--select method
說明：由於上一篇文章的分頁控件需要一個總頁數的參數，因此將@TotalPage 作為輸出參數返回符合記錄的總頁數。@CurrentPage為0表示
第一頁。
執行存儲過程代碼
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @return_value int,
@TotalPage int
EXEC @return_value = [dbo].[SelectProduct]
@PageSize = 50,
@CurrentPage = 1,
@TotalPage = @TotalPage OUTPUT
SELECT @TotalPage as N'@TotalPage'
SELECT 'Return Value' = @return_value
SET STATISTICS PROFILE OFF
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
測試環境:
Win2003、SqlServer2005、720775條記錄、每頁50條記錄、本機直接訪問數據庫、每組10次查詢取平均值
連續表的分頁方案：
方案：利用ID篩選出要得到的數據
CREATE PROCEDURE SelectProduct
@PageSize int,
@CurrentPage int,
@TotalPage int output
BEGIN
EXEC('SELECT TOP '+@PageSize+'* FROM Product WHERE ProductId>('+@PageSize+'*'+@CurrentPage+')')
SELECT @TotalPage=COUNT(*)/@PageSize FROM Product
說明：SELECT TOP 後面不能直接跟變量，所以采用了拼接sql的辦法
測試結果：
頁碼 執行時間（ms）
1 1
100 2
1000 4
5000 9
10000 13
14000 16
分析結果發現在百萬級數據都在小於0.1秒，這足以滿足大多數要求，但是隨著數據頁的增大呈現一種查詢變緩的趨勢。
當然還有其他對ID進行比較的如：between and and so，當然還有游標分頁，雖然通用性很好，但是性能很差。今天我就不一一列舉，因
為今天要討論的重點是不連續表的分頁技術。
不連續表的分頁技術
為了讓上面的表不連續我們將部分記錄刪除
DELETE FROM Product WHERE ProductId%24=0
執行完成後(30032 行受影響)即30032條記錄被刪除，如果我們再用連續表的分頁方式在此表上分頁就不再適用。因為檢索的記錄中存在斷
號現象。所以我們需尋求新的方法分頁
方案一：重建數據表的唯一自增索引
DBCC CHECKIDENT (Product, RESEED,1)
重建之後采用連續表的分頁方式，因為該方式是效率最高的分頁查詢。該方案不適用於將該唯一自增索引作為其他表外鍵的關系型數據庫，
這樣將會導致數據混亂，望慎用。
方案二：采用臨時表分頁
局部臨時表的生存期一次會話過程，說得簡單點就是當一個用戶執行一個查詢時創建，查詢執行完成後自動刪除。
CREATE PROCEDURE [dbo].[SelectProduct]
@PageSize int,
@CurrentPage int,
@TotalPage int output
BEGIN
DECLARE @BeginID INT ,@EndID INT
SET @BeginId=@PageSize*@CurrentPage
SET @EndID=@PageSize*(@CurrentPage+1)
CREATE TABLE #TmpProduct(
Id int IDENTITY(1,1) PRIMARY KEY,
ProductId int not null)
INSERT INTO #TmpProduct(ProductId) SELECT ProductId FROM Product
SET @TotalPage=@@ROWCOUNT/@PageSize
SELECT * FROM Product as p,#TmpProduct as t WHERE p.ProductId=t.ProductId and t.ProductId BETWEEN @BeginId AND
@EndID
測試結果：
記錄總數 查詢時間(ms)
10000 198
100000 669
250000 1454
500000 3980
700000 5543
由此我們發現規律，當數據量越小查詢速度也就越快，因此該方法適用於小數據量的表。從執行計劃中發現向臨時表中插入數據占用了整個
查詢過程的90%-95%時間，而真正查詢我們想要的產品記錄僅僅占了5%-10%，那麼有沒有辦法不去反復執行插入過程呢？那麼我們可以采用
全局臨時表或者普通表，代碼如下：
首先創建全局臨時表並插入記錄：
CREATE TABLE ##TmpProduct(
Id int IDENTITY(1,1) PRIMARY KEY,
ProductId int not null)
INSERT INTO ##TmpProduct(ProductId) SELECT ProductId FROM Product
重寫存儲過程
CREATE PROCEDURE [dbo].[SelectProduct]
@PageSize int,
@CurrentPage int,
@TotalPage int output
BEGIN
DECLARE @BeginID INT ,@EndID INT
SET @BeginId=@PageSize*@CurrentPage
SET @EndID=@PageSize*(@CurrentPage+1)
SELECT * FROM Product as p,##TmpProduct as t WHERE p.ProductId=t.ProductId and t.ProductId BETWEEN @BeginId AND
@EndID
SELECT @TotalPage=COUNT(*)/@PageSize FROM PRODUCT
測試結果：
記錄總數 查詢時間(ms)
10000 10
100000 27
250000 41
500000 69
700000 86
綜合兩種臨時表分頁方法分析，很明顯采用全局臨時表分頁的效率遠高於局部臨時表分頁，但是全局臨時表需要定時維護，包括記錄改變，
索引改變。這種維護成本限制了該方法的發展。所以，在小數據量的情況下建議使用局部臨時表分頁，如果數據量較大請參考下面的方案。
方案三：采用ROW_NUMBER()分頁
CREATE PROCEDURE [dbo].[SelectProduct]
@PageSize int,
@CurrentPage int,
@TotalPage int output
BEGIN
DECLARE @BeginID INT ,@EndID INT
SET @BeginId=@PageSize*@CurrentPage
SET @EndID=@PageSize*(@CurrentPage+1)
SELECT * FROM (SELECT *,ROW_NUMBER()OVER(order by ProductId) AS ROWNUM FROM Product) as t WHERE ROWNUM BETWEEN
@BeginId AND @EndID
SELECT @TotalPage=COUNT(*)/@PageSize FROM Product
測試結果：
記錄總數 查詢時間(ms)
10000 210
100000 150
250000 165
500000 69
700000 86
從統計數據中不難看出
綜合所有分頁我們發現，分頁無非就是對記錄的編號進行處理，如果編號符合我們要求的我們就可以用連續表的方式直接使用，如果不符合
要求的，我們便改變這種編號使其符合要求，如重新建立編號，其實臨時表和ROW_NUMBER()均屬於重建編號的過程。
備注：文章中的數據均親自實測得來，該數據僅作參考和比較，不同的目標機運行時間都將不同。如果文章中存在不正確的觀點和看法，望
大家指出，不能誤導讀者。請大家尊重筆者的勞動果實，轉載望注明出處：http://www.cnblogs.com/4inwork。當明白了存儲過程分頁的原
理子後下篇文章將結合控件來一個具體事例。
