大量データをデータベースに一括登録したいとき、BULK INSERT がよく利用されます。
今回は INSERT ではなく UPDATE の処理速度について検証してみました。
Ruby on Rails + PostgreSQL での検証です。
というわけではじめまして。インゲージには9月に入社したばかりの knsk765 です。 前職では C# と SQL Server での開発がほとんどでした。はじめての Rails + PostgreSQL に色々戸惑いながらも勉強し続ける日々です。
目次
前置き
BULK INSERTって?
INSERT文を1件ずつ実行するのではなく、1回のコマンドで複数件のデータを登録するしくみです。実現方法はデータベース(RDBMS)ごとに結構な違いがあります。
SQL Server だと BULK INSERT 構文でデータファイルを読み込んで一括登録します。
BULK INSERT Sales.Orders FROM 'C:\Hoge\Fuga\orders.dat';
BULK INSERT (Transact-SQL) - SQL Server | Microsoft Learn
PostgreSQL だとひとつの INSERT 文に複数の VALUE を指定する形式になります。
INSERT INTO users (id, name) VALUES (1, '松崎 しげろ'), (2, '竹内 刀'), (3, '空条 Q太郎')
じゃあ、BULK UPDATE もあるの?
あります。これもデータベースごとに実現方法が違います。PostgreSQL の場合について説明しますが、その前に UPSERT に触れておきます。
UPSERT
INSERT しようとしてキーが重複したら(すでにレコードがあったら)、UPDATE に切り替えるしくみを UPSERT と言います。
INSERT INTO users (id, name) VALUES (2, '竹肉 力') ON CONFLICT (id) DO UPDATE SET name=EXCLUDED.name
ON CONFLICT … DO UPDATE SET …
の構文についての詳細は今回は割愛します。
BULK UPSERT
この ON CONFLICT と前述の BULK INSERT を組み合わせることで BULK UPSERT が実現できます。
さっき BULK UPDATE はあると言いましたが、厳密には BULK UPSERT ですね。
INSERT INTO users (id, name) VALUES (1, '松崎 しげゑ'), (2, '竹肉 力'), (3, '空条 O次郎') ON CONFLICT (id) DO UPDATE SET name=EXCLUDED.name
PostgreSQL 14.0文書 SQLコマンド INSERT
本題
大量に高速 UPDATE したい
BULK INSERT が通常の INSERT を複数回繰り返すよりも速いというのは知っていましたが、一括更新したい場合はどうなのか?
大量データを UPSERT したいわけではなく、UPDATE したい。
でも、前述の BULK UPSERT って INSERT に引っ掛かってから UPDATE をするので、そんなにパフォーマンス出ないんじゃね?
件数分、すなおに複数回 UPDATE してもかわらないんじゃね?っていう疑問が生じたので検証してみました。
Ruby on Rails での実現方法
Rails 6 からは標準で BULK INSERT/UPSERT ができる*1のですが、今回はそれ以前から使われていた activerecord-import での検証です。
import (BULK INSERT)
User.import users
こんな感じで import メソッドにモデルの配列を渡すことで BULK INSERT できます。
配列の中身は Active Record モデルでもハッシュでもいけます。
import (BULK UPSERT)
import メソッドのオプション指定で、UPSERT も実現できます。
User.import users, on_duplicate_key_update: { conflict_target: [:id], columns: [:name] }
登録しようとして id が重複したら(conflict_target: [:id]
)、name を更新する(columns: [:name]
)ってことですね。
これで実際にはさっきの例のような SQL が発行されます。
INSERT INTO users (id, name) VALUES (1, '松崎 しげゑ'), (2, '竹肉 力'), (3, '空条 O次郎') ON CONFLICT (id) DO UPDATE SET name=EXCLUDED.name
で、下記のようにぐるぐる回して1件ずつ更新するのとどっちが速いの?ってことを検証します。
users.each do |user| user.update_column(:name, ユーザーごとの名前) end
検証コード
require 'benchmark' class BulkImportBenchmark def initialize(times) @times = times ActiveRecord::LogSubscriber.logger.level = Logger::ERROR end def compare_bulk_update ActiveRecord::Base.transaction do Benchmark.bm 23 do |r| users_as_hash = [] r.report "prepare models" do @times.times do users_as_hash << FactoryBot.build(:user).attributes end end r.report "bulk insert by hash" do User.import users_as_hash end updating_users = User.all.map { |u| u } r.report "update model for each" do updating_users.each do |u| u.memo08 = 'hogeee' u.save end end r.report "update column for each" do updating_users.each do |u| u.update_column(:memo08, 'fugaaa') end end r.report "bulk update by model" do User.import updating_users, validate: false, raise_error: true, timestamps: false, on_duplicate_key_update: { conflict_target: [:id], columns: [:memo08] } end updating_users_as_hash = updating_users.map { |u| u.attributes } r.report "bulk update by hash" do User.import updating_users_as_hash, validate: false, raise_error: true, timestamps: false, on_duplicate_key_update: { conflict_target: [:id], columns: [:memo08] } end end raise ActiveRecord::Rollback end end end
処理内容
prepare models
最初に FactoryBot でモデルを作成bulk insert by hash
import メソッドで BULK INSERTupdate model for each
1件ずつモデルの save メソッドで UPDATE する速度の検証update column for each
1件ずつモデルの update_column メソッドで UPDATE する速度の検証bulk update by model
import に Active Record モデル配列を渡して BULK UPDATE する速度の検証bulk update by hash
import に ハッシュ配列を渡して BULK UPDATE する速度の検証
検証結果
検証環境
MacBook Pro M1Pro 14インチ(8コアCPU、メモリ16GB)で検証しました。
Rails 7 + PostgreSQL 14.5 で環境構築しています。
更新レコード数
10,000件
以下、5回実行した平均値です。
user | system | total | real | |
---|---|---|---|---|
update model for each | 3.3676 | 0.2716 | 3.6392 | (6.6716) |
update column for each | 1.4030 | 0.2308 | 1.6338 | (4.3271) |
bulk update by model | 1.3385 | 0.0177 | 1.3562 | (4.1822) |
bulk update by hash | 0.8160 | 0.0095 | 0.8256 | (4.0461) |
bulk update by hash ... 最速
import に ハッシュ配列を渡して BULK UPDATE
update model for each ... ほぼ同等(数%↓)
1件ずつモデルの save メソッドで UPDATE
ソース一式
docker で実行できるソース一式をここに置きました。 今回の記事では触れていませんが、BULK INSERT と通常 INSERT の比較もあります。
まとめ
import での一括更新はそんなに速くない
UPSERT であるがゆえか、通常の UPDATE 繰り返しに比べてそんなに速くはなかったです。
もちろん更新のみではなく登録と更新が混在する場合は import が有用です。
わかったこと
- 1件ずつの通常 UPDATE の場合
- save よりも update_column メソッドの方がだいぶ速い
- 一括更新(import)の場合
- Active Record 配列よりもハッシュ配列の方が多少速い
今回は簡易な検証なのでもっと条件を突き詰めれば結果は変わってくるかもしれませんが、倍以上の差が出ることはなさそうです。
もっと改善策があるといった情報をお持ちの方はぜひ教えてください。
それではまた。
*1:insert_all, upsert_all というメソッドが用意されています(Ruby on Rails 6.0 リリースノート - Railsガイド)