.Net源码论坛 [ 繁体中文 ]

返回列表 12345678» / 10
发新话题 回复该主题

从网上搜集的几种数据分页的总结 [复制链接]

楼主
从网上搜集了两篇关于数据库分页性能分析总结的文章,分页方式大同小异。


第一篇、转载自comaple 的博客



    本试验在于探讨分页的性能问题,当然客户端分页也是一种分页的策略。不过这种分页方式已经过时了,建议不要采用。这里我们只讨论服务器端分页。


实验环境:
Pentium(R) dual-Core CPU E5300 @ 2.6GHz 2.59GHz, 2.00GB内存


SqlServer2008 数据库环境,数据库中我们要用到的的表:
dbo.GMpipe
CREATE TABLE [dbo].[GMpipe](
[GMDataID] [uniqueidentifier] NOT NULL,
[pointID] [uniqueidentifier] NULL,
[measurePipe] [varchar](10) NULL,
[measureTime] [datetime] NULL,
[measureCycle] [varchar](10) NULL,
[MeasureData] [int] NULL,
[doseRateValue] [decimal](18, 10) NULL,
CONSTRAINT [PK_GMPIPE] PRIMARY KEY CLUSTERED
(
[GMDataID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
目前该表中存在1157226条数据,用select语句查询耗时为:17s
SELECT  * FROM dbo.GMpipe ORDER BY measureTime DESC


接下来我们就来一起体验一下把:
第一种方式
使用top语句(本文只列出常用的):
分页的存储过程,已实现好了如下:
CREATE PROCEDURE paging1
@pageNum INT –-页码
,@Num INT    --每页条数
AS
BEGIN
SELECT TOP (@Num) *  FROM
(
SELECT TOP (@Num*@pageNum) * FROM dbo.GMpipe ORDER BY dbo.GMpipe.measureTime asc
) b ORDER BY b.measureTime DESC;
END
go
这个中方法先把数据库中的前@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


第二中方式
使用临时表
分页的存储过程,实现如下:
CREATE PROCEDURE paging2
@pageNum INT
,@Num INT
AS
BEGIN
SELECT  measurePipe,measureTime,measureCycle,MeasureData,doseRateValue,IDENTITY(int) Num INTO #temp FROM dbo.GMpipe ORDER BY measureTime ASC
SELECT * FROM #temp WHERE  Num<
=@Num*@pageNum AND Num> @Num*(@pageNum-1)
ORDER BY Num ASC
DROP TABLE #temp
END
Go
这种方式是将表中的数据全部查出,然后加入标识行号的列Num并将其装入临时表#temp中然后可根据行号列进行分页查询。
看性能
EXEC paging2 2,5;--每页五条,第二页数据 耗时:3s
EXEC paging2 200,5;--每页五条,第二百页数据 耗时:3s
EXEC paging2 20000,5;--每页五条,第二万页数据 耗时:3s
EXEC paging2 200000,5;--每页五条,第二十万页数据 耗时:3s

第三种方式
采用系统提供的ROW_NUMBER()函数
存储过程实现如下:
CREATE PROCEDURE paging0  
@pageNum INT
,@Num INT
AS
begin
SELECT * FROM
(
SELECT measurePipe,measureTime,measureCycle,MeasureData,doseRateValue,ROW_NUMBER() OVER(ORDER BY  GMpipe.measureTime ASC ) AS NUM
FROM GMpipe)A
WHERE A.NUM<
=@Num*@pageNum AND 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

改进第三种方式:


之所以要改进第三种方式那是因为,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 paging2 5000,200;--每页两百条,第五千页数据 耗时:7s
Top语句方式:
EXEC paging1 5000,200;-- 每页两百条,第五千页数据 耗时: 3s

ROW_NUMBER()函数方式:
EXEC paging0 5000,200;--每页五条,第二十万页数据 耗时:1s

分析:这样我们就能看到很清楚了吧,影响top语句方式的因素是你要取的页数,即越靠后耗时也明显。影响临时表的因素则比较多了首先是数据的总条数,其次是分页方式即每页的数据量。而ROW_NUMBER()函数的影响则可能只有总的数据量,并且性能可是不错的哦!
我想对与一般的系统而言二十万页的数据分页量已经够用了吧,呵呵!再多的话我们也看不过来啊






第二篇、转载载源自李洪根的blog




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
         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
                 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的游标存储过程分页)    效率最差,但是最为通用
在实际情况中,要具体分析。


********************************************************************
只是把文章转载了一下,没有再加整理。希望对开发有所启发和帮助!
最后编辑灵雨飘零 最后编辑于 2011-10-29 09:09:39
本主题由 超级管理员 自由极光 于 6/6/2013 11:09:47 AM 执行 审核帖子 操作
分享 转发
★欢迎访问我的博客★
博客园—灵雨飘零:http://www.cnblogs.com/kingboy2008/
CSDN—灵雨飘零:http://hi.csdn.net/kingboy2008

TOP
沙发

很好 学习
积分换礼品:点这里!
.NET小常识:点这里!
.NET相关帮助:点这里!
源码下载:点这里!
欢迎大家来论坛交流!进入论坛!
TOP
板凳

,很好
宁静致远,从容应对。
TOP
地板

由于公司是2k数据库。所以只能用top了。学习了。
如今我已不再是那个擅弹琴曲的仙人,而即将成为蓬莱国的永恒之主!
TOP
五楼

good
TOP
六楼

学习了
TOP
七楼

学习哈。。。。。。
TOP
八楼

该用户帖子内容已被屏蔽
TOP
九楼

楼主技术研究能力很强啊
淘宝网女装 www.cjpfw.com www.tbshcw.com
TOP
十楼

感谢楼主分享,总结这些不容易啊
TOP
发新话题 回复该主题