SQL語言信息處理中的執(zhí)行效率

[摘 要] 本文以SQL Server 2000為數(shù)據(jù)庫實驗平臺，基于結(jié)構(gòu)化查詢語言SQL這一綜合通用的、功能極強的關(guān)系數(shù)據(jù)庫語言功能，從應(yīng)用角度，探析合理利用SQL語言功能快速實現(xiàn)實驗室信息管理系統(tǒng)的信息處理，并對如何提高其執(zhí)行效率給出具體分析與處理方法。此應(yīng)用過程對處理其他數(shù)據(jù)庫管理系統(tǒng)也具有通用性。

[關(guān)鍵詞] SQL語言; 執(zhí)行效率; 數(shù)據(jù)庫系統(tǒng); 實驗室信息管理系統(tǒng)

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2010 . 14 . 042

[中圖分類號]G642; TP311.3 [文獻標識碼]A [文章編號]1673 - 0194(2010)14- 0109 - 03

1引言

我國高等教育快速發(fā)展的今天，利用計算機技術(shù)管理數(shù)據(jù)已成為數(shù)據(jù)管理的必然趨勢。結(jié)構(gòu)化查詢語言SQL是英文Structured Query Language的縮寫。按照美國國家標準協(xié)會(ANSI)的規(guī)定，SQL被作為關(guān)系型數(shù)據(jù)庫管理系統(tǒng)的標準語言，SQL語言是功能很強的、公認的數(shù)據(jù)庫管理系統(tǒng)的檢索工具，SQL語句可以用來執(zhí)行各種操作。目前，絕大多數(shù)流行的關(guān)系數(shù)據(jù)管理系統(tǒng)(DBMS)，如Oracle，Sybase，SQL Server，Visual FoxPro等都采用了SQL標準語言。盡管查詢數(shù)據(jù)是SQL最重要的功能之一，但它絕不僅僅是一個查詢工具，它還為用戶提供了用于控制數(shù)據(jù)庫管理系統(tǒng)的所有功能，包括數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)控制等功能。使用SQL語句來處理數(shù)據(jù)，不僅方便了編程，而且注重SQL 語言不同實現(xiàn)方式之間執(zhí)行效率性能上的差異，還可以提高數(shù)據(jù)處理的速度。

“實驗室信息管理系統(tǒng)”是為適合高等院校實驗室需求及管理特點而開發(fā)的一套具有綜合管理功能的系統(tǒng)軟件。而在基于結(jié)構(gòu)化查詢語言SQL語言對實驗室信息管理系統(tǒng)的信息處理應(yīng)用中，對SQL語言的索引、臨時表、謂詞嵌套、SQL 語言開發(fā)應(yīng)用系統(tǒng)技術(shù)等功能，不僅在執(zhí)行速度和效率問題上進行探析，而且在對其各功能實現(xiàn)的分析研究中，可以很方便地實現(xiàn)對數(shù)據(jù)庫的瀏覽、修改、查詢和報表輸出，以減輕管理人員的基礎(chǔ)工作量，從而更好地完成實驗教學任務(wù)，提高實驗室教學管理效率。同時對處理其他數(shù)據(jù)庫管理系統(tǒng)也具有實用性。

2實驗信息管理系統(tǒng)

“實驗室信息管理系統(tǒng)”開發(fā)中，數(shù)據(jù)庫采用SQL Server 2000，設(shè)計上采用了Java，ASP.NET，VB.NET多種軟件完成，各模塊的劃分與設(shè)計基于 B/S 和 C/ S體系結(jié)構(gòu)實現(xiàn)。

2.1 實驗室管理系統(tǒng)

根據(jù)系統(tǒng)設(shè)計的目標， 實驗室管理系統(tǒng)所具備的主要功能有:學生可以查閱與自己相關(guān)的實踐課程信息，教師與系統(tǒng)管理員可以實現(xiàn)對學生與教師基本信息、實驗室課程教學計劃、學生實驗成績信息的錄入修改刪除查詢、數(shù)據(jù)庫的管理操作，并可以實現(xiàn)按照用戶自定義條件進行查詢統(tǒng)計生成報表。本文討論的問題涉及以上一部分功能。

2.2 系統(tǒng)的數(shù)據(jù)庫

