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();
}<?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>
ウズウズカレッジ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;
}
}その他のソースコード内容は、以下のサイトを参照のこと。
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の実行回数を減らした方が、処理時間を大幅に短くすることができる。
