Oracle上で1件ずつデータを追加した場合と一定件数毎にまとめてデータを追加した場合の性能を測定してみた

Oracle上でデータ追加を行う際、1レコードずつ追加するより、複数件まとめて追加することでSQLの実行回数を減らした方が、処理時間を大幅に短くすることができる。

今回は、Oracle上で1件ずつデータを追加した場合と、一定件数(1,000件)毎にまとめてデータを追加した場合それぞれでSQLの実行速度を測定するプログラムを作成してみたので、共有する。

なお、一括インサートするデータ量が多すぎると、バインド変数の数の上限を超えてしまうため、以下のサイトにある「ORA-01745: ホスト/バインド変数名が無効です。」が発生してしまうので、注意が必要である。
https://ameblo.jp/archive-redo-blog/entry-10304607870.html

前提条件

下記記事の実装が完了していること。

Oracle上でselect文で「*」を利用した場合とカラム名を利用した場合の性能を測定してみたSQL SELECT文を記載する際、「SELECT *」よりは「SELECT (カラム名)」を、「SELECT COUNT(*)」よりは...

また、Oracle上でMyBatisを利用して複数レコードをまとめて追加／更新する処理については、以下の記事を参考にすること。

Oracle上でMyBatisを利用して複数レコードをまとめて追加／更新してみたSpring BootとMyBatisを利用して、Oracleに接続するアプリケーション上では、引数に追加・更新対象データのリストを渡し...

サンプルプログラムの作成

作成したサンプルプログラムの構成は、以下の通り。

なお、上記の赤枠は、前提条件のプログラムから変更したプログラムである。

Mapperインタフェース・Mapper XMLの内容は以下の通りで、1件レコードを追加するSQL・複数件レコードを追加するSQL・その他必要なSQLを用意している。

package com.example.demo;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface UserDataMapper {

    /**
     * ユーザーデータテーブル(user_data)の全データを削除する
     */
    void truncateData();

    /**
     * 指定した1件のユーザーデータテーブル(user_data)のデータを追加する
     * @param userData ユーザーデータテーブル(user_data)の追加データ
     */
    void insertDataOne(UserData userData);

    /**
     * 指定したユーザーデータテーブル(user_data)のデータのリストをまとめて追加する
     * @param userDataList ユーザーデータテーブル(user_data)の追加データリスト
     */
    void insertDataMulti(@Param("userDataList") List<UserData> userDataList);

    /**
     * ユーザーデータテーブル(user_data)のデータ件数を取得する
     * @return ユーザーデータテーブル(user_data)のデータ件数
     */
    Long countAll();
}

package com.example.demo;

import org.apache.ibatis.annotations.Mapper;

import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper

public interface UserDataMapper {

/**

* ユーザーデータテーブル(user_data)の全データを削除する

void truncateData();

/**

* 指定した1件のユーザーデータテーブル(user_data)のデータを追加する

* @param userData ユーザーデータテーブル(user_data)の追加データ

void insertDataOne(UserData userData);

/**

* 指定したユーザーデータテーブル(user_data)のデータのリストをまとめて追加する

* @param userDataList ユーザーデータテーブル(user_data)の追加データリスト

void insertDataMulti(@Param("userDataList") List<UserData> userDataList);

/**

* ユーザーデータテーブル(user_data)のデータ件数を取得する

* @return ユーザーデータテーブル(user_data)のデータ件数

Long countAll();

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.UserDataMapper">
    <update id="truncateData">
        TRUNCATE TABLE USER_DATA
    </update>
    <insert id="insertDataOne" parameterType="com.example.demo.UserData">
        INSERT INTO USER_DATA (
              id
            , name
            , birth_year
            , birth_month
            , birth_day
            , sex
            , memo
        ) VALUES (
              #{id}
            , #{name}
            , #{birthY}
            , #{birthM}
            , #{birthD}
            , #{sex}
            , #{memo,jdbcType=VARCHAR}
        )
    </insert>
    <insert id="insertDataMulti" parameterType="java.util.List">
        INSERT INTO USER_DATA (
              id
            , name
            , birth_year
            , birth_month
            , birth_day
            , sex
            , memo
        )
        <foreach collection="userDataList" item="userData" separator="union all">
            ( SELECT
                  #{userData.id}
                , #{userData.name}
                , #{userData.birthY}
                , #{userData.birthM}
                , #{userData.birthD}
                , #{userData.sex}
                , #{userData.memo,jdbcType=VARCHAR}
              FROM DUAL
            )
        </foreach>
    </insert>
    <select id="countAll" resultType="long">
        SELECT COUNT(id) FROM USER_DATA
    </select>
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

TRUNCATE TABLE USER_DATA

</update>

INSERT INTO USER_DATA (

, name

, birth_year

, birth_month

, birth_day

, sex

, memo

) VALUES (

#{id}

, #{name}

, #{birthY}

, #{birthM}

, #{birthD}

, #{sex}

, #{memo,jdbcType=VARCHAR}

)

</insert>

INSERT INTO USER_DATA (

, name

, birth_year

, birth_month

, birth_day

, sex

, memo

)

( SELECT

#{userData.id}

, #{userData.name}

, #{userData.birthY}

, #{userData.birthM}

, #{userData.birthD}

, #{userData.sex}

, #{userData.memo,jdbcType=VARCHAR}

FROM DUAL

)

</foreach>

</insert>

SELECT COUNT(id) FROM USER_DATA

</select>

</mapper>

ウズウズカレッジJavaコースはわかりやすい動画教材と充実した就業サポートで優良企業を目指せるプログラミングスクールだったJavaは、世界中で広く使われていて、現在の需要が高く将来性もある開発言語になります。 https://www.acrovision....

また、サービスクラスのサブクラスの内容は以下の通りで、1件ずつデータを追加した場合と一定件数(1,000件)毎にまとめてデータを追加した場合の処理を実行し、それぞれの実行時間を表示するようにしている。

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

@Service
public class DemoServiceSub {

    /**
     * 追加するユーザーデータの数
     */
    private static final int CNT_USER_DATA = 100000;

