用ADO.Net實現Oracle大批量資料更新最佳化)

在業務系統應用中，經常會使用到大量資料的的提交（包括查詢、更新或刪除），如果目標資料庫的資料量較大，一次需要處理的操作較多，就會出現系統執行效率低下等問題。文本以Oracle9i資料庫為例，通過對ADO.Net中的資料庫支援的應用實踐，說明幾種常見的最佳化處理方法，並對比其中的優劣。

為了說明情況，以某業務資料填報功能為例，假設有100個使用者每周需要填報某統計數量，填報明細的資料量約為200條，有專門的填報頁面實現一次提交，這樣一周的資料增量約為2萬，一年為100多萬，要保證系統有效運行6年以上，需要考慮資料存放區（增、刪、改）效率問題，（資料庫本身的最佳化配置，包括資料表空間、索引等查詢效率已經考慮，不在此討論範疇）。這類業務的特點是，資料操作量較大，但執行的指令複雜度較低，包含簡單的新增、修改、刪除3類。

 

 

 

傳統處理方法存在的問題
對每一個要處理的操作，直接對目標表執行對應的SQL操作（或預存程序），可使用ADO.Net的參數化SQL或通過DataSet與DataAdapter來間接處理。這樣每個使用者批量提交資料時，需要執行大約200次 SQL操作，雖然資料庫進行了最佳化，單次執行SQL的效率並不低，但由於一次執行的指令較多，隨著目標資料容量的增加，效率會逐步降低，最終不可忍受。

 

最佳化方法一：暫存資料表處理模式
對於大規模的目標資料庫表，進行多次修改、刪除或更新操作，效率必定較慢，要降低對目標表的操作次數，可以採用暫存資料表的解決辦法。具體方法為：建立一個與目標表結構類似的暫存資料表（由於B/S模式的特點，暫存資料表是基於事務的，而不是基於串連的），並增加操作模式標記欄位，在執行操作前，將本次要操作的資料，就是某個使用者，每周的資料（約200條左右，第一次處理時應該沒有資料）一次查詢轉入暫存資料表，再對暫存資料表執行修改、更新、刪除（作刪除標記）操作，處理完畢後，分別將暫存資料表的資料分三類提交到目標表。流程如所示：

 

 

 

刪除
Delete From TARGET_TABLE Where KEY In(Select KEY

From TEMP_TABLE Where STATE=’Delete’

新增
Insert Into TARGET_TABLE …

軟體開發網 www.mscto.com

 

Select … From TEMP_TABLE Where STATE=’Insert’

 

修改
Update TARGET_TABLE Set …

 

Where KEY=TEMP_TABLE.KET AND TEMP_TABLE.STATE=’Update’

 

實驗證明，在50萬資料量的條件下，此方法能比傳統的方法快40倍左右，且執行效率受目標資料庫容量的影響較小。

最佳化方法二：使用SQL批處理
SQL批處理一般有2種模式：一種是將要執行的SQL語句，串連形成批處理指示，一次提交到伺服器執行；一種是對執行的SQL指令，傳遞多組參數，批執行。這兩種方法都需要資料庫及ADO.Net的支援。

軟體開發網 www.mscto.com

 

System.Data.OracleClient 的ADO.Net 2.0版本支援第一種方式的的批處理，如通過DataAdapter對DataSet的批量資料提交時，系統會根據資料集合中的新增，修改，刪除標識，構造批處理指示，形成SQL指令段，提交伺服器執行。這種方式是將多個SQL指令形成一組SQL指令的方法，實現多個指令的批執行，能一定程度提高功能的執行效率。原理如下所示：

 

Begin

Insert Into TAREGT_TABLE(A,B,C) Values(:1,:2,:3);

Insert Into TAREGT_TABLE(A,B,C) Values(:4,:5,:6); 軟體開發網 www.mscto.com

Insert Into TAREGT_TABLE(A,B,C) Values(:7,:8,:9);

……

Insert Into TAREGT_TABLE(A,B,C) Values(:n,:n+1,:n+2); 軟體開發網 www.mscto.com

end;

 

此方法形成的批處理SQL指令及參數會隨著資料量的增加而成倍增加，資料更新量與執行效率受到限制。而微軟的Oracle ADO.Net實現並沒有將批處理方法直接對外公開，只能通過DataSet的資料批次更新間接使用。

 

 

 

另一種處理方法是使用Oracle的ADO.Net實現。Oracle.DataAccess.Client實現的ADO.Net支援第二種模式的批處理指示，其利用Oracle資料庫內建的批處理功能，通過設定OracleCommand的ArrayBindCount來實現對參數數組的傳遞。當 ArrayBindCount設定為大於1時，傳遞給一個OracleCommand的參數不再是參數值，而是參數數組，這樣，一條Command指令就可以執行多個處理，如：插入100條資料。使用這種方法，利用了資料庫本身對批量資料操作的最佳化機制，極大提高了資料操作效率。通過對目標資料庫容量為 50萬的目標表測試發現，此方法執行比傳統方法的執行效率提高50倍以上，在測試過程中發現，100萬的目標資料量的情況下，一次插入1萬條資料，只需要 1秒左右，且操作效率受目標資料量的影響較小。 軟體開發網 www.mscto.com

結論
綜合以上分析，我們認為，採用暫存資料表的方法及批處理的手段都能較好解決大規模資料量模式下的批量資料提交的問題。其中，Oracle的ADO.Net的實現效率最高，處理最簡單，微軟ADO.Net2.0的實現沒有完全利用資料庫本身的功能，功能及效率受到局限。而暫存資料表的處理方法編程比較複雜，適合於在使用微軟的ADO.Net的情況下使用。更進一步，Oracle預存程序支援參數數組的傳遞，也可以採用通過傳遞參數數組的預存程序來實現，前提也是必須採用Oracle的ADO.Net實現，因為微軟的ADO.Net實現不支援參數數組傳遞；而Oracle資料庫也支援Bulk Insert功能，如果有批量的資料需要插入，可以考慮使用此方法，此處不詳細討論。