DWH系の大量データの計算(集計してから更新すると約360倍速い)

データベース技術

22年前に書いた記事です。製品やサービスの情報は公開当時のものです。

目次(3項目)
  1. 課題
  2. 比べた3つの方法
  3. 結果

DWH系の大量データの計算(集計してから更新すると約360倍速い)のイメージ(挿絵)

DWH系の大量データを集計してマスタへ反映するとき、SQLの書き方でどれだけ速度が変わるのかというお話です。

課題

次の2つのテーブルがあります。

テーブル 件数 項目
会員マスタ 5万件 会員ID(PK), 会員名, 平均購入金額, ...
POSデータ 500万件 POSID(PK), 会員ID(FK), 取引日時, 購入金額, ...

このテーブルにおいて、POSデータの購入金額から平均購入金額を求めて、会員マスタの平均購入金額へ値を反映する場合、適切なINDEXが構成済みの環境下で、以下のどれが高速か?

比べた3つの方法

1) UPDATE句+副問い合わせで一発更新

SQL1本で済ませる方法です。会員マスタの行ごとに、副問い合わせでPOSデータからその会員の平均を求めます。

UPDATE 会員マスタ SET 平均購入金額 = (
 SELECT AVG(購入金額) FROM POSデータ
 WHERE POSデータ.会員ID = 会員マスタ.会員ID
);

2) 会員マスタ一覧抽出→1人ずつ平均を求めて更新

カーソルで会員マスタから会員IDを1件ずつ取り出し、ループの中で副問い合わせを使ってその会員の平均を求めます。

DECLARE
 CURSOR cur IS
  SELECT 会員ID FROM 会員マスタ;
BEGIN
 FOR rec IN cur LOOP
  UPDATE 会員マスタ SET 平均購入金額 = (
   SELECT AVG(購入金額) FROM POSデータ
   WHERE POSデータ.会員ID = rec.会員ID
  )
 END LOOP ;
END;
/

3) GROUP BY句で会員毎の平均計算→会員マスタ更新

先にPOSデータをGROUP BYで集計して、会員毎の平均をまとめて計算しておきます。その結果をカーソルで1件ずつ取り出し、会員IDを指定して会員マスタを更新します。

DECLARE
 CURSOR cur IS
  SELECT 会員ID, AVG(購入金額) AS 平均 FROM POSデータ GROUP BY 会員ID;
BEGIN
 FOR rec IN cur LOOP
  UPDATE 会員マスタ SET 平均購入金額 = rec.平均
  WHERE 会員マスタ.会員ID = rec.会員ID;
 END LOOP ;
END;
/

結果

結果は実に約360倍もの差が出た。

方法 かかった時間
1) と 2) 30時間程度
3) 5分未満
  1. と 2) は、会員1人ごとにPOSデータから平均を求める副問い合わせが走ります。3) は、最初に会員毎の平均をまとめて計算し、あとはその値で会員マスタを更新するだけです。

集計関数を実施する際は、一気に集計値を計算してから処理する方が劇的に高速な事がわかります。

最終更新