タグ別アーカイブ: パフォーマンス

クエリの高速化・関数

キー設定

Table.AddKey

文法

マージを行う際に、主キーを設定してクエリ処理を高速化します

*明細側(多)より集計側(1)で高速化の効果が高いです

Table.AddKey(テーブル名, 主キーにする列, 主キー有無)

使用方法

let
With.Addkey1=Table.AddKey(売上台帳,{“商品コード”},true),
With.Addkey2=Table.AddKey(商品台帳,{“商品コード”},true),
ソース = 売上台帳,
マージされたクエリ数 = Table.NestedJoin(With.Addkey1, {“商品コード”}, With.Addkey2, {“商品コード”}, “商品台帳”, JoinKind.LeftOuter),
#”展開された 商品台帳” = Table.ExpandTableColumn(マージされたクエリ数, “商品台帳”, {“販売単価”}, {“商品台帳.販売単価”}),
挿入された乗算 = Table.AddColumn(#”展開された 商品台帳”, “乗算”, each [商品台帳.販売単価] * [販売個数], Int64.Type),
#”名前が変更された列 ” = Table.RenameColumns(挿入された乗算,{{“乗算”, “販売金額”}})
in
#”名前が変更された列 “

バッファー化

マージ対象のクエリをバッファー化します

文法

=Table.Buffer(最終ステップ名)


にほんブログ村

参照先バッファー化によるパワーマンス向上~M関数~

 パワークエリで大量のデータを処理していると、悩みとして出てくるのがクエリの動きが「重たい」という問題です

 対処方法として様々な方法がありますが、今回紹介する「バッファー化処理」は特に有効なので、ぜひ試してみてください

バッファー化処理

 よく代表例として紹介されるのはプリンターの処理ですが(出典先)、ここではバイキングで料理を食べるシーンを例にとって考えてみましょう

 バイキングに行くと上の画像のように、料理がまとめて「」の上にのっています

 もし、皿を使っていなかったら、厨房に都度行って料理人の方に欲しい料理を作ってもらうしかありません

このバイキングのがバッファー化です

 もし、クエリのステップ内にマージや追加により参照しているクエリ/テーブルがあったら、上の皿のようにバッファー化を行っておきましょう!

 そうすることで、クエリに関する処理のパフォーマンスは向上します!

解説に使用するクエリ

上の画像は今回解説に使用するクエリの依存関係を示した図です

 図の下の方に「売上結果_NO」と「売上結果_Buffering」という2つのクエリがあります

 この2つのクエリの違いは、マージして参照しているクエリが違っているだけです

売上結果_NO ➡ 商品台帳/バッファー化なし

売上結果_Buffering ➡ 商品台帳_Buffering/バッファー化あり

参照しているクエリの違いは、バッファー化がある・なしの違いだけです

 商品台帳クエリ内にある12行のレコードを、皿に盛りつけておき、取り易いようにしておくイメージです

 次のGIF画像では、バッファー化の有無の違いが「実際の時間」で分かるようになっています

 同じ1万行のデータを読込んでいますが、待ち時間の違いが明らかだと思います

 では、バッファー化の効果を解説したので、具体的なバッファー化処理・手順の解説に移ります

バッファー化処理

バッファー化には、「Table.Buffer」というM関数を使用します

書き方は次のようになります

=Table.Buffer(テーブル名)

 次の画像は売上結果_Bufferingクエリが参照している「商品台帳_Buffering」のエディタの画像です

 数式バーの中に注目して頂くと、M関数/Table.Bufferの()の中に前ステップ名が入っています

 ここでは、ステップの繰り上げ処理を行って参照ステップを作成し、M関数/Table.Buffer内で参照しています

<まとめ>

いかがでしたでしょうか?

バッファー化と聞くと難しそうですが、結構、簡単な処理だったと思います

要は、料理をまとめて皿の上に盛り付けておくイメージです

 ぜひ、「バッファー化の有無」の違いを実際に手を動かして体感してみてください

 もし、バッファー化・有無の違いを体感するためにデータを倍増させたい場合には専用のM関数のTable.Repeatがあります

次の画像は、前述の「売上結果_Buffering」クエリのソースステップの内容です

 第一引数の「売上台帳」はシート上のテーブル名であり、第二引数の「3000」は売上台帳テーブルを3000回繰り返してテーブルを作成し直した、という意味です

 なお、クエリを高速化するのに必要なのは、今回紹介した技術だけではありません

 もし、フィルタ作業をした後に、データの削除を行っている場合であれば、データの削除をフィルタ作業の前に行いましょう!

作業の対象データを縮小しておいた方が、クエリの処理は高速化します

このような、小さな工夫も大事なことを最後に付け加えておきます

最後まで記事を読んで下さり、誠にありがとうございます

追記:2021年6月20日

 バッファー化をしていった結果「メモリが不足している」というエラーメッセージがでる場合があります

その場合には、下の画像の「バックグラウンドで更新する」のチェックを外してみてください!

 このチェックボタンは、ワークシートで該当のクエリ上で右クリックし、次に「プロパティ」をクリックすると表示することができます

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村