研究《DAX查询计划 白皮书》中的案例(三)优化方案（番外）

已于 2024-05-19 21:28:56 修改
阅读量839 收藏 23
点赞数 16
分类专栏： # 研究《DAX查询计划 白皮书》中的案例 文章标签： powerbi
于 2024-05-13 23:55:27 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Randvalue/article/details/138824513
版权

‍上一篇给出的优化方案中使用了两个 FILTER 函数，如果将两个 FILTER 函数合并成一个，可以使计算性能进一步提升，但是白皮书中并没有记录该方案。为了查明原因，比较优劣，本篇先将白皮书的其他优化方案抛开，先研究一下这个番外优化方案的秘诀。

研究《DAX查询计划 白皮书》中的案例(一)初始方案
研究《DAX查询计划 白皮书》中的案例(二)优化方案1
研究《DAX查询计划 白皮书》中的案例(三)优化方案(番外)
研究《DAX查询计划 白皮书》中的案例(四)优化方案2
研究《DAX查询计划 白皮书》中的案例(四)优化方案3

优化方案（番外）

DAX 查询

相对上一篇的优化方案1，本篇只做了一点小小的改变，将两个 FILTER​ 的条件合并到一条 FILTER​ 里

// 优化方案（番外）
EVALUATE
ADDCOLUMNS (
    VALUES ( 'Calendar'[Date] ),
    "OpenOrders",
        CALCULATE (
            COUNTROWS ( Sales ),
            FILTER (
                sales,
                sales[OrderDate] < 'Calendar'[Date]
                    && sales[ShipDate] > 'Calendar'[Date]
            ) 
        )
)

运行效率

以连续5次冷启动的结果进行统计

  • 环境1

平均总耗时 56.8 毫秒，标准差 3.27 毫秒

其中 SE（存储引擎）平均耗时 4.80 毫秒，标准差 0.84 毫秒

SE/总耗时 = 8.451%

  • 环境2

平均总耗时 53.8 毫秒，标准差 3.56 毫秒

其中 SE（存储引擎）平均耗时 5.0 毫秒，标准差 1.0 毫秒

SE/总耗时 = %

把目前所有方案的运行效率依次做比较，记录如下，数字下方的括号是相对前一个方案的性能变化情况。

环境参数初始方案优化方案1优化方案（番外）
环境1平均总耗时（毫秒）4389.4290.4
(↑ 93.38%)
56.8
(↑ 80.44%)
SE耗时
（毫秒）		14.44.8
(↑ 66.67%)		4.8
(-)
SE占比0.328%1.653%
(↑ 403.83%)		8.45%
(↑ 411.27%)
环境2平均总耗时（毫秒）4017.0300.60
(↑ 92.52%)		53.8
(↑ 82.10%)
SE耗时
（毫秒）		17.25.8
(↑ 66.28%)		5.0
(↑ 13.79%)
SE占比0.428%1.929%
(↑ 350.62%)		9.29%
(↑ 381.67%)

优化方案（番外）的总耗时 56.8 毫秒，相对优化方案1的总耗时 290.4 毫秒，运行效率提升了 80.44%。

仅仅是将两个 FILTER​ 函数合并成了一个，就使得性能提升八成，这背后的原因是什么，下面进行分析。

存储引擎部分

存储引擎这次执行了 3 个 xmSQL 查询：

1、VQ1：将 Sales 表以 [OrderDate] 和 [ShipDate] 分组，返回 {[OrderDate], [ShipDate]} 值对，共 1081 个值对。后文以VQ1:[OrderDate], [ShipDate]​ 表示该结果

// xmSQL VQ1 
SELECT
    'Sales'[OrderDate],
    'Sales'[ShipDate]
FROM 'Sales';

2、VQ2：与上一篇优化方案1 中的 VQ2 完全一致，将 Calendar 表中以 [Date] 分组，返回 Calendar[Date] 列的不重复值，共 1081 行数据。后文以 VQ2:Calendar[Date]​表示该结果

// VQ2
SELECT
    'Calendar'[Date]
FROM 'Calendar';

3、VQ3：与上一篇优化方案1 中的 VQ3 类似，也是从 Sales 中筛选符合条件的行，以 Sales[OrderDate]、Sales[ShipDate] 分组，并计算每一组的行数。结果包含 3 列 Sales[OrderDate]、Sales[ShipDate]、[COUNT]，共 1074 行数据。后文以 VQ3:Sales[OrderDate],Sales[ShipDate],[COUNT]​表示该结果

SELECT
    'Sales'[OrderDate],
    'Sales'[ShipDate],
    COUNT ( )
