研究《DAX查询计划白皮书》中的案例(四)优化方案2-优快云博客

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

优化方案1将原始方案的计算效率提高了 90% 以上，而白皮书认为这还不是最好的方案，原作者给出了另外两个更好的方案，这两个算法都不再基于对计算要求的直接理解，更换了 DAX 写法。本篇分析白皮书提供的优化方案2，并与优化方案1和优化方案(番外)进行比较，进一步认识性能优劣的底层原因。

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

优化方案2

DAX 查询

// 优化方案2（原版）
EVALUATE
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "OpenOrders",
        COUNTROWS (
            FILTER(
                'Internet Sales',
                CONTAINS (
                    DATESBETWEEN (
                        'Date'[Date],
                        'Internet Sales'[Order Date], // 白皮书提供的代码在这里没有+1
                        'Internet Sales'[Ship Date] - 1
                    ),
                    'Date'[Date],
                    'Date'[Date]
                )
            )
        )
    )

这段代码与第一篇原始方案的结构类似，都是 COUNTROWS + FILTER 的写法，不过 FILTER 条件参数不再是简单的 Sales[OrderDate] < Calendar[Date] && Sales[ShipDate] > Calendar[Date] ，改成了 CONTAINS + DATESBETWEEN的判断。

白皮书发布于 2013 年，当时 DAX 还不支持 IN 这个符号（IN发布于 2016年12月），放在今天，这段代码可以修改成下面这样，后文的分析基于修改后的代码。

另外，白皮书给的算法有误，DATESBETWEEN('Calendar'[Date],Sales[OrderDate], Sales[ShipDate] - 1 ) 应该改成DATESBETWEEN('Calendar'[Date],Sales[OrderDate] +1 , Sales[ShipDate] - 1 )，起点不应该包括在内。

// 优化方案2（修正）
EVALUATE
ADDCOLUMNS (
    VALUES ( 'Calendar'[Date] ),
    "OpenOrders",
        COUNTROWS (
            FILTER (
                Sales,
                'Calendar'[Date] IN  // 修改了这里l
                    DATESBETWEEN ( 
                        'Calendar'[Date], 
                        Sales[OrderDate] + 1  // 修改了这里
                        Sales[ShipDate] - 1 
                    )
            )
        )
)

运行效率

以连续5次冷启动的结果进行统计，将各方案的情况列写在表格里，数字下方括号里记录的是相对前一个方案的性能变化情况。相对优化方案1，优化方案2的总耗时又减少了1个数量级，从约百级降低到十级，但是比优化方案(番外)耗稍长一些。

环境	参数	初始方案	优化方案1	优化方案2	优化方案(番外)
环境1	平均总耗时（毫秒）	4389.4	290.4 (↑ 93.38%)	79.6 (↑ 72.59%)	56.8 (↑ 28.64%)
	SE耗时（毫秒）	14.4	4.8	13.6	4.8
	SE占比	0.328%	1.653% (↑ 403.83%)	17.09% (↑ 933.67%)	8.45% (↓50.54%)
环境2	平均总耗时（毫秒）	4017.0	300.60 (↑ 92.52%)	76.4 (↑ 74.58%)	53.8 (↑ 29.58%)
	SE耗时（毫秒）	17.2	5.8 (↑ 66.28%)	18.2	5
	SE占比	0.428%	1.929% (↑ 350.62%)	23.82% (↑ 1134.64%)	9.29% (↓ 60.99%)

白皮书原本的行文结构是逐步优化至最优，但是上一篇由于我的好奇整出个白皮书之外的优化方案(番外)，比本篇的优化方案2运行更快，最快的放在表格最后一列上。

存储引擎部分

有必要把存储引擎相关界面截图，因为这里出现了新情况

从 xmSQL 列表看，一共有 3 个 Scan 即 3 个 xmSQL 查询，左下角显示 SE Cache 命中1次。这 3 个 Scan 中有 2 个 xmSQL 查询是完全相同的，出现在 Line 2 和 Line 4，命中的 Cache 在 Line 3。

实际上只有 2 个 xmSQL，其中 1 个被重复使用。