    /**
     * 一括追加する場合のレコード数
     */
    private static final int CNT_INSERT_MULTI = 1000;

    /**
     * ユーザーデータテーブル(user_data)へアクセスするマッパー
     */
    @Autowired
    private UserDataMapper userDataMapper;

    /**
     * 性能検証を行うための個別サービス
     */
    public void verifyPerformanceEach() {
        // 追加するユーザーデータリストを生成する
        List<UserData> userDataList = new ArrayList<>();
        for (int i = 0; i < CNT_USER_DATA; i++) {
            userDataList.add(makeUserData(i));
        }

        // ユーザーデータテーブル(user_data)を全件削除する
        userDataMapper.truncateData();

        System.out.println("--- ユーザーデータテーブル(user_data)にデータを追加する"
                + "処理を1レコードずつ実施します start. ---");

        // 処理前の現在時刻を取得
        long startTime = System.currentTimeMillis();

        // ユーザーデータテーブル(user_data)にデータを追加する処理を1レコードずつ実施
        for (int i = 0; i < CNT_USER_DATA; i++) {
            userDataMapper.insertDataOne(userDataList.get(i));
        }

        // 処理後の現在時刻を取得
        long endTime = System.currentTimeMillis();

        System.out.println("処理件数 : " + userDataMapper.countAll());
        System.out.println("処理時間：" + (endTime - startTime) + " ms");
        System.out.println("--- ユーザーデータテーブル(user_data)にデータを追加する"
                + "処理を1レコードずつ実施します end. ---");
        System.out.println();

        // ユーザーデータテーブル(user_data)を全件削除する
        userDataMapper.truncateData();

        // CNT_INSERT_MULTIレコードずつ追加する際のループ数を算出する
        int cntInsertLoop = (CNT_USER_DATA + CNT_INSERT_MULTI - 1) / CNT_INSERT_MULTI;

        System.out.println("--- ユーザーデータテーブル(user_data)にデータを追加する処理を"
                + CNT_INSERT_MULTI + "レコードずつ実施します start. ---");

        // 処理前の現在時刻を取得
        startTime = System.currentTimeMillis();

        // ユーザーデータテーブル(user_data)にデータを追加する処理を
        // CNT_INSERT_MULTIレコードずつ実施
        for (int i = 0; i < cntInsertLoop; i++) {
            userDataMapper.insertDataMulti(
                    userDataList.subList(CNT_INSERT_MULTI * i
                            , Math.min(CNT_INSERT_MULTI * (i + 1), CNT_USER_DATA)));
        }

        // 処理後の現在時刻を取得
        endTime = System.currentTimeMillis();

        System.out.println("処理件数 : " + userDataMapper.countAll());
        System.out.println("処理時間：" + (endTime - startTime) + " ms");
        System.out.println("--- ユーザーデータテーブル(user_data)にデータを追加する処理を"
                + CNT_INSERT_MULTI + "レコードずつ実施します end. ---");
        System.out.println();
    }

    /**
     * 引数のIDをもつユーザーデータを生成する
     * @param id ID
     * @return ユーザーデータ
     */
    private UserData makeUserData(int id) {
        UserData userData = new UserData();
        userData.setId(id);
        userData.setName("テスト　プリン" + id);
        userData.setSex(Integer.toString(id % 2 + 1));
        userData.setBirthY(2012);
        userData.setBirthM(1);
        userData.setBirthD(15);
        userData.setMemo("テスト" + id);
        return userData;
    }
}

100

101

102

103

104

105

106

107

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import java.util.ArrayList;

import java.util.List;

@Service

