几种分页方案详尽分析---10万条记录实例测试

测试表名： QC

测试记录数：约10万条

测试环境：SqlServer2008

 

器材(QC)表

 

记录条数：103997

select COUNT(*) from QC     执行结果 103997

 

分页方案一：(利用Not In和SELECT TOP分页)

 declare @currtime1 datetime set @currtime1=GETDATE() print @currtime1 declare @pagesize int declare @currentpage int set @pagesize=10 set @currentpage=5000 select top 10 * from QC where (QC_ID not in (select top (@pagesize*(@currentpage-1)) QC_ID from QC order by QC_ID)) declare @currtime2 datetime set @currtime2=GETDATE() print @currtime2 print DateDiff(Ms,@currtime1,@currtime2) go

 

执行结果：

结果显示：执行查询时间为20923毫秒，也就是接近21秒。

 

方案原理：

 

 

 

 

 

分页方案2：利用ID大于多少和SELECT TOP分页

 declare @currtime1 datetime set @currtime1=GETDATE() print @currtime1 declare @pagesize int declare @currentpage int set @pagesize=10 set @currentpage=5000 select top 10 * from QC where (QC_ID > (select MAX(QC_ID) from (select top (@pagesize*(@currentpage-1)) QC_ID from QC order by QC_ID) as T)) order by QC_ID declare @currtime2 datetime set @currtime2=GETDATE() print @currtime2 print DateDiff(Ms,@currtime1,@currtime2) go

 

测试结果：

 

 结果显示：查询所用时间为13毫秒，也就是0.013秒

方案原理：

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

 

分页方案三：用TOP+ID大于（或小于）多少的存储过程

use WZGL_3 go if exists(select * from sysobjects where name='proc_page') drop procedure proc_page go Create proc proc_page ( @pageIndex int=5000, @pageSize int=10 ) AS SELECT TOP(@pageSize) * FROM QC WHERE QC_ID < (SELECT MIN(QC_ID) FROM ( SELECT TOP ((@pageIndex-1) * @pageSize) QC_ID FROM QC ORDER BY QC_ID DESC)B ) ORDER BY QC_ID DESC DECLARE @starttime datetime=GETDATE() EXEC proc_page DECLARE @endtime datetime=GETDATE() print datediff(MS,@starttime,@endtime)

 

测试结果

结果显示：执行查询所需时间为16毫秒也就是0.016秒

 

分页方案4：使用RowNumber()的存储过程

use WZGL_3 go if exists(select * from sysobjects where name='proc_PageByRowNumber') drop procedure proc_PageByRowNumber go CREATE proc proc_PageByRowNumber ( @pageIndex int=5000, @pageSize int=10 ) AS DECLARE @startRow int, @endRow int Set @startRow = (@pageIndex - 1) * @pageSize +1 SET @endRow = @startRow + @pageSize -1 SELECT* FROM ( SELECT *,ROW_NUMBER() OVER (ORDER BY QC_ID DESC) AS RowNumber FROM QC ) T WHERE T.RowNumber BETWEEN @startRow AND @endRow DECLARE @starttime datetime=GETDATE() EXEC proc_PageByRowNumber DECLARE @endtime datetime=GETDATE() print '---------使用RowNumber()的分页方法-----------' print datediff(MS,@starttime,@endtime)

执行结果：

 

结果显示：执行花费时间为63毫秒，也就是0.063秒

 

结果已经很明了了。当然可能由于测试并不是十分严格，比如打印时间误差等等因素。自己选择一种适合的分页方法吧。

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