在實際管理中，用到許多由SQL Server 2000 建立的實驗室信息管理系統(tǒng)的數(shù)據(jù)庫中的數(shù)據(jù)表，以下是本文研究中所涉及的數(shù)據(jù)表，內(nèi)容如下:

(1) 實驗教師科研信息表(kyxx):(tch_ID)、(xueke)、 (kytm)，(cbs)、(fbrq)、 (kyfz)。

(2) 教師基本信息表(tch_info):(tch_ID)、(tch_name)、(zhicheng_ID)、(tch_age)。

(3)學生實驗成績數(shù)據(jù)表(stu_achieve):(stu_ID)、(stu_su)、(stu_ax)、(stu_at)、(stu_bx)， (stu_as)。

(4) 實驗課程表(course_info):(lab_ID)、(tch_name)、(class_name)、(time_name)、(tutorshipID)。

(5) 實驗室基本信息表(lab_info):(lab_ID)、(lab_name)、(lab_computornum)、(lab_introducton)。

(6) 學生基本信息表(stu_info):(stu_ID)、(stu_name)、(class_ID)。

3SQL語言信息處理中的執(zhí)行效率

以下探討SQL語言在SQL Server 2000中如何提高執(zhí)行效率，快速實現(xiàn)實驗室信息管理信息處理的具體應(yīng)用。

3.1 用視圖整理多個實驗系統(tǒng)數(shù)據(jù)表

在實驗教學應(yīng)用中往往需要查詢的數(shù)據(jù)，分別放在實驗室管理系統(tǒng)的多個數(shù)據(jù)庫表中，將實際需要的屬于同一類數(shù)據(jù)的表做成視圖。視圖是由基表或其他視圖導出的虛表，當使用建立好的一個視圖時，就如調(diào)用一個自由表。利用視圖限制對特定行或列的訪問， 可以增強系統(tǒng)的安全性， 利用視圖連接相關(guān)的表， 可以簡化程序員的工作，提高編譯效率。

如從教師基本信息表(tch_info)、實驗教師科研信息表(kyxx)中查詢教師科研的信息。

CREATEviewtch_view as

SELECT kyxx.tch_id，tch_name， zhicheng_id， xueke， kytm，cbs，fbrq，kyfz

From tch_info ， kyxx

Where kyxx.tch_id = tch_info.tch_id

在實驗室這個大的信息管理系統(tǒng)中，從視圖中取出數(shù)據(jù)往往要讀所有的表， 實際應(yīng)用中是采用將查詢結(jié)合起來綜合考慮，適當建立視圖的方法。

3.2 用聯(lián)合(UNION)查詢實驗課內(nèi)容

在實際應(yīng)用中，用OR 子句可以分解成多個查詢， 但OR是低效率的子句。為提高效率可通過UNION 連接多個查詢。OR與UNION，兩者的速度都與使用索引有關(guān)，如果查詢需要用到聯(lián)合索引，用UNION all 執(zhí)行的效率更高。

如對于學生實驗成績數(shù)據(jù)表(stu_achieve)， 查詢平時成績或考勤成績不合格的學生的學號。

(1) SELECTstu_ID

FROM stu_achieve

WHERE stu_ax<’60’

ORstu_at <’60’

(2) SELECTstu_ID

FROM stu_achieve

WHERE stu_ax<’60’

UNIONSELECTstu_ID

FROM stu_achieve WHERE stu_at <’60’

在實驗室管理系統(tǒng)的應(yīng)用中，以上所采用的OR子句與UNION子句中，通常使用聯(lián)合(UNION)可以加速執(zhí)行效率。但如要在stu_info這個大表上執(zhí)行這種查詢， 在請求下會建立大量數(shù)據(jù)的臨時表以保留每一步的查詢結(jié)果， 如果這個臨時表很大， 就有可能占滿所有的臨時存儲空間，會影響執(zhí)行效率。

3.3 查詢實驗成績使用聚集函數(shù)與相關(guān)子查詢

實驗室管理系統(tǒng)用到聚集函數(shù)與相關(guān)子查詢，內(nèi)部函數(shù)SQL 標準中只使用 COUNT 、SUM 、AVG 、MAX 、MIN 函數(shù)，稱之為聚集函數(shù)( Set Function )，當一個子查詢涉及一個來自外部查詢的列時，稱為相關(guān)子查詢(Correlated Subquery)。使用聚集函數(shù)中避免相關(guān)子查詢，由于一個列的標簽同時在主查詢和where子句中的查詢中出現(xiàn)，很可能當主查詢中的列值改變之后，子查詢必須重新查詢一次。實際應(yīng)用中，在子查詢中過濾掉盡可能多的行，同時盡量避免使用子查詢處理實驗室管理系統(tǒng)中的數(shù)據(jù)。

