SQL Server 中幾種分頁方法的再測試

剛搬家到CSDN，決定貢獻第一篇文章。：）
 
最近做一個新項目，想使用一下SQL Server 2005中的 ROW_NUMBER的分頁方式，因為這個新的特性可以使得分頁代碼的編寫變得輕松。唯一擔心的是性能問題，於是我上網搜索了一下關於 ROW_NUMBER的性能的討論，可是發現有些人的測試表明，該特性形同雞肋，並不能帶來性能的顯著提高。對此我頗為懷疑，為了求證這種分頁方法的的性能到底如何。只好親自操刀，對於現在流行的3種分頁方法做一個對比測試。
 
對比測試的方法如下：

•  利用TOP N 分頁
•  利用臨時表分頁
•  ROW_NUMBER 分頁

測試環境：

• Windows Server 2003
• Intel Core2 6300/4G 內存
• Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Mar 23 2007 16:28:52   Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

測試說明：

• 分別用三種方法建立Stored procedures參數統一如下:
  @pageindex int=1,
  @pagesize int=10,
  @ordercolumn nvarchar(20)='''',
  @order bit =0,-- 0 stands for ''ASC'' 1 stands for ''DESC''
  @totalcount int output
  
• 測試表插入300萬條記錄，按照如下分頁規則測試：
   1) 每頁顯示30條記錄，返回第10頁
   2) 每頁顯示30條記錄，返回第1000頁
   3) 每頁顯示30條記錄，返回第10000頁
   4) 每頁顯示20條記錄，返回第100000頁

測試腳本如下：

--分頁性能測試
 
 --1 建立測試表
 CREATE TABLE [dbo].[tb_group](
     [ID] [int] IDENTITY(1,1) NOT NULL,
     [Name] [nvarchar](50) NULL,
     [city] [nvarchar](50) NULL,
     [province] [nvarchar](50) NULL,
     [country] [nvarchar](50) NULL,
  CONSTRAINT [PK_tb_group] PRIMARY KEY CLUSTERED 
 (
     [ID] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 
 --2 插入300萬條記錄
 select @@version
 declare @count int
 declare @ch char(4)
 declare @c nvarchar(2)
 declare @i int
 
 set @count=3000000
 while @count>0
 begin
 set @i=65+floor(rand()*26)
 set @c=convert(nvarchar(2),@i)
 set @ch=replicate(char(@i),4)
 
 insert tb_group ([name],[city],[province],[country]) values 
 (@ch,''C''+@c,''P''+@c,@ch)
set @count=@count-1
 end
 
 --select count(*) from tb_group
 
 --方法一
 -- TOP N 分頁
 create proc sp_pagination1
 @pageindex int=1,
 @pagesize int=10,
 @ordercolumn nvarchar(20)='''',
 @order bit =0,-- 0 is''ASC'' 1 is ''DESC''
 @totalcount int output
 as
 declare @sql nvarchar(4000)
 declare @strOrder1 nvarchar (4)
 declare @strOrder2 nvarchar (4)
 declare @strOrderColumn nvarchar(60)
 
 select @totalcount=count(id) from tb_group
 
 if (@order=0)
 begin
     set @strOrder1 =''asc''
     set @strOrder2 =''desc''
 end else begin
     set @strOrder1 =''desc''
     set @strOrder2 =''asc''
 end
 
 if (@ordercolumn<>'''')
 begin
     set @strOrderColumn=''order by '' + @ordercolumn + '' ''
 end else begin
     set @strOrderColumn=''''
     set @strOrder1 =''''
     set @strOrder2 =''''
 end
 if (@pageindex<=0 or @pagesize<=0 or (@pagesize*(@pageindex-1)>@totalcount)) begin
     set @sql=''select * from tb_group where 1<>1''
 end else begin
if (@pagesize*@pageindex>@totalcount)
     begin
         set @sql=''select * from ''+
             ''(select top '' + convert(nvarchar(10),@totalcount-(@pagesize*(@pageindex-1))) +
             '' * from tb_group '' + @strOrderColumn + @strOrder2 +'',id desc) t1 '' +
             @strOrderColumn + @strOrder1 +'',id''
     end else begin
         set @sql=''select * from ''+
             ''(select top '' +convert(nvarchar(10),@pagesize)+'' * from '' +
             ''(select top '' + convert(nvarchar(10),@pagesize+@pagesize*(@pageindex-1)) +
             '' * from tb_group '' + @strOrderColumn + @strOrder1+ '',id) t1 '' + @strOrderColumn + @strOrder2 +
             '',id desc) t2 '' + @strOrderColumn + @strOrder1 +'',id''
     end
 end
 
 exec (@sql)
 
 --方法二
 -- 臨時表分頁
 create proc sp_pagination2
 @pageindex int=1,
 @pagesize int=10,
 @ordercolumn nvarchar(20)='''',
 @order bit =0,-- 0 is''ASC'' 1 is ''DESC''
 @totalcount int output
 as
 
 declare @sql nvarchar(4000)
 declare @strOrder nvarchar (4)
 declare @strOrderColumn nvarchar(60)
 
 if (@order=0)
 begin
     set @strOrder =''asc''
 end else begin
    &nbsp;set @strOrder =''desc''
 end
 
 if (@ordercolumn<>'''')
 begin
     set @strOrderColumn=''order by '' + @ordercolumn + '' ''
 end else begin
     set @strOrderColumn=''''
     set @strOrder =''''
 end
 
 select @totalcount=count(id) from tb_group
 
 create table #pager (id int, pagerid int identity(1, 1) not null)
 
 set @sql= ''insert into #pager (id) '' +
     ''select top '' +convert(varchar(10),@pageindex*@pagesize) + '' id from tb_group '' + @strOrderColumn + @strOrder + '',id''
 exec (@sql)
 
 set @sql=''select * from tb_group as A inner join #pager as B on A.id=B.id'' +
     '' where b.pagerid > ''+ convert(varchar(10),@pagesize * (@pageindex-1))+'' and b.pagerid < '' +
     convert(varchar(10),@pagesize * (@pageindex) +1)
 exec (@sql)
 
 --方法三
 -- ROW_Number分頁
 
 --在開始測試這個方法之前我發現一個問題,下面兩個語句照道理應該一模一樣，可是上面用小寫英文的卻無法執行，查詢KB也沒有發現相關問題。莫非我發現了一個BUG？
 
 select * from (select *,row_number() over (order by country) as rowno from tb_group) as t1
 where rowno > 0 and rowno < 61
 
 SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY country) AS RowNo FROM tb_group) AS T1
 WHERE RowNo > 30 and RowNo < 61
 
 --下面是測試代碼
 
 create proc sp_pagination3
 @pageindex int=1,
 @pagesize int=10,
 @ordercolumn nvarchar(20)='''',
 @order bit =0,-- 0 is''ASC'' 1 is ''DESC''
 @totalcount int output
 as
 
 declare @sql nvarchar(4000)
 declare @strOrder nvarchar (4)
 declare @strOrderColumn nvarchar(60)
 
 if (@order=0)
 begin
     set @strOrder =''asc''
 end else begin
     set @strOrder =''desc''
 end
 
 if (@ordercolumn<>'''')
 begin
     set @strOrderColumn=''order by '' + @ordercolumn + '' ''
 end else begin
     set @strOrderColumn=''''
     set @strOrder =''''
 end
 
 select @totalcount=count(id) from tb_group
 
 
 set @sql= ''SELECT * FROM (SELECT *,ROW_NUMBER() OVER ('' + @strOrderColumn + @strOrder +'') AS RowNo FROM tb_group) AS T1'' +
     '' WHERE RowNo > ''+ convert(varchar(10),@pagesize * (@pageindex-1)) +
     '' and RowNo < '' + convert(varchar(10),@pagesize * (@pageindex) +1)
 exec (@sql)