1、VQ1：将 Calendar 表中按 Calendar[Date] 分组，返回由 [Date] 的不重复值，结果包含1列，共 1081 行数据

// xmSQL VQ1
SELECT
    'Calendar'[Date]
FROM 'Calendar';

2、VQ2：将 Sales 表按 Sales[RowNumber]、Sales[OrderDate]、Sales[ShipDate] 分组，并返回这3列上的值。

SELECT
    'Sales'[RowNumber],
    'Sales'[OrderDate],
    'Sales'[ShipDate]
FROM 'Sales';

读过前几篇文章的朋友知道，[RowNumber] 的出现，会使得查询结果包含 Sales 表的每一行，所以该查询返回 121253 行数据，共 3 列 Sales[RowNumber]、Sales[OrderDate]、Sales[ShipDate]

第一篇文章分析初始方案的性能瓶颈说过 [RowNumber] 的出现可能会导致后续出现超大规模的交叉表，而这次 [RowNumber] 也出现了，为什么性能没有低下反倒还提高了？之前也强调过了，并没有什么通用的优化写法，自然也就没有绝对禁用的禁忌写法，关键还得看各种写法生成的物理查询计划。

公式引擎部分

逻辑查询计划

终于，这次逻辑查询计划出现值得注意的信息了

AddColumns: RelLogOp DependOnCols()() 0-1 RequiredCols(0, 1)('Calendar'[Date], ''[OpenOrders])
├── Scan_Vertipaq: RelLogOp DependOnCols()() 0-0 RequiredCols(0)('Calendar'[Date])
└── CountRows: ScaLogOp DependOnCols(0)('Calendar'[Date]) Integer DominantValue=BLANK
    └── Filter: RelLogOp DependOnCols(0)('Calendar'[Date]) 1-15 RequiredCols(0, 1, 6, 7)('Calendar'[Date], 'Sales'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61], 'Sales'[OrderDate], 'Sales'[ShipDate])
        ├── Scan_Vertipaq: RelLogOp DependOnCols()() 1-15 RequiredCols(1, 6, 7)('Sales'[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61], 'Sales'[OrderDate], 'Sales'[ShipDate])
        └── Not: ScaLogOp DependOnCols(0, 6, 7)('Calendar'[Date], 'Sales'[OrderDate], 'Sales'[ShipDate]) Boolean DominantValue=false
            └── IsEmpty: ScaLogOp DependOnCols(0, 6, 7)('Calendar'[Date], 'Sales'[OrderDate], 'Sales'[ShipDate]) Boolean DominantValue=true
                └── Filter: RelLogOp DependOnCols(0, 6, 7)('Calendar'[Date], 'Sales'[OrderDate], 'Sales'[ShipDate]) 16-16 RequiredCols(0, 6, 7, 16)('Calendar'[Date], 'Sales'[OrderDate], 'Sales'[ShipDate], 'Calendar'[Date])
                    ├── DatesBetween: RelLogOp DependOnCols(6, 7)('Sales'[OrderDate], 'Sales'[ShipDate]) 16-16 RequiredCols(6, 7, 16)('Sales'[OrderDate], 'Sales'[ShipDate], 'Calendar'[Date])
                    │   ├── 'Sales'[OrderDate]: ScaLogOp DependOnCols(6)('Sales'[OrderDate]) DateTime DominantValue=NONE
                    │   └── Subtract: ScaLogOp DependOnCols(7)('Sales'[ShipDate]) DateTime DominantValue=NONE
                    │       ├── 'Sales'[ShipDate]: ScaLogOp DependOnCols(7)('Sales'[ShipDate]) DateTime DominantValue=NONE
                    │       └── Constant: ScaLogOp DependOnCols()() Integer DominantValue=1
                    └── Is: ScaLogOp DependOnCols(0, 16)('Calendar'[Date], 'Calendar'[Date]) Boolean DominantValue=false
                        ├── 'Calendar'[Date]: ScaLogOp DependOnCols(16)('Calendar'[Date]) DateTime DominantValue=NONE
                        └── 'Calendar'[Date]: ScaLogOp DependOnCols(0)('Calendar'[Date]) DateTime DominantValue=NONE