如查詢各實驗課程的平均成績，按課程號分組且只選擇考勤成績合格的學生成績。

SELECT stu_su as 科目，avg(stu_bx) as 平均成績

FROM stu_achieve

GROUP BY stu_su having stu_at >’60’

其中HAVING 子句用于在計算出聚集之后對行的查詢進行控制。

3.4 合理使用索引查詢實驗室排課信息

在SQL 的查詢語句中，使用索引這一重要的數(shù)據(jù)結(jié)構(gòu)，目的就是為了提高查詢效率。在對經(jīng)常進行連接，但是沒有指定為外鍵的列上建立索引，而對不經(jīng)常連接的字段則由優(yōu)化器自動生成索引。在頻繁進行排序或分組(即進行g(shù)roup by或order by操作)的列上建立索引。不可簡單使用index，進行耗空間的全文索引也不可取。對CHAR 類型查詢時，查詢耗時和字段值總長度成正比，所以不能用CHAR 類型。而對VARCHAR類型. 對于字段的值很長的要建立全文索引。

如對課程進行查詢， 對課程表的 lab_ID字段建立了index索引。返回某一實驗室的排課信息。

SELECTlab_name，tch_name，class_name，time_

name，tutorshipID

FROMcourse_info，lab_info

WHERElab_ID = ’2’

在SQL語句的子查詢的條件where子句中使用了表course_info的索引字段lab_ID，where 子句，它不僅指出查詢的條件，同時它的編寫方法、編寫次序和編寫方案直接關(guān)系到整個查詢的效率。因此對表course_info的掃描只在與索引有關(guān)的范圍而無須對整個表掃描， 執(zhí)行效率很高。

3.5 使用臨時表查詢?nèi)簩W生的成績信息

SQL語句查詢?yōu)榱藴p少對公共數(shù)據(jù)的訪問，提高系統(tǒng)的運行效率，在系統(tǒng)中只對公共數(shù)據(jù)進行一次查詢，將查詢結(jié)果保存到系統(tǒng)臨時表中，當系統(tǒng)中其他模塊需要該數(shù)據(jù)時，直接從臨時表提取，查詢結(jié)束清除變量和關(guān)閉臨時表即可。 在實驗室管理關(guān)系系統(tǒng)中建立臨時存放公共數(shù)據(jù)的臨時表student1，將從課程表中查詢的數(shù)據(jù)放入student1 中的SQL 語句如下:

insert into student1

(lab_ID，tch_name，class_name，time_name， tutorshipID)

SELECTlab_ID，tch_name，class_name，time_name，tutorshipID

FROMcourse_info

如在全院學生信息這一很大的事務(wù)表中， 包含了百萬以上行， 而要查詢其中的幾百條數(shù)據(jù)， 且執(zhí)行多次，這時可創(chuàng)建一個臨時表， 并在臨時表的一個子集建立必要的索引，這樣能加速查詢，提高執(zhí)行效率。它有助于避免多重排序操作，還能簡化優(yōu)化器的工作，臨時表創(chuàng)建后不會反映主表的修改，但防止頻繁修改主表數(shù)據(jù)以免丟失數(shù)據(jù)。

例如: (1) 查詢?nèi)簩W生的實驗成績信息。

SELECT stu_ID，stu_name，stu_xu，stu_ax，stu_at，stu_bx

FROM stu_achieve，stu_info

WHERE stu_achieve.stu_ID = stu_info.stu_ID

ORDER BY stu_achieve.stu_ID

(2) 將2008級學生的成績找出來放在一個臨時表achieve_temp中，并按學生的學號進行排序:

insert into achieve_temp(stu_ID，stu_name，stu_xu，stu_ax，stu_at，stu_bx)

select stu_ID，stu_name，stu_xu，stu_ax，stu_at，stu_bx

FROM stu_achieve，stu_info

WHERE stu_achieve.stu_ID = stu_info.stu_ID

And stu_achieve.stu_ID >=’802000000’

And stu_achieve.stu_ID <=’802999999’

