三种数据分页方法的效率分析

甘群文　林颖明

摘 要:对三种存储过程分页算法的响应速度进行比较。采用WAST1.1测试工具,对每种算法分别以5个不同级别的记录数进行测试,并记下满足查询条件的记录数首页、末页的第一个字节到达客户端的时间(即TTFB)。对于小数据量,三种算法中首页、末页的TTFB相差不大;对于大数据量,算法Ⅰ和算法Ⅱ的首页、末页的TTFB相差较大,而算法Ⅲ却相差无几。在海量数据下,算法Ⅲ是三者中效率最高的分页算法。

关键词:SQL;存储过程;分页算法;测试;效率分析

中图分类号:TP392文献标识码:B

文章编号:1004-373X(2009)10-045-03

Efficiency Analysis of Three Kinds of Data Pagination Methods

GAN Qunwen1,LIN Yingming2

(1.Information and Technology Department of Library,Guangxi Traditional Chinese Medical University,Nanning,530001,China;

2.Grand & Loan Office of the Education Department of Guangxi,Nanning,530021,China)

Abstract:To compare the response rates of three storage process pagination algorithms.Using the WAST1.1 test tool,testing each algorithm with five levels′records,and taking down the Total Time to First Byte (TTFB) of first page and the last page in the records which meet the query criteria.In small data sets,there is slight difference between TTFB of the first page and the last page.In large amount data,the difference of the TTFB between the first page and the last page in algorithms I and algorithms II is big,but there is hardly any difference in algorithm III.In mass data,the algorithm III is the most efficient pagination one in the three algorithms.

Keywords:SQL;storage process;pagination algorithm;test;efficiency analysis

0 引 言

随着互联网的不断发展,Web数据库的应用越来越广泛,用户对访问Web数据库页面的效率要求也越来越高,对于大型数据模型而言,把成千上万条满足查询条件的所有记录一次性地输出到客户端是不现实的,一是浏览的页面内容过多;二是用户等待的时间过长;三是浪费服务器资源而使其负载过重。然而采用分页技术,每次只发送一个页面给用户,提高了页面响应速度,减轻了数据库服务器的负担[1] 。因此数据分页技术是Web数据库系统开发中不可忽视的一项重要工作,现在流行的分页方法一般是检索页面大小的块区数据返回给客户端[2]。

1 三种存储过程分页技术分析

存储过程是在SQL Server数据库建立的能够完成一定操作的一组SQL语句,在ASP.NET代码中调用;它执行时只需要SQL Server 对其进行一次解析、编译和优化,能够显著提高数据库驱动Web网站性能[3]。它能减少与数据库交互次数,有利于SQL语句重用[4]。三种存储过程的分页技术都是根据页面大小和页码来提取块区数据的,并把当前页推送到客户端。

(1)这种分页存储过程,是把满足查询条件的所有记录的关键字段(ID号)值保存到临时表#temptalbe,再根据临时表及数据库中的相同关键字段值(ID号),把当前页要显示、记录的相关内容提取出来。算法Ⅰ的代码如下[5-7]:

CREATE procedure page1(@PageSize int,@CurrPage int,@where_sql nvarchar(200),@key_sort nvarchar(50),@Count int Output)

AS

declare @previous int,@topnum int

select @previous=(@CurrPage-1)*@PageSize//计算显示起点

select @topnum=@CurrPage*@PageSize//计算显示终点

select @Count = NULL//返回所有记录的参数

create table #indextable(iid int identity(1,1),nid int NOT NULL)//创建临时表

declare @strID nvarchar(500),@str_sql nvarchar(500)

if (ltrim(rtrim(@where_sql))<>' ') //把满足查询条件的

所有记录的主键ID值存到临时表

select @strID="insert into #indextable(nid) select ID from book"+" where "+@where_sql+" order by "+@key_sort+" Desc"

else

select @strID="insert into #indextable(nid) select ID from book"+" order by "+@key_sort +" Desc"

exec(@strID)

set @Count=@@ROWCOUNT//返回满足查询条件的所有记录给变量

select @str_sql="select distinct t.iid,o.ID,o.书名,o.作者,o.出版社,o.索书号 from book as o,#indextable as t where (o.ID=t.nid) and (t.iid>"+rtrim(cast(@previous as char))+" and t.iid<="+rtrim(cast(@topnum as char))+") order by "+@key_sort+" Desc"

