甘群文　林穎明

摘 要:對(duì)三種存儲(chǔ)過(guò)程分頁(yè)算法的響應(yīng)速度進(jìn)行比較。采用WAST1.1測(cè)試工具,對(duì)每種算法分別以5個(gè)不同級(jí)別的記錄數(shù)進(jìn)行測(cè)試,并記下滿足查詢條件的記錄數(shù)首頁(yè)、末頁(yè)的第一個(gè)字節(jié)到達(dá)客戶端的時(shí)間(即TTFB)。對(duì)于小數(shù)據(jù)量,三種算法中首頁(yè)、末頁(yè)的TTFB相差不大;對(duì)于大數(shù)據(jù)量,算法Ⅰ和算法Ⅱ的首頁(yè)、末頁(yè)的TTFB相差較大,而算法Ⅲ卻相差無(wú)幾。在海量數(shù)據(jù)下,算法Ⅲ是三者中效率最高的分頁(yè)算法。

關(guān)鍵詞:SQL;存儲(chǔ)過(guò)程;分頁(yè)算法;測(cè)試;效率分析

中圖分類號(hào):TP392文獻(xiàn)標(biāo)識(shí)碼:B

文章編號(hào):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 引 言

隨著互聯(lián)網(wǎng)的不斷發(fā)展,Web數(shù)據(jù)庫(kù)的應(yīng)用越來(lái)越廣泛,用戶對(duì)訪問(wèn)Web數(shù)據(jù)庫(kù)頁(yè)面的效率要求也越來(lái)越高,對(duì)于大型數(shù)據(jù)模型而言,把成千上萬(wàn)條滿足查詢條件的所有記錄一次性地輸出到客戶端是不現(xiàn)實(shí)的,一是瀏覽的頁(yè)面內(nèi)容過(guò)多;二是用戶等待的時(shí)間過(guò)長(zhǎng);三是浪費(fèi)服務(wù)器資源而使其負(fù)載過(guò)重。然而采用分頁(yè)技術(shù),每次只發(fā)送一個(gè)頁(yè)面給用戶,提高了頁(yè)面響應(yīng)速度,減輕了數(shù)據(jù)庫(kù)服務(wù)器的負(fù)擔(dān)[1] 。因此數(shù)據(jù)分頁(yè)技術(shù)是Web數(shù)據(jù)庫(kù)系統(tǒng)開(kāi)發(fā)中不可忽視的一項(xiàng)重要工作,現(xiàn)在流行的分頁(yè)方法一般是檢索頁(yè)面大小的塊區(qū)數(shù)據(jù)返回給客戶端[2]。

1 三種存儲(chǔ)過(guò)程分頁(yè)技術(shù)分析

存儲(chǔ)過(guò)程是在SQL Server數(shù)據(jù)庫(kù)建立的能夠完成一定操作的一組SQL語(yǔ)句,在ASP.NET代碼中調(diào)用;它執(zhí)行時(shí)只需要SQL Server 對(duì)其進(jìn)行一次解析、編譯和優(yōu)化,能夠顯著提高數(shù)據(jù)庫(kù)驅(qū)動(dòng)Web網(wǎng)站性能[3]。它能減少與數(shù)據(jù)庫(kù)交互次數(shù),有利于SQL語(yǔ)句重用[4]。三種存儲(chǔ)過(guò)程的分頁(yè)技術(shù)都是根據(jù)頁(yè)面大小和頁(yè)碼來(lái)提取塊區(qū)數(shù)據(jù)的,并把當(dāng)前頁(yè)推送到客戶端。

(1)這種分頁(yè)存儲(chǔ)過(guò)程,是把滿足查詢條件的所有記錄的關(guān)鍵字段(ID號(hào))值保存到臨時(shí)表#temptalbe,再根據(jù)臨時(shí)表及數(shù)據(jù)庫(kù)中的相同關(guān)鍵字段值(ID號(hào)),把當(dāng)前頁(yè)要顯示、記錄的相關(guān)內(nèi)容提取出來(lái)。算法Ⅰ的代碼如下[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//計(jì)算顯示起點(diǎn)

select @topnum=@CurrPage*@PageSize//計(jì)算顯示終點(diǎn)

select @Count = NULL//返回所有記錄的參數(shù)

create table #indextable(iid int identity(1,1),nid int NOT NULL)//創(chuàng)建臨時(shí)表

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

if (ltrim(rtrim(@where_sql))<>' ') //把滿足查詢條件的

所有記錄的主鍵ID值存到臨時(shí)表

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.書(shū)名,o.作者,o.出版社,o.索書(shū)號(hà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) //把當(dāng)前頁(yè)所有記錄的相關(guān)內(nèi)容提取出來(lái)

GO

