常用的数据分页技术总结

        根据2个博文的总结

        常用的数据分页技术总结：http://www.cnblogs.com/refactor/archive/2012/06/28/2552700.html

        网上搜集的几种数据分页的总结 http://www.cnblogs.com/kingboy2008/archive/2011/06/22/2086710.html

                                            http://www.cnblogs.com/builderman/archive/2011/07/19/2110108.html

1.数据分页概述 　　

• 通常在Web页面中，在数据量比较大时，无法在一个页面中显示所有数据 　　

• 在某些特定场景下，并不需要返回所有满足条件的数据 　　

• 从数据的角度看，需要返回指定范围内的数据

2.数据层分页技术 　　

• 在数据库查询时，只查询特定页面的数据 　　

• 主要由T -SQL来完成 　　

• 适用于比较大的数据表 　　

• 优点：返回的结果集小，查询速度快 　　

• 缺点：需要多次查询数据库

3.数据层分页技术－使用临时表

• 在查询数据前，创建一个临时表 　　

• 表的列比要返回的结果集多一个标识列 　　

• 按照特定列排序，将结果插入到临时表中 　　

• 此时，可以按照标识列返回特定页面

CREATE PROCEDURE paging2
 
 @pageNum INT
 
,@Num INT
 
AS
 
BEGIN
 
SELECT  AddressLine1,AddressLine2,City,PostalCode,IDENTITY(int) Num INTO #temp 
FROM Person.Address ORDER BY AddressID ASC 

SELECT * FROM #temp WHERE  Num<=@Num*@pageNum AND Num> @Num*(@pageNum-1)
 
ORDER BY Num ASC
 
DROP TABLE #temp
 
END
EXEC paging2 20,5;--每页五条，第二十页数据 耗时： 1s
 
EXEC paging0 20000,5;--每页五条，第二万页数据 耗时: 1s
 
EXEC paging0 200000,5;--每页五条，第二十万页数据 耗时： 1s

 

4.数据层分页技术－使用表变量 　　

• 在查询数据前，创建一个表变量 　　

• 表的列比要返回的结果集多一个标识列 　　

• 按照特定列排序，将结果插入到表变量中 　　

• 此时，可以按照标识列返回特定页面

5.数据层分页技术－反复取Top　　 　　

• 若要按特定列正排序取第91－100 行数据 　　

• 首先，按特定列正排序取Top 100 　　

• 然后，反向排序取Top 10 　　

• 最后，再将结果正排序后返回

create PROCEDURE fenye
 (
 @pageNum INT 
 
 ,@Num INT    
 )
AS
 
BEGIN
 
SELECT * from (SELECT TOP (@Num) *  FROM 
(SELECT TOP (@Num*@pageNum) * FROM Person.Address ORDER BY AddressID asc) b ORDER BY b.AddressID desc
 )c ORDER BY c.AddressID asc
END 

这个中方法先把数据库中的前@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

　　

　　

6.数据层分页技术－使用行号 　　

• 在SQL Server 2005/2008中 　　

• 查询结果集时，添加一个ROW NUMBER() 表示行号 　(不懂ROW NUMBER()：详见：http://www.cnblogs.com/fxgachiever/archive/2010/09/15/1826792.html)　

• 将上述结果集作为子查询，通过ROW_NUMBER() 筛选出特定页面

 之所以要改进第三种方式那是因为，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 paging0 20,5;--每页五条，第二十页数据 耗时： 1s
 
EXEC paging0 20000,5;--每页五条，第二万页数据 耗时: 1s
 
EXEC paging0 200000,5;--每页五条，第二十万页数据 耗时： 1s

 

7.代码如下:

View Code 

USE AdventureWorks2008
GO
SELECT COUNT(*) FROM Production.TransactionHistoryArchive
GO
SELECT TOP 50 * FROM Production.TransactionHistoryArchive
ORDER BY ReferenceOrderID ASC
GO

--Use Top*Top
DECLARE @Start datetime,@end datetime;
SET @Start=getdate();

DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);
SET @PageNumber=5000;
SET @Count=10;
SET @Sql='SELECT T2.* FROM (
    SELECT TOP 10 T1.* FROM 
        (SELECT TOP ' + STR(@PageNumber*@Count) +' * FROM Production.TransactionHistoryArchive
        ORDER BY ReferenceOrderID ASC) AS T1
    ORDER BY ReferenceOrderID DESC) AS T2
ORDER BY ReferenceOrderID ASC';
EXEC (@sql);

SET @end=getdate();
PRINT Datediff(millisecond,@Start,@end);
GO

