在Power BI中优化Power Query合并的性能：删除列

Power Query中的合并（或用SQL术语来说是联接）表是刷新性能问题的常见原因。我们经常想知道是否可以采取任何措施来优化不可折叠数据源的合并性能，因此我们运行一些测试以尝试回答所遇到的问题。在这一系列文章中，将告诉你一些发现。

对于这些测试，使用的唯一数据源是具有一百万行和七个数字列（分别为A，B C，D，E，F和G）的CSV文件：

使用SQL Server Profiler来衡量执行查询所花费的时间。实际上有两个Profiler事件，它们在评估刷新性能时的持续时间很重要：

• 进度报告结束/ 25执行SQL

• 进度报告结束/ 17读取数据

事实证明，这两个事件提供了一些有关Power Query引擎中如何处理某些转换的见解，但我们稍后会介绍。

第一个问题是：表中的列数是否会影响合并的性能？

首先，创建两个相同的查询，分别称为First和Second，它们连接到CSV文件，使用文件中的第一行作为标题，并将所有七个列的数据类型设置为Whole Number。没什么特别的，这里的M代码都一样：

let

Source = Csv.Document(

File.Contents("C:\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}

}

)

in

#"Changed Type"

我们禁用这些查询，好方便我们不将其加载到数据集中。

接下来，创建第三个查询，该查询使用Table.NestedJoin函数通过内部联接合并来自这两个查询的数据，并返回来自两个源查询的所有列：

let

Source = Table.NestedJoin(

First, {"A"}, Second, {"A"},

"Second", JoinKind.Inner),

#"Expanded Second" = Table.ExpandTableColumn(

Source,

"Second",

{"A", "B", "C", "D", "E", "F", "G"},

{"Second.A", "Second.B", "Second.C",

"Second.D", "Second.E", "Second.F", "Second.G"}

)

in

#"Expanded Second"

当刷新此查询时，在事件探查器中，上面提到的两个事件的持续时间如下：

• 进度报告结束/ 25执行SQL – 40秒

• 进度报告结束/ 17读取数据– 56秒

很慢，但是，当你将两个表合并为一个列而不是七个列时，性能如何？

为了测试这一点，在“第一”和“第二”查询中来添加一个额外的步骤，删除了除A列（合并所需的列）之外的所有列，如下所示：

let

Source = Csv.Document(

File.Contents("C:\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}

}

),

#"Removed Other Columns" =

Table.SelectColumns(#"Changed Type", {"A"})

in

#"Removed Other Columns"

然后，更新包含合并的第三个查询以反映此更改：

let

Source = Table.NestedJoin(

First, {"A"}, Second, {"A"},

"Second", JoinKind.Inner),

#"Expanded Second" =

Table.ExpandTableColumn(Source, "Second", {"A"}, {"Second.A"})

in

#"Expanded Second"

刷新此查询后，Profiler显示以下持续时间：

• 进度报告结束/ 25执行SQL – 9秒

• 进度报告结束/ 17读取数据– 1秒

这个查询要快得多，但是后来我们想到：如果性能更多地取决于查询返回的表的大小而不是合并的结果呢？因此，我们在合并查询的末尾添加了一个额外的步骤，如下所示：

let

Source = Table.NestedJoin(

First, {"A"}, Second, {"A"},

"Second", JoinKind.Inner),

#"Expanded Second" =

Table.ExpandTableColumn(Source, "Second", {"A"}, {"Second.A"}),

#"Counted Rows" = Table.RowCount(#"Expanded Second")

in

#"Counted Rows"

然后重新执行上述两个测试。我们的想法是，现在合并查询仅返回单个值，查询返回的数据量不应成为查询持续时间的因素。

以下是在表中有七列的版本进行合并的时间：

• 进度报告结束/ 25执行SQL – 56秒

• 进度报告结束/ 17读取数据– 0秒

以下是仅在一列上进行表合并的版本的时间：

• 进度报告结束/ 25执行SQL – 14秒

• 进度报告结束/ 17读取数据– 0秒

这似乎确实可以确认表中的列数会影响合并的性能，尽管当然，对具有更多列的表中的行进行计数可能需要更长的时间。

这也显示了其他情况：与前两个测试相比，读取数据在两种情况下都是即时的，而前两个测试花费的时间比Execute SQL事件要长。

为什么列数会影响合并的性能？合并必须在内存中进行，因此，可以猜想合并中涉及的表越大，则需要的内存就越多，如果发生以下情况，则分页就越多，超过了256MB的限制。查看针对最后两个查询生成的性能计数器数据，结果发现上述两个最后两个查询的确超出了256MB的限制，但是，将具有两列的表连接到该版本的最大提交为584MB，该版本的最大提交用七个列加入表的空间几乎为3GB。

总而言之，至少可以这样说：

在Power Query中合并两个表之前，删除所有不需要的列将提高刷新性能。