利用SQL Server存儲(chǔ)過(guò)程提高在線考試系統(tǒng)的性能
隨著計(jì)算機(jī)網(wǎng)絡(luò)技術(shù)和數(shù)據(jù)庫(kù)技術(shù)的飛速發(fā)展,高校的數(shù)字化校園建設(shè)進(jìn)程日益深入,其中數(shù)字化教學(xué)與學(xué)習(xí)環(huán)境建設(shè)成為其核心內(nèi)容。為推動(dòng)數(shù)字化校園建設(shè)進(jìn)程,創(chuàng)建數(shù)字化教學(xué)環(huán)境,利用Delphi7和SQL Server2000開發(fā)出適用于多學(xué)科的高校通用計(jì)算機(jī)在線考試軟件系統(tǒng)。Delphi在基于C/S模式的數(shù)據(jù)庫(kù)應(yīng)用開發(fā)領(lǐng)域具有很強(qiáng)的數(shù)據(jù)庫(kù)支持能力,因?yàn)樗峁┝素S富的數(shù)據(jù)庫(kù)操作組件,常用的有TQuery、TTable、TADOQuery和TADOTable等[1]。通過(guò)這些組件,程序員可以在短時(shí)間內(nèi)完成一個(gè)應(yīng)用程序模塊的設(shè)計(jì),大大減少了開發(fā)人員的工作量。但是,這些組件在操作數(shù)據(jù)庫(kù)時(shí)卻存在局限性,即必須將數(shù)據(jù)庫(kù)中的記錄從服務(wù)器端取出放入到客戶端,再根據(jù)查詢條件進(jìn)行選擇。這種實(shí)現(xiàn)方法,如果待訪問(wèn)的數(shù)據(jù)量較大,勢(shì)必因?yàn)椴樵儠r(shí)間較長(zhǎng)而降低程序執(zhí)行的速度,從而降低應(yīng)用程序的性能。解決這一問(wèn)題利用了SQL Server2000的存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)[2]。
1 存儲(chǔ)過(guò)程及其優(yōu)點(diǎn)
1.1 存儲(chǔ)過(guò)程的定義
存儲(chǔ)過(guò)程是存儲(chǔ)在服務(wù)器上的一組預(yù)先定義并編譯好的用來(lái)實(shí)現(xiàn)某種特定功能的SQL語(yǔ)句。它可以接受參數(shù)、返回狀態(tài)值或參數(shù)值。存儲(chǔ)過(guò)程在服務(wù)器端執(zhí)行,通過(guò)應(yīng)用程序調(diào)用的方法實(shí)現(xiàn),執(zhí)行結(jié)束后再把結(jié)果返回到客戶端。
1.2 存儲(chǔ)過(guò)程的優(yōu)點(diǎn)
(1)存儲(chǔ)過(guò)程執(zhí)行速度快,便于提高系統(tǒng)的性能。由于存儲(chǔ)過(guò)程在第一次執(zhí)行之后,其執(zhí)行規(guī)劃就儲(chǔ)存在高速緩沖存儲(chǔ)區(qū)中,在以后的操作中。只需從高速緩沖存儲(chǔ)區(qū)中調(diào)用編譯好的二進(jìn)制形式存儲(chǔ)過(guò)程來(lái)執(zhí)行[3]。
(2)減少網(wǎng)絡(luò)流量,提高系統(tǒng)執(zhí)行效率??蛻舳藨?yīng)用程序調(diào)用一個(gè)存儲(chǔ)過(guò)程,只需通過(guò)網(wǎng)絡(luò)發(fā)送該過(guò)程名和少量入口參數(shù),數(shù)據(jù)庫(kù)服務(wù)器就可執(zhí)行該過(guò)程,執(zhí)行完成后,只返回結(jié)果狀態(tài)或?qū)⒆罱K結(jié)果集給客戶端應(yīng)用程序,無(wú)需通過(guò)網(wǎng)絡(luò)傳送大量的SQL操作命令和中間結(jié)果,最大限度地減少網(wǎng)絡(luò)通信負(fù)擔(dān),提高了執(zhí)行效率。
(3)維護(hù)了數(shù)據(jù)庫(kù)的安全性。如果用戶被授予執(zhí)行存儲(chǔ)過(guò)程的權(quán)限,即使該用戶沒(méi)有在執(zhí)行該存儲(chǔ)過(guò)程中可參考的表或視圖的訪問(wèn)權(quán)限,該用戶也可以完全執(zhí)行該存儲(chǔ)過(guò)程而不受影響。因此,可以通過(guò)創(chuàng)建存儲(chǔ)過(guò)程來(lái)完成數(shù)據(jù)的插入、更新和刪除等操作,并且可以通過(guò)編程方式控制在操作中對(duì)信息的訪問(wèn)權(quán)限[4]。
(4)提高軟件系統(tǒng)的可維護(hù)性。存儲(chǔ)過(guò)程在創(chuàng)建時(shí)就在服務(wù)器上經(jīng)過(guò)調(diào)試和編譯,可以避免不必要的錯(cuò)誤出現(xiàn)。另外,存儲(chǔ)過(guò)程將相關(guān)業(yè)務(wù)邏輯封裝在一起,可以大大提高整個(gè)軟件系統(tǒng)的可維護(hù)性。因?yàn)楫?dāng)相關(guān)業(yè)務(wù)邏輯發(fā)生變化時(shí),不需要修改并編譯客戶端應(yīng)用程序,只需要修改位于服務(wù)器端的實(shí)現(xiàn)相應(yīng)業(yè)務(wù)邏輯的存儲(chǔ)過(guò)程即可。
(5)充分增強(qiáng)SQL語(yǔ)言的功能和靈活性。存儲(chǔ)過(guò)程可以用流控制語(yǔ)句編寫,有很強(qiáng)的靈活性,可以完成復(fù)雜的判斷和運(yùn)算。
基于以上優(yōu)點(diǎn),在進(jìn)行C/S模式數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)開發(fā)時(shí),通常把比較浪費(fèi)時(shí)間、影響網(wǎng)絡(luò)傳送的相關(guān)業(yè)務(wù)邏輯編寫成存儲(chǔ)過(guò)程由服務(wù)器執(zhí)行,同時(shí)利用服務(wù)器的SQL語(yǔ)法進(jìn)行優(yōu)化處理,只將少量的結(jié)果顯示在客戶端,充分提高軟件系統(tǒng)的性能[5]。
2 存儲(chǔ)過(guò)程的創(chuàng)建方法
以SQL Server 2000數(shù)據(jù)庫(kù)管理系統(tǒng)為背景,創(chuàng)建方法如下:
CREATE PROC[EDURE]
procedure_name[;number]
[{@parameter data_type}[VARYING][=default] [OUTPUT] ][,...n]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]
[FOR REPLICATION]
AS sql_statement [...n ]
下面對(duì)語(yǔ)法中各選項(xiàng)進(jìn)行說(shuō)明:
(1)選項(xiàng)“procedure_name”指存儲(chǔ)過(guò)程名,不能超過(guò)128個(gè)字符,“[;number]”指程序編號(hào),用于區(qū)別同名的存儲(chǔ)過(guò)程。
(2)選項(xiàng)“@parameter”表示參數(shù),每個(gè)存儲(chǔ)過(guò)程中最多可以設(shè)定1 024個(gè)參數(shù),要求在每個(gè)參數(shù)名前加上符號(hào)“@”。
(3)選項(xiàng)“[=default]”指為對(duì)應(yīng)參數(shù)設(shè)定默認(rèn)值。
(4)選項(xiàng)“OUTPUT”用來(lái)指定參數(shù)既有輸入又有輸出。即在調(diào)用這個(gè)存儲(chǔ)過(guò)程時(shí),如果所指定的參數(shù)是要輸入的參數(shù),同時(shí)也需要在結(jié)果中輸出,則該項(xiàng)必須為“OUTPUT”。
(5)選項(xiàng)“WITH”用來(lái)指定存儲(chǔ)過(guò)程的處理方式。
(6)選項(xiàng)“FOR REPLICATION”表示只能由復(fù)制方式執(zhí)行[6]。
3 存儲(chǔ)過(guò)程在考試軟件系統(tǒng)中的應(yīng)用
(1)考試系統(tǒng)在運(yùn)行時(shí)要經(jīng)過(guò)三個(gè)步驟,考生抽取套題編號(hào)、抽取套題內(nèi)容和將套題內(nèi)容插入答題表進(jìn)行答題。如果不采用存儲(chǔ)過(guò)程,則需用下面方法實(shí)現(xiàn)。
①隨機(jī)抽取套題編號(hào)
select 套題編號(hào)from tblcrecord order by newid()
②抽取對(duì)應(yīng)套題內(nèi)容,套題編號(hào)存儲(chǔ)在dm.strNum中
with qry1 do
Begin
Close ;
SQL.Clear ;
SQL.Add(′Select * from tblTK Where 套題編號(hào)
='''+dm.strNum +''');
Open ;
End;
③插入考試題到考試表tblKS中
qry1.First;
While not qry1.Eof Do
begin
qry2.Append;
qry2.FieldByName(‘考生學(xué)號(hào)’).Value:= qry1.Field-
ByName(‘考生學(xué)號(hào)’).Value;
…
qry2.Post;
end;
顯然,此種方法步驟繁瑣,耗時(shí)較多,在考試過(guò)程中會(huì)產(chǎn)生抽題緩慢的現(xiàn)象,系統(tǒng)性能嚴(yán)重降低。為避免此種情況的發(fā)生,考試系統(tǒng)采用了存儲(chǔ)過(guò)程的方法,代碼如下[7]:
CREATE PROCEDURE cp_Paper_Add
@MyNo char(10),
@Mynum char(20)
AS
set nocount on
exec(′insert into tblKS (學(xué)生學(xué)號(hào)、套題編號(hào)、試題序號(hào)、試題內(nèi)容、試題答案、試題分?jǐn)?shù)、是否答題、套題名稱、考試科目、試題類型、學(xué)生分?jǐn)?shù)、類別序號(hào)) select''''+@MyNo+'''',''''+@Mynum+''',試題序號(hào),試題內(nèi)容,試題答案,試題分?jǐn)?shù),“否”,套題名稱,科目名稱,試題類型,0,類別序號(hào) from tblTK where 套題編號(hào)='''+@Mynum+''' )
GO
通過(guò)執(zhí)行存儲(chǔ)過(guò)程,將題庫(kù)中與考生抽取套題編號(hào)相匹配的記錄提取出來(lái),并批量插入到考試表中,同時(shí)對(duì)考生分?jǐn)?shù)進(jìn)行了初始化為0的動(dòng)作。而本考試系統(tǒng)就是將考生的學(xué)號(hào)和考生抽取的套題編號(hào)作為參數(shù)傳遞給存儲(chǔ)過(guò)程,因此,大幅度提高了考試軟件的效率。
(2)在考試系統(tǒng)進(jìn)行不同類型題目抽取時(shí),如果在Delphi中直接應(yīng)用SQL語(yǔ)句,應(yīng)使用下面代碼:
with cds1 do
Begin
Close ;
SQL.Clear ;
SQL.Add(′Select * from tblKS Where 學(xué)生學(xué)號(hào)=′);
SQL.Add(‘ ?蓯+dm.strNo +?蓯 and 試題類型=′′單項(xiàng)選擇題′′ order by 試題序號(hào)′);
Open ;
End;
此時(shí),程序是在運(yùn)行時(shí)才編譯的。這樣就增加了運(yùn)行的時(shí)間,同時(shí)還要把完整的SQL語(yǔ)句傳遞給Sql Server服務(wù)器執(zhí)行,這樣考試過(guò)程中勢(shì)必增加網(wǎng)絡(luò)流量,降低軟件的運(yùn)行效率。
如果采用存儲(chǔ)過(guò)程,可用下面方法實(shí)現(xiàn):
CREATE PROCEDURE sp_Query_All
@strNo char(10)
AS
set nocount on
Select Select * from tblKS Where 學(xué)生學(xué)號(hào)=@strNO and 試題類型=′′單項(xiàng)選擇題′′ order by 試題序號(hào)
GO
在客戶端調(diào)用方法如下:
with cds1 do
Begin
Close ;
SQL.Clear ;
SQL.Add('exec sp_Query_All :strNO');
Params.ParamByName('strNO').Value:=DM.strNO;
Open ;
End;
此時(shí),存儲(chǔ)過(guò)程是預(yù)先編譯的,自然節(jié)省了編譯的時(shí)間,同時(shí)只向服務(wù)器傳遞了一個(gè)參數(shù)DM.strNO,大大減少了網(wǎng)絡(luò)流量,提高了考試系統(tǒng)的性能。
(3)在考試系統(tǒng)中添加考生信息時(shí),首先要判斷是否存在該考生學(xué)號(hào)信息,然后再進(jìn)行添加操作。如果采用普通SQL語(yǔ)句,代碼如下:
with qry1 do
Begin
Close ;
SQL.Clear ;
SQL.Add(′select * from tblstudents where考生學(xué)號(hào)=
:strNO′);
Params.ParamByName('strNO').Value :=DM.strNO;
Open ;
if recordcount=0 then
begin
qry2.Append;
qry2.FieldByName(‘考生學(xué)號(hào)’).Value:= DM.strNO;
…
qry2.Post;
End;
采用存儲(chǔ)過(guò)程,可以用流控制語(yǔ)句編寫。采用存儲(chǔ)過(guò)程代碼如下:
CREATE PROCEDURE sp_Stud_Add
@No char(10),
@Name char(20),
@Sex char(2),
@Class char(30),
@XueYuan char(20),
@XH smallint
AS
if not exists (Select * from tblStudents where 學(xué)生學(xué)號(hào)=
@No)
begin
insert into tblStudents values(@No,@Name,@Sex,@Class,
@XueYuan,@XH)
end
GO
通過(guò)if 流控制語(yǔ)句實(shí)現(xiàn)了相同學(xué)號(hào)不能插入的功能,增強(qiáng)了SQL語(yǔ)言的功能和靈活性,避免了普通SQL語(yǔ)句的繁瑣與低效,提高了考試系統(tǒng)的性能。
在開發(fā)數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)時(shí),合理使用存儲(chǔ)過(guò)程來(lái)完成相關(guān)的業(yè)務(wù)邏輯操作,能夠顯著提高系統(tǒng)的性能、運(yùn)行效率和可維護(hù)性。當(dāng)然,并非所有的業(yè)務(wù)邏輯都要使用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)。如果都使用存儲(chǔ)過(guò)程的方法實(shí)現(xiàn),一定會(huì)增加服務(wù)器的負(fù)擔(dān);同時(shí),客戶端的資源也不能得到充分的利用[8]。因此,要根據(jù)具體情況,將那些使用頻繁、比較復(fù)雜的業(yè)務(wù)邏輯用存儲(chǔ)過(guò)程的方法實(shí)現(xiàn),合理規(guī)劃服務(wù)器端和客戶端程序,使相關(guān)事務(wù)能夠均衡處理,以此提高軟件系統(tǒng)的性能和效率。
參考文獻(xiàn)
[1] 吳小林,蔣先剛,高艷錦.基于Delphi的多層數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)連接技術(shù)的研究[J].華東交通大學(xué)學(xué)報(bào),2005,22(1):68-70.
[2] 史創(chuàng)明.SQL Server 2000中文版數(shù)據(jù)庫(kù)管理與應(yīng)用標(biāo)準(zhǔn)教程[M].北京:清華大學(xué)出版社,2006:264-278.
[3] 張莉,陳雷,孫龍清,等.SQL Server數(shù)據(jù)庫(kù)原理及應(yīng)用(第2版)[M]. 北京:清華大學(xué)出版社,2009:265-275.
[4] 李舒,陳麗君.高校學(xué)生成績(jī)管理系統(tǒng)的設(shè)計(jì)與實(shí)現(xiàn)[J].遼寧大學(xué)學(xué)報(bào).自然科學(xué)版,2006,33(3):284-285.
[5] 曾毅,王玉萍.SQL Server數(shù)據(jù)庫(kù)中存儲(chǔ)過(guò)程的實(shí)現(xiàn)[J].科技信息,2008(25):88-89.
[6] 肖輝輝,段艷明.SQL Server 2000存儲(chǔ)過(guò)程在VB中的應(yīng)用[J].福建電腦,2007(12):189-190.
[7] 馬曉梅.SQL Server 2000實(shí)驗(yàn)指導(dǎo)(第2版)[M].北京:清華大學(xué)出版社,2008:105-112.
[8] 孫曉楓,范正翹,袁海文.存儲(chǔ)過(guò)程在SQL Server數(shù)據(jù)庫(kù)自我管理中的高級(jí)應(yīng)用[J].計(jì)算機(jī)應(yīng)用,2002,22(4):92-93.
評(píng)論