研究《DAX查询计划白皮书》中的案例(二)优化方案1

本文链接：https://blog.youkuaiyun.com/Randvalue/article/details/138767581

上一篇文章中介绍了初始方案的查询计划，找到了性能瓶颈来自 FILTER 的一参使用 Sales 整表，在底层执行时需要计算数据量巨大的交叉表，导致公式引擎部分耗费大量时间，确定了改进方向是想法提高 FILTER 的性能，减少迭代对象的势。不过白皮书给出的第一个方案并没有直接从减少 FILTER 迭代对象的势入手，而是采取了 CALCULATE+FILTER 的写法，这也是许多初学者可能采取的方案。那么本篇文章将继续通过理解查询计划，了解其底层执行过程，分析其优劣势。

运行环境、模拟数据、计算要求与上一篇相同。

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

优化方案1

DAX 查询

// 优化方案1
EVALUATE
ADDCOLUMNS (
    VALUES ( 'Calendar'[Date] ),
    "OpenOrders",
        CALCULATE (
            COUNTROWS ( Sales ),
            FILTER ( sales, sales[OrderDate] < 'Calendar'[Date] ), 
            FILTER ( sales, sales[ShipDate] > 'Calendar'[Date] )
        ) // 白皮书这里给的方案就是两个 FILTER
)

运行效率

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

环境1

平均总耗时 290.4 毫秒，标准差 26.07 毫秒

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

SE/总耗时 = 1.653%

环境2

平均总耗时 300 毫秒，标准差 2.88 毫秒

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

SE/总耗时 = 1.929%

与之前的原始方案做比较：

环境	参数	初始方案	优化方案1	提升幅度
环境1	平均总耗时	4389.4	290.4	93.38%
	SE耗时	14.4	4.8	66.67%
	SE占比	0.328%	1.653%	403.83%
环境2	平均总耗时	4017.0	300.60	92.52%
	SE耗时	17.2	5.8	66.28%
	SE占比	0.428%	1.929%	350.62%

与初始方案对比，优化方案1带来的性能提升非常明显，平均总耗时从 4389.4 毫秒缩减到 290.4 毫秒，计算效率提升约 90% 以上, SE 耗时占比也大幅提高。

从 DAX 代码来看，FILTER 仍然使用整个 Sales 表作为迭代对象，与初始方案的区别并不大，究竟是哪个部分使其获得了性能提升，它是如何解决超大交叉表的性能瓶颈的，本篇继续通过查看查询计划来一探究竟。

存储引擎部分

存储引擎这次执行了 4 个 xmSQL 查询，分别是：

1、VQ1：从 Sales 表中以 [OrderDate] 分组，返回 [OrderDate] 列的不重复值，该查询返回 1081 行数据

// VQ1
SELECT
    'Sales'[OrderDate]
FROM 'Sales';

2、VQ2：从 Calendar 表中以 [Date] 分组，返回 [Date] 列的不重复值，该查询返回 1081 行数据

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

3、VQ3：从 Sales 表中以 [ShipDate] 分组，返回 [ShipDate] 列的不重复值，该查询返回 1075 行数据

// VQ3
SELECT
    'Sales'[ShipDate]
FROM 'Sales';

4、VQ4：该 xmSQL 与之前的有些区别，先看看代码

// VQ4
SELECT
    'Sales'[OrderDate],
    'Sales'[ShipDate],
    COUNT ( )
FROM 'Sales'
WHERE
    '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] ) ;

该 xmSQL 查询的 WHERE 子句中有许多字符，限定了 Sales[OrderDate] 和 Sales[ShipDate] 的值范围，

‘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 子句将 Sales[Order] 限定在 1080 个值的列表中，Sales[ShipDate] 限定在 1074 个值的列表中。这些列表怎么来的，后面的物理查询计划会给出答案。

整个 xmSQL 查询以 Sales[OrderDate]、Sales[ShipDate]为分组依据，从符合 WHERE 子句条件的 Sales 表数据中返回 {[OrderDate]、[ShipDate]} 的非重复值对，并计算每组值对出现的次数，共 1079 行，包括3列：

Sales[OrderDate], Sales[ShipDate], [COUNT]

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

// DAX
DEFINE
    VAR OrderDateList = ...
    VAR ShipDateList = ...

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

公式引擎部分

逻辑查询计划

逻辑查询计划的代码和流程图如下：

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]) 31-45 RequiredCols(0)('Calendar'[Date])
    ├── Filter: RelLogOp DependOnCols(0)('Calendar'[Date]) 1-15 RequiredCols(0, 6)('Calendar'[Date], 'Sales'[OrderDate])
    │   ├── Scan_Vertipaq: RelLogOp DependOnCols()() 1-15 RequiredCols(6)('Sales'[OrderDate])
    │   └── 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
    └── Filter: RelLogOp DependOnCols(0)('Calendar'[Date]) 16-30 RequiredCols(0, 22)('Calendar'[Date], 'Sales'[ShipDate])
        ├── Scan_Vertipaq: RelLogOp DependOnCols()() 16-30 RequiredCols(22)('Sales'[ShipDate])
        └── GreaterThan: ScaLogOp DependOnCols(0, 22)('Calendar'[Date], 'Sales'[ShipDate]) Boolean DominantValue=NONE
            ├── 'Sales'[ShipDate]: ScaLogOp DependOnCols(22)('Sales'[ShipDate]) DateTime DominantValue=NONE
            └── 'Calendar'[Date]: ScaLogOp DependOnCols(0)('Calendar'[Date]) DateTime DominantValue=NONE