优化Power BI中的Power Query合并查询效率，Part 3：Table.Join和SortMerge

前两篇文章中：

优化Power BI中的Power Query合并查询效率，Part 1：通过删除列来实现

优化Power BI中的Power Query合并查询效率，Part 2：合并查询前or后删除多余的列有区别吗？

我们讲过了合并查询时，删除不必要的列可以极大地提升性能和效率。本篇文章，再介绍一个新的技巧，同样可以提升性能。

当对两个表进行合并查询时，系统默认使用的是Table.NestedJoin函数。其实查阅文档可以发现，M函数还提供了一个Table.Join函数来合并表。

这个函数比较有意思的一点在于，它提供了一个Table.NestedJoin并没有的参数：joinAlgorithm，允许用户在对表进行合并查询时可以设定具体的方式。文档本身并没有提供具体的参数值代表什么样的计算方式，不过，在编辑器中输入#shared可以得到结果：

可以看到，joinAlgorithm提供了包括左联结、右联结等在内的很多联结方式。

今天我们重点来说一下SortMerge，排序合并连接。在之前的文章中提到过，之所以合并查询大数据量的文件会比较慢是因为计算过程中会把表存入内存中。

然而，有一个例外：如果你提前知道了这两个表中被用来做合并查询的列是按照升序排列的，那么就可以使用Table.Join函数并设定SortMerge参数来实现，这样计算过程就是按照顺序从两个表中获取数据，像数据流一样，而不是先在内存中排序了再计算，自然就会更高效。

还是举个例子说明一下吧：

像之前一样，同一个SCV格式的文件，每个文件7列100万行，建立两个独立的查询，保留所有的列和行。两个查询，命名为查询1和查询2，同样不加载到数据集中，以下是查询1的M代码：

let
Source = Csv.Document(
File.Contents("C:\Users\chwebb\Documents\NumbersMoreColumns.csv"),[Delimiter = ",", Columns = 7, Encoding = 65001, QuoteStyle = QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{
{"A", Int64.Type},
{"B", Int64.Type},
{"C", Int64.Type},
{"D", Int64.Type},
{"E", Int64.Type},
{"F", Int64.Type},
{"G", Int64.Type}
}
),
#"Renamed Columns" = Table.RenameColumns(
#"Changed Type",
{{"A", "A1"}, {"B", "B1"}, {"C", "C1"}, {"D", "D1"}, {"E", "E1"}, {"F", "F1"}, {"G", "G1"}}
)
in
#"Renamed Columns"

查询2基本雷同。

以下是默认使用Table.NestedJoin进行合并查询的代码，之前的文章中提到过：

let
Source = Table.NestedJoin(First, {"A1"}, Second, {"A2"}, "Second", JoinKind.Inner),
#"Expanded Second" = Table.ExpandTableColumn(
Source,
"Second",
{"A2", "B2", "C2", "D2", "E2", "F2", "G2"},
{"Second.A2", "Second.B2", "Second.C2", "Second.D2", "Second.E2", "Second.F2", "Second.G2"}
)
in
#"Expanded Second"

合并查询的时间分析如下：

• Progress Report End/25 Execute SQL – 54 秒
• Progress Report End/17 Read Data – 58 秒

接下来，我们先将两张表按照A列升级排列，所以我可以直接使用Table.Join函数并设定SortMerge参数来实现了：

let
Source =
Table.Join(
First, {"A1"}, Second, {"A2"},JoinKind.Inner, JoinAlgorithm.SortMerge)
in
Source

这个版本的查询刷新的平均时间：

• Progress Report End/25 Execute SQL – 0.1 秒
• Progress Report End/17 Read Data – 21 秒

哇哦，很明显，时间要远远短于使用Table.NestedJoin进行合并查询，性能得到巨大提升。

但是需要我们注意的是：虽然单单看合并查询的时间，使用Table.Join要远远比Table.NestedJoin要少，但是别忘了，在使用Table.Join和SortMerge之前，我们先对两个表进行了排序工作，排序也是需要时间的，而且排序恰好就是把表放在内存中进行，你会发现对两张表排序的时间差不多也就是两种方法的时间差，所以性能上也没啥区别。

另外，使用Table.Join和SortMerge之前必须对两张表进行升序排列，否则一定会得到错误的结果，所以说还是得慎用。

不过，很多时候当你从某个系统中导出CSV或其他格式的数据时，一般也会有选项或者默认就是升序排列，也就是符合了使用Table.Join和SortMerge的默认条件。另外，当你准备从两个不同的数据库中导入数据并进行合并查询，比如SQL Server 和 Oracle，两者都支持从文件夹中获取数据并排序，这个过程中的排序的时间，很有可能会小于使用Table.Join和SortMerge时节省的时间，所以，这么看还是划算。

另外，经常查询一下M语言的文档，你会发现很多有意思的函数，尤其是相似的但却能实现不同功能的函数。比如我们之前介绍过的Folder.Files和Folder.Contents等。

参考文档：

https://docs.microsoft.com/zh-cn/powerquery-m/table-join

https://blog.crossjoin.co.uk/2020/06/07/optimising-the-performance-of-power-query-merges-in-power-bi-part-3-table-join-and-sortmerge/

https://radacad.com/power-query-library-of-functions-shared-keyword