常用的数据分页技术及比较
1.数据分页概述
• 通常在Web页面中，在数据量比较大时，无法在一个页面中显示所有数据
• 在某些特定场景下，并不需要返回所有满足条件的数据
• 从数据的角度看，需要返回指定范围内的数据
2.数据层分页技术
• 在数据库查询时，只查询特定页面的数据
• 主要由T -SQL来完成
• 适用于比较大的数据表
• 优点：返回的结果集小，查询速度快
• 缺点：需要多次查询数据库
3.数据层分页技术－使用临时表
• 在查询数据前，创建一个临时表
• 表的列比要返回的结果集多一个标识列
• 按照特定列排序，将结果插入到临时表中
• 此时，可以按照标识列返回特定页面
4.数据层分页技术－使用表变量
• 在查询数据前，创建一个表变量
• 表的列比要返回的结果集多一个标识列
• 按照特定列排序，将结果插入到表变量中
• 此时，可以按照标识列返回特定页面
5.数据层分页技术－反复取Top
• 若要按特定列正排序取第91－100 行数据
• 首先，按特定列正排序取Top 100
• 然后，反向排序取Top 10
• 最后，再将结果正排序后返回
6.数据层分页技术－使用行号
• 在SQL Server 2005/2008中
• 查询结果集时，添加一个ROW NUMBER() 表示行号
• 将上述结果集作为子查询，通过ROW_NUMBER() 筛选出特定页面
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
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.应用层分页技术－LINQ
• 自定义数据绑定
• 使用LINQ 进行查询
• 使用Skip() 和Take() 函数
12.展现层分页技术
• 在客户端进行数据分页
• 主要通过客户端脚本来实现
• 优点：减少网络传输量，提高带宽利用率
• 缺点：需要编写大量客户端脚本，增加开发与维护成本
13.展现层分页技术－UpdatePanel
• 通过ASP.NET AJAX 中的UpdatePanel 控件，可以将应用层所提供的分页功能转换到客户端
• 无需维护代码
• 充分利用服务器端控件功能
14.展现层分页技术－Data Services
• 使用Data Services所提供的基于URI 的数据访问，可以实现数据分页
• 在展现层，可以直接通过Javascript 进行解析
• 轻量级数据传输格式：XML/JSON
分类: C# , SqlServer
作者： Leo_wl
出处： http://www.cnblogs.com/Leo_wl/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
版权信息声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did48666