kagamihogeの日記

kagamihogeの日記です。

JDBC経由で100万件取得・追加してみた

Java Oracle DB

環境

setFetchSizeの変更による影響

JDBC経由でデータ取得するとき、java.sql.Statement#setFetchSize(int rows)でフェッチする行数を指定できる。この行数を変えたとき、実行時間がどの程度変わるかを確認する。


まず下記の適当なテーブルを作りレコードを100万件入れておく。

CREATE TABLE FROM_A_TBL (CLM VARCHAR2(16));

次にこんな感じのコードを実行する。実行時間の計測手順は、同一フェッチサイズで三回連続実行する。その後、フェッチサイズを変更して再度実行、という流れ。

        long start = System.currentTimeMillis();
        
        Connection connection = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:XE", "xxxx", "xxxx");
        Statement s = connection.createStatement();
        s.setFetchSize(50);//ここを変更
        ResultSet r = s.executeQuery("SELECT CLM FROM TO_A_TBL");
        int i=0;
        System.out.println(r.getFetchSize());
        while (r.next()) {
            String s1 = r.getString("CLM");
            i++;
        }
        System.out.println(i);
        
        r.close();
        s.close();
        connection.close();
        
        long end = System.currentTimeMillis();
        System.out.println(end - start);


下記の表の時間はend - startをそのまま載せている。fetchSizeの10（デフォルト）はsetFetchSizeで明示的に数値を指定しなかった場合（OracleのJDBCは10がデフォらしい）である。

fetchSize／時間 １ ２ ３
1 46937 48688 49297
10（デフォルト） 5750 5781 5703
50 2078 2078 2000
100 1641 1594 1578
1000 1219 1110 1109
10000 1047 1047 1062
100000 1282 1297 1265

サイズを1にするとやはり驚異的に遅くなる。やはり一件取得するごとにDBとの通信が発生するような状況にすると相当遅くなるようだ。
フェッチサイズを上げていくと、基本的には大きくした分だけのスピードアップがある。ただし、無闇にデカイ数値なら良いというもんでもなく、100000は10000に比べて逆に遅くなっている。いくらDBとの通信回数を減らせるからといって、余りに大きいフェッチサイズにすすると逆にその処理が足を引っ張り始めるということなんだろう。
この結果だけ見ると10000前後が一番適正数値っぽいが、これは環境依存と思われる。マシンのスペックだとかのハード性能、ブロックサイズや問い合わせクエリの性質やキャッシュの状態だとかのOracleミドルウェアの設定、ネットワークの回線速度などなどに左右されるのではなかろうか。

addBatch, executeBatchの頻度変更による影響

JDBC経由でデータ追加するとき、java.sql.Statement#addBatch(String sql) や java.sql.PreparedStatement#addBatch() などで複数のINSERTやUPDATEを追加し、java.sql.Statement#executeBatch() でまとめてDBに送り込める。executeBatchをaddBatchの何回目で実行するかを変更したとき、実行時間がどの程度変わるかを確認する。

まず下記の適当なテーブルを作り、このテーブルに100万件INSERTしていくことにする。また、計測一回ごとにDROPしてCREATEと適当なテーブルを作りなおしてカラにしておく。

CREATE TABLE FROM_A_TBL (CLM VARCHAR2(16));
DROP TABLE TO_A_TBL;

addBatch,executeBatch の前に、それらを使わずに100万件INSERTした場合の実行時間を計測しておく。プログラムはこんな感じ。実行時間の結果は、後述の表にまとめて載せる。

        long start = System.currentTimeMillis();
        
        Connection c = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:XE", "xxxx", "xxxx");
        c.setAutoCommit(false);
        PreparedStatement p = c.prepareStatement("INSERT INTO TO_A_TBL VALUES (?)");
        for (int i=0; i<1000000; i++) {
            p.setString(1, RandomStringUtils.randomAlphanumeric(16));
            p.executeUpdate();
        }
        p.close();
        c.commit();
        c.close();
        
        long end = System.currentTimeMillis();
        System.out.println(end - start);

次にこんな感じのコードを実行する。実行時間の計測手順は、同一頻度で三回連続実行する。その後、頻度を変更して再度実行、という流れ。

        long start = System.currentTimeMillis();
        
        Connection c = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:XE", "xxxx", "xxxx");
        c.setAutoCommit(false);
        PreparedStatement p = c.prepareStatement("INSERT INTO TO_A_TBL VALUES (?)");
        int j=0;
        for (int i=0; i<1000000; i++) {
            p.setString(1, RandomStringUtils.randomAlphanumeric(16));
            p.addBatch();
            if (j == 10) {//ここを変更
                p.executeBatch();
                j=0;
            }
            j++;
        }

        p.close();
        c.commit();
        c.close();
        
        long end = System.currentTimeMillis();
        System.out.println(end - start);

例外処理とか、合計件数が頻度で割り切れなかったら終端はどうなるんだとか、細かい突っ込みどころは沢山あるが、その辺は省略。

下記の表の時間はend - startをそのまま載せている。「なし」はaddBatch,executeBatchを使用しないものの数値。

頻度／時間 １ ２ ３
なし 68781 69140 70031
10 10094 11203 10453
100 3672 5000 3922
1000 2985 4204 3000
10000 2820 2828 2891
100000 3063 5500 3688

フェッチサイズの時と同様、DBとの通信回数が減れば減るほど実行時間が短縮される傾向にある。なしと10のときとで隔絶しているのも同様である。
また、この環境では10000あたりが限度のようである。これもフェッチサイズ同様、それぞれの環境での適正数値は異なると思われる。

まとめ

  • JDBC経由で大量データを取得するとき、フェッチサイズをある程度大きくすることで実行時間の短縮が見込める。
  • JDBC経由で大量データを追加するとき、addBatch,executeBatch を適切に使用することで実行時間の短縮が見込める。

といったところが確認できました。

2012/07/04追記

バッチ・サイズを100以下の範囲に保つことをお薦めします。バッチが大きくなると、パフォーマンスはほとんど、またはまったく向上せず、実際には、大きなバッチを処理するために必要なクライアント・リソースのために、パフォーマンスが低下する可能性があります。

Oracle Database JDBC開発者ガイド11gリリース2（11.2）- コーディングのヒント - 標準バッチ更新とOracleバッチ更新 より抜粋　太字部分は俺によるもの

関連エントリ