三種數(shù)據(jù)分頁方法的效率分析

甘群文　林穎明

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

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

中圖分類號:TP392文獻(xiàn)標(biāo)識碼: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 引 言

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

1 三種存儲過程分頁技術(shù)分析

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

(1)這種分頁存儲過程,是把滿足查詢條件的所有記錄的關(guān)鍵字段(ID號)值保存到臨時表#temptalbe,再根據(jù)臨時表及數(shù)據(jù)庫中的相同關(guān)鍵字段值(ID號),把當(dāng)前頁要顯示、記錄的相關(guān)內(nè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//計算顯示起點

select @topnum=@CurrPage*@PageSize//計算顯示終點

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

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

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) //把當(dāng)前頁所有記錄的相關(guān)內(nèi)容提取出來

GO

(2) 對第(1)種算法進(jìn)行改進(jìn),不使用臨時表,因為臨時表存放著所有滿足查詢條件的記錄的關(guān)鍵字段ID值,占用服務(wù)器大量的內(nèi)存空間。然而每次查詢,以獲取當(dāng)前頁的ID值的字符串,并賦給某一變量。在過程中使用TOP,它能提高查找效率;同時也使用IN,但它降低了查找效率。使用TOP和IN都有一個弱點,那就是滿足查詢條件的頁數(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.書名,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(字段)來提取某個字段中的最大或最小值,所以如果這個字段不重復(fù),則可以利用這些不重復(fù)字段的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 測 試

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

2.1 測試環(huán)境

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

2.2 測試工具

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

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

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

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

2.4 測試結(jié)果

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

3 測試結(jié)果分析

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

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

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

4 結(jié) 語

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

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

參考文獻(xiàn)

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

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

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

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

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

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

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

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

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

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

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