【分享】從網上搜集的幾種資料分頁的總結

 

從網上搜集了兩篇關於資料庫分頁效能分析總結的文章，分頁方式大同小異。

 

第一篇、轉載自comaple 的部落格

 

    本實驗在於探討分頁的效能問題，當然用戶端分頁也是一種分頁的策略。不過這種分頁方式已經過時了，建議不要採用。這裡我們只討論區伺服器端分頁。

 

實驗環境：

Pentium（R） dual-Core CPU E5300 @ 2.6GHz 2.59GHz， 2.00GB記憶體

 

SqlServer2008 資料庫環境，資料庫中我們要用到的的表：

dbo.GMpipe

CREATE TABLE [dbo].[GMpipe](

[GMDataID] [uniqueidentifier] NOT NULL,

[pointID] [uniqueidentifier] NULL,

[measurePipe] [varchar](10) NULL,

[measureTime] [datetime] NULL,

[measureCycle] [varchar](10) NULL,

[MeasureData] [int] NULL,

[doseRateValue] [decimal](18, 10) NULL,

 CONSTRAINT [PK_GMPIPE] PRIMARY KEY CLUSTERED

(

[GMDataID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

目前該表中存在1157226條資料，用select語句查詢耗時為：17s

SELECT  * FROM dbo.GMpipe ORDER BY measureTime DESC

 

接下來我們就來一起體驗一下把：

第一種方式

使用top語句（本文只列出常用的）：

分頁的預存程序，已實現好了如下：

CREATE PROCEDURE paging1

 @pageNum INT –-頁碼

 ,@Num INT    --每頁條數

AS

BEGIN

SELECT TOP (@Num) *  FROM

(

SELECT TOP (@Num*@pageNum) * FROM dbo.GMpipe ORDER BY dbo.GMpipe.measureTime asc

) b ORDER BY b.measureTime DESC;

END

go

這個中方法先把資料庫中的前@Num*@pageNum條資料取出，再從結果集中取出最後的@Num條資料，當然兩個定序是不一樣的這點很重要，不然起不到分頁效果。 你可以具體試一下就明白了。

看效能
EXEC paging1 2,5;--每頁五條，第十頁資料 耗時：1s

EXEC paging1 200,5;--每頁五條，第200頁資料 耗時：1s

EXEC paging1 20000,5;--每頁五條，第20000頁資料 耗時：1s

EXEC paging1 200000,5;--每頁五條，第二十萬頁資料 耗時： 3s

 

第二中方式

使用暫存資料表

分頁的預存程序，實現如下：

CREATE PROCEDURE paging2

 @pageNum INT

,@Num INT

AS

BEGIN

SELECT  measurePipe,measureTime,measureCycle,MeasureData,doseRateValue,IDENTITY(int) Num INTO #temp FROM dbo.GMpipe ORDER BY measureTime ASC

SELECT * FROM #temp WHERE  Num<=@Num*@pageNum AND Num> @Num*(@pageNum-1)

ORDER BY Num ASC

DROP TABLE #temp

END

Go

這種方式是將表中的資料全部查出，然後加入標識行號的列Num並將其裝入暫存資料表#temp中然後可根據行號列進行分頁查詢。

看效能
EXEC paging2 2,5;--每頁五條，第二頁資料 耗時：3s

EXEC paging2 200,5;--每頁五條，第二百頁資料 耗時：3s

EXEC paging2 20000,5;--每頁五條，第二萬頁資料 耗時：3s

EXEC paging2 200000,5;--每頁五條，第二十萬頁資料 耗時：3s

第三種方式

採用系統提供的ROW_NUMBER()函數

預存程序實現如下：

CREATE PROCEDURE paging0 

 @pageNum INT

 ,@Num INT

 AS

 begin

SELECT * FROM

(

SELECT measurePipe,measureTime,measureCycle,MeasureData,doseRateValue,ROW_NUMBER() OVER(ORDER BY  GMpipe.measureTime ASC ) AS NUM

FROM GMpipe)A

WHERE A.NUM<=@Num*@pageNum AND A.NUM> @Num*(@pageNum-1) ORDER BY A.measureTime desc

END

Go

這種方式就不多說了大家一看就明白，直接看效能。

看效能
EXEC paging0 20,5;--每頁五條，第二十頁資料 耗時： 1s

EXEC paging0 20000,5;--每頁五條，第二萬頁資料 耗時: 1s

EXEC paging0 200000,5;--每頁五條，第二十萬頁資料 耗時： 1s

改進第三種方式：

  

 之所以要改進第三種方式那是因為，Top關鍵字其實是

已經經過效能最佳化了的之所以比不過ROW_NUMBER()的執行效率是因為用了兩次，那麼既然如此，我們何不將二者結合起來使用，效果豈不更佳。那就讓我們改進一下吧。

 

CREATE PROCEDURE paging0

@pageNum INT

,@Num INT

AS

begin

SELECT * FROM

(

SELECT TOP (@Num*@pageNum)  measurePipe,measureTime,measureCycle,MeasureData,

          doseRateValue,ROW_NUMBER() OVER(ORDER BY GMpipe.measureTime ASC ) AS NUM

FROM GMpipe)A

WHERE A.NUM> @Num*(@pageNum-1) ORDER BY A.measureTime desc

END

Go

 這樣一來執行效率更高了呵呵！

 

總結

我們再來改變一下每頁的條數看看

暫存資料表方式：

EXEC paging2 5000,200;--每頁兩百條，第五千頁資料 耗時：7s

Top語句方式：

EXEC paging1 5000,200;-- 每頁兩百條，第五千頁資料 耗時： 3s

ROW_NUMBER()函數方式：

EXEC paging0 5000,200;--每頁五條，第二十萬頁資料 耗時：1s

分析：這樣我們就能看到很清楚了吧，影響top語句方式的因素是你要取的頁數，即越靠後耗時也明顯。影響暫存資料表的因素則比較多了首先是資料的總條數，其次是分頁方式即每頁的資料量。而ROW_NUMBER()函數的影響則可能只有總的資料量，並且效能可是不錯的哦！

我想對與一般的系統而言二十萬頁的資料分頁量已經夠用了吧，呵呵！再多的話我們也看不過來啊

 

 第二篇、轉載載源自李洪根的blog

 

 

SQL Server 預存程序的分頁，這個問題已經討論過幾年了，很多朋友在問我，所以在此發表一下我的觀點
建立表：

CREATE TABLE [TestTable] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

 

插入資料：(2萬條，用更多的資料測試會明顯一些)
SET IDENTITY_INSERT TestTable ON

declare @i int
set @i=1
while @i<=20000
begin
    insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
    set @i=@i+1
end

SET IDENTITY_INSERT TestTable OFF

 

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

分頁方案一：(利用Not In和SELECT TOP分頁)
語句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 20 id
         FROM TestTable
         ORDER BY id))
ORDER BY ID

SELECT TOP 頁大小 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 頁大小*頁數 id
         FROM 表
         ORDER BY id))