exec(@str_sql) //把当前页所有记录的相关内容提取出来

GO

(2) 对第(1)种算法进行改进,不使用临时表,因为临时表存放着所有满足查询条件的记录的关键字段ID值,占用服务器大量的内存空间。然而每次查询,以获取当前页的ID值的字符串,并赋给某一变量。在过程中使用TOP,它能提高查找效率;同时也使用IN,但它降低了查找效率。使用TOP和IN都有一个弱点,那就是满足查询条件的页数越多,要抽取靠后的数据,获取的速度越慢;但它仍然比第(1)种方法查找效率高。算法Ⅱ的代码如下[5-7] :

CREATE procedure page2(@PageSize int,@CurrPage int,@where_sql nvarchar(200),@key_sort nvarchar(50),@Count int Output)

AS

declare @previous int,@topnum int

select @previous=(@CurrPage-1)*@PageSize

select @topnum=@CurrPage*@PageSize

declare @i int,@IDstr nvarchar(500),@strSQL nvarchar(1500)

select @i=0

select @strSQL="",@IDstr=""

select @strSQL=@strSQL+" select top "+ltrim(rtrim(str(@topnum)))+" " +"@i=@i+1"

select @strSQL=@strSQL+", @IDstr="

select @strSQL=@strSQL+ "case when @i >"+ltrim(rtrim(str(@previous))) +" then @IDstr+ ltrim(rtrim(str(ID))) +′,′" +" else "end"

+" from book where "+ltrim(rtrim(@where_sql))+"order by "+@key_sort+" desc"

exec sp_executesql @strSQL,N'@i int output,@IDstr nvarchar(500) output',@i output,@IDstr output

if len(rtrim(ltrim(@IDstr)))>0

select @IDstr=left(@IDstr,len(@IDstr)-1)

select @strSQL="select distinct o.ID,o.书名,o.作者,o.出版社,o.索书号 from book as o where o.ID in ("+@IDstr +") order by "+@key_sort+" desc"

exec(@strSQL)

GO

(3) 为提高分页算法速度,尽量避免使用IN或NOT IN。为此寻找更佳的分页算法,几乎任何字段,可以通过max(字段)或min(字段)来提取某个字段中的最大或最小值,所以如果这个字段不重复,则可以利用这些不重复字段的max或min作为分水岭,使其成为分页算法中分开每页的参照物。在这里,可以用操作符“>”或“<”号来完成这个使命,使查询语句符合SARG形式。由 select top 10 * from table where ID>200改造成所需要的语句,算法Ⅲ的代码如下[5-7]: CREATE procedure page3(@PageSize int,@CurrPage int,@where_sql nvarchar(200),@key_sort nvarchar(50),@Count int Output)

AS

declare @previous int,@topnum int

select @previous=(@CurrPage-1)*@PageSize

select @topnum=@CurrPage*@PageSize

declare @strSQL nvarchar(1500),@Fieldstr nvarchar(500)

select @Fieldstr="ID,书名,作者,出版社,索书号"

if @CurrPage=1

select @strSQL="select top "+ltrim(rtrim(str(@PageSize)))+" "+@Fieldstr +" from book where "+@where_sql +" order by "+@key_sort+" desc"

else

select @strSQL="select top "+ltrim(rtrim(str(@PageSize)))+" "+@Fieldstr +" from book where "+@key_sort+"<( select min("+@key_sort+") from (select top "+ltrim(rtrim(str((@CurrPage-1)*@PageSize))) +" "+

@key_sort+" from book where "+@where_sql +" order by "+@key_sort+" desc ) as tmp) and "+@where_sql +" order by "+@key_sort+" desc"

exec(@strSQL)

set @Count=@@ROWCOUNT

PRINT '@Count=' + CONVERT(nvarchar,@Count)

GO

2 测 试

分别对三种分页算法进行测试,并给出测试结果。

2.1 测试环境

测试环境的软硬件配置如表1所示。

2.2 测试工具

测试工具采用的Microsoft Web Application Stress Tool 1.1(WAST)[8],除了默认配置外,对三种分页算法的测试参数进行统一配置见表2。

