クエリフォールディングがサポートされないデータソース もしくは クエリフォールディングが機能できない状態のクエリで、テーブルのマージ処理をすると遅いのだ。Power Query 自体はデータベースではないし、有限リソースであるメモリ上だけで処理するので遅くなるのは当たり前である。扱う行数が少なければ気にならないけれども、行や列はいつも少ないとは限らないから、なんで遅くなったのだと考えてしまうのかもしれないね。
いわゆる表引き
マージ処理後に展開する列だけに制限する
案外簡単に試せる Excel ワークシート上のテーブルからマージ結果を出力例を提示するので試してみてね。
- Sales テーブルには ProductID を含む行が 1,000,000 行。Excel でも扱える行数よね
- Products テーブルには ProductID をプライマリキーとして 100,000 行。ちょっと盛ったけど異常な行数ではない。
- Products テーブルには 属性値 29 列存在し、30列になる。列数は確認できるように盛った。
そこで、マージをして Products の属性値 Column2 を追加する。で、次の通りになるはず。
let
Source = Table.NestedJoin(
Sales, {"ProductID"},
Products,
{"ProductID"},
"Products",
JoinKind.LeftOuter
),
ExpandedProductsColumn2 = Table.ExpandTableColumn(
Source,
"Products",
{"Column2"}
)
in
ExpandedProductsColumn2
私の環境では、おおむね 13 から 14 sec ほど。
そこで、マージに利用するキー列とマージ後展開する属性値の列のみで処理を試みる。
let
Source = Table.NestedJoin(
Sales, {"ProductID"},
Products[[ProductID], [Column2]], // 👈ココ
{"ProductID"},
"Products",
JoinKind.LeftOuter
),
ExpandedProductsColumn2 = Table.ExpandTableColumn(
Source,
"Products",
{"Column2"}
)
in
ExpandedProductsColumn2
おおむね 7 から 8 sec になる。
なぜ速く処理されるかは、プロセスのメモリ利用状況を確認するとよい。マージ処理はすべてメモリ上で行われていて、このとき明示的に指定しない限りすべての列を読み込んでいるのでしょう。結果として、メモリが必要以上に消費され、特に Excel の場合はプロセス当たりのキャパシティがさほど大きくないから、遅い動作になると。
Sales テーブルにも属性値の列盛ってみた。当然だけどマージ先の列も制限するとよいかもだけど、
let
Source = Table.NestedJoin(
Sales[[ProductID], [Column3]], // 👈ココも
{"ProductID"},
Products[[ProductID], [Column2]], // 👈ココ
{"ProductID"},
"Products",
JoinKind.LeftOuter
),
ExpandedProductsColumn2 = Table.ExpandTableColumn(
Source,
"Products",
{"Column2"}
)
in
ExpandedProductsColumn2
これでもよいかもね。
let
Source = Table.NestedJoin(
Sales, {"ProductID"},
Products[[ProductID], [Column2]], // 👈ココ
{"ProductID"},
"Products",
JoinKind.LeftOuter
),
ExpandedProductsColumn2 = Table.ExpandTableColumn(
Source,
"Products",
{"Column2"}
),
// 👇 ココ
RemovedOtherColumns = Table.SelectColumns(
ExpandedProductsColumn2,
{"ProductID", "Column3", "Column2"}
)
in
RemovedOtherColumns
思ったこと🙄
必要以上にマージしたがるのはなぜなんだぜ？と思うのです。みんな大好き VLOOKUP のノリでしょうか？ 今でこそ Excel の VLOOKUP は速くなったとされているのだけど、それまでは工夫をしてたよね。じゃぁ、なんで Power Query でも工夫をしないんだい？
その他