ORDER BY ID

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

分頁方案二：(利用ID大於多少和SELECT TOP分頁）
語句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 20 id
                 FROM TestTable
                 ORDER BY id) AS T))
ORDER BY ID

SELECT TOP 頁大小 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 頁大小*頁數 id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID

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

分頁方案三：(利用SQL的遊標預存程序分頁)
create  procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查詢字串
@currentpage int, --第N頁
@pagesize int --每頁行數
as
set nocount on
declare @P1 int, --P1是遊標的id
 @rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 總頁數--,@rowcount as 總行數,@currentpage as 當前頁 
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off

其它的方案：如果沒有主鍵，可以用暫存資料表，也可以用方案三做，但是效率會低。
建議最佳化的時候，加上主鍵和索引，查詢效率會提高。

通過SQL 查詢分析器，顯示比較：我的結論是:
分頁方案二：(利用ID大於多少和SELECT TOP分頁）效率最高，需要拼接SQL語句
分頁方案一：(利用Not In和SELECT TOP分頁)   效率次之，需要拼接SQL語句
分頁方案三：(利用SQL的遊標預存程序分頁)    效率最差，但是最為通用

在實際情況中，要具體分析。

******************************************************************** 

只是把文章轉載了一下，沒有再加整理。希望對開發有所啟發和協助！