分頁控制項之分頁演算法 —— for SQL Server 版。百萬級的資料只需要15毫秒到900毫秒

下載分頁控制項（包括源碼和示範）

分頁控制項使用方法

關於分頁的誤區
    誤區1：分頁的時候，只有使用預存程序，效率才高。
    誤區2：忽略了索引的作用。

上兩篇好像介紹的不太詳細，這裡詳細說明一下分頁控制項裡使用的分頁演算法，也就是SQL語句。

分頁一般分為四種情況

1、單欄位排序，排序欄位沒有重複值。
2、單欄位排序，排序欄位有重複值。
3、多欄位排序，最後一個排序欄位沒有重複值。
4、多欄位排序，最後一個排序欄位有重複值。

其中第2、4 情況都可以再加一個排序欄位（比如說主鍵），就可以轉換成第三種情況。

所以分頁針對1、3兩種情況設定了兩種分頁演算法。

1、單欄位排序，排序欄位沒有重複值。

    公式：

1declare @col int
2
3select top {PageSize * (PageIndex-1)+1} @col = [排序欄位]
4from [表名 ¦視圖名]
5[ where 查詢條件 ]
6order by [排序欄位] asc ¦desc
7
8select top PageSize 需要顯示的欄位
9from [表名 ¦視圖名]
10where [排序欄位] >= @col
11[ and 查詢條件 ]
12order by [排序欄位] asc ¦desc
    以NorthWind 資料庫裡的 Products 表為例，假設一頁顯示10條資料，CategoryID = 3 為查詢條件，按照ProductID 倒序，如果想顯示第二頁的資料，那麼SQL語句就是
    declare @col int

select top 11 @col = ProductID from Products where CategoryID = 3 order by ProductID desc   

select top 10 * from Products where ProductID >= @col and CategoryID = 3 order by ProductID desc   
  說明：
    第一行的定義，要根據欄位類型來修改，看是比較麻煩，但是這個麻煩交給分頁控制項就可以了，使用者，只要設定分頁控制項的屬性就可以了。
    第五行和第十一行，如果需要加查詢條件的話就可以在這裡添加。

    第三行是一個“定位”，這個可以算是SQL Server 所特有的吧，也是SQL Server 很寬容的地方。以Products 表的例子，執行完第一條select 語句之後， @col 裡面記錄的是 在CategoryID = 3 的記錄裡面，按照ProductID 倒序，排行在11位的記錄的值。
   
    第一個select 語句定位以後，第二個select 語句就可以根據這個“位置”繼續向下尋找資料了。

    雖然例子裡面使用了ProductID（主鍵）來排序，但是並不是說這個演算法只能用主鍵來排序，哪個欄位都可以，但是要符合第一種情況，就是“只有一個排序欄位，且排序欄位裡的記錄沒有重複值”！

3、多欄位排序，最後一個排序欄位沒有重複值。

    如果 Products 表想要用 UnitPrice 欄位來排序怎麼辦呢？上面的演算法是不適合的，我們需要使用另一種演算法，這個和顛顛倒倒法有些類似，但是我做了一些最佳化。

    公式：
select [需要顯示的欄位] from [表名 ¦視圖名] where [主鍵欄位] in

    ( select top PageSize [主鍵欄位] from
        (select top {PageSize * PageIndex} [主鍵欄位] , [排序欄位] from    --有幾個排序欄位就寫幾個欄位

            [表名 ¦視圖名]
            [ where 查詢條件 ]
            order by
           
                [排序欄位1] asc ¦desc ,
                [排序欄位2] desc ¦asc,
               
                [主鍵欄位] asc ¦desc   
        ) as aa   
        order by   
            [排序欄位1] desc ¦asc,        --如果上面是倒序，那麼這裡就是正序，下同
                [排序欄位2] asc ¦desc ,
                   
                    [主鍵欄位] desc ¦asc

    )

