中讲解了在Power BI中对两个表进行合并查询,数据集大小影响了效率。尤其是在进行合并查询之前删除了不需要的列,可以较大地提升合并查询的效率。但是我们不禁要问:
合并查询之前还是之后删除多余的列,对查询的效率有影响吗?
上一篇文章中提到过,测试中遇到了一个问题,哪怕我将7列数据删掉6列只剩下1列,去合并查询这两个百万行的表,也会超过256MB的内存大小限制,从而使用了页面文件。这就意味着每次刷新时从SQL Server 事件探查器查看时间,会有1-2秒的差异。当你去比较两个合并查询,一个用时50秒,一个用时10秒左右,1-2秒的误差对你判断这两个时间的长短基本造不成影响。然而,当两个查询本身的时间差不多的时候,1-2秒的差异就会引起比较大的麻烦。
为了解决这个问题,我将两个用来合并查询的表的行数删减为30万行,这样,刷新数据时占用的内存就用不了256MB,也就无需使用页面文件,每次相同查询的误差就会降到最低。
我将数据缩减为30万行,并且删减到1列去进行合并查询时,多次测试求了个平均值,所需的时间:
接下来,我改变了一下查询步骤,首先将所有的7列数据都进行合并查询,然后再将除了A列以外的列删除,M代码如下:
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"}
),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Second", {"A", "Second.A"}),
#"Counted Rows" = Table.RowCount(#"Removed Other Columns")
in
#"Counted Rows"
多次刷新,求平均值,发现,时间和上面的相同:
最后,我又测试了另外一种情况:第一个查询,将除了A列以外的列删除,第二个查询全部保留不删除列,然后合并查询,再展开只保留A列。
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"
结果如下:
这次查询时间稍微慢一点,但是200毫秒的差异完全可以忽略不计。
因此,我们可以得出结论:
在合并查询后紧接着删除不必要的列,和在合并查询的上一步进行删除不必要的列,没有任何区别。
what?
why?为什么两个7列的表合并查询完再删除多余的列会表现得这么好?
不应该啊。
7列的表合并查询和1列的表合并查询,完全不可同日而语啊。
答案是:
因为我们是在合并查询之后马上进行了删除多余列的操作,所以Power Query编辑器并不会真的将多余的列也进行合并查询,然后再删除这些多余的列。它会“很聪明地”意识到既然这些列在合并查询之后将要被删除,也就是多余的列,那么干脆就别合并查询了,先删除再合并查询,从而节省算力。
就像在以下两篇文章中:
我们总结过的:
节省算力1:在局部标签切换中,提前知晓将要切换的部分,直接进行特定部分切换而不是对整个页面切换。
节省算力2:CALCULATE计算时,提前知晓同一字段的所有筛选器,先进行合并,再进行计算,避免对同一字段重复计算。
节省算力3:计划刷新时,提前知晓将要刷新的数据集与数据量,规划排队,最优调用服务器。
今天我们再加上一条:
节省算力4:合并查询时,提前知晓某些列将要被删除,那么它会将先删除这些列,再进行合并查询,避免无效计算。
关于以上这个Power Query的引擎计算原理在Ben Gribaudo的一系列文章中说的很清楚,可以参考文末的文档。
需要指出的是,有些时候会出现以上测试失灵的情况,或者并没有那么有效,所以还是建议对于多余的列,能早一个步骤删除就早一点删除。
参考文档:
https://bengribaudo.com/blog/2018/02/28/4391/power-query-m-primer-part5-paradigm
https://blog.crossjoin.co.uk/2020/06/03/optimising-the-performance-of-power-query-merges-in-power-bi-part-2/
本文分享自 PowerBI生命管理大师学谦 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!