eclipselinkでbatch insert

eclipselinkでは、jdbc.batch-writingとjdbc.batch-writing.sizeの2つのプロパティを指定することでbatch insertが使用されます。

jdbc.batch-writingには、基本的にjdbcを指定すればよく、jdbc.batch-writing.sizeにはバッチサイズを指定する。

例えば、以下のように設定するとバッチサイズは100となります。

<properties>
  <property name="javax.persistence.schema-generation.database.action" value="drop-and-create" />

  <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>

  <property name="eclipselink.jdbc.batch-writing" value="JDBC" />
  <property name="eclipselink.jdbc.batch-writing.size" value="100" />
</properties>

結果を見ると、batch insertのほうが早くなっているのがわかります。

batch insertあり

---------- 1回目: 625ms ----------
---------- 2回目: 319ms ----------
---------- 3回目: 274ms ----------

batch insertなし

---------- 1回目: 1630ms ----------
---------- 2回目: 1342ms ----------
---------- 3回目: 1146ms ----------

hibernateでbatch-insert

hibernate.jdbc.batch_sizeに任意の数字を指定すると、その値が自動的にJDBCのバッチサイズになります。

例えば、以下の設定を行うとバッチサイズは100となります。

<properties>
  <property name="javax.persistence.schema-generation.database.action" value="drop-and-create" />
  <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver" />
  <property name="javax.persistence.jdbc.url" value="url"/>
  <property name="javax.persistence.jdbc.user" value="user"/>
  <property name="javax.persistence.jdbc.password" value="password"/>

  <property name="hibernate.jdbc.batch_size" value="100" />
</properties>

試しに以下のコードでbatch insertの効果があるのか確認してみます。

private static void insert(EntityManager em) {
        final EntityTransaction transaction = em.getTransaction();
        transaction.begin();
        for (int i = 1; i <= 1000; i++) {
            final UserEntity entity = new UserEntity();
            entity.setName("name_" + i);
            em.persist(entity);
        }
        transaction.commit();
    }

結果をみると、batch insertの効果で5倍ぐらい早くなっているのがわかります。

batch insertあり

---------- 1回目: 766ms ----------
---------- 2回目: 390ms ----------
---------- 3回目: 317ms ----------

batch insertなし

---------- 1回目: 2369ms ----------
---------- 2回目: 2347ms ----------
---------- 3回目: 1589ms ----------

PostgreSQLのsleepファンクション

pg_sleepを使うことでデータベースサーバ側でsleep処理ができる。
pg_sleepの引数には、sleepしたい秒数を指定する。

-- 5秒sleep
select pg_sleep(5);
 pg_sleep
----------

(1 行)

時間: 5016.963 ms

-- 3秒sleep
select pg_sleep(3);

 pg_sleep
----------

(1 行)

時間: 3001.497 ms

IS DISTINCT FROM演算子

IS DISTINCT FROM(IS NOT DISTINCT FROM)演算子の使い方。PostgreSQLでは使うことができる。Oracleは12c時点では使うことができない。
この演算子を使うと、一方がnullの可能性がある場合の条件をcol1 = :col1 or col1 is nullのようにnullを考慮する必要がなくなる。

使い方

IS NOT DISTINCT FROMは、以下の条件と同じ動きとなる。

(expo IS NOT NULL
AND exp2 IS NOT NULL
AND exp1 = exp2)
OR (exp1 IS NULL AND exp2 IS NULL)

例

検索対象のデータ

データの内容(@は、nullを示しています)

select * from test;
 col
-----
   1
   2
   3
   @
(4 行)

IS NOT DISTINCT FROM

is not distinct from nullとするとnullのレコードのみ取得できる。

select * from test where col is not distinct from null;
 col
-----
   @

条件を指定すると、そのレコードのみ取得できる。

select * from test where col is not distinct from 3;
 col
-----
   3
(1 行)

[Oracle12c]SQL*Loaderのエクスプレス・モード