FROM 'Sales'
WHERE
     ( 'Sales'[OrderDate], 'Sales'[ShipDate] ) IN { ( 43341.000000, 43348.000000 ) , ( 43494.000000, 43501.000000 ) , ( 43416.000000, 43423.000000 ) , ( 43646.000000, 43653.000000 ) , ( 43721.000000, 43728.000000 ) , ( 43570.000000, 43577.000000 ) , ( 43007.000000, 43014.000000 ) , ( 43150.000000, 43157.000000 ) , ( 43656.000000, 43663.000000 ) , ( 43264.000000, 43271.000000 ) ..[1,074 total tuples, not all displayed]};

WHERE 子句有变化，这次限定的是 (Sales[OrderDate], Sales[ShipDate]) 值对的取值范围，注意是值对。

( ‘Sales’[OrderDate], ‘Sales’[ShipDate] ) IN { ( 43341.000000, 43348.000000 ) , ( 43494.000000, 43501.000000 ) , ( 43416.000000, 43423.000000 ) , ( 43646.000000, 43653.000000 ) , ( 43721.000000, 43728.000000 ) , ( 43570.000000, 43577.000000 ) , ( 43007.000000, 43014.000000 ) , ( 43150.000000, 43157.000000 ) , ( 43656.000000, 43663.000000 ) , ( 43264.000000, 43271.000000 ) …[1,074 total tuples, not all displayed]}

WHERE 子句现在是 (Sales[Order], Sales[ShipDate]) IN {( ), ( ),...}​的结构，最后中括号中用的词是 tuples（元组）。 tuples 指的是由多个数据构成的一个集合，比如 (1, 2, 3) 可以算作一个 tuple。

该 xmSQL 查询的结果可以借用这段 DAX 查询来理解：

// DAX
DEFINE
    VAR tuples = TREATAS(
            {(),()...}, 
            Sales[OrderDate], Sales[ShipDate]
        )

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE ( Sales, Sales[OrderDate], sales[ShipDate] ),
        "COUNT", CALCULATE ( COUNTROWS ( Sales ) )
    ),
    tuples
)

而上一篇中此部分的内容是 Sales[Order] IN {...} VAND Sales[ShipDate] IN {...}​，最后中括号中用的词是 values

‘Sales’[OrderDate] IN ( 43768.000000, 43809.000000, 43850.000000, 43893.000000, 43934.000000, 43975.000000, 43014.000000, 43426.000000, 43468.000000, 43513.000000…[1,080 total values, not all displayed] ) VAND
‘Sales’[ShipDate] IN ( 43775.000000, 43816.000000, 43857.000000, 43900.000000, 43941.000000, 43982.000000, 43028.000000, 43433.000000, 43475.000000, 43520.000000…[1,074 total values, not all displayed] )

上一篇已经分析过这种 WHERE 子句的值列表由公式引擎提供，稍后会再次通过物理查询计划得到印证。

公式引擎部分

逻辑查询计划

已经第三篇了，这里就不再说重复的话了

AddColumns: RelLogOp DependOnCols()() 0-1 RequiredCols(0, 1)('Calendar'[Date], ''[OpenOrders])
├── Scan_Vertipaq: RelLogOp DependOnCols()() 0-0 RequiredCols(0)('Calendar'[Date])
└── Calculate: ScaLogOp DependOnCols(0)('Calendar'[Date]) Integer DominantValue=BLANK
    ├── Count_Vertipaq: ScaLogOp DependOnCols(0)('Calendar'[Date]) Integer DominantValue=BLANK
    │   └── Scan_Vertipaq: RelLogOp DependOnCols(0)('Calendar'[Date]) 16-30 RequiredCols(0)('Calendar'[Date])
    └── Filter: RelLogOp DependOnCols(0)('Calendar'[Date]) 1-15 RequiredCols(0, 6, 7)('Calendar'[Date], 'Sales'[OrderDate], 'Sales'[ShipDate])
        ├── Filter: RelLogOp DependOnCols(0)('Calendar'[Date]) 1-15 RequiredCols(0, 6, 7)('Calendar'[Date], 'Sales'[OrderDate], 'Sales'[ShipDate])
        │   ├── Scan_Vertipaq: RelLogOp DependOnCols()() 1-15 RequiredCols(6, 7)('Sales'[OrderDate], 'Sales'[ShipDate])
        │   └── LessThan: ScaLogOp DependOnCols(0, 6)('Calendar'[Date], 'Sales'[OrderDate]) Boolean DominantValue=NONE
        │       ├── 'Sales'[OrderDate]: ScaLogOp DependOnCols(6)('Sales'[OrderDate]) DateTime DominantValue=NONE
        │       └── 'Calendar'[Date]: ScaLogOp DependOnCols(0)('Calendar'[Date]) DateTime DominantValue=NONE
        └── GreaterThan: ScaLogOp DependOnCols(0, 7)('Calendar'[Date], 'Sales'[ShipDate]) Boolean DominantValue=NONE
            ├── 'Sales'[ShipDate]: ScaLogOp DependOnCols(7)('Sales'[ShipDate]) DateTime DominantValue=NONE
            └── 'Calendar'[Date]: ScaLogOp DependOnCols(0)('Calendar'[Date]) DateTime DominantValue=NONE