参数Stress Level决定Web Application Stress(WAS)同时运行的测试线程;Stress Multiplier数决定每个WAS测试线程创建的Socket数,Stress Level和Stress Multiplier的乘积等于WAS所仿真的客户端数目,该测试中仿真20个客户端。Test Run Time表示测试运行时间;Request Delay表示网页请求连接延迟;Warm Up 表示测试预热时间;Bandwidth表示测试时的网络带宽。

2.3 数据库测试方案

查询表建立了一个主键值(ID值),同时不再对查询表作任何聚类索引,因为索引对查询性能影响比较大。三种算法根据数据表中记录数的不同进行统一测试,见表3。

2.4 测试结果

表4是三种算法TTFB(Total Time the First Byte is Received)平均值的对比情况,TTFB是客户端收到第一个字节的时间,其大小体现了算法的响应速度,值越小,响应越快。首页时间表示客户端收到满足查询条件的首页中第一个字节的时间,末页时间表示客户端收到满足查询条件的末页中第一个字节的时间(ms)。

3 测试结果分析

算法所用的时间T可以分为Ts,TI,TIO,T=Ts+TI+TIO。Ts为排序所用的时间;TI为建立索引所用的时间;TIO为I/O操作所用的时间。

有主键或聚集索引时,可以极大地减少Ts ,因为在聚集索引中 ,行的物理顺序与索引顺序完全相同,建立主键时,将自动建立聚集索引[9]。上述三种算法是在有主键的表进行查询的,Ts时间极大减少,因此性能和效率都比无索引时高。三种算法中,没有对数据表建立索引,故TI基本不用考虑,主要考虑TIO。

对于这三种算法,在50万条记录数之内时,首页、末页的TTFB平均值相差无几, TIO时间相差不大,都能快速响应,但算法Ⅰ占用内存较大,用户可根据硬件情况选择算法。对于数据量较大(大于70万条记录数)时,以100万条记录数为例,从表4可看出,三种算法中,首页的TTFB平均值相差不大,算法Ⅲ 比算法Ⅰ的响应速度提高11.73%,算法Ⅲ 比算法Ⅱ 的响应速度提高6.63%;末页的TTFB平均值相差较大,TIO时间相差也较大,算法Ⅲ 比算法Ⅰ的响应速度提高35.05%,算法Ⅲ 比算法Ⅱ 的响应速度提高19.73%。同时,随着记录数不断增大,算法Ⅲ 中首页、末页的TTFB平均值相差不大,TIO时间比较平稳。故对于海量数据分页查询,算法Ⅲ 效率高,无疑是最佳选择,其响应速度也比其他两种算法的要好。

4 结 语

在实际工作中,应根据服务器配置、网络状况、数据量大小,选择合适的算法,以实现最佳的效率。作者在一个省级课题项目《广西中医科普网服务平台建设》研发当中,采用算法Ⅲ明显提高了分页查询效率,优化了I/O性能,提高了响应速度。

该实例是在Visual Studio 2005+SQL Server 2000+C#[10]环境下编程,在C#代码中调用存储过程[11],用Microsoft WAST1.1工具进行测试的。

参考文献

[1]崔娟,阎冰洁,熊前兴.基于ASP的数据库分页技术[J].电脑知识与技术,2006(23):6,17.

[2]王博,任涛.Web数据库分页浏览方法性能分析[J].现代电子技术,2006,29(10):68-70.

[3]陆小兵,邹丰奕.SQL Server 2000培训教程[M].北京:清华大学出版社,2002.

[4]裴海桥,陈国旗.ASP.NET中运用存储过程实现Web数据分页查询[J].中国科技信息,2007(16):98-100.

[5]赵松涛.SQL Server2000应用及实例集锦(中文版)[M].北京:人民邮电出版社,2002.

[6]程媛..NET平台下调用存储过程的方法[J].电脑开发与应用,2007(11):80.

[7]于海滨,邓小娣.用存储过程进行动态查询[J].电脑编程技巧与维护,2007(1):41-43.

[8]邓先炳.运用WAST对Web应用程序进行压力测试[J].岳阳职业技术学院学报,2008,23(3):72-74.

[9]李兵,刘淑芬.海量数据下的Web分页呈现研究[J].吉林大学学报:信息科学版,2005,25(3):517-519.

[10]孙永强,戴锋.Visual C#.NET程序设计基础[M].北京:清华大学出版社,2002.

[11]周华清,宋文琳.在C#.NET中应用存储过程[J].科技广场,2005(10):63-65.