測試用例代碼：

 

--測試在排序 “無索引列” 時的分頁性能
 
 DBCC FREEPROCCACHE
 GO
 select * from tb_group order by country asc
 --DBCC FREESESSIONCACHE
 declare @starttime datetime
 declare @endtime datetime
 declare @time datetime
 declare @total int
 set @starttime=getdate()
 exec sp_pagination3 1,10,''country'',0,@total output
 set @endtime=getdate()
 set @time=@endtime-@starttime
 select @total,@time
 
 
 
 --測試在排序 “有索引列” 時的分頁性能
 
 DBCC FREEPROCCACHE
 GO
 --DBCC FREESESSIONCACHE
 select * from tb_group
 declare @starttime datetime
 declare @endtime datetime
 declare @time datetime
 declare @total int
 set @starttime=getdate()
 exec sp_pagination3 3,5,''id'',0,@total output
 set @endtime=getdate()
 set @time=@endtime-@starttime
 select @total,@time

 

測試結果：

利用TOP N 分頁

測試項目

測試用時

無索引列排序

有索引列排序

每頁顯示30條記錄，返回第10頁 :

 1:05分220毫秒

每頁顯示30條記錄，返回第1000頁 :

 1:07分

 280毫秒

每頁顯示30條記錄，返回第10000頁 :

 1:07分

 640毫秒

每頁顯示20條記錄，返回第100000頁 :

 1:15分

 2秒640毫秒

 

 

 

 

 

臨時表分頁

測試項目

測試用時

無索引列排序

有索引列排序

每頁顯示30條記錄，返回第10頁 :

 27秒407毫秒

 2秒627毫秒

每頁顯示30條記錄，返回第1000頁 :

 28秒170

毫秒

 2秒873毫秒

每頁顯示30條記錄，返回第10000頁 :

31秒500毫秒

 5秒783毫秒

每頁顯示20條記錄，返回第100000頁 :

 45秒937毫秒

 15秒750毫秒

 

 

 

 

 

ROW_NUMBER 分頁

測試項目

測試用時

無索引列排序

有索引列排序

每頁顯示30條記錄，返回第10頁 :

9秒107毫秒

 250毫秒

每頁顯示30條記錄，返回第1000頁 :

 9秒873毫秒

263毫秒

每頁顯示30條記錄，返回第10000頁 :

12秒230毫秒

 517毫秒

每頁顯示20條記錄，返回第100000頁 :

 19秒640毫秒

 1秒983毫秒

 

 

 

 

 

結果分析

• 利用TOP N 分頁
  
  缺點: 如果排序列不是索引列，性能低下。最後一頁的顯示會錯誤。除非添加代碼對最後一頁進行處理。另外如果排序列有重復值，排序發生錯誤，解決方案是進行二次排序。 （e.g.: order by column1, PKcolum）。需要編寫代碼判斷pageindex的有效性。如果用時間列作為排序列似乎需要進一步更改代碼，筆者未作進一步嘗試。
  
• 利用臨時表分頁
  
  缺點: 消耗服務器IO，如果數據量大，可能因磁盤性能導致查詢速度下降。 如果插入臨時表時不使用top 性能下降，如果使用top，對有重復值的列排序會不正確。解決方案是進行二次排序.
  
• ROW_NUMBER 分頁
  
  缺點: 結果較為滿意，缺點是只能用於SQL2005

從上面的分析可以看出，在有條件的情況下（有SQL2005）應該優先考慮使用ROW_NUMBER 排序。此外，如果僅對於索引列排序，可以使用TOP N排序獲得較好的性能。但是如果需要使用無索引列排序，可以考慮使用臨時表。或者修改表結構建立索引。