‍
1:AddColumns
2:Scan_Vertipaq
Calendar[Date]
3:Calculate
4:Count_Vertipaq
5:Scan_Vertipaq
Calendar[Date]
6:Filter
7:Filter
8:Scan_Vertipaq
Sales[OrderDate]
Sales[ShipDate]
9:LessThan
10:'Sales'[OrderDate]
11:'Calendar'[Date]
12:GreaterThan
13:'Sales'[ShipDate]
14:'Calendar'[Date]

说一下与上一篇的区别，这次逻辑查询计划里两个 FILTER 嵌套了，上一篇是并列的，不过具体的区别还得看物理查询计划。

物理查询计划

先截图
请添加图片描述
​
去掉 Line 和 Records ，画上层次结构线

AddColumns: IterPhyOp LogOp=AddColumns IterCols(0, 1)('Calendar'[Date], ''[OpenOrders])
├── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Scan_Vertipaq IterCols(0)('Calendar'[Date]) #Records=1081 #KeyCols=26 #ValueCols=0
│   └── ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=1081
│       └── Cache: IterPhyOp #FieldCols=1 #ValueCols=0
└── SpoolLookup: LookupPhyOp LogOp=Count_Vertipaq LookupCols(0)('Calendar'[Date]) Integer #Records=1079 #KeyCols=1 #ValueCols=1 DominantValue=BLANK
    └── AggregationSpool<AggFusion<Sum>>: SpoolPhyOp #Records=1079
        └── CrossApply: IterPhyOp LogOp=Count_Vertipaq IterCols(0)('Calendar'[Date])
            ├── Spool_MultiValuedHashLookup: IterPhyOp LogOp=Filter LookupCols(6, 7)('Sales'[OrderDate], 'Sales'[ShipDate]) IterCols(0)('Calendar'[Date]) #Records=6444 #KeyCols=3 #ValueCols=0
            │   └── AggregationSpool<GroupBy>: SpoolPhyOp #Records=6444
            │       └── Filter: IterPhyOp LogOp=Filter IterCols(0, 6, 7)('Calendar'[Date], 'Sales'[OrderDate], 'Sales'[ShipDate])
            │           └── Extend_Lookup: IterPhyOp LogOp=GreaterThan IterCols(0, 7)('Calendar'[Date], 'Sales'[ShipDate])
            │               ├── Filter: IterPhyOp LogOp=Filter IterCols(0, 6, 7)('Calendar'[Date], 'Sales'[OrderDate], 'Sales'[ShipDate])
            │               │   └── Extend_Lookup: IterPhyOp LogOp=LessThan IterCols(0, 6)('Calendar'[Date], 'Sales'[OrderDate])
            │               │       ├── CrossApply: IterPhyOp LogOp=LessThan IterCols(0, 6)('Calendar'[Date], 'Sales'[OrderDate])
            │               │       │   ├── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Scan_Vertipaq IterCols(0)('Calendar'[Date]) #Records=1081 #KeyCols=26 #ValueCols=0
            │               │       │   │   └── ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=1081
            │               │       │   │       └── Cache: IterPhyOp #FieldCols=1 #ValueCols=0
            │               │       │   └── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Scan_Vertipaq IterCols(6, 7)('Sales'[OrderDate], 'Sales'[ShipDate]) #Records=1081 #KeyCols=26 #ValueCols=0
            │               │       │       └── ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=1081
            │               │       │           └── Cache: IterPhyOp #FieldCols=2 #ValueCols=0
            │               │       └── LessThan: LookupPhyOp LogOp=LessThan LookupCols(0, 6)('Calendar'[Date], 'Sales'[OrderDate]) Boolean
            │               │           ├── ColValue<'Sales'[OrderDate]>: LookupPhyOp LogOp=ColValue<'Sales'[OrderDate]>'Sales'[OrderDate] LookupCols(6)('Sales'[OrderDate]) DateTime
            │               │           └── ColValue<'Calendar'[Date]>: LookupPhyOp LogOp=ColValue<'Calendar'[Date]>'Calendar'[Date] LookupCols(0)('Calendar'[Date]) DateTime
            │               └── GreaterThan: LookupPhyOp LogOp=GreaterThan LookupCols(0, 7)('Calendar'[Date], 'Sales'[ShipDate]) Boolean
            │                   ├── ColValue<'Sales'[ShipDate]>: LookupPhyOp LogOp=ColValue<'Sales'[ShipDate]>'Sales'[ShipDate] LookupCols(7)('Sales'[ShipDate]) DateTime
            │                   └── ColValue<'Calendar'[Date]>: LookupPhyOp LogOp=ColValue<'Calendar'[Date]>'Calendar'[Date] LookupCols(0)('Calendar'[Date]) DateTime
            └── Cache: IterPhyOp #FieldCols=2 #ValueCols=1
                └── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Filter IterCols(0, 6, 7)('Calendar'[Date], 'Sales'[OrderDate], 'Sales'[ShipDate]) #Records=6444 #KeyCols=3 #ValueCols=0
                    └── AggregationSpool<GroupBy>: SpoolPhyOp #Records=6444
                        └── Filter: IterPhyOp LogOp=Filter IterCols(0, 6, 7)('Calendar'[Date], 'Sales'[OrderDate], 'Sales'[ShipDate])
                            └── Extend_Lookup: IterPhyOp LogOp=GreaterThan IterCols(0, 7)('Calendar'[Date], 'Sales'[ShipDate])
                                ├── Filter: IterPhyOp LogOp=Filter IterCols(0, 6, 7)('Calendar'[Date], 'Sales'[OrderDate], 'Sales'[ShipDate])
                                │   └── Extend_Lookup: IterPhyOp LogOp=LessThan IterCols(0, 6)('Calendar'[Date], 'Sales'[OrderDate])
                                │       ├── CrossApply: IterPhyOp LogOp=LessThan IterCols(0, 6)('Calendar'[Date], 'Sales'[OrderDate])
                                │       │   ├── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Scan_Vertipaq IterCols(0)('Calendar'[Date]) #Records=1081 #KeyCols=26 #ValueCols=0
                                │       │   │   └── ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=1081
                                │       │   │       └── Cache: IterPhyOp #FieldCols=1 #ValueCols=0
                                │       │   └── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Scan_Vertipaq IterCols(6, 7)('Sales'[OrderDate], 'Sales'[ShipDate]) #Records=1081 #KeyCols=26 #ValueCols=0
                                │       │       └── ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=1081
                                │       │           └── Cache: IterPhyOp #FieldCols=2 #ValueCols=0
                                │       └── LessThan: LookupPhyOp LogOp=LessThan LookupCols(0, 6)('Calendar'[Date], 'Sales'[OrderDate]) Boolean
                                │           ├── ColValue<'Sales'[OrderDate]>: LookupPhyOp LogOp=ColValue<'Sales'[OrderDate]>'Sales'[OrderDate] LookupCols(6)('Sales'[OrderDate]) DateTime
                                │           └── ColValue<'Calendar'[Date]>: LookupPhyOp LogOp=ColValue<'Calendar'[Date]>'Calendar'[Date] LookupCols(0)('Calendar'[Date]) DateTime
                                └── GreaterThan: LookupPhyOp LogOp=GreaterThan LookupCols(0, 7)('Calendar'[Date], 'Sales'[ShipDate]) Boolean
                                    ├── ColValue<'Sales'[ShipDate]>: LookupPhyOp LogOp=ColValue<'Sales'[ShipDate]>'Sales'[ShipDate] LookupCols(7)('Sales'[ShipDate]) DateTime
                                    └── ColValue<'Calendar'[Date]>: LookupPhyOp LogOp=ColValue<'Calendar'[Date]>'Calendar'[Date] LookupCols(0)('Calendar'[Date]) DateTime

