torutkのブログ

ソフトウェア・エンジニアのブログ

H2 databaseでのレコード登録時間

Java読書会の宿題より

Java読書会「基礎からのServlet/JSP 第5版」を読む会（第3回）の宿題で、int（32bit整数）を超える行のテーブルを作成して、一気にintを超える行の更新をした時の戻り値を調べてみよう、の取り組みです。

H2データベースへのデータ登録に要する時間

primary keyもunique属性もない単一カラムのテーブルを定義

CREATE TABLE bigdata (
    id int
);
単純 insert

32bit符号付整数値の最大値がおよそ21億なので、30億行のレコードを登録します。 まずは、単純に INSERT INTO bigdata(id) values (1); のようにデータをインサートするクエリを30億回実行します。

  • 方法1 自動コミットが有効（デフォルト）
  • 方法2 自動コミットを無効にし、100万行ごとにcommit

方法1は、100万行のinsertに20秒を要しました。30億行のinsertに推定17時間要する見積です。自動コミットが有効だと、insertを実行するごとにコミットが働くので遅いだろうと想定していました。

方法2で、自動コミットを無効として、一定量毎にcommitをしてみました。 ところが、方法2でも、100万行のinsertに20秒を要し、方法1とほとんど処理時間が変わりません。

バッチinsert

次は、バッチ登録を実施します。複数のinsert文をバッチ化してからバッチを実行します。100から1000個のisertをバッチにまとめます。

     PreparedStatement ps = conn.prepareStatement("insert into bigdata values(?)");
       :       
    for (int i = 0; i < 1000; i++) {
        ps.setInt(1, count);
            ps.addBatch();
    }
    ps.executeBatch();

バッチ登録の場合、自動コミットはオフとし、最後にcommitします。*1

さて、期待していた登録速度ですが、なんと方法3でも100万行のinsertに20秒を要し、方法1、2とほとんど同じでした。

ここまでの結果から、H2 databaseはSQLのリクエストのオーバーヘッド、自動コミットのオーバーヘッドがほとんどなく、クエリの処理数がほぼそのまま処理時間になっているものと推測します。

すると、クエリの処理数を減らして登録行数を増やすことができれば処理時間を短縮できるのではと考えます。

複数行を1つのinsertで

ここで、insert文は、複数行のデータを登録することができます。

insert into bigdata(id) values (1), (3), (5), (7), (11), (13);

このinsert文は、6行のレコードを登録します。

  • 方法4 自動コミットを無効にして、10行のデータを1つのinsert文で登録する

100万行の登録が2-3秒と短縮することができました。

  • 方法5 自動コミットを無効にして、100行のデータを1つのinsert文で登録する

100万行の登録が1秒とさらに短縮することができました。これであれば、30億行の登録に50分の見積もりです。

実行環境
実行結果

*1:30億行となると、さすがに小分けにcommitした方が良いかも