Oracleには、マルチテーブル・インサートという単一のINSERT文で複数のテーブルに挿入できる機能がある。
参考:
- INSERT - Oracle Database SQL言語リファレンス 11gリリース2(11.2)
- SQLクリニック(12):1つのSQL文で複数の表にINSERTする絶品テクニック (1/2) - @IT
名称の通り、SELECTで受けた結果を複数のテーブルに振り分けるのが主要な用途であるが、下記のように単一のテーブルに対するINSERTをただ単にたばねるという操作も可能である。
INSERT ALL INTO A(COLUMN1) VALUES(?) INTO A(COLUMN1) VALUES(?) INTO A(COLUMN1) VALUES(?) SELECT 1 FROM DUAL;
今回は、このマルチ数(このエントリ内でのみ使用する独自の用語。上記のSQL例での「INTO A(COLUMN1) VALUES(?)」の件数を指すことにする)を増やしたとき、速度に差が出るかどうかを試すことにする。
環境
準備
適当なテーブルを準備する。インデックスとか制約とかはなんもなし。
CREATE TABLE A (COLUMN1 VARCHAR2(16));
同一条件下のテスト(後述)完了後に、一旦テーブル削除&Oracle再起動をする。
DROP TABLE A PURGE;
計測用のプログラム
long start = System.currentTimeMillis(); final int COUNT = 100000; final int MULTI_SIZE = 2; StringBuilder sqlString = new StringBuilder(); sqlString.append("INSERT ALL "); for (int i=0; i<MULTI_SIZE; i++) { sqlString.append("INTO A(COLUMN1) VALUES(?)"); } sqlString.append("SELECT 1 FROM DUAL"); try (Connection connection = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.0.12:1521:XE", "kagamihoge", "xxxx"); PreparedStatement sql = connection.prepareStatement(sqlString.toString());) { connection.setAutoCommit(false); final int n = COUNT / MULTI_SIZE; for (int i = 0; i < n; i++) { for (int j = 1; j <= MULTI_SIZE; j++) { sql.setString(j, RandomStringUtils.randomAlphanumeric(16)); } sql.executeUpdate(); } connection.commit(); } catch (SQLException e) { e.printStackTrace(); } long end = System.currentTimeMillis(); System.out.println("end" + (end - start));
MULTI_SIZEがマルチ数に相当する。この値を変えて試行を行う。合計の処理件数は10万で固定。最終的にテーブルに10万挿入するまでにかかった時間を比べる。よって、マルチ数が1のときはINSERTの実行回数は10万、2のときは5万、となる。
計測は、あるマルチ数×3回→テーブル削除&作成→次のマルチ数×3回、という流れで実施。
結果
↓回 マルチ数→ | 1 | 2 | 10 | 100 | 1000 |
---|---|---|---|---|---|
1 | 35000 | 19125 | 7859 | 5610 | 6625 |
2 | 35031 | 20109 | 6422 | 6797 | 6688 |
2 | 35750 | 19766 | 6406 | 5671 | 6594 |
感想とか
マルチ数を1から2にすると、約1.7倍ほど速度に差が出る。これは、SQLの発行回数が半分になるので、そのくらいの速度差が出るのは感覚的に理解できる。プログラム的にもforのオーダーがマルチ数に左右されるので、妥当な結果といったところである。
しかし、増やしたら増やしただけ速度向上するかというとそうでもない。ウチの環境では100より1000の方がほぼ確実に遅くなっており、10と100とでは微妙なラインになっているので、おそらく20〜50あたりが適正&限界値なのだと思われる。が、これは当然環境に依存するので20〜50というのは参考値でしかない。どちらにせよ、あるマルチ数を越えると、小出しのINSERTをまとめる効果よりも巨大なINSERT文を解釈するための負荷の方が上回ってしまうのだと思われる。
また、今回は実験ということでコミット間隔のことは考慮していない。実戦では当然意識しなければならないので、コミット間隔の観点からもマルチ数は適正に保つ必要がある。マルチ数1000とかにしたらその単位でしかコミットorロールバックができなくなるんで。
マルチテーブル・インサートを速度向上の手段に使うのは妥当かどうか。個人的には、ちょっと微妙な気がしている。その理由は、クエリの発行回数を減らすのであれば、SQL*Loaderのようなツールがまず考慮に上がる。また、DBとのラウンドトリップを減らすのでれば、JDBCであればバッチ更新がある。なんちゅーか、複数のINSERTをマルチテーブル・インサートでまとめるので、結果として早くなるけどそれが本筋の機能ではない、という感じ? 「マルチテーブル・インサート文のどの部分にも順序を指定することはできません。」とか制限もあるし…… まー早くなるんならそれでいいんじゃねーの? ってのも意見の一つだと思うんで、このへんで終わり。