預存程序分頁演算法（收藏）

很久以前看過,不過因為一直無接觸過大資料量的資料庫,所以一直無細心瞭解,現在再看一下作為補充,下面的方法我想會比較適合50萬以內的記錄數.

其實只最佳化了sql部分。因為你的代碼也可以自訂sql，所以可以寫一個sql產生來類來最佳化一下sql查詢語句，當然可以內建到你的類中，請看下面的內容（來源於網路）：
前傳
     變數                  用途
@PageSize             每頁顯示的記錄總數
@PageCount       分頁總數
@RecordCount       資料表的記錄總數
@PageIndex      當前頁的索引
@FirstIndex             第一頁的索引
@MiddleIndex       中間頁的索引
@LastIndex             最後一頁的索引
@TableName       資料庫表名稱
@PrimaryKey       主鍵欄位名稱
@QueryFields       要查詢的欄位集
@Condition             篩選條件

 
定義：
@PageCount  = (int)Math.Ceiling((double)@RecordCount / @PageSize)
@FirstIndex = 0
@LastIndex  = @PageCount - 1
@MiddleIndex     = (int)Math.Ceiling((double)@PageCount / 2) – 1
 
預設：
@PageSize = 2
@RecordCount = 9
@PageCount = 4
 
     現在先讓我們來看看速度最慢的 SQL 陳述式：
 
SELECT TOP @PageSize * FROM @TableName AS a
WHERE @PrimaryKey NOT IN (
     SELECT TOP @PageSize*@PageIndex @PrimaryKey FROM @TableName AS b
     ORDER BY @PrimaryKey
)
ORDER BY @PrimaryKey
 
     這條語句慢就慢在 NOT IN 這裡，主 SELECT 語句遍曆的每個 @PrimaryKey 的值都要跟子 SELECT 語句的結果集中的每一個 @PrimaryKey 的值進行比較，這樣時間複雜度非常大。其實我們平時編寫 SQL 陳述式的時候應該盡量避免用 NOT IN 語句，因為它往往會提高整個 SQL 陳述式的時間複雜度。
 
     還有一種是用兩個 TOP 的 SQL 陳述式，如下所示：
 
SELECT * FROM (
     SELECT TOP @PageSize * FROM (
          SELECT TOP @PageSize*(@PageIndex+1) * FROM @TableName
          ORDER BY @PrimaryKey
     ) TableA
     ORDER BY @PrimaryKey DESC
) TableB
ORDER BY @PrimaryKey
 
     這條 SQL 陳述式空間複雜度比較大。如果要顯示的分頁面剛好是最後一頁，那麼它的效率比直接SELECT 出所有的記錄還要低。

一、相關概念
     在 ACCESS 資料庫中，一個表的主鍵（PRIMARY KEY，又稱主索引）上必然建立了唯一索引（UNIQUE INDEX），因此主鍵欄位的值是不會重複的。並且索引頁依據索引列的值進行排序，每個索引記錄包含一個指向它所引用的資料行的指標。我們可以利用主鍵這兩個特點來實現對某條記錄的定位，從而快速地取出某個分頁上要顯示的記錄。
 
     舉個例子，假設主鍵欄位為 INTEGER 型，資料庫表中的記錄已經按主鍵欄位的值升序排好，那麼主鍵欄位的值為“11”的記錄肯定剛好在值為“12”的記錄前面（假設資料庫表中存在主鍵的值為“12”的記錄）。如果主鍵欄位不具備 UNIQUE 約束，資料庫表中將有可能存在兩個或兩個以上主鍵欄位的值為“11”的記錄，這樣就無法確定這些記錄之間的前後位置了。
 
     下面就讓我們看看如何利用主鍵來進行資料的分段查詢吧。
二、升序
（1）@PageIndex <= @FirstIndex
 
     取第一頁的資料是再簡單不過了，我們只要用TOP @PageSize 就可以取出第一頁要顯示的記錄。因為資料表中的記錄已經按主鍵欄位的值升序排好，所以省去了 ORDER BY 子句，速度更快。
 
SELECT TOP @PageSize @QueryFields
FROM @TableName
WHERE @Condition
-- ORDER BY @PrimaryKey ASC

@PageIndex = 0
 
（2）@FirstIndex < @PageIndex <= @MiddleIndex
 
     把取資料表前半部分記錄和取後半部分記錄的 SQL 陳述式分開寫，可以有效地改善效能。後面我再詳細解釋這個問題。現在看看取前半部分記錄的 SQL 陳述式。先取出當前頁之前的所有記錄的主索引值，再從中選出最大值，然後取出主索引值大於該最大值的前 @PageSize 條記錄。值得注意的是，這裡省去了兩個 ORDER BY @PrimaryKey ASC 語句，分別在最裡面和最外面的 SELECT 語句。前面已經說過，資料庫表中的記錄已經按主鍵欄位的值升序排好，所以我們沒有必要畫蛇添足。
 
SELECT TOP @PageSize @QueryFields
FROM @TableName
WHERE @PrimaryKey > (
     SELECT MAX(@PrimaryKey) FROM (
          SELECT TOP @PageSize*@PageIndex @PrimaryKey
          FROM @TableName
          WHERE @Condition
          -- ORDER BY @PrimaryKey ASC
      ) TableA
) WHERE @Condition
-- ORDER BY @PrimaryKey ASC

 
例如：@PageIndex = 1 ，紅 --> 黃 --> 藍

 
（3）@MiddleIndex < @PageIndex < @LastIndex
 
     接下來看看取資料表後半部分記錄的 SQL 陳述式。該語句跟前面的語句演算法的原理是一樣的，只是方法稍微不同。
 
     先取出當前頁之後的所有記錄的主索引值，再從中選出最小值，然後取出主索引值小於該最小值的前 @PageSize 條記錄。
 
