jdbc批量插入資料庫

jdbc批量插入資料庫 翻譯：陳先波([email protected])

閱讀原文：http://www.theserverside.com/articles/article.tss?l=JDBCPerformance_PartIII

一、使用Statement而不是PreparedStatement對象

JDBC驅動的最佳化是基于使用的是什麼功能. 選擇PreparedStatement還是Statement取決于你要怎麼使用它們. 對于隻執行一次的SQL語句選擇Statement是最好的. 相反, 如果SQL語句被多次執行選用PreparedStatement是最好的.

PreparedStatement的第一次執行消耗是很高的. 它的性能展現在後面的重複執行. 例如, 假設我使用Employee ID, 使用prepared的方式來執行一個針對Employee表的查詢. JDBC驅動會發送一個網絡請求到資料解析和優化這個查詢. 而執行時會産生另一個網絡請求. 在JDBC驅動中，減少網絡通訊是最終的目的. 如果我的程式在運作期間隻需要一次請求, 那麼就使用Statement. 對于Statement, 同一個查詢隻會産生一次網絡到資料庫的通訊.

對于使用PreparedStatement池的情況下, 本指導原則有點複雜. 當使用PreparedStatement池時, 如果一個查詢很特殊, 并且不太會再次執行到, 那麼可以使用Statement. 如果一個查詢很少會被執行,但連接配接池中的Statement池可能被再次執行, 那麼請使用PreparedStatement. 在不是Statement池的同樣情況下, 請使用Statement.

二、使用PreparedStatement的Batch功能

Update大量的資料時, 先Prepare一個INSERT語句再多次的執行, 會導緻很多次的網絡連接配接. 要減少JDBC的調用次數改善性能, 你可以使用PreparedStatement的AddBatch()方法一次性發送多個查詢給資料庫. 例如, 讓我們來比較一下下面的例子.

例 1: 多次執行Prepared Statement   PreparedStatement ps = conn.prepareStatement(      "INSERT into employees values (?, ?, ?)" );      for  (n =  0 ; n <  100 ; n++) {        ps.setString(name[n]);     ps.setLong(id[n]);     ps.setInt(salary[n]);     ps.executeUpdate();   }   例 2: 使用Batch   PreparedStatement ps = conn.prepareStatement(      "INSERT into employees values (?, ?, ?)" );      for  (n =  0 ; n <  100 ; n++) {        ps.setString(name[n]);     ps.setLong(id[n]);     ps.setInt(salary[n]);     ps.addBatch();   }   ps.executeBatch();   在例 1中, PreparedStatement被用來多次執行INSERT語句. 在這裡, 執行了100次INSERT操作, 共有101次網絡往返. 其中,1次往返是預儲statement, 另外100次往返執行每個疊代. 在例2中, 當在100次INSERT操作中使用addBatch()方法時, 隻有兩次網絡往返. 1次往返是預儲statement, 另一次是執行batch指令. 雖然Batch指令會用到更多的資料庫的CPU周期, 但是通過減少網絡往返，性能得到提高. 記住, JDBC的性能最大的增進是減少JDBC驅動與資料庫之間的網絡通訊.

注：Oracel 10G的JDBC Driver限制最大Batch size是16383條，如果addBatch超過這個限制，那麼executeBatch時就會出現“無效的批值”（Invalid Batch Value） 異常。是以在如果使用的是Oracle10G，在此bug減少前，Batch size需要控制在一定的限度。

三、選擇合适的光标類型

的光标類型以最大限度的适用你的應用程式. 本節主要讨論三種光标類型的性能問題.

對于從一個表中順序讀取所有記錄的情況來說, Forward-Only型的光标提供了最好的性能. 擷取表中的資料時, 沒有哪種方法比使用Forward-Only型的光标更快. 但不管怎樣, 當程式中必須按無次序的方式處理資料行時, 這種光标就無法使用了.

對于程式中要求與資料庫的資料同步以及要能夠在結果集中前後移動光标, 使用JDBC的Scroll-Insensitive型光标是較理想的選擇. 此類型的光标在第一次請求時就擷取了所有的資料(當JDBC驅動采用'lazy'方式擷取資料時或許是很多的而不是全部的資料)并且儲存在用戶端. 是以, 第一次請求會非常慢, 特别是請求長資料時會理嚴重. 而接下來的請求并不會造成任何網絡往返(當使用'lazy'方法時或許隻是有限的網絡交通) 并且處理起來很快. 因為第一次請求速度很慢, Scroll-Insensitive型光标不應該被使用在單行資料的擷取上. 當有要傳回長資料時, 開發者也應避免使用Scroll-Insensitive型光标, 因為這樣可能會造成記憶體耗盡. 有些Scroll-Insensitive型光标的實作方式是在資料庫的臨時表中緩存資料來避免性能問題, 但多數還是将資料緩存在應用程式中.

Scroll-Sensitive型光标, 有時也稱為Keyset-Driven光标, 使用辨別符, 像資料庫的ROWID之類. 當每次在結果集移動光标時, 會重新該辨別符的資料. 因為每次請求都會有網絡往返, 性能可能會很慢. 無論怎樣, 用無序方式的傳回結果行對性能的改善是沒有幫助的.