前两篇也是去掉了 Line 和 Records，其实 Records 还挺重要的，Records 记录的是该步骤物化了多少数据量，大概可以理解为这一步产生了多少数据量，不过有些步骤产生的数据量没有标记出来，需要使用者自行分析计算。

下面是结构流程图，我也学《白皮书》的作者故意省略了大量操作符，这样图画出来会小一点。
1:AddColumns
4:Cache(VQ2)
Calendar[Date]
6:Sum
7:CrossApply
8:Spool_MultiValuedHashLookup
Sales[OrderDate]
Sales[ShipDate]
10:Filter
12:Filter
14:CrossApply
17:Cache(VQ2)
Calendar[Date]
20:Cache(VQ1)
Sales[OrderDate]
Sales[ShipDate]
21:LessThan
Sales[OrderDate]<
Calendar[Date]
24:GreaterThan
Sales[ShipDate]>
Calendar[Date]
27:Cache(VQ3)
Sales[OrderDate]
Sales[ShipDate]
[COUNT]
30:Filter
32:Filter
34:CrossApply
37:Cache(VQ2)
Calendar[Date]
40:Cache(VQ1)
Sales[OrderDate]
Sales[ShipDate]
41:LessThan
Sales[OrderDate]<
Calendar[Date]
44:GreaterThan
Sales[ShipDate]>
Calendar[Date]

