三种分页方式效率的简单测试

三种分页方式效率的简单测试

Posted on 2004-10-02 13:17  squirrel_sc 阅读(10009)  评论(15)  编辑  收藏 所属分类:  others 

本文的三种分页方案来自于：
http://blog.youkuaiyun.com/lihonggen0/archive/2004/09/14/103511.aspx
只是做了更大数据量、不同位置页的对比。

建立表：

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

 

插入数据：(100万条)

SET   IDENTITY_INSERT  TestTable  ON
declare  @i  int
set  @i = 1
while  @i <= 1000000
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  页大小  *
FROM  TestTable
WHERE  (ID  NOT   IN
          ( SELECT   TOP  页大小 * 页数 id
          FROM  表
          ORDER   BY  id))
ORDER   BY  ID

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

分页方案二：(利用ID大于多少和SELECT TOP分页）

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

测试结果：
测试均为每页10条，三个数字依次为三种方案出结果需要的时间，单位为秒：
第2页：18，10，29
第500页：12，8，21
第50000页：16，18，22
第500000页：24，16，22
    这次测试的主要目的是对大数据量不同部分的翻页效率的测试。本以为应该是一个线性的结果，结果发现变化很奇怪。多测试几次结果误差在1、2秒之内，估计sql server对于翻页也是根据不同位置有优化的。看了查询分析，主要的花销还是order by，这还是主键的，如果不是主键，或者是字符串，估计更慢。
    因为还有别的事情要忙，也没有做进一步的测试，有兴趣的朋友可以继续做做10万条、无索引、字符串内容的各种测试，记得告诉我一下结果。