【iDempiere Lab】ピボットウィンドウの大量データ検証

ピボットウィンドウで大量データを取り扱うための指標となるように、性能検証してみました。

【検証結果まとめ】

 データ件数が多くなるにつれデータを表示するまでの時間は長くなりますが、いったん表示されれば、その後の分析軸の追加変更は100万件レベルくらいまで検証しましたが、とても軽快に行う事ができました。

 データ表示までの時間は、データ件数にもよりますが、適正なインデックスがはられているかどうかや、関数(Function)の使用の有無、ピボットウィンドウのフィールド数、SQLの書き方などに大きく依存します。これらを考慮して適切な設定および準備を行っておけば、100万件を超えるような大量なデータに対しても、ピボットウィンドウは活用できると思います。

 

検証環境

検証した環境は次の通りです。1台のノートPCにDBとアプリケーションサーバーが同居している環境です。

Windows10 - 64bit ノートPC

  • Intel Core i7 - 5500U CPU @2.40GHz
  • メモリ16GB
  • SSD(※ただしボリュームテストのDBはUSB3接続の外付けSSD)
  • PostgreSQL9.4

 

取引先マスタ 約1,000件

品目マスタ 約1万件

検証1:受注伝票明細

検証用として、下記のイメージの受注伝票明細のピボットウィンドウを作成しました。

ピボットウィンドウ
ピボットウィンドウ

メインのSQLは下記のようにシンプルなもので、関数なども使用していない比較的単純なSQLのピボットウィンドウです

ピボットウィンドウ設定
ピボットウィンドウ設定

受注伝票明細:10万件   表示時間:約23秒~25秒

注文日付をFrom~Toで指定して、ピボットウィンドウ集計対象レコード件数が約10万件になるようにして、ピボットウィンドを表示させた結果、複数回実施して概ね23秒~25秒で表示されました一度、表示させてしまえば、分析項目の変更はとても軽快に行えます

※受注伝票明細の抽出条件に指定した注文日付には、インデックスをはっています。

ピボットウィンドウ10万件
ピボットウィンドウ10万件

受注伝票明細:20万件   表示時間:約43秒~47秒

注文日付をFrom~Toで指定して、ピボットウィンドウ集計対象レコード件数が約20万件になるようにして、ピボットウィンドを表示させた結果、複数回実施して概ね43秒~47秒で表示されました一度、表示させてしまえば、分析項目の変更はとても軽快に行えます

※受注伝票明細の抽出条件に指定した注文日付には、インデックスをはっています。

ピボットウィンドウ20万件
ピボットウィンドウ20万件

受注伝票明細:30万件   表示時間:1分8秒~10秒

上記と同じ条件で、集計対象レコード件数が30万件で実施しました。データ表示後の分析軸の追加変更は軽快に行えます。

受注伝票明細:40万件   表示時間:1分28秒~1分30秒

上記と同じ条件で、集計対象レコード件数が40万件で実施しました。データ表示後の分析軸の追加変更は軽快に行えます。

受注伝票明細:50万件   表示時間:約1分53秒

上記と同じ条件で、集計対象レコード件数が50万件で実施しました。データ表示後の分析軸の追加変更は軽快に行えます。

受注伝票明細:60万件   表示時間:約2分8秒

上記と同じ条件で、集計対象レコード件数が60万件で実施しました。データ表示後の分析軸の追加変更は軽快に行えます。

受注伝票明細:70万件   表示時間:約2分33秒

上記と同じ条件で、集計対象レコード件数が70万件で実施しました。データ表示後の分析軸の追加変更は軽快に行えます。

受注伝票明細:80万件   表示時間:約2分58秒

上記と同じ条件で、集計対象レコード件数が80万件で実施しました。データ表示後の分析軸の追加変更は軽快に行えます。

受注伝票明細:90万件   表示時間:約3分16秒

上記と同じ条件で、集計対象レコード件数が90万件で実施しました。データ表示後の分析軸の追加変更は軽快に行えます。

受注伝票明細:100万件   表示時間:約3分45秒

上記と同じ条件で、集計対象レコード件数が100万件で実施しました。データ表示後の分析軸の追加変更は軽快に行えます。

受注伝票明細:110万件   表示時間:約4分9秒

上記と同じ条件で、集計対象レコード件数が110万件で実施しました。データ表示後の分析軸の追加変更は軽快に行えます。