我特意省略了部分操作符，使两个 10:Filter​ 和 30:Filter​ 往下的操作符正好并列摆布，这样就能很明显地看出来，10:Filter​ 和 30:Filter​ 往下的操作符是完全相同的，所以，我要把这张图再精简一下。
1:AddColumns
4:Cache(VQ2)
Calendar[Date]
6:Sum
7:CrossApply
8:Spool_MultiValuedHashLookup
Sales[OrderDate]
Sales[ShipDate]
10/30:Filter
12/32:Filter
14/34:CrossApply
17/37:Cache(VQ2)
Calendar[Date]
20/40:Cache(VQ1)
Sales[OrderDate]
Sales[ShipDate]
21/41:LessThan
Sales[OrderDate]<
Calendar[Date]
24/44:GreaterThan
Sales[ShipDate]>
Calendar[Date]
27:Cache(VQ3)
Sales[OrderDate]
Sales[ShipDate]
[COUNT]

在上一篇我们也这样干过，这么做主要是想说明物理查询计划里有些步骤是重复的，可以合并起来看，不用把每个分支都去分析一遍。

底层计算过程

一

从下往上，先看重复出现的那几个步骤是在做什么，把这部分命名为 T​

T
12/32:Filter
24/44:GreaterThan
Sales[ShipDate]>
Calendar[Date]
10/30:Filter
14/34:CrossApply
21/41:LessThan
Sales[OrderDate]<
Calendar[Date]
17/37:Cache(VQ2)
Calendar[Date]
20/40:Cache(VQ1)
Sales[OrderDate]
Sales[ShipDate]

1、14/34:CrossApply​ 对 17/37:Cache(VQ2)​与 20/40:Cache(VQ1)​求交叉表

将 VQ1:[OrderDate], [ShipDate]​ 与 VQ2:Calendar[Date]​ 求交叉表，得到的结果有 3 列 [OrderDate], [ShipDate], Calendar[Date]​，包含 Sales 表中全部 1081 个 {[OrderDate], [ShipDate]} 值对与 Calendar[Date] 全部 1081 个值的笛卡尔积，共 1081×1081 = 1168561 行

2、12/32:Filter​ 从 1# 的结果中筛选出Sales[OrderDate] < Calendar[Date]​的数据，583740 行

3、10/30:Fitler​ 从 2# 的结果中筛选出 Sales[ShipDate] > Calendar[Date]​的数据，6444 行

这部分执行的操作，是从 {[OrderDate], [ShipDate]} 值对与 Calendar[Date] 的交叉表中筛选出满足Sales[OrderDate] < 'Calendar'[Date] && Sales[ShipDate] > 'Calendar'[Date]​的全部数据，其结果包含 Sales[OrderDate], Sales[ShipDate], Calendar[Date] 3列，后文用 T:[OrderDate], [ShipDate], Calendar[Date]​ 表示 T​ 的结果表

可以用以下 DAX 查询的结果来帮助理解（注意用这段查询的运行结果去理解T​的结果，并不是说T​执行了下面这段 DAX 查询代码）

//DAX
EVALUATE
GENERATE (
    VALUES ( 'Calendar'[Date] ),
    FILTER (
        ALL ( sales[OrderDate], sales[ShipDate] ),
        [orderdate] < [Date]
            && [Date] < [ShipDate]
    )
)

其结果如下，右下角记录了该结果有 6444 行。

在这里插入图片描述
​

二

从 ​T​ 往上看

‍
7:CrossApply
8:Spool_MultiValuedHashLookup
Sales[OrderDate]
Sales[ShipDate]
27:Cache(VQ3)
Sales[OrderDate]
Sales[ShipDate]
[COUNT]
T

‍

1、27:Cache(VQ3)​ 从 T​ 的结果中取出 {Sales[OrderDate], Sales[ShipDate]} 值对，正好 1074 个，这 1074 行数据构成了 VQ3 的 WHERE 子句中的 1074 个 tuples（组元），27:Cache(VQ3)​从存储引擎读取 VQ3 的结果

​在这里插入图片描述
​

在存储引擎一节已经讲过 VQ3 的作用：

从 Sales 中筛选符合条件的行，以 Sales[OrderDate]、Sales[ShipDate] 分组，并计算每一组的行数

当时没有讲符合什么样的筛选条件，现在知道 WHERE 子句中的组元来自 T​ ，可以把 VQ3 的作用补全了:

从 Sales 中筛选出对于 Calendar 表中任意 [Date]，都能满足 [OrderDate] < Calendar[Date] < [ShipDate] 的数据行，以 Sales[OrderDate]、Sales[ShipDate] 分组，并计算每一组的行数

2、7:CrossApply​ 以 8:Spool_MultiValuedHashLookup​ 作为参数，将 T:[OrderDate], [ShipDate], Calendar[Date]​ 与 VQ3:Sales[OrderDate],Sales[ShipDate],[COUNT]​ 以 Sales[OrderDate]、Sales[ShipDate] 为匹配列进行连接，类似 PowerQuery 中的合并查询，得到的新结果包含 [OrderDate], [ShipDate], Calendar[Date], [COUNT]​ 4 列。

这一步的作用，可以通过一段 PowerQuery 代码的结果来理解：

