關(guān)于考勤管理系統(tǒng)中的SQL語句優(yōu)化解析

黃娟

摘要：該文針對沙鋼集團(tuán)人力資源管理信息系統(tǒng)中月初匯總上一月考勤數(shù)據(jù)的數(shù)據(jù)庫過程運行效率低得問題，通過優(yōu)化SQL語句，提高了匯總過程的執(zhí)行效率。

關(guān)鍵詞：ORACLE；SQL；考勤；數(shù)據(jù)庫訪問；索引

中圖分類號：TP3 文獻(xiàn)標(biāo)識碼：A 文章編號：1009-3044（2017）33-0031-01

沙鋼集團(tuán)人力資源管理信息系統(tǒng)中考勤模塊記錄考勤信息時每人每天兩條數(shù)據(jù)，分別記錄上午和下午的出勤情況。以集團(tuán)本部19500人計算，每個月的考勤記錄大約是117萬條數(shù)據(jù)，加上成員企業(yè)的考勤記錄和考勤表中需要有三個月左右數(shù)據(jù)，日常考勤表中維持的數(shù)據(jù)量大概有600萬條數(shù)據(jù)左右。本部薪酬計算用到的考勤數(shù)據(jù)需要從這張考勤表中取出數(shù)據(jù)并按工號及部門分別進(jìn)行匯總形成每人每個部門（考慮到人員部門調(diào)動情況）一條考勤數(shù)據(jù)。

1 難點及主要采取措施

考勤數(shù)據(jù)匯總中遇到的難點一是數(shù)據(jù)量大，數(shù)據(jù)在600萬條以上，對這些數(shù)據(jù)的處理需要考慮ORACLE的性能；二是對新入職員工職校考勤時的考勤項目名稱是對應(yīng)正?？记陧椖棵Q后加“*”，但是在數(shù)據(jù)庫中記錄的考勤項目時是記錄對應(yīng)項目的FID。因此在匯總考勤數(shù)據(jù)前需要將職校考勤數(shù)據(jù)做特殊處理，將考勤項目轉(zhuǎn)換成對應(yīng)的正常考勤項目；三是人力資源管理信息系統(tǒng)數(shù)據(jù)庫中各類基礎(chǔ)表比較多，考勤表中記錄的信息主要是各類信息的FID，需要多表關(guān)聯(lián)才能將匯總表中的信息全部顯示出來。

針對這些情況，主要采取的措施有以下幾個方面：

1） 減少數(shù)據(jù)庫訪問次數(shù)。在執(zhí)行每條SQL語句時， ORACLE內(nèi)部執(zhí)行了許多工作： 解析SQL語句， 估算索引的利用率，綁定變量， 讀數(shù)據(jù)塊等等。因此減少訪問數(shù)據(jù)庫的次數(shù)， 就能實際上減少ORACLE的工作量。

2） 用索引提高效率。索引是表的一個概念部分，用來提高檢索數(shù)據(jù)的效率。實際上，ORACLE使用了一個復(fù)雜的自平衡B-tree結(jié)構(gòu)。當(dāng)ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時， ORACLE優(yōu)化器將使用索引。 同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率。 除了那些LONG或LONG RAW數(shù)據(jù)類型，你可以索引幾乎所有的列。在大型表中使用索引特別有效。

3） 盡量多的使用COMMIT。只要有可能，在程序中盡量多使用COMMIT，這樣程序的性能能得到提高，需求也會因為COMMIT所釋放的資源而減少：COMMIT所釋放的資源有回滾段上用于恢復(fù)數(shù)據(jù)的信息，被程序語句獲得的鎖，REDO LOG BUFFER中的空間，ORACLE為管理上述3種資源中的內(nèi)部花費。

2 具體應(yīng)用

針對具體在考勤匯總中遇到的問題主要在下面幾個方面做了修改：

1） 盡量減少對數(shù)據(jù)庫的訪問次數(shù)，具體體現(xiàn)在能夠一次SELECT，INSERT，UPDATE操作的數(shù)據(jù)不要分多次循環(huán)處理。因此將過程中對每個工號的員工進(jìn)行循環(huán)統(tǒng)計各考勤項目的天數(shù)修改成對考勤項目進(jìn)行循環(huán)統(tǒng)計所有人該考勤項目的對應(yīng)天數(shù)。這種處理方式使循環(huán)次數(shù)由原來的19500次左右縮短到15次左右。大大縮短了過程執(zhí)行的時間。

2） 用索引提高效率，具體應(yīng)用是將過程中涉及的表按照條件中應(yīng)用到的列的次序建立索引。這里要提到的是一開始只注意到考勤表數(shù)據(jù)量大，需要建立索引，沒有注意到匯總表。雖然考勤表以百萬計，而匯總表只以萬計，但是在操作匯總表時索引的創(chuàng)建極大地提高了效率。對匯總表操作UPDATE語句由原來的數(shù)小時降低到半分鐘左右。

3） 盡量多的使用COMMIT。在循環(huán)處理各考勤項目匯總時，對產(chǎn)生的數(shù)據(jù)進(jìn)行COMMIT操作。這樣做的好處不僅是釋放了數(shù)據(jù)庫資源，同時在數(shù)據(jù)中也即是保存了數(shù)據(jù)，能即是了解數(shù)據(jù)處理到哪一個環(huán)節(jié)。

4） 盡量不在條件語句中出現(xiàn)的列上使用函數(shù)。過程中使用到的日期在數(shù)據(jù)庫中存儲的是TIMESTAMP類型，而統(tǒng)計的維度是一個自然月。這里對進(jìn)行比較的自然月進(jìn)行了處理，將月初月末類型裝換成TIMESTAMP類型來取代在原先采用的將數(shù)據(jù)庫表日期列裝換成自然月的字符串類型的方法。這種方法使數(shù)據(jù)轉(zhuǎn)換由原來的每條數(shù)據(jù)一次縮減到一條SELECT一次，減少了ORACLE轉(zhuǎn)換數(shù)據(jù)類型需要的時間。

3 結(jié)束語

ORACLE SQL性能優(yōu)化的一些措施在人力資源管理信息系統(tǒng)的考勤匯總中的應(yīng)用大大提高了數(shù)據(jù)庫匯總過程的執(zhí)行效率，為考勤記錄的統(tǒng)計、考勤報表的生產(chǎn)提供了數(shù)據(jù)，為薪酬模塊、績效模塊和獎金模塊的順利上線做好了數(shù)據(jù)準(zhǔn)備。

參考文獻(xiàn)：

[1] 黃河. Oracle9i數(shù)據(jù)庫系統(tǒng)培訓(xùn)教程[M]. 北京： 清華大學(xué)出版社， 2002.

[2] Thomas Kyte. Oracle專家高級編程[M]. 北京： 清華大學(xué)出版社， 2002.

[3] Kevin Loney. Oracle9i： The Complete Reference[Z].Oracle公司技術(shù)參考書.