--USE table value
DECLARE @Start datetime,@end datetime;
SET @Start=getdate();
DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);
SET @PageNumber=5000;
SET @Count=10;

DECLARE @local_variable table (RowNumber int identity(1,1),[TransactionID] [int],
    [ProductID] [int],
    [ReferenceOrderID] [int],
    [ReferenceOrderLineID] [int],
    [TransactionDate] [datetime],
    [TransactionType] [nchar](1),
    [Quantity] [int],
    [ActualCost] [money],
    [ModifiedDate] [datetime]);
insert into @local_variable (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate) 
SELECT TOP 50000 TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate from Production.TransactionHistoryArchive ORDER BY ReferenceOrderID ASC
select * from @local_variable where RowNumber > (@PageNumber-1)*@Count and RowNumber <= @PageNumber*@Count

SET @end=getdate();
PRINT Datediff(millisecond,@Start,@end);
GO

--USE temp table
DECLARE @Start datetime,@end datetime;
SET @Start=getdate();
DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);
SET @PageNumber=5000;
SET @Count=10;

create table #local_variable(RowNumber int identity(1,1),[TransactionID] [int],
    [ProductID] [int],
    [ReferenceOrderID] [int],
    [ReferenceOrderLineID] [int],
    [TransactionDate] [datetime],
    [TransactionType] [nchar](1),
    [Quantity] [int],
    [ActualCost] [money],
    [ModifiedDate] [datetime]);
insert into #local_variable (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate) 
SELECT TOP 50000 TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate from Production.TransactionHistoryArchive ORDER BY ReferenceOrderID ASC
select * from #local_variable where RowNumber > (@PageNumber-1)*@Count and RowNumber <= @PageNumber*@Count

SET @end=getdate();
PRINT Datediff(millisecond,@Start,@end);
GO

--Use ROW_NUMBER
DECLARE @Start datetime,@end datetime;
SET @Start=getdate();

DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);
SET @PageNumber=5000;
SET @Count=10;
SELECT * FROM 
(    SELECT ROW_NUMBER()  
      OVER(ORDER BY ReferenceOrderID) AS RowNumber,    
      *
    FROM Production.TransactionHistoryArchive) AS T
WHERE T.RowNumber<=@PageNumber*@Count AND T.RowNumber>(@PageNumber-1)*@Count;

SET @end=getdate();
PRINT Datediff(millisecond,@Start,@end);
GO

　　

8.应用层分页技术（自定义分页方法） 　　

• 在应用层/逻辑层缓存数据，并进行数据的分段显示 　　

• 主要由程序代码完成 　　

• 优点：查询数据库次数少，每次返回结果快 　　

• 缺点：第一次查询慢，占用应用层内存资源

9.应用层分页技术－GridView 　　

• 应用GridView的分页功能 　　

• 启用XxxDataSource 控件的缓存功能

10.应用层分页技术－DataPager 　　

• .NET 3.5 当中的新控件 　　

• 更加灵活定义分页格式

11.应用层分页技术－PagedDataSource 对象分页 　　

• .NET  当中的对象 　　

• 更加灵活定义分页格式，但是代码量特多，不好控制

12.应用层分页技术－LINQ 　　

• 自定义数据绑定 　　

• 使用LINQ 进行查询 　　

• 使用Skip() 和Take() 函数

12.展现层分页技术 （JS,jquery组合成的第三方View控件）　

• 在客户端进行数据分页 　　

• 主要通过客户端脚本来实现 　　

• 优点：减少网络传输量，提高带宽利用率 　　

• 缺点：需要编写大量客户端脚本，增加开发与维护成本

13.展现层分页技术－UpdatePanel 　　

• 通过ASP.NET AJAX 中的UpdatePanel 控件，可以将应用层所提供的分页功能转换到客户端 　　

• 无需维护代码 　　

• 充分利用服务器端控件功能

14.展现层分页技术－Data Services 　　

• 使用Data Services所提供的基于URI 的数据访问，可以实现数据分页 　　

• 在展现层，可以直接通过Javascript 进行解析 　　

• 轻量级数据传输格式：XML/JSON

总结

我们再来改变一下每页的条数看看

临时表方式：

EXEC paging2 5000,200;--每页两百条，第五千页数据 耗时：7s

Top语句方式：

EXEC paging1 5000,200;-- 每页两百条，第五千页数据 耗时： 3s

ROW_NUMBER()函数方式：

EXEC paging0 5000,200;--每页五条，第二十万页数据 耗时：1s

 

 

 

另外的SQL分页

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-1          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-1                  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的游标存储过程分页)    效率最差，但是最为通用

在实际情况中，要具体分析。