// Power Query
let
    MultiValuedHashLookup = 
        Table.NestedJoin(
            VQ3, {"OrderDate", "ShipDate"}, 
            T, {"OrderDate", "ShipDate"}, 
            "查询Date", JoinKind.LeftOuter
        ), // 查询合并
    CrossApply = 
        Table.ExpandTableColumn(MultiValuedHashLookup, "查询Date", {"Date"}, {"Date"}) // 展开
in
    CrossApply

三

接着往上
1:AddColumns
4:Cache(VQ2)
Calendar[Date]
6:Sum
7:CrossApply

1、6:Sum​ 从 7:CrossApply​的结果表中，以 Calendar[Date] 为分组，对 [Count] 进行累加，得到 Calendar[Date], [SUM]

2、4:Cache(VQ2)​ 从存储引擎获取 VQ2:Calendar[Date]​，

3、1:AddColumns​ 以此为基准，从 6:Sum​ 中查询对应 Calendar[Date] 的 [SUM] 值作为新加列 [OpenOrders] 的内容，得到最终的查询计算结果

性能提升原因分析

物理查询计划已经分析完了，相对于上一篇，优化方案（番外）性能更好的原因是什么？

先看底层原因，将两个方案的物理查询计划按相同的原则进行精简，然后放一起做比较，并在图上标出每个步骤产生的数据量。

​在这里插入图片描述
​

两个方案最顶端的步骤是一样的（用蓝框标记），不同的步骤体现在蓝框部分下方

左边的优化方案（番外）用了5步，产生大量中间数据的步骤只出现在第1、2步；

右边的优化方案1则用了7步，而且产生大量中间数据的步骤出现在了第1、2、3、4、6步。

优化方案（番外）使用了更少的步骤并很快地缩小了数据规模，因此计算效率更高。

这一优势来自于优化方案（番外）的 VQ1 使用了 Sales[OrderDate]，Sales[ShipDate]，将两个条件列合并在同一个 FILTER 操作中

// xmSQL VQ1 
SELECT
    'Sales'[OrderDate],
    'Sales'[ShipDate]
FROM 'Sales';

再看代码层面，将之前两个分开的 FILTER 函数合并成一个是关键原因

// 运行较慢的方案
CALCULATE (
    COUNTROWS ( Sales ),
    FILTER ( sales, sales[OrderDate] < 'Calendar'[Date] ), 
    FILTER ( sales, sales[ShipDate] > 'Calendar'[Date] )
        )

// 运行较快的方案
CALCULATE (
    COUNTROWS ( Sales ),
    FILTER (
        sales,
        sales[OrderDate] < 'Calendar'[Date]
            && sales[ShipDate] > 'Calendar'[Date]
    )
)

总结

通过最近这三篇案例分析可以得出如下经验：

1、在迭代器中使用 FILTER 函数与行上下文中的值进行比较时，在底层会生成 交叉表+判断 操作

ADDCOLUMNS(
    日期表,
    "COUNT",
    COUNTROWS(
        FILTER(数据表, 数据表[OrderDate] < 日期表[Date])
    )
)
Filter
日期表[Date] × 相关数据列
数据表[OrderDate] < 日期表[Date]

相关数据列，通常只包含参与判断的列，不会出现数据表中其他无关的列；但某些情况下会出现 [RowNumber] 列。

2、在 1# 条件下，FILTER 的条件包含多个列时，在底层执行生成上下嵌套的 FILTER 操作

ADDCOLUMNS(
    日期表,
    "COUNT",
    COUNTROWS(
        FILTER(
            数据表, 
                数据表[OrderDate] < 日期表[Date] && 
                数据表[ShipDate] > 日期表[Date]
            )
    )
)
Filter
Fitler
日期表[Date] × 相关数据列
数据表[OrderDate] < 日期表[Date]
数据表[ShipDate] > 日期表[Date]

3、在 1# 条件下，如果使用类似 COUNTROWS(FILTER(...))​的写法作为迭代计算，会在 xmSQL 查询中带入 [RowNumber] ，可能造成数据庞大的交叉表，如

ADDCOLUMNS(
    日期表,
    "COUNT",
    COUNTROWS(
        FILTER(数据表, 数据表[OrderDate] < 日期表[Date])
    )
)

生成的 xmSQL 可能是这样的，其结果的基数等于数据表的基数

// xmSQL
SELECT
    数据表[RowNumber], 数据表[OrderDate]
FORM
    数据表

【相关数据列】将包括 [RowNumber] 和 数据表[OrderDate]
Filter
日期表[Date] × {数据表[RowNumber], 数据表[OrderDate]}
数据表[OrderDate] < 日期表[Date]

4、在 1# 条件下，使用 CALCULATE + FILTER 的写法，可以避免出现 3# 中说的 [RowNumber]，不过也会出现需要公式引擎提供 WHERE 子句值列表的 xmSQL 查询

