kagamihogeの日記

kagamihogeの日記です。

マルチテーブル・インサートで同一テーブルに複数データ挿入してみる

Oracleには、マルチテーブル・インサートという単一のINSERT文で複数のテーブルに挿入できる機能がある。

参考:

名称の通り、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をマルチテーブル・インサートでまとめるので、結果として早くなるけどそれが本筋の機能ではない、という感じ? 「マルチテーブル・インサート文のどの部分にも順序を指定することはできません。」とか制限もあるし…… まー早くなるんならそれでいいんじゃねーの? ってのも意見の一つだと思うんで、このへんで終わり。