これまたやる前から結果分かってることなんですが。ループでINSERT文を発行する処理を想定したとして、commitをループの1件ごとにするときと、ループ終了後に1回だけするとき、とではどのくらい速度に差が出るものなのかをやってみる。
環境
準備
適当なテーブルを準備する。インデックスとか制約とかはなんもなし。
CREATE TABLE A (COLUMN1 VARCHAR2(16));
同一条件下のテスト（後述）完了後に、一旦テーブル削除＆Oracle再起動をする。
DROP TABLE A PURGE;
計測用のプログラム
「commitをループの1件ごとにする」はこんな感じ。
long start = System.currentTimeMillis(); try (Connection connection = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.0.12:1521:XE", "xxxx", "xxxx"); PreparedStatement sql = connection.prepareStatement("INSERT INTO A(COLUMN1) VALUES(?)");) { connection.setAutoCommit(false); for (int i = 0; i < 100000; i++) { sql.setString(1,RandomStringUtils.randomAlphanumeric(16)); sql.executeUpdate(); connection.commit(); } } catch (SQLException e) { e.printStackTrace(); } long end = System.currentTimeMillis(); System.out.println("end" + (end - start));
「（commitを）ループ終了後に1回だけする」はこんな感じ＆変更点のみ抜粋。
for (int i = 0; i < 100000; i++) { sql.setString(1,RandomStringUtils.randomAlphanumeric(16)); sql.executeUpdate(); } connection.commit();
計測は、1件ごと×3回→テーブル削除＆Oracle再起動→1回だけ×3回、という流れで実施。
結果
| 1 | 2 | 3 | |
| 1件ごと | 140250 | 126297 | 130047 |
| 1回だけ | 37735 | 36484 | 34922 |
というわけで、1回だけの方が1件ごとより4倍ちょい早い、という結果になりました。
感想とか
コミットのオーバーヘッドって意外とあるんだなぁ、というのが正直なところ。速度に差がどんくらい出るのかは、マシンスペックによってだいぶ変わってきそうではあるので、約4倍差というのは参考値でしかない。とはいえ原理的にもナッシングオアオール方式が速いのは明らかではある。テスト条件が、10万件・制約もインデックスも何もないテーブルへのINSERTのみ、という至極単純なもので、現場ではもっと複雑なことを幾らでも行われる。そのため、現実的にはもっと差が出そうな気がしないでもない。
ただし、このエントリにおけるテストは開始から終了までの実行時間しか計測していない。他の要因についても調べないとフェアでないのは確かなんだけど……その一つに、UNDO表領域にどういう影響が及ぶのか？ が思いつく。俺のOracle理解があまり深くないんで調査対象にしなかったし、なので以下は推測が混じる点なんですが。UNDO表領域とて無限にあるわけでなし、ものすごい量のコミットデータを溜め込んだとき様々な問題が発生しうることは予想が付く。ロックに関しても似たような感じ。
なので、一般的にはテキトーな間隔でCOMMITするのがフツーっぽい気がする。参考：津島博士のパフォーマンス講座 第15回 バッチ処理について
あとは、あっちこっちのテーブル更新したり削除したりするようなややこしいバッチが途中でコケたりすると、再実行するための手順がやたら難しくなった経験があり。そういう時、コミット間隔みたいなの設けるより、最後に1回だけコミットする方にしたほうが良かったのかなぁー？とか思ったりもした。Oracleにはメッチャ負荷がかかるけど、運用チームが実行する手順が軽減されるのに比べると、うーん……みたいな。
もう一つ。ここんとこ「Oracleが遅い」ってどういうことだろうか？ てなことを考えていたのだけど。OracleとSQLを勉強するにつれ、「Oracleが遅い」のではなく、正確には「Oracleを使うアプリケーション・SQLの作り方が悪くて遅い」っぽいんですよね。勿論Oracleとてバグが無いわけではないけど、フツーに使ってる分にはOracleが悪いんではなくOracleを使う方が悪い、という事が理解できつつある。このエントリに関してだけでも、コミット間隔次第で速度に差が出ることは分かる。それに、ループ処理がそもそもSQLでは不利になりやすい、という原理もある。参考：カーソルのループでINSERT100万とSELECT INSERT100万の速度比較してみた - kagamihogeのblog 単にデータがロードされれば良いというだけならSQL*Loaderという選択肢もあるし……などなど。
