バルクインサートでタイムアウトを解決！データ登録処理の効率化

背景と目的

データ登録処理でタイムアウトが生じ、画面遷移が出来ない問題に直面しました。

その際にバルクインサートという方法を用いることで、処理時間を大幅に短縮でき、タイムアウトを防ぐことが出来ました。
この記事では、実際の事例と共にバルクインサートについて紹介します。

バルクインサートの一般的説明

バルクインサートとは？

バルクインサートとは、複数のデータを一つのクエリで一括挿入する方法です。
データベースへのアクセス回数を減らし、効率的にデータを登録できます。

通常のインサート処理との違い

通常のインサート処理では、1件ずつデータを登録します。

INSERT INTO table_name (column1) VALUES (value1);

この方法で複数のデータをインサートするためには、以下のように複数回インサート処理を実行する必要があります。

INSERT INTO table_name (column1) VALUES (value1);
INSERT INTO table_name (column1) VALUES (value2);
INSERT INTO table_name (column1) VALUES (value3);
・・・（略）​

一方、バルクインサートでは複数のデータをまとめて一回で登録します。

INSERT INTO table_name (column1) VALUES 
(value1),
(value2),
(value3),
・・・（略）);

実務での具体例

タイムアウトとなった記述

今回の事例では、全ユーザに対して処理を繰り返し行い、それぞれのユーザに関連付けられたエンティティを作成し、そのエンティティを保存する登録処理を実装しました。

ユーザ数が約7500人存在していたため、単純な繰り返し処理で、1人ずつデータをインサートしていく方法では、タイムアウトが発生してしまいました。

// Symfonyを使用したコード例

$users = $this->entityManager->getRepository(Users::class)->findBy(['isDeleted' => 0]); //全ユーザを取得

foreach ($users as $user) {
    $targetEntity = new TargetEntity();
    $targetEntity->setReferenceId($referenceEntity->getId()); // 参照データのIDを設定
    $targetEntity->setOwnerId($user->getId()); // 所有者（ユーザ）のIDを設定

    $this->entityManager->persist($targetEntity); // 永続化対象に設定
}

バルクインサートを用いた記述

次に、バルクインサートの実際のコード例を見てみましょう。
この例では、ユーザ情報を一括で挿入する処理を行っています。

// Symfonyを使用したバルクインサートの例

$users = $this->entityManager->getRepository(Users::class)->findBy(['isDeleted' => 0]); // 全ユーザを取得

$batchSize = 100;
$counter = 0;

// バルクインサート用のVALUES句を格納する配列
$values = [];

// TargetEntityエンティティをバルクインサート
foreach ($users as $user) {
    // VALUES句を作成
    $values[] = sprintf(
        "('%s', '%s')", // 参照データのIDと所有者（ユーザ）のIDを設定
        $referenceEntity->getId(), // 参照データのIDを設定
        $user->getId() // 所有者（ユーザ）のIDを設定
    );

    $counter++;

    // バッチサイズごとにインサート処理を実行
    if ($counter % $batchSize == 0) {
        $this->executeBulkInsertTargetEntity($values);
        // メモリの解放
        $values = [];
    }
}

// 残ったデータがあれば一括挿入
if (!empty($values)) {
    $this->executeBulkInsertTargetEntity($values);
}

private function executeBulkInsertTargetEntity(array $values): void
{
    // INSERT文を作成
    $sql = sprintf(
        "INSERT INTO target_entity (reference_id, owner_id) VALUES %s",
        implode(',', $values) // バッチ処理で作成したVALUES句を結合
    );

    // SQLを実行
    $this->entityManager->getConnection()->executeStatement($sql);
}

このコードは、Usersエンティティから全ユーザを取得し、そのユーザに紐づくデータを一括でTargetEntityテーブルに挿入する処理です。

1. データの取得: findBy()メソッドを使って、削除されていない全ユーザーを取得しています。
2. バルクインサート用の配列作成: 各ユーザごとに、挿入するデータを$values配列に格納します。このとき、sprintfを使ってSQL用の文字列を作成しています。
3. バッチ処理: 一度に挿入するデータの数を指定するために、$batchSizeを設定しています。指定した数ごとに、まとめてデータベースに挿入を実行します。これにより、メモリを節約しつつ効率的な挿入ができます。
4. SQL実行: バッチごとに、作成したVALUES句をSQL文として実行します。

メモリオーバーフローのリスクについて

バルクインサートを利用する際の注意点として、メモリオーバーフローのリスクがあります。
大量のデータを一度に挿入することで、メモリの使用量が急激に増加し、最終的にシステムのメモリ容量を超えてしまう可能性があります。
この問題を回避するためには、バッチサイズを適切に設定することが重要です。
例えば、前述のコードでは $batchSize を100に設定していますが、このサイズは実際のシステムのメモリ状況に応じて調整する必要があります。
大きすぎるバッチサイズを設定すると、メモリに過剰な負荷がかかり、最悪の場合、メモリオーバーフローやシステムのクラッシュを引き起こす可能性があります。

処理時間の比較結果

実際に、1件ずつインサートを行った場合に比べ、バルクインサートを使用した処理では、登録に掛かる時間が50秒から2秒へと劇的に短縮されました。

	ユーザ数	登録処理に掛かった時間（秒）
一人ずつインサート	7500	50
バルクインサート	7500	2

まとめ

今回の事例では、約7500人のユーザーに対してデータを登録する際に発生したタイムアウト問題を、バルクインサートを使用することで解決することができました。

大量データを扱うシステムでは、バルクインサートは非常に有効な手法です。
是非、データ登録処理の改善に役立ててください。