Personal Insights
パフォーマンスのためのHANA View モデリングガイドライン
はじめに
SAP HANAはインメモリ/カラムストアを活かした高速なデータベースです。処理の負荷が高い複雑なクエリになるとだんだんレスポンス時間は伸びていってしまいます。本ブログでは、SAP HANAの特性を理解した上で、より性能を引き出すためにHANA Viewをモデリングする際に気をつけるべきポイントについて、以下のモデリングガイドラインに従って紹介します。
※本記事の中で紹介している内容は、HANA Cloudおよびオンプレ版のどちらにも対応する内容です。いくつかHANA Cloudのヘルプドキュメントについてリンクを貼っていますが、同様の内容がオンプレ版のヘルプでも参照いただけるかと思います。
パフォーマンスのためのモデリングガイドライン
1. Query only columns that are necessary (avoid SELECT *).
SELECT *のように「とりあえず全てのデータを取得しておく、、、」といったことは避け、必要なカラムのみを取得するようにしましょう。
SAP HANAで分析用途に使用するデータは主にカラムストアで格納されているデータです。ローストアと違い、カラムストアは各カラム毎にデータが格納されています。カラムストアでは、クエリで要求されたカラムを読む際には不要なカラムを読まなくて良いというメリットがある反面、同じレコード内のカラム間の結合が必要となります。そのため、必要以上なカラムをクエリで読み取ろうとするとカラム間の結合がオーバヘッドとなります。
また、HANA Viewでは主に分析用として使われるため、データが集約されて結果を返します。必要以上のカラムを取得しようとすると、集約のキーで使用する項目(GROUP BY句で指定する項目)が多くなり、計算量も多くなり、また、結果セットも大きくなってしまいます。
後からプログラムを改修しやすいようにとりあえず全部のデータを取得して、アプリケーション側で必要なデータに加工するのではなく、必要なデータのみを取得するようにしましょう。
2. Avoid the transfer of large result sets between the SAP HANA DB and client / frontend tools (e.g. by HAVING, TOP, LIMIT, …), this also takes time, and too many data records can be anyway not analyzed properly.
HAVING、TOP、LIMITおよびWHERE句などを使用して、クライアント/フロントエンドツールとSAP HANA DB間の大きなデータセットの転送は避けましょう。
いくらSAP HANAで高速にデータを処理しても、クライアントへのデータの転送(フェッチ)に要する時間は、ネットワークや取得するクライアントに大きく依存してしまうため、SAP HANAの性能を活かすことは難しくなります。
また、大量のデータをクライアントで表示させてもデータの扱い方が難しくなるだけで、正しく分析することはとても困難です。
大量データを取得するのはデータ連携などの必要なシナリオのみにとして、一般的な分析シナリオの中では、必要なデータのみを取得するようにしましょう。
3. Avoid calculations before aggregation. Avoid/Minimize complex expensive calculations. Transform expensive calculations in advance using ELT/SLT, DataServices or generated columns.
集約前の計算処理・複雑な計算処理はできる限り避け、最小化しましょう。そのような処理が必要な場合には、あらかじめ計算結果をテーブルのカラムに格納しておくことを検討してください
当然ですが、必要な計算量を少なくすることができれば、パフォーマンスは向上します。
HANA Viewは主に分析用途で使用されますので、集約(SUM, AVGなど)が基本的に行われます。1000万行にテーブルに対して、集約前にカラムの計算処理を実行すると、レコード毎に1000万回計算が必要になります。しかし、集約後に10行のレコードになるとしたら、必要な計算回数は10回ですみます（集約キーによりますが）
HANAではできる限り集約後に計算処理をしようと実行計画がたてられます。しかし、結合やフィルタの条件に使われるなど後続の処理で事前に計算する必要がある場合には、集約前で処理せざるおえません。また、HANA viewの計算項目(Calculated Column)を定義する際に「Calculation before aggregation」と言うオプションをつけることにより集約前の計算処理を明示的に宣言できますが、これは集約後に計算すると結果が変わってしまう場の必要な場合にのみ使用してください。
集約前の計算処理が必要な場合には、あらかじめ物理テーブルに計算結果をカラムに追加しておくのも1つの手段です。ETLツールで計算してレコードを挿入するのも良いですし、generated column(*)を使うこともできます。
パフォーマンスチューニングのためにテーブルの定義を変更することに抵抗がある方もいるかもしれませんが、そこはパフォーマンスが出ずに使ってもらえないよりマシとわりきって、柔軟に考えていただけると。
(*) generated column:
カラムの定義として、generated always as <計算処理>が使用できます。テーブルにレコードが挿入・更新された際にgenerated columnの計算結果の値がカラムとして追加されます。これにより、SQLやHANA Viewで計算処理を行う必要がなくなります。
結合条件やフィルタ条件、集約キーなどで使用するカラムなどはgenerated columnで定義しておくと良いです。
構文はSQL reference – Create tablelの<col_gen_as_expression>を参照してください
4. Do aggregate data records (e.g using GROUP BY, Aggregation/Projection nodes) by using fewer Columns as early as possible.
出来る限り早い段階で少ないカラムで集約して中間結果を少なくするようにしましょう。
HANA Viewの定義の下のレイヤーで集約し、中間結果を少なく出来ると後続の処理対象のデータ量が少なくなるので性能が出やすくなります。
例えば、主キーに使われているような選択性の高い項目が使われていると、いつまでたっても中間結果は小さくならず応答時間が長くなったり、メモリを多く使用してしまう結果となります。
HANA Viewの定義の中では可能なタイミングでAggregartionノードを使用して早めにデータが集約されるように定義しましょう。
5. Consider using different Join types like Left Outer or Referential Join instead of Inner Join or vice versa.
要件上、Inner joinが必須でないならば、Left Outer joinを使用しましょう。
Left Outer Joinの場合、テーブル間のカーディナリティがN:1 or 1:1の時パフォーマンスが早いケースが多いです。また、Left Outer Joinの場合、HANA ViewにてカーディナリティをN:1 or 1:1で設定し、結合したRightテーブルのカラムがクエリで使用されていないケースでは、HANAはJoinをしなくても良いと判断してHANA Viewの実行を省略してくれます。スタースキーマ型の場合には効果を発揮します。ただし、カーディナリティが間違っていると結果が異なってしまうのでご注意ください。
6. Avoid joining big tables/Views, even in Data Foundation. For large datasets, ensure filtering or test UNION instead.
データセットの多いテーブル・ビュー同士のJoinは避け、フィルタしてJoinするかUnionが変わり使えないか検討しましょう。
トランザクションデータが入っている大量レコード同士のテーブル同士の結合は、パフォーマンスボトルネックになるケースが多いです。
まずはフィルタやWHERE句により対象データが絞り込まれるようにしてください。
もし、テーブル間のカーディナリティが1:1の場合はUNIONと集約の組み合わせで同様の結果が得られるケースがあります。このような場合には、UNIONでパフォーマンスが改善するか試してみてください。
UNIONで置き換えた場合のイメージ
7. Avoid joins/Filter on Calculated Columns
計算項目を結合条件にしたりフィルタの条件にするのは避けましょう
3.でも触れましたが、結合するにあたって計算を実行する必要があります。結合条件やフィルタの条件に結合項目を使用すると、データの絞り込みが適切にプッシュダウンされず、中間結果が大きくなる可能性があります。
generated columnを使ったり、他のカラムを利用できないか検討してみてください。
とはいえ、それでも避けられないケースも多いかと思います。
計算項目を結合条件ではなくフィルタで実装する例
また、日付のカラム(e.g. 2020/11/11)から月(11)や年(12)でフィルタリングする際には、substringなどの計算項目を使用するのではなく、Time Dimesionテーブルを活用してください。カレンダーマスターとして使用することができ、必要な年・月・四半期・上半期/下半期・会計期間などが取得できます。
Time Dimensionの設定
8. Filter data amount as early as possible in the lower layers (design filters, WHERE Clause, Input Parameters, Prompts, Analytic Privileges remove outdated data, inconsistent data, unnecessary columns, …)
処理対象のデータは出来る限り早い段階で減らしましょう
パフォーマンスをあげるには処理データ量を減らすことが一番です。フィルタやWHERE句、分析権限を使って、出来る限り物理テーブルからデータを取得する段階で対象レコードを減らせるようにしましょう。
フィルタの場所のイメージ
7.で書いたように計算項目をフィルタに使用すると絞り込みがプッシュダウンできないケースがあります。そのような場合には、input parameterを使って明示的に下のノードやHANA Viewに対してフィルタをかけることも1つの手段です
Input Parameterの使用例
まとめ
色々とポイントを書かせていただきました。その中には、HANAならではという点もあるのですが、他のRDBで重いようなSQLクエリやHANA Viewの実装をするとHANAでもそれなりに早いものの、HANA本来の性能を引き出すことができないケースが多くなります。
今ある要件や設計に囚われずに同じ処理結果を得るには、他にどのような実装方法があるか検討いただき、モデリングを実施いただければと思います。
また、HANAのhelpドキュメントのPerformance Guide for Developer(HANA Cloud / オンプレ)にもこのブログで書いていない様々な対応について記載がありますので、ご一読いただければと思います。