(2) 對(duì)第(1)種算法進(jìn)行改進(jìn),不使用臨時(shí)表,因?yàn)榕R時(shí)表存放著所有滿足查詢條件的記錄的關(guān)鍵字段ID值,占用服務(wù)器大量的內(nèi)存空間。然而每次查詢,以獲取當(dāng)前頁(yè)的ID值的字符串,并賦給某一變量。在過(guò)程中使用TOP,它能提高查找效率;同時(shí)也使用IN,但它降低了查找效率。使用TOP和IN都有一個(gè)弱點(diǎn),那就是滿足查詢條件的頁(yè)數(shù)越多,要抽取靠后的數(shù)據(jù),獲取的速度越慢;但它仍然比第(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.書(shū)名,o.作者,o.出版社,o.索書(shū)號(hào) from book as o where o.ID in ("+@IDstr +") order by "+@key_sort+" desc"

exec(@strSQL)

GO

(3) 為提高分頁(yè)算法速度,盡量避免使用IN或NOT IN。為此尋找更佳的分頁(yè)算法,幾乎任何字段,可以通過(guò)max(字段)或min(字段)來(lái)提取某個(gè)字段中的最大或最小值,所以如果這個(gè)字段不重復(fù),則可以利用這些不重復(fù)字段的max或min作為分水嶺,使其成為分頁(yè)算法中分開(kāi)每頁(yè)的參照物。在這里,可以用操作符“>”或“<”號(hào)來(lái)完成這個(gè)使命,使查詢語(yǔ)句符合SARG形式。由 select top 10 * from table where ID>200改造成所需要的語(yǔ)句,算法Ⅲ的代碼如下[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,書(shū)名,作者,出版社,索書(shū)號(hào)"

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 測(cè) 試

分別對(duì)三種分頁(yè)算法進(jìn)行測(cè)試,并給出測(cè)試結(jié)果。

2.1 測(cè)試環(huán)境

測(cè)試環(huán)境的軟硬件配置如表1所示。

2.2 測(cè)試工具

測(cè)試工具采用的Microsoft Web Application Stress Tool 1.1(WAST)[8],除了默認(rèn)配置外,對(duì)三種分頁(yè)算法的測(cè)試參數(shù)進(jìn)行統(tǒng)一配置見(jiàn)表2。

參數(shù)Stress Level決定Web Application Stress(WAS)同時(shí)運(yùn)行的測(cè)試線程;Stress Multiplier數(shù)決定每個(gè)WAS測(cè)試線程創(chuàng)建的Socket數(shù),Stress Level和Stress Multiplier的乘積等于WAS所仿真的客戶端數(shù)目,該測(cè)試中仿真20個(gè)客戶端。Test Run Time表示測(cè)試運(yùn)行時(shí)間;Request Delay表示網(wǎng)頁(yè)請(qǐng)求連接延遲;Warm Up 表示測(cè)試預(yù)熱時(shí)間;Bandwidth表示測(cè)試時(shí)的網(wǎng)絡(luò)帶寬。

2.3 數(shù)據(jù)庫(kù)測(cè)試方案

查詢表建立了一個(gè)主鍵值(ID值),同時(shí)不再對(duì)查詢表作任何聚類索引,因?yàn)樗饕龑?duì)查詢性能影響比較大。三種算法根據(jù)數(shù)據(jù)表中記錄數(shù)的不同進(jìn)行統(tǒng)一測(cè)試,見(jiàn)表3。

2.4 測(cè)試結(jié)果

表4是三種算法TTFB(Total Time the First Byte is Received)平均值的對(duì)比情況,TTFB是客戶端收到第一個(gè)字節(jié)的時(shí)間,其大小體現(xiàn)了算法的響應(yīng)速度,值越小,響應(yīng)越快。首頁(yè)時(shí)間表示客戶端收到滿足查詢條件的首頁(yè)中第一個(gè)字節(jié)的時(shí)間,末頁(yè)時(shí)間表示客戶端收到滿足查詢條件的末頁(yè)中第一個(gè)字節(jié)的時(shí)間(ms)。

3 測(cè)試結(jié)果分析

算法所用的時(shí)間T可以分為T(mén)s,TI,TIO,T=Ts+TI+TIO。Ts為排序所用的時(shí)間;TI為建立索引所用的時(shí)間;TIO為I/O操作所用的時(shí)間。

有主鍵或聚集索引時(shí),可以極大地減少Ts ,因?yàn)樵诰奂饕?,行的物理順序與索引順序完全相同,建立主鍵時(shí),將自動(dòng)建立聚集索引[9]。上述三種算法是在有主鍵的表進(jìn)行查詢的,Ts時(shí)間極大減少,因此性能和效率都比無(wú)索引時(shí)高。三種算法中,沒(méi)有對(duì)數(shù)據(jù)表建立索引,故TI基本不用考慮,主要考慮TIO。