order by
    [排序欄位1] asc ¦desc,    --如果上面是倒序，那麼這裡就是正序，所謂顛顛倒倒嘛。
    [排序欄位2] desc ¦asc,
   
    [主鍵欄位] asc ¦desc
    以NorthWind 資料庫裡的 Products 表為例，假設一頁顯示10條資料，CategoryID = 3 為查詢條件，按照UnitPrice 倒序，由於UnitPrice 欄位可能有重複值，所以加上一個排序欄位——ProductID ，即按照 UnitPrice  desc,ProductID  來排序。 如果想顯示第二頁的資料，那麼SQL語句就是
select * from Products where ProductID in
    ( select top 10 ProductID from
        (select top 20 ProductID , UnitPrice from   
            Products
            where CategoryID = 3
            order by
                UnitPrice desc ,
                ProductID     
        ) as aa   
        order by   
            UnitPrice asc,        --如果上面是倒序，那麼這裡就是正序，下同
                ProductID desc
    )
order by
    UnitPrice desc,    --如果上面是倒序，那麼這裡就是正序，所謂顛顛倒倒嘛。
    ProductID
    說明：
    1、這裡查詢條件加一次就可以了。
    2、是不是看 asc ¦desc 倒來倒去的有點暈，恩，這就對了，顛顛倒倒嘛。
    3、最主要的就是第三個select 語句，他要取從第一條資料到要顯示的頁的資料，可見越是後面的記錄，top n 就會越大，所以這裡提取的資料就要做一個精簡，唯寫排序需要的欄位（主鍵欄位和排序欄位）。
    4、第二個select 語句是去掉前面不需要的頁裡的資料，只保留要顯示的頁號裡的資料。
    5、第一個select 語句，用主鍵欄位 in () 的方式提取其他需要的欄位。
    6、這種分頁演算法有一個小的bug，就是顯示最後一頁資料的時候，會多出來幾條記錄，不過這個bug已經在分頁控制項裡面修正了，最後一頁的分頁演算法，採用特殊的select語句。
    7、效率，設定好索引，效率是沒有問題的，上一篇隨筆已經測試過了。
    8、這種演算法有一個“侵入性”，就是要求表必須有主鍵，而且不能是聯合主鍵，引為要用 in 的方式查詢資料。但是並沒有要求主鍵自身必須能夠排序。

測試效果
記錄數：2523136條。
一頁顯示5條記錄。

//分頁演算法1 單欄位排序，且排序欄位是叢集索引。
  //1000 頁以內 15毫秒
  //10000頁以內 30毫秒
  //50000頁以內 100多毫秒
  //100000頁以內 200多毫秒
  //最後幾頁 第一次跳轉到 4秒多
  //最後幾頁 連續向前翻頁 1秒156毫秒

  //頁號大範圍跳轉的時候需要的時間比較長，但是也小於1秒，同時SQL Server 佔用的記憶體有所增加 120M。最後幾頁時達到320M

===================================================================
以下是多排序欄位的分頁情況，排序欄位是 UnitPrice,ProductID 

  //分頁演算法2 無索引  首頁 8秒187毫秒 。
  //10 頁以內 2秒812毫秒
  //速度太慢下面的就不測試了

  //分頁2 非叢集索引 UnitPrice  首頁 468毫秒
  //10 頁以內 2秒671毫秒
  //速度太慢下面的就不測試了

 
  //分頁演算法2 非叢集索引 UnitPrice,ProductID  首頁 500毫秒
  //10 頁以內 2秒796毫秒
  //100頁以內 4秒796毫秒
  //速度太慢下面的就不測試了

 
  //分頁演算法2 非叢集索引 UnitPrice,ProductID desc  首頁 500毫秒
  //10 頁以內 0-15毫秒
  //100頁以內 15-46毫秒
  //1000頁以內 31-62毫秒
  //10000頁以內 100毫秒左右
  //50000頁以內 400-500毫秒
  //100000頁以內 900毫秒左右
  //最後幾頁 第一次跳轉到 4秒421毫秒
  //最後幾頁 連續向前翻頁 4秒375毫秒

  //頁號大範圍跳轉的時候需要的時間比較長，但是也小於1秒，
  //這回SQL Server 佔用的記憶體增加幅度不大 120M左右