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

原创 已于 2024-05-19 21:28:56 修改 · 974 阅读 · 23 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#powerbi

于 2024-05-13 23:55:27 首次发布

‍上一篇给出的优化方案中使用了两个 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]
8.23 PowerBI系列之DAX函数专题-报表性能优化
qq_33842426的博客
05-30 779
（2）将更多复杂的数据清晰任务交给数据源端处理，比如链接的数据源是数据库，那么数据查询性能的调优和数据清晰任务可以交给SQL处理，SQL处理完的结果再导入Power BI。（1）可以使用dataflow数据流（输一个数据表/源由多个报表使用）或共享数据集（一个数据集由多个报表使用）（4）规范度量值存储，存储在单独的度量值管理表中，方便查找和管理。（6）尽量使用显式度量值，减少对快速度量值和隐式度量值的依赖。（2）尽量避免使用DAX迭代器函数（如SUMX，MAXX）
Power BI 傻瓜入门 20. 使用Power BI优化DAX的十种方法
Martin-Mei的博客
11-02 1489
数据分析表达式（DAX）语言是一种在许多Microsoft产品中使用的公式表达式语言。如果您曾经使用过Analysis Services、Excel中的Power Pivot或Power BI等产品，那么您已经对DAX略知一二了。在许多情况下，Power BI中用于对关系表中的数据执行高级计算和查询的函数、运算符和值与其他Microsoft产品中使用的函数、操作符和值相同。然而，通过Power BI，微软创建了仅适用于其企业商业智能产品的特定利基DAX功能。
研究《DAX查询计划 白皮书》中的案例(二)优化方案1
一棵松的博客
05-12 510
上一篇文章中介绍了初始方案的查询计划，找到了性能瓶颈来自 FILTER 的一参使用 Sales 整表，在底层执行时需要计算数据量巨大的交叉表，导致公式引擎部分耗费大量时间，确定了改进方向是想法提高 FILTER 的性能，减少迭代对象的势。不过白皮书给出的第一个方案并没有直接从减少 FILTER 迭代对象的势入手，而是采取了 CALCULATE+FILTER 的写法，这也是许多初学者可能采取的方案。那么本篇文章将继续通过理解查询计划，了解其底层执行过程，分析其优劣势。
研究《DAX查询计划 白皮书》中的案例(五)优化方案3
一棵松的博客
05-19 1309
这是本系列的最后一个优化方案，这一篇的优化方案3是所有方案中最优秀的，它解决了优化方案2中产生的[RowNumber] 问题，使整个底层计算过程中全部步骤都没有出现数据量超过万行的情况，使性能获得了极大的提升！
研究《DAX查询计划 白皮书》中的案例(四)优化方案2
一棵松的博客
05-17 1359
优化方案1将原始方案的计算效率提高了 90% 以上，而白皮书认为这还不是最好的方案，原作者给出了另外两个更好的方案，这两个算法都不再基于对计算要求的直接理解，更换了 DAX 写法。本篇分析白皮书提供的优化方案2，并与优化方案1和优化方案(番外)进行比较，进一步认识性能优劣的底层原因。
研究《DAX查询计划 白皮书》的案例(一)初始方案
一棵松的博客
05-10 968
SQLBI 网站上提供的[《White paper: Understarnding DAX Query Plans》，发布于2023年7月17日，距今已超过10年，随着DAX 版本不断更新优化，原文中关于查询计划的描述已不再符合当前实际情况。为充分理解、运用该文阐述的分析方法，以当前 DAX 版本运行文中案例，尝试理解查询计划及优化思路。
DAX权威指南9：DAX 查询分析与优化1
qq_56591814的博客
06-08 1441
  DAX Studio 是一个开源工具，专门用于编辑、测试和优化 DAX 查询。它能够连接到 Power BI、Analysis Services 和 Power Pivot for Excel 等工具，并捕获 DAX 查询的执行事件。与 SQL Server Profiler 等通用工具相比，DAX Studio 能够以更直观和高效的方式显示查询计划和性能数据，是 DAX 查询优化的首选工具。  在Power BI中，每个报告页面通过刷新DAX查询更新视觉对象，切换页面、更改切片器或点击刷新视觉对象按钮
PowerBI/DAX之时间智能函数（三）
07-07 1085
*作用：**在指定的日期字段的当前可见数据中，以最小的日期为基准，返回它的上一天/上一个月/上一个季度/上一年的所有日期。**作用：**在指定的日期字段的当前可见数据中，以最大的日期为基准，返回它的下一天/下一个月/下一个季度/下一年的所有日期。1、日期参数可以接受三种类型的输入，分别为：对日期列的引用、具有单个日期列的表，以及定义单个日期列表的布尔表达式。1、日期参数可以接受三种类型的输入，分别为：对日期列的引用、具有单个日期列的表，以及定义单个日期列表的布尔表达式。
《dax 查询计划 白皮书》 优化方案2 计算过程模拟演示
05-18
通过图示和动态计算，在 Excel 中演示优化方案2的底层计算过程。...详细解析见文章 《研究〈白皮书-理解DAX查询计划〉中的案例(四)优化方案2》 https://blog.youkuaiyun.com/Randvalue/article/details/138979537
《dax 查询计划 白皮书》 优化方案3 计算过程模拟演示
05-28
本系列的最后一个优化方案，这一篇的优化方案3是所有方案中最优秀的，它解决了优化方案2中产生的[RowNumber] 问题，使整个底层计算过程中全部步骤都没有出现数据量超过万行的情况，使性能获得了极大的提升！...
研究《白皮书-理解DAX查询计划中》的案例所用数据
05-10
系列文章【研究《白皮书-理解DAX查询计划中》的案例】所用数据，二进制 Excel 文档，包含 Sales 表一张，详情见文章链接 https://blog.youkuaiyun.com/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常见报错故障
专注“目标检测”领域技术分享
11-30 133
因为日期没有和销售额所在的表建立联系建立关系以后。
ISO 10605-2023.pdf
12-03
ISO 10605-2023.pdf
（42页PPT）PP某省市大脑数据中台一网统管可视化平台建设方案.pptx
12-03
（42页PPT）PP某省市大脑数据中台一网统管可视化平台建设方案.pptx
六自由度机械臂ANN人工神经网络设计：正向逆向运动学求解、正向动力学控制、拉格朗日-欧拉法推导逆向动力学方程(Matlab代码实现)
最新发布
12-03
内容概要：本文围绕六自由度机械臂的人工神经网络（ANN）设计展开，重点研究了正向与逆向运动学求解、正向动力学控制以及基于拉格朗日-欧拉法推导逆向动力学方程，并通过Matlab代码实现相关算法。文章结合理论推导与仿真实践，利用人工神经网络对复杂的非线性关系进行建模与逼近，提升机械臂运动控制的精度与效率。同时涵盖了路径规划中的RRT算法与B样条优化方法，形成从运动学到动力学再到轨迹优化的完整技术链条。; 适合人群：具备一定机器人学、自动控制理论基础，熟悉Matlab编程，从事智能控制、机器人控制、运动学六自由度机械臂ANN人工神经网络设计：正向逆向运动学求解、正向动力学控制、拉格朗日-欧拉法推导逆向动力学方程(Matlab代码实现)建模等相关方向的研究生、科研人员及工程技术人员。; 使用场景及目标：①掌握机械臂正/逆运动学的数学建模与ANN求解方法；②理解拉格朗日-欧拉法在动力学建模中的应用；③实现基于神经网络的动力学补偿与高精度轨迹跟踪控制；④结合RRT与B样条完成平滑路径规划与优化。; 阅读建议：建议读者结合Matlab代码动手实践，先从运动学建模入手，逐步深入动力学分析与神经网络训练，注重理论推导与仿真实验的结合，以充分理解机械臂控制系统的设计流程与优化策略。
mall-swarm是一个基于SpringCloudHoxton与Alibaba生态构建的微服务分布式电商平台系统_它包含了完整的商品管理订单处理会员中心营销活动库存管.zip
12-03
mall-swarm是一个基于SpringCloudHoxton与Alibaba生态构建的微服务分布式电商平台系统_它包含了完整的商品管理订单处理会员中心营销活动库存管.zip
eBestMall_B2B2C商城系统_是一个基于Yii20框架构建的面向传统企业与创业者的综合性电子商务解决方案平台_它整合了零售网店网上商城多级分销B2B2C多商户商.zip
12-03
eBestMall_B2B2C商城系统_是一个基于Yii20框架构建的面向传统企业与创业者的综合性电子商务解决方案平台_它整合了零售网店网上商城多级分销B2B2C多商户商.zip
基于Python的Flask轻量级Web框架构建的现代化全栈应用开发项目_包含用户认证系统RESTfulAPI设计数据库模型与迁移前端模板渲染静态文件服务表单处理与验证.zip
12-03
基于Python的Flask轻量级Web框架构建的现代化全栈应用开发项目_包含用户认证系统RESTfulAPI设计数据库模型与迁移前端模板渲染静态文件服务表单处理与验证.zip
DAX查询工具使用与优化技术详解
DAX（Data Analysis ...整体而言，该指南不仅覆盖了DAX查询的语言基础与工具链生态，更深入揭示了其背后的计算引擎逻辑与优化策略，是软件开发人员、数据工程师和BI分析师构建高质量数据分析解决方案的必备参考资料。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值