對(duì)于這三種算法,在50萬(wàn)條記錄數(shù)之內(nèi)時(shí),首頁(yè)、末頁(yè)的TTFB平均值相差無(wú)幾, TIO時(shí)間相差不大,都能快速響應(yīng),但算法Ⅰ占用內(nèi)存較大,用戶可根據(jù)硬件情況選擇算法。對(duì)于數(shù)據(jù)量較大(大于70萬(wàn)條記錄數(shù))時(shí),以100萬(wàn)條記錄數(shù)為例,從表4可看出,三種算法中,首頁(yè)的TTFB平均值相差不大,算法Ⅲ 比算法Ⅰ的響應(yīng)速度提高11.73%,算法Ⅲ 比算法Ⅱ 的響應(yīng)速度提高6.63%;末頁(yè)的TTFB平均值相差較大,TIO時(shí)間相差也較大,算法Ⅲ 比算法Ⅰ的響應(yīng)速度提高35.05%,算法Ⅲ 比算法Ⅱ 的響應(yīng)速度提高19.73%。同時(shí),隨著記錄數(shù)不斷增大,算法Ⅲ 中首頁(yè)、末頁(yè)的TTFB平均值相差不大,TIO時(shí)間比較平穩(wěn)。故對(duì)于海量數(shù)據(jù)分頁(yè)查詢,算法Ⅲ 效率高,無(wú)疑是最佳選擇,其響應(yīng)速度也比其他兩種算法的要好。

4 結(jié) 語(yǔ)

在實(shí)際工作中,應(yīng)根據(jù)服務(wù)器配置、網(wǎng)絡(luò)狀況、數(shù)據(jù)量大小,選擇合適的算法,以實(shí)現(xiàn)最佳的效率。作者在一個(gè)省級(jí)課題項(xiàng)目《廣西中醫(yī)科普網(wǎng)服務(wù)平臺(tái)建設(shè)》研發(fā)當(dāng)中,采用算法Ⅲ明顯提高了分頁(yè)查詢效率,優(yōu)化了I/O性能,提高了響應(yīng)速度。

該實(shí)例是在Visual Studio 2005+SQL Server 2000+C#[10]環(huán)境下編程,在C#代碼中調(diào)用存儲(chǔ)過(guò)程[11],用Microsoft WAST1.1工具進(jìn)行測(cè)試的。

參考文獻(xiàn)

[1]崔娟,閻冰潔,熊前興.基于ASP的數(shù)據(jù)庫(kù)分頁(yè)技術(shù)[J].電腦知識(shí)與技術(shù),2006(23):6,17.

[2]王博,任濤.Web數(shù)據(jù)庫(kù)分頁(yè)瀏覽方法性能分析[J].現(xiàn)代電子技術(shù),2006,29(10):68-70.

[3]陸小兵,鄒豐奕.SQL Server 2000培訓(xùn)教程[M].北京:清華大學(xué)出版社,2002.

[4]裴海橋,陳國(guó)旗.ASP.NET中運(yùn)用存儲(chǔ)過(guò)程實(shí)現(xiàn)Web數(shù)據(jù)分頁(yè)查詢[J].中國(guó)科技信息,2007(16):98-100.

[5]趙松濤.SQL Server2000應(yīng)用及實(shí)例集錦(中文版)[M].北京:人民郵電出版社,2002.

[6]程媛..NET平臺(tái)下調(diào)用存儲(chǔ)過(guò)程的方法[J].電腦開(kāi)發(fā)與應(yīng)用,2007(11):80.

[7]于海濱,鄧小娣.用存儲(chǔ)過(guò)程進(jìn)行動(dòng)態(tài)查詢[J].電腦編程技巧與維護(hù),2007(1):41-43.

[8]鄧先炳.運(yùn)用WAST對(duì)Web應(yīng)用程序進(jìn)行壓力測(cè)試[J].岳陽(yáng)職業(yè)技術(shù)學(xué)院學(xué)報(bào),2008,23(3):72-74.

[9]李兵,劉淑芬.海量數(shù)據(jù)下的Web分頁(yè)呈現(xiàn)研究[J].吉林大學(xué)學(xué)報(bào):信息科學(xué)版,2005,25(3):517-519.

[10]孫永強(qiáng),戴鋒.Visual C#.NET程序設(shè)計(jì)基礎(chǔ)[M].北京:清華大學(xué)出版社,2002.

[11]周華清,宋文琳.在C#.NET中應(yīng)用存儲(chǔ)過(guò)程[J].科技廣場(chǎng),2005(10):63-65.