SELECT * FROM (
     SELECT TOP @PageSize @QueryFields
     FROM @TableName
     WHERE @PrimaryKey < (
          SELECT MIN(@PrimaryKey) FROM (
               SELECT TOP (@RecordCount-@PageSize*(@PageIndex+1)) @PrimaryKey
               FROM @TableName
               WHERE @Condition
               ORDER BY @PrimaryKey DESC
          ) TableA
     ) WHERE @Condition
     ORDER BY @PrimaryKey DESC
) TableB
ORDER BY @PrimaryKey ASC

 
     之所以把取資料表前半部分記錄和取後半部分記錄的 SQL 陳述式分開寫，是因為使用取前半部分記錄的 SQL 陳述式時，當前頁前面的記錄數目隨頁數遞增，而我們還要從這些記錄中取出它們的主鍵欄位的值再從中選出最大值。這樣一來，分頁速度將隨著頁數的增加而減慢。因此我沒有這樣做，而是在當前頁索引大於中間頁索引時（@MiddleIndex < @PageIndex）選用了分頁速度隨著頁數的增加而加快的演算法。由此可見，假設把所有分頁面劃分為前面、中間和後面三部分，則最前面和最後面的分頁速度最快，最中間的分頁速度最慢。
 
例如：@PageIndex = 3 ，紅 --> 黃 --> 藍

 
 
（4）@PageIndex >= @LastIndex
 
     取最後一頁的記錄時要先計算出該頁的記錄數，作為 TOP 語句的條件，而不能直接用 TOP @PageSize，這樣取出來的記錄並不只是最後一頁的。其實很多網站確實這樣做。
 
SELECT * FROM (
     SELECT TOP (@RecordCount-@PageSize*@LastIndex) @QueryFields
     FROM @TableName
     WHERE @Condition
     ORDER BY @PrimaryKey DESC
) TableA
ORDER BY @PrimaryKey ASC

 
例如：@PageIndex = 4

三、降序
（1）@PageIndex <= @FirstIndex
SELECT TOP @PageSize @QueryFields
FROM @TableName
WHERE @Condition
ORDER BY @PrimaryKey DESC

 
（2）@FirstIndex < @PageIndex <= @MiddleIndex
 
SELECT TOP @PageSize @QueryFields
FROM @TableName
WHERE @PrimaryKey < (
    SELECT MIN(@PrimaryKey) FROM (
        SELECT TOP @PageSize*@PageIndex @PrimaryKey
        FROM @TableName
        WHERE @Condition
        ORDER BY @PrimaryKey DESC
    ) TableA
) WHERE @Condition
ORDER BY @PrimaryKey DESC

 
 
（3）@MiddleIndex < @PageIndex < @LastIndex
 
SELECT * FROM (
    SELECT TOP @PageSize @QueryFields
    FROM @TableName
    WHERE @PrimaryKey > (
        SELECT MAX(@PrimaryKey) FROM (
            SELECT TOP (@RecordCount-@PageSize*(@PageIndex+1)) @PrimaryKey
            FROM @TableName
            WHERE @Condition
            -- ORDER BY @PrimaryKey ASC
        ) TableA
    ) WHERE @Condition
    -- ORDER BY @PrimaryKey ASC
) TableB
ORDER BY @PrimaryKey DESC

 
 
（4）@PageIndex >= @LastIndex
 
SELECT * FROM (
    SELECT TOP (@RecordCount-@PageSize*@LastIndex) @QueryFields
    FROM @TableName
    WHERE @Condition
    ORDER BY @PrimaryKey ASC
) TableA
ORDER BY @PrimaryKey DESC 
 

符一個這種寫法的sp
CREATE PROCEDURE PageView
@tblName varchar(255),                  -- 表名
@strGetFields varchar(1000) = '*',    -- 需要返回的列
@fldName varchar(255)='',               -- 排序的欄位名
@PageSize int = 10,                         -- 頁尺寸
@PageIndex int = 1,                         -- 頁碼
@doCount bit = 0,                             -- 返回記錄總數, 非 0 值則返回
@OrderType bit = 0,                         -- 設定排序類型, 非 0 值則降序
@strWhere varchar(1500) = ''           -- 查詢條件 (注意: 不要加 where)
AS

declare @strSQL varchar(5000)  -- 主語句
declare @strTmp varchar(110)    -- 臨時變數
declare @strOrder varchar(400)  -- 排序類型

if @doCount != 0
begin
if @strWhere !=''
set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere
else
set @strSQL = "select count(*) as Total from [" + @tblName + "]"
end
--以上代碼的意思是如果@doCount傳遞過來的不是0，就執行總數統計。以下的所有代碼都是@doCount為0的情況

else
begin
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"
--如果@OrderType不是0，就執行降序，這句很重要！
end

else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @fldName +"] asc"
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder
else
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder
--如果是第一頁就執行以上代碼，這樣會加快執行速度
end

else
begin

來自：http://www.cnblogs.com/lymph/articles/499455.html