三種數據分頁方法的效率分析

甘群文　林穎明

摘 要:對三種存儲過程分頁算法的響應速度進行比較。采用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.