SQLSERVER 預存程序分頁演算法研究(支援千萬級)

1.“俄羅斯預存程序”的改良版

CREATE procedure pagination1
(@pagesize int, --頁面大小，如每頁儲存20條記錄
@pageindex int --當前頁碼)
as set nocount on

begin

declare @indextable table(id int identity(1,1),nid int) --定義表變數
declare @PageLowerBound int --定義此頁的底碼
declare @PageUpperBound int --定義此頁的頂碼

set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select gid from TGongwen where fariqi >dateadd(day,-365,getdate()) order by fariqi desc
select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t where O.gid=t.nid
and t.id>@PageLowerBound and t.id <=@PageUpperBound order by t.id

end

set nocount off

文章中的點評：

以上預存程序運用了SQL SERVER的最新技術――表變數。應該說這個預存程序也是一個非常優秀的分頁預存程序。當然，在這個過程中，您也可以把其中的表變數寫成暫存資料表：CREATE TABLE #Temp。但很明顯，在SQL SERVER中，用暫存資料表是沒有用表變數快的。所以筆者剛開始使用這個預存程序時，感覺非常的不錯，速度也比原來的ADO的好。但後來，我又發現了比此方法更好的方法。

從感覺上講，效率不是太高。

2. not in 的方法：
從publish 表中取出第 n 條到第 m 條的記錄：

SELECT TOP m-n+1 * FROM publish WHERE (id NOT IN (SELECT TOP n-1 id FROM publish))

id 為publish 表的關鍵字

文章中的點評：

我當時看到這篇文章的時候，真的是精神為之一振，覺得思路非常得好。等到後來，我在作辦公自動化系統（ASP.NET+ C#＋SQL SERVER）的時候，忽然想起了這篇文章，我想如果把這個語句改造一下，這就可能是一個非常好的分頁預存程序於是我就滿網上找這篇文章，沒想到，文章還沒找到，卻找到了一篇根據此語句寫的一個分頁預存程序，這個預存程序也是目前較為流行的一種分頁預存程序。

使用了 not in 而 not in 是無法使用索引的，所以從效率上講還是差了一點。

3. max 的方法：

select top 頁大小 * from table1 where id>
(select max (id) from
(select top ((頁碼-1)*頁大小) id from table1 order by id) as T)
order by id

文章中的點評：

我們知道，幾乎任何欄位，我們都可以通過max(欄位)或min(欄位)來提取某個欄位中的最大或最小值，所以如果這個欄位不重複，那麼就可以利用這些不重複的欄位的max或min作為分水嶺，使其成為分頁演算法中分開每頁的參照物。在這裡，我們可以用操作符“>”或“ <”號來完成這個使命，使查詢語句符合SARG形式。如：
Select top 10 * from table1 where id>200

這個就高高效了一點。但是不清楚 max的工作原理，不知道它的效能如何。

心得：

1、追求高效的翻頁演算法 —— 定位法。

declare @pageSize int --返回一頁的記錄數
declare @CurPage int --頁號（第幾頁）0：第一頁；-1最後一頁。

declare @Count int
declare @id int

set @pageSize=10
set @CurPage =1

--定位
if @CurPage = -1
begin
--最後一頁
set rowcount @pageSize
select @id=newsID from newsTemp order by newsID
end

if @CurPage > 0
begin
set @Count = @pageSize * (@CurPage -1) + 1
set rowcount @Count
select @id=newsID from newsTemp order by newsID desc
end

--返回記錄
set rowcount @pageSize
select * from newsTemp where newsID <=@id order by newsID desc

set rowcount 0

思路：就是上面的演算法的延續，就是說呢避免使用 not in 和 max 的方法。也就是這個思路：Select top 10 * from table1 where id>200，定位 —— 就是說要找到“臨界點”，分頁的臨界點。找到了之後剩下的事情就好辦了。

缺點：單欄位排序、排序欄位的值不能重複（不是絕對不能重複，可以有少量的重複）。

2、通用法 —— 顛顛倒倒法

有的時候“定位法”的缺點是不可以接受的，但是沒有關係，可以用這個的。

select * from table where id in
(
select top 10 ID from
(
select top 20 ID,addedDate from table
order by addedDate desc
) as aa order by addedDate
)
order by addedDate desc

ID 是主鍵，addedDate 是排序欄位。

缺點：必須有主鍵。

 

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------

給一下簡單在SQL例子對萬條資料沒問題的，在0.5秒沒問題的。希望有所協助:
如:
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

------------------------

用SQL2005 就沒那麼麻煩了 只要用下 Row_NUMBER這個函數就行
For Example:
select * from

(select ROW_NUMBER()Over(order by itemid desc) as rowId,*

from items

) as mytable where rowId between 10 and 20

 

不知道你是否真的在千萬級的資料庫上測試過這些東西，其實很多局限性的

1、變數表 變數表也不是什麼新技術，至少在SQL2000時代就有了，變數表適合小的資料容量，當資料量比較大（數萬條）而且並發使用者數比較多（100個）時，資料庫伺服器和死機差不多了

2、定位元模式 其實後面的都是這種模式，只不過說法不同而已
這種模式的前提一般是索引必須建立，而且索引最好是單欄位索引，如果是多欄位索引，反覆倒過來倒過去，伺服器不爬窩估計用戶端早就逾時了。