ADDCOLUMNS(
    日期表,
    "COUNT",
    CALCULATE(
        COUNTROWS(数据表),
        FILTER(数据表, 数据表[OrderDate] < 日期表[Date])
    )
)
Filter
日期表[Date] × 数据表[OrderDate]
数据表[OrderDate] < 日期表[Date] 
// xmSQL
SELECT
    'Sales'[OrderDate]  
    COUNT ( )
FROM 'Sales'
WHERE
    'Sales'[OrderDate] IN ( 43768.000000, 43809.000000......)

5、在 4# 条件下，当在 CALCULATE 的参数中使用n个独立的 FILTER 语句，会在底层生成n个独立的 交叉表+判断 操作，增加交叉表的数量

ADDCOLUMNS(
    日期表,
    "COUNT",
    CALCULATE(
        COUNTROWS(数据表),
        FILTER(数据表, 数据表[OrderDate] < 日期表[Date]),
        FILTER(数据表, 数据表[ShipDate] > 日期表[Date])
    )
)
Filter
日期表[Date] × 数据表[OrderDate]
数据表[OrderDate] < 日期表[Date]
Filter
日期表[Date] × 数据表[ShipDate]
数据表[ShipDate] > 日期表[Date]

6、在 5# 条件下，如果将多个 FILTER 合并成一个，可以恢复到 2# 的情况，并且不会出现 3# 的 [RowNumber]