ORDER BY stu_achieve.stu_ID

(3) 在臨時表中查詢:

SELECT * FROM achieve_temp

WHERE stu_ID = ’802080134’

臨時表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤I/O，查詢工作量可以得到大幅減少，提高執(zhí)行效率。

3.6 使用謂詞嵌套查詢

使用謂詞嵌套查詢使得查詢求解層次分明，有結(jié)構(gòu)化程序之特征，如果要用子查詢，那就用EXISTS替代IN、用NOT EXISTS替代NOT IN。因為EXISTS引入的子查詢只是測試是否存在符合子查詢中指定條件的行，效率較高。NOT IN都是最低效的。因為它對子查詢中的全表執(zhí)行。

例如:查詢實驗課中“程序設(shè)計基礎(chǔ)”不及格的學生的學號，姓名。

SELECT stu_ID，stu_name

FROMstu_info

WHERE stu_ID in

SELECTstu_ID

FROMstu_achieve

WHERE stu_bx <’60’and stu_su = ’程序設(shè)計基礎(chǔ)’

使用謂詞EXISTS的嵌套查詢。

SELECT stu_ID，stu_name

FROMstu_info

WHERE EXISTS(

SELECT*

FROMstu_achieve

WHERE stu_bx <’60’and stu_su =’程序設(shè)計基礎(chǔ)’)

3.7 SQL 編程中存儲過程的使用

使用SQL 編程來訪問和管理數(shù)據(jù)庫中數(shù)據(jù)的方式主要有:嵌入式SQL，PL/SQL，ODBC，JDBC 以及OLEDB編程等方式?，F(xiàn)僅對經(jīng)常使用的嵌入式SQL 和PL/SQL 來做分析。PL/SQL 是編寫數(shù)據(jù)庫存儲過程的一種過程語言，存儲過程允許預(yù)定義通用數(shù)據(jù)庫操作，只需要調(diào)用存儲過程就可以啟用這些操作，這種方式可有效地提高執(zhí)行效率并減少可能出現(xiàn)錯誤的機會。開發(fā)存儲過程，可以提高軟件的可維護性和系統(tǒng)的運行速度。

例如:定義一個存儲過程用于向?qū)W生基本信息表(stu_info)中插入新記錄。

CREATE proc insert_stuinfo(@stuid int，@stu_name varchar(15)，@classid int)

AS

BEGIN

INSERT into stu_info(stu_id，stu_name，class_id)values(@stuid，@stu_name，@classid)

END

四、結(jié)束語

實驗室教學管理是學院教學工作的重要組成部分， 通過以上分析，用SQL 語言來完成對實驗室管理系統(tǒng)信息數(shù)據(jù)庫的信息處理，是可以采用多種SQL 語句的編寫策略來提高查詢對象的執(zhí)行效率，因此在SQL 語言功能實現(xiàn)的同時，探討SQL 語言不同實現(xiàn)方式之間的執(zhí)行效率的性能上的差異， 要根據(jù)數(shù)據(jù)庫應(yīng)用系統(tǒng)的實際情況給出適當?shù)恼{(diào)整來滿足系統(tǒng)的運行要求，同時還要采取適當?shù)臄?shù)據(jù)策略， 盡量減少查詢范圍，減少計算機系統(tǒng)需要計算的數(shù)據(jù)，編寫優(yōu)化的SQL 語句，既可以提高效率， 又可以提高信息的正確度，隨著大型數(shù)據(jù)庫的使用， SQL 語言將不僅用于實驗室信息管理， 而且會被廣泛地應(yīng)用于各種信息處理領(lǐng)域。

主要參考文獻

[1] [美]詹姆斯·R·格羅夫(James R Groff)，等. SQL完全手冊[M]. 第2版，王麗敏，等譯. 北京:電子工業(yè)出版社，2006.

[2] 王寅永，李降宇，等. SQL Server深入詳解[M]. 北京:電子工業(yè)出版社，2008.

[3] 韓景田， 陳小義. 完善實驗室管理的方法探究[J]. 中國科技信息， 2005(10).

[4] 羅運模，王珊. SQL Server數(shù)據(jù)庫系統(tǒng)基礎(chǔ)[M]. 北京:電子工業(yè)出版社，2005.

[5] 盧鎮(zhèn)波，李青. 編程實例入門與提高[M].北京:電子工業(yè)出版社，2005.