受注伝票明細:120万件   表示時間:約4分24秒

上記と同じ条件で、集計対象レコード件数が120万件で実施しました。データ表示後の分析軸の追加変更は軽快に行えます。

受注伝票明細:132万件   表示時間:約4分39秒

上記と同じ条件で、集計対象レコード件数が1,321,416件で実施しました。データ表示後の分析軸の追加変更は軽快に行えます。

ピボットウィンドウ132万件
ピボットウィンドウ132万件

検証2:引当チェックピボット(組織倉庫引当)

JPIERE-0360:引当チェックピボット(組織倉庫引当)を使用して、SQLの違によるピボットテーブルが表示されるまでの時間の長さを検証してみました。

引当チェックリストピボット(組織倉庫引当)
引当チェックリストピボット(組織倉庫引当)

検証2-1:分析軸にサロゲートキーのカラムを多数使用している場合

分析軸に"テーブル名+_ID"のサロゲートキーをカラムを多数使用している場合です。ピボットウィンドウのエンジンではサロゲートキーの値を、識別子の情報に置き換えますので、データが多くなれば多くなるほど、その処理に時間がかかります。

サロゲートキーを分析軸にする場合、SQLはシンプルで済み多言語化対応が簡単にできますがが、その分データが表示されるまでに時間がかかります。
サロゲートキーを分析軸にする場合、SQLはシンプルで済み多言語化対応が簡単にできますがが、その分データが表示されるまでに時間がかかります。

◆データ集計対象:10万件   表示時間:約43秒

集計対象レコード件数が約10万件になるように、納品予定日を使用して抽出しました。データ表示後の分析軸の追加変更は軽快に行えます。

◆データ集計対象:20万件   表示時間:約82秒

集計対象レコード件数が約20万件になるように、納品予定日を使用して抽出しました。データ表示後の分析軸の追加変更は軽快に行えます。

◆データ集計対象:30万件   表示時間:約120秒

集計対象レコード件数が約30万件になるように、納品予定日を使用して抽出しました。データ表示後の分析軸の追加変更は軽快に行えます。

 

検証2-2:分析軸にサロゲートキーのカラムを使用しない

 サロゲートキーのカラムは、識別子の情報に置き換える処理が余計に必要となるため、分析軸となる情報はあらかじめSQLでStringとして取得するようにします。

サロゲートキーを識別子の情報に置き換える処理が必要ないように、分析軸となる情報はあらかじめSQLでStringとして取得する
サロゲートキーを識別子の情報に置き換える処理が必要ないように、分析軸となる情報はあらかじめSQLでStringとして取得する

◆データ集計対象:10万件   表示時間:約21秒

集計対象レコード件数が約10万件になるように、納品予定日を使用して抽出しました。データ表示後の分析軸の追加変更は軽快に行えます。

◆データ集計対象:20万件   表示時間:約37秒

集計対象レコード件数が約20万件になるように、納品予定日を使用して抽出しました。データ表示後の分析軸の追加変更は軽快に行えます。

◆データ集計対象:30万件   表示時間:約54秒

集計対象レコード件数が約20万件になるように、納品予定日を使用して抽出しました。データ表示後の分析軸の追加変更は軽快に行えます。

ピボットテーブルを早く表示させるためのポイント

 サロゲートキーのカラムは、サロゲートキーを識別子の情報で置き換える処理が必要になるため、その分データ量が多くなればなるほど処理時間が余計にかかります。

 ビューやJOIN句を活用して、サロゲートキーを分析軸に使用せずにすめば、その分処理は早くなり大量データを取り扱うには有利です。上記の検証のケースでは、分析軸にサロゲートキーの無い場合のデータが表示されるまでの時間は、サロゲートキーがある場合の半分以下で済んでいます。

 しかしサロゲートキーは分析軸を多言語対応できるので、多言語対応が必要な場合は便利です。

 一長一短ありますので、上手に使い分けて下さい。

ピボットウィンドウはJPiereサポーターは無料で使用できます。

 ピボットウィンドウは、iDempiereのWeb-UIの開発フレームワークであるZKの商用ライセンスのコンポーネントを使用して開発しており、JPiereサポーターになる事で、無料で使用する事ができます。

JPiereでは、大量ボリュームを意識した各種設定を予め施しています。詳しくは"JPiereのパフォーマンス改善への取り組み"を参照して下さい。