現在來解釋一下這個, 來看這種情況. 一個程式要正常的傳回1000行資料到程式中. 在執行時或者第一行被請求時, JDBC驅動不會執行程式提供的SELECT語句. 相反, 它會用鍵辨別符來替換SELECT查詢, 例如, ROWID. 然後修改過的查詢都會被驅動程式執行，跟着會從資料庫擷取所有1000個鍵值. 每一次對一行結果的請求都會使JDBC驅動直接從本地緩存中找到相應的鍵值, 然後構造一個包含了'WHERE ROWID=？'子句的最佳化查詢, 再接着執行這個修改過的查詢, 最後從伺服器取得該資料行.

當程式無法像Scroll-Insensitive型光标一樣提供足夠緩存時, Scroll-Sensitive型光标可以被替代用來作為動态的可滾動的光标.  

四、使用有效的getter方法

JDBC提供多種方法從ResultSet中取得資料, 像getInt(), getString(), 和getObject()等等. 而getObject()方法是最泛化了的, 提供了最差的性能。 這是因為JDBC驅動必須對要取得的值的類型作額外的處理以映射為特定的對象. 是以就對特定的資料類型使用相應的方法.

要更進一步的改善性能, 應在取得資料時提供字段的索引号, 例如, getString(1), getLong(2), 和getInt(3)等來替代字段名. 如果沒有指定字段索引号, 網絡交通不會受影響, 但會使轉換和查找的成本增加. 例如, 假設你使用getString("foo") ... JDBC驅動可能會将字段名轉為大寫(如果需要), 并且在到字段名清單中逐個比較來找到"foo"字段. 如果可以, 直接使用字段索引, 将為你節省大量的處理時間.

例如, 假設你有一個100行15列的ResultSet, 字段名不包含在其中. 你感興趣的是三個字段 EMPLOYEENAME (字串型), EMPLOYEENUMBER (長整型), 和SALARY (整型). 如果你指定getString(“EmployeeName”), getLong(“EmployeeNumber”), 和getInt(“Salary”), 查詢旱每個字段名必須被轉換為metadata中相對應的大小寫, 然後才進行查找. 如果你使用getString(1), getLong(2), 和getInt(15). 性能就會有顯著改善.

五、擷取自動生成的鍵值

有許多資料庫提供了隐藏列為表中的每行記錄配置設定一個唯一鍵值. 很典型, 在查詢中使用這些字段類型是取得記錄值的最快的方式, 因為這些隐含列通常反應了資料在磁盤上的實體位置. 在JDBC3.0之前, 應用程式隻可在插入資料後通過立即執行一個SELECT語句來取得隐含列的值.

例 3:  JDBC3.0之前   //插入行    int  rowcount = stmt.executeUpdate (      "insert into LocalGeniusList (name) values ('Karen')" );   // 現在為新插入的行取得磁盤位置 - rowid    ResultSet rs = stmt.executeQuery (      "select rowid from LocalGeniusList where name = 'Karen'" );   這種取得隐含列的方式有兩個主要缺點. 第一, 取得隐含列是在一個獨立的查詢中, 它要透過網絡送到伺服器後再執行. 第二, 因為不是主鍵, 查詢條件可能不是表中的唯一性ID. 在後面一個例子中, 可能傳回了多個隐含列的值, 程式無法知道哪個是最後插入的行的值.

(譯者：由于不同的資料庫支援的程度不同，傳回rowid的方式各有差異。在SQL Server中，傳回最後插入的記錄的id可以用這樣的查詢語句：SELECT @IDENTITY )

JDBC3.0規範中的一個可選特性提供了一種能力, 可以取得剛剛插入到表中的記錄的自動生成的鍵值.  

例 4: JDBC3.0之後   int  rowcount = stmt.executeUpdate (      "insert into LocalGeniusList (name) values ('Karen')" ,   // 插入行并傳回鍵值    Statement.RETURN_GENERATED_KEYS);   ResultSet rs = stmt.getGeneratedKeys ();   // 得到生成的鍵值    現在, 程式中包含了一個唯一性ID, 可以用來作為查詢條件來快速的存取資料行, 甚至于表中沒有主鍵的情況也可以.

這種取得自動生成的鍵值的方式給JDBC的開發者提供了靈活性, 并且使存取資料的性能得到提升.

六、選擇合适的資料類型

接收和發送某些資料可能代價昂貴. 當你設計一個schema時, 應選擇能被最有效地處理的資料類型. 例如, 整型數就比浮點數或實數處理起來要快一些. 浮點數的定義是按照資料庫的内部規定的格式, 通常是一種壓縮格式. 資料必須被解壓和轉換到另外種格式, 這樣它才能被資料的協定處理.

七、擷取ResultSet

由于資料庫系統對可滾動光标的支援有限, 許多JDBC驅動程式并沒有實作可滾動光标. 除非你确信資料庫支援可滾動光标的結果集, 否則不要調用rs.last()和rs.getRow()方法去找出資料集的最大行數. 因為JDBC驅動程式模拟了可滾動光标, 調用rs.last()導緻了驅動程式透過網絡移到了資料集的最後一行. 取而代之, 你可以用ResultSet周遊一次計數或者用SELECT查詢的COUNT函數來得到資料行數.

通常情況下，請不要寫那種依賴于結果集行數的代碼, 因為驅動程式必須擷取所有的資料集以便知道查詢會傳回多少行資料.