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

apinerand

已于 2024-05-19 21:28:56 修改

阅读量928

点赞数 16

CC 4.0 BY-SA版权

分类专栏： # 研究《DAX查询计划白皮书》中的案例文章标签： powerbi

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

本文链接：https://blog.youkuaiyun.com/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.4	290.4 (↑ 93.38%)	56.8 (↑ 80.44%)
	SE耗时（毫秒）	14.4	4.8 (↑ 66.67%)	4.8 (-)
	SE占比	0.328%	1.653% (↑ 403.83%)	8.45% (↑ 411.27%)
环境2	平均总耗时（毫秒）	4017.0	300.60 (↑ 92.52%)	53.8 (↑ 82.10%)
	SE耗时（毫秒）	17.2	5.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