Oracle12CからはSQL*Loaderのエクスプレス・モードを使用してコントロールファイルを作らずにデータをロードすることができるようです。
この機能は、表の列がすべて文字列、数値または日付型で、入力ファイルにデリミタ付きの文字列データが含まれている場合(例えばCSVファイル)に、テーブル名の指定のみでロードが実行できます。

エクスプレスモードの例

テーブル定義

主キーと属性1つづつのシンプルなテーブルで試してみます。

SQL> desc users;
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_ID       NOT NULL NUMBER(10)
 NAME          VARCHAR2(100 CHAR)

CSVファイルの内容

データが5行のCSVファイルです。

1,user1
2,user2
3,user3
4,user4
5,user5

実行結果

実行結果の標準出力内容です。5行のロードが正常に終わっていることがわかります。
実行時には、接続情報をテーブル名のみを与えるのみで、コントロールファイルは作成していません。
入力ファイルを指定しない場合、デフォルトでは「テーブル名.dat」となります。今回の場合は、「users.dat」が入力ファイルとなります。

$ sqlldr hoge/hoge@orcl TABLE=users

SQL*Loader: Release 12.1.0.2.0 - Production on Sat Jun 27 10:49:48 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: USERS
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table USERS:
  5 Rows successfully loaded.

Check the log files:
  users.log
  users_%p.log_xt
for more information about the load.

テーブルにも5レコード格納されています。

SQL> r
  1* select * from users

   USER_ID NAME
---------- -------------------------
  1 user1
  2 user2
  3 user3
  4 user4
  5 user5

どんな定義でロードされるの？

実行時の定義（コントロールファイルの内容）は、実行ログから確認できます。
今回の場合は、以下の定義でロードされています。

OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'users'
APPEND
INTO TABLE USERS
FIELDS TERMINATED BY ","
(
  USER_ID,
  NAME CHAR(400)
)

デフォルト値の上書き方法

デフォルト値はコマンドラインパラメータで上書きします。例えば、フィールドの区切り文字をデフォルトのカンマ(,)から縦棒(|)に変更したい場合には以下のようにします。

sqlldr hoge/hoge@orcl TABLE=users TERMINATED_BY='|'

変更できる項目はマニュアルで参照できます。

SQL*Loaderでインプットファイルの論理行番号をテーブルに格納する

SQL*Loaderでデータロード時に、ファイルの論理レコード番号をテーブルに格納したい場合には、RECNUMパラメータを使用する。
RECNUMを使用するとスキップしたレコードや不良レコード、破棄レコードも1レコードとしてカウントされる。

ロード先のテーブル定義

名前       NULL?    型
--------- -------- ----------------------------
REC_NUM   NOT NULL NUMBER
NAME               VARCHAR2(100)

コントロールファイルの内容

論理行番号を格納したいカラムにRECNUMパラメータを使用する。

OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET 'AL32UTF8'
INFILE 'input.csv' "str '\n'"
BADFILE 'input.bad'
DISCARDFILE 'input.dsc'
TRUNCATE
INTO TABLE HOGE
FIELDS TERMINATED BY ',' 
(
REC_NUM RECNUM,
NAME OPTIONALLY ENCLOSED BY '"' 
)

CSVファイルの内容

タイトル行あり、不正なレコード(空行)ありのファイル。

name
あ
い

う
え
お

実行結果の確認

以下のコマンドで、上のCSVファイルを取り込みます。

sqlldr 接続先情報 control=input.ctl

スキップしたヘッダー行や不正なレコードの空行もレコード数としてカウントアップされているのが分かる。

1* select * from hoge

REC_NUM    NAME
---------- ------------------------------
2          あ
3          い
5          う
6          え
7          お

[PostgreSQL]単一のINSERT文で複数レコードを登録

INSERT文のVALUES区に登録対象のレコード情報をカンマ(,)区切りで複数列挙することで一括登録ができる

SQL例

使い方は簡単で、INSERTのVALUE句に設定する。

INSERT INTO users (id, name) VALUES
    (1, 'hoge'),
    (2, 'fuga')