ADDCOLUMNS(
    日期表,
    "COUNT",
    CALCULATE(
        COUNTROWS(数据表),
        FILTER(
            数据表, 
            数据表[OrderDate] < 日期表[Date] &&
            数据表[ShipDate] > 日期表[Date])
    )
)
Filter
Fitler
日期表[Date] × {数据表[OrderDate], 数据表[ShipDate]}
数据表[OrderDate] < 日期表[Date]
数据表[ShipDate] > 日期表[Date]
apinerand
关注
  • 16
    点赞
  • 踩
  • 23
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
可视化实战应用-PowerBI中dax的使用实例
wenyusuran的专栏
06-07 1106
计算表返回值是一个二维表，比如下面返回一个只有一个时间列的表。时间是连续的，结束于6月。会扫描模型里的最大时间和最小时间，然后涵盖掉。 计算列单行内计算，非聚合。和我们的非聚合型计算字段类似。 注意，单行内的计算，是不能跨表的，这点和我们是一样的。但是也允许通过RELATED 、 RELATEDTABLE和LOOKUPVALUE函数引用其他关联表的字段，比如下面这样，跨sales表和product两张表的字段： 度量聚合后的字段称为度量。度量分为隐式度量和显式度量，用DAX创建的叫显示度量
DAX 综合实战案例—简单的中国式报表详解（三）
PowerPivotShop的博客
03-27 775
小伙伴们大家好！ ☞DAX综合实战案例-简单的中国式报表详解（一） ☞DAX综合实战案例-简单的中国式报表详解（二） 中讲解了中国式表头的构建，按列排序，时间智能函数，这些菜都已经做好，最后一步就是拼盘了，如何把它们按照需要的样子拼在一起呢？ 接下来就处理这个问题。 我们再来看下报表的样子： 报表先是展示各月的预算达成情况，然后是年累计、上年全年、上年年累计情况，最后是差异比较。表头通过按...
研究《DAX查询计划 白皮书》中的案例(五)优化方案3
一棵松的博客
05-19 1195
这是本系列的最后一个优化方案，这一篇的优化方案3是所有方案中最优秀的，它解决了优化方案2中产生的[RowNumber] 问题，使整个底层计算过程中全部步骤都没有出现数据量超过万行的情况，使性能获得了极大的提升！
研究《DAX查询计划 白皮书》中的案例(二)优化方案1
一棵松的博客
05-12 348
上一篇文章中介绍了初始方案的查询计划，找到了性能瓶颈来自 FILTER 的一参使用 Sales 整表，在底层执行时需要计算数据量巨大的交叉表，导致公式引擎部分耗费大量时间，确定了改进方向是想法提高 FILTER 的性能，减少迭代对象的势。不过白皮书给出的第一个方案并没有直接从减少 FILTER 迭代对象的势入手，而是采取了 CALCULATE+FILTER 的写法，这也是许多初学者可能采取的方案。那么本篇文章将继续通过理解查询计划，了解其底层执行过程，分析其优劣势。
研究《DAX查询计划 白皮书》的案例(一)初始方案
一棵松的博客
05-10 822
SQLBI 网站上提供的[《White paper: Understarnding DAX Query Plans》，发布于2023年7月17日，距今已超过10年，随着DAX 版本不断更新优化，原文中关于查询计划的描述已不再符合当前实际情况。为充分理解、运用该文阐述的分析方法，以当前 DAX 版本运行文中案例，尝试理解查询计划及优化思路。
研究《DAX查询计划 白皮书》中的案例(四)优化方案2
一棵松的博客
05-17 1202
优化方案1将原始方案的计算效率提高了 90% 以上，而白皮书认为这还不是最好的方案，原作者给出了另外两个更好的方案，这两个算法都不再基于对计算要求的直接理解，更换了 DAX 写法。本篇分析白皮书提供的优化方案2，并与优化方案1和优化方案(番外)进行比较，进一步认识性能优劣的底层原因。
8.23 PowerBI系列之DAX函数专题-报表性能优化
qq_33842426的博客
05-30 370
（2）将更多复杂的数据清晰任务交给数据源端处理，比如链接的数据源是数据库，那么数据查询性能的调优和数据清晰任务可以交给SQL处理，SQL处理完的结果再导入Power BI。（1）可以使用dataflow数据流（输一个数据表/源由多个报表使用）或共享数据集（一个数据集由多个报表使用）（4）规范度量值存储，存储在单独的度量值管理表中，方便查找和管理。（6）尽量使用显式度量值，减少对快速度量值和隐式度量值的依赖。（2）尽量避免使用DAX迭代器函数（如SUMX，MAXX）
在DAX studio 中的DAX 查询
Babyfatliang的博客
12-02 3033
转自简书：https://www.jianshu.com/p/4c4b3d551201   DAX查询编写 DAX查询的语法为: 根据 微软官方DAX查询参考 可知: DAX 语言提供了一种新语法，可从查询中返回表数据。 通过 DAX 查询，用户可从内存分析引擎 (VertiPaq) 来检索由表表达式定义的数据。 用户可将度量值作为查询的一部分创建；查询结束后，这些结果会被释放，除非导出这些...
精品丨DAX性能优化问题
Fabric丨白茶
07-31 852
BOSS：那个，白茶啊，这个报表刷新有点慢啊，你看，每次我点筛选或者刷新都会让我等很久。 白茶：（认真脸）BOSS，那您再等会就好！ … BOSS：白茶！！两小时了！！还没出来！！ 白茶：（思考）老板，这个有点难啊，这个问题技术要求比较高。 BOSS：加钱！！！！ 白茶：好嘞！ 一张好的报表是如何界定的？DAX计算无误、前端展现明了、业务思路清晰、报表响应速度，白茶觉得这些因素就可以界定一张好的报表。 本期我们来聊一聊PowerBI中DAX函数性能优化的问题。 毕竟一张可视化报表需要15分钟刷新才能呈..
《dax 查询计划 白皮书》 优化方案2 计算过程模拟演示
05-18
通过图示和动态计算，在 Excel 中演示优化方案2的底层计算过程。...详细解析见文章 《研究〈白皮书-理解DAX查询计划〉中的案例(四)优化方案2》 https://blog.csdn.net/Randvalue/article/details/138979537
《dax 查询计划 白皮书》 优化方案3 计算过程模拟演示
05-28
本系列的最后一个优化方案，这一篇的优化方案3是所有方案中最优秀的，它解决了优化方案2中产生的[RowNumber] 问题，使整个底层计算过程中全部步骤都没有出现数据量超过万行的情况，使性能获得了极大的提升！...
研究《白皮书-理解DAX查询计划中》的案例所用数据
05-10
系列文章【研究《白皮书-理解DAX查询计划中》的案例】所用数据，二进制 Excel 文档，包含 Sales 表一张，详情见文章链接 https://blog.csdn.net/Randvalue/article/details/138683483
DAX Studio 数据查询软件 v2.13.1官方版
12-18
为您提供DAX Studio 数据查询软件下载，...基本简介　DAX Studio是一个客户端工具，用于对各种数据源执行DAX查询，包括：　Excel中的PowerPivot　Power BI桌面/SSDT集成工作区　Analysis Services表格　Azure分析服务
PowerBi 柱形图，数据标签无法显示在端外
y523648的博客
08-29 231
按backspace键删除，然后，鼠标在任意空白处点击一下，就自动变成”自动”了。原因其实是因为Y轴的数据范围设置不当，如图，当前Y轴范围是0到自动。即使设置了“数据标签”显示“端外“，仍然不作用。只需要修改为最大和最小值都是自动即可，选中0。
大数据处理框架：Flink：FlinkSQL入门与实践.docx
09-02
大数据处理框架：Flink：FlinkSQL入门与实践.docx
数据集成工具：Talend：Talend数据集成最佳实践与案例分析.docx
09-02
数据集成工具：Talend：Talend数据集成最佳实践与案例分析.docx
实时计算：Azure Stream Analytics：数据流窗口与聚合操作.docx
09-02
实时计算：Azure Stream Analytics：数据流窗口与聚合操作.docx
利于python爬虫爬取淘宝司法拍卖和京东司法拍卖土地的每日信息.zip
最新发布
09-02
利于python爬虫爬取淘宝司法拍卖和京东司法拍卖土地的每日信息
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值