分类目录

链接

2012年六月
« 5月   7月 »
 123
45678910
11121314151617
18192021222324
252627282930  

近期文章

热门标签

博主推荐

现在位置:    首页 > SQL Server > 正文
数据分页技术总结
SQL Server 暂无评论 阅读(2,167)

 

 

1.数据分页概述

 

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

 

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

 

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

 

2.数据层分页技术

 

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

 

• 主要由T -SQL来完成

 

• 适用于比较大的数据表

 

• 优点:返回的结果集小,查询速度快

 

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

 

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

 

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

 

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

 

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

 

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

 

View Code

复制代码
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

 

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

 

 

 

 

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

 

• 在SQL Server 2005/2008中

 

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

 

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

 

View Code

复制代码
 之所以要改进第三种方式那是因为,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的游标存储过程分页)    效率最差,但是最为通用

 

根据2个博文的总结

 

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

 

 

 

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

 

 

 

 

 

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

本文版权归数据库之家所有,转载引用请完整注明以下信息:
本文作者:Bruce
本文地址:数据分页技术总结 | 数据库之家

发表评论

留言无头像?