public class DemoServiceSub {

/**

* 追加するユーザーデータの数

private static final int CNT_USER_DATA = 100000;

/**

* 一括追加する場合のレコード数

private static final int CNT_INSERT_MULTI = 1000;

/**

* ユーザーデータテーブル(user_data)へアクセスするマッパー

@Autowired

private UserDataMapper userDataMapper;

/**

* 性能検証を行うための個別サービス

public void verifyPerformanceEach() {

// 追加するユーザーデータリストを生成する

List<UserData> userDataList = new ArrayList<>();

for (int i = 0; i < CNT_USER_DATA; i++) {

userDataList.add(makeUserData(i));

}

// ユーザーデータテーブル(user_data)を全件削除する

userDataMapper.truncateData();

System.out.println("--- ユーザーデータテーブル(user_data)にデータを追加する"

+ "処理を1レコードずつ実施します start. ---");

// 処理前の現在時刻を取得

long startTime = System.currentTimeMillis();

// ユーザーデータテーブル(user_data)にデータを追加する処理を1レコードずつ実施

for (int i = 0; i < CNT_USER_DATA; i++) {

userDataMapper.insertDataOne(userDataList.get(i));

}

// 処理後の現在時刻を取得

long endTime = System.currentTimeMillis();

System.out.println("処理件数 : " + userDataMapper.countAll());

System.out.println("処理時間：" + (endTime - startTime) + " ms");

System.out.println("--- ユーザーデータテーブル(user_data)にデータを追加する"

+ "処理を1レコードずつ実施します end. ---");

System.out.println();

// ユーザーデータテーブル(user_data)を全件削除する

userDataMapper.truncateData();

// CNT_INSERT_MULTIレコードずつ追加する際のループ数を算出する

int cntInsertLoop = (CNT_USER_DATA + CNT_INSERT_MULTI - 1) / CNT_INSERT_MULTI;

System.out.println("--- ユーザーデータテーブル(user_data)にデータを追加する処理を"

+ CNT_INSERT_MULTI + "レコードずつ実施します start. ---");

// 処理前の現在時刻を取得

startTime = System.currentTimeMillis();

// ユーザーデータテーブル(user_data)にデータを追加する処理を

// CNT_INSERT_MULTIレコードずつ実施

for (int i = 0; i < cntInsertLoop; i++) {

userDataMapper.insertDataMulti(

userDataList.subList(CNT_INSERT_MULTI * i

, Math.min(CNT_INSERT_MULTI * (i + 1), CNT_USER_DATA)));

}

// 処理後の現在時刻を取得

endTime = System.currentTimeMillis();

System.out.println("処理件数 : " + userDataMapper.countAll());

System.out.println("処理時間：" + (endTime - startTime) + " ms");

System.out.println("--- ユーザーデータテーブル(user_data)にデータを追加する処理を"

+ CNT_INSERT_MULTI + "レコードずつ実施します end. ---");

System.out.println();

}

/**

* 引数のIDをもつユーザーデータを生成する

* @param id ID

* @return ユーザーデータ

private UserData makeUserData(int id) {

UserData userData = new UserData();

userData.setId(id);

userData.setName("テスト　プリン" + id);

userData.setSex(Integer.toString(id % 2 + 1));

userData.setBirthY(2012);

userData.setBirthM(1);

userData.setBirthD(15);

userData.setMemo("テスト" + id);

return userData;

}

その他のソースコード内容は、以下のサイトを参照のこと。
https://github.com/purin-it/java/tree/master/spring-boot-oracle-performance-multi-insert/demo

サラリーマン型フリーランスSEという働き方でお金の不安を解消しよう先日、「サラリーマン型フリーランスSE」という働き方を紹介するYouTube動画を視聴しましたので、その内容をご紹介します。「サ...

サンプルプログラムの実行結果

Spring Bootのメインクラス(DemoApplication.java)を実行した結果、コンソールログに出力される内容は以下の通りで、一定件数(1,000件)毎にまとめてデータを追加した方が、SQLが速くなることが確認できる。

＜1回目＞

＜2回目＞

要点まとめ

Oracle上でデータ追加を行う際、1レコードずつ追加するより、複数件まとめて追加することでSQLの実行回数を減らした方が、処理時間を大幅に短くすることができる。

前提条件

サンプルプログラムの作成

サンプルプログラムの実行結果

要点まとめ

Oracle上でWHERE句内でインデックスを利用した場合と利用しなかった場合の性能を測定してみた

Oracle上でselect文で「*」を利用した場合とカラム名を利用した場合の性能を測定してみた

Oracle上でWHERE句内でBETWEEN句を利用した場合と利用しなかった場合の性能を測定してみた

Oracle上でWHERE句内でIN句を利用した場合とEXISTS句を利用した場合の性能を測定してみた

Oracle上でテーブルを結合しデータを取得する際、テーブル別名をつけた場合とつけなかった場合の性能を測定してみた