原创： 2024-04-19 17:07
哪些产品在特定区域、商店或时间段内没有销售?这对许多企业来说可能是一个重要的分析。有多种方法可以获得期望的结果。由于用户或模型需求，可能需要一些特定的实现，而开发人员可以在几种情况下选择公式。又或者你可能只是在网上找到一个解决方案，然后盲目地实施它，而不去质疑是否有更好的方法来实现你想要的。
事实证明，不同的公式表现得非常不同。在您的场景中选择正确的报表可以使慢速报告变快。本文分析了同一算法的不同表述的性能。有些很简单，有些很复杂。本文的要点不是哪个公式运行得最好，而是如何测试度量的性能，以及在将度量投入生产之前执行性能分析的相关性如何。
我们需要一个检查产品是否产生销售的度量。我想到的第一个实现是使用Sales Amount度量并将其值与零进行比较:
DEFINE
MEASURE Sales[HasNoSales] =
[Sales Amount] = 0
EVALUATE
FILTER (
SUMMARIZECOLUMNS (
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name],
"Has no sales", [HasNoSales 0]
),
[Has no sales] = TRUE ()
)
ORDER BY
'Date'[Year],
'Date'[Month Number]
生成的报告显示了在特定月份没有销售的产品。
我们正在使用Contoso 100M，这是Contoso的一个版本，在Sales表中有大约2亿行。当您使用可下载的演示文件(在Sales表中只包含几千行)测试查询时，您的结果可能会有所不同，但是我们需要一个大型数据库来执行性能测试。
在接受第一次尝试作为最终解决方案之前，我们想测试其他公式以获得相同的结果。我们总共有六个可能的解。下面是包含六个度量的完整查询。我们只需要改变SUMMARIZECOLUMNS中引用要测试的度量的那一行，就可以执行相同的查询:
DEFINE
MEASURE Sales[HasNoSales Sales Amount] = [Sales Amount] = 0
MEASURE Sales[HasNoSales COUNTROWS] =
COUNTROWS ( Sales ) = 0
MEASURE Sales[HasNoSales ISEMPTY] =
ISEMPTY ( Sales )
MEASURE Sales[HasNoSales INTERSECT] =
ISEMPTY (
INTERSECT ( VALUES ( 'Product'[ProductKey] ), VALUES ( Sales[ProductKey] ) )
)
MEASURE Sales[HasNoSales EXCEPT] =
NOT ISEMPTY (
EXCEPT ( VALUES ( 'Product'[ProductKey] ), VALUES ( Sales[ProductKey] ) )
)
MEASURE Sales[HasNoSales SELECTEDVALUE] =
NOT ( SELECTEDVALUE ( 'Product'[ProductKey] ) IN VALUES ( Sales[ProductKey] ) )
EVALUATE
FILTER (
SUMMARIZECOLUMNS (
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name],
"Has no sales", [HasNoSales Sales Amount]
),
[Has no sales] = TRUE ()
)
ORDER BY
'Date'[Year],
'Date'[Month Number]
关于不同度量的一些注意事项--它们都以HasNoSales开头，后跟后缀:
Sales Amount:我们的第一个测试检查Sales Amount的值是否等于零。
COUNTROWS:它不计算Sales Amount，而是计算Sales表中的行数，以检查筛选器上下文中是否没有行。
ISEMPTY:与COUNTROWS相同，但它使用ISEMPTY函数来检查是否没有行。
INTERSECT:它执行Sales表和product表中的产品键的交集，检查是否没有匹配的行。
EXCEPT:类似于INTERSECT，但它使用EXCEPT来检查Product表中是否有未在Sales表中引用的行。
SELECTEDVALUE:它使用SELECTEDVALUE和IN操作符来检查当前选择的产品是否再Sales中不存在。
如您所见，像HasNoSales这样的简单度量提供了多种可能的实现。选择合适的度量需要大量的测试。我们查看每个度量的算法并执行一些注意事项。
测试HasNoSales Sales Amount
第一个方法只是检查Sales Amount是否为零:
DEFINE
MEASURE Sales[HasNoSales Sales Amount] = [Sales Amount] = 0
EVALUATE
FILTER (
SUMMARIZECOLUMNS (
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name],
"Has no sales", [HasNoSales Sales Amount]
),
[Has no sales] = TRUE ()
)
ORDER BY
'Date'[Year],
'Date'[Month Number]
服务器计时窗格显示了三个不同的VertiPaq查询。
前两个查询很简单，它们检索产品名称和年月号的组合。它们是如此之快，以至于分析它们没有什么意思。第三个xmSQL查询是真正的工作所在:
WITH
$Expr0 := ( PFCAST ( 'Sales'[Quantity] AS INT ) * PFCAST ( 'Sales'[Net Price] AS INT ) )
SELECT
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name],
SUM ( @$Expr0 )
FROM 'Sales'
LEFT OUTER JOIN 'Date'
ON 'Sales'[Order Date]='Date'[Date]
LEFT OUTER JOIN 'Product'
ON 'Sales'[ProductKey]='Product'[ProductKey];
该查询实现了完整的SUMMARIZECOLUMNS，因为它计算年、月和产品名称的所有组合的销售额。一旦存储引擎计算出结果，公式引擎就会删除零并返回结果。
因为我们正在评估性能，所以两个重要的数字是SE CPU(2766毫秒)和FE(296毫秒)。总执行时间很大程度上受到并行性的影响，因此我们不单独查看总执行时间，因为它是一个弱的效率指标。
测试HasNoSales COUNTROWS版本
第二种度量采用了不同的算法。假设只需检查销售中是否存在行列就能检测出产品是否有销售额，那么它通过计算销售中的行列数并验证是否存在零行列来表达算法：
DEFINE
MEASURE Sales[HasNoSales COUNTROWS] =
COUNTROWS ( Sales ) = 0
EVALUATE
FILTER (
SUMMARIZECOLUMNS (
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name],
"Has no sales", [HasNoSales COUNTROWS]
),
[Has no sales] = TRUE ()
)
ORDER BY
'Date'[Year],
'Date'[Month Number]
只要Sales表只包含销售交易，该假设就有效。例如，如果销售额还包含退货（负值），那么第二个版本就不是一个好的选择。不过，如果假设成立，我们希望第二种计算方法更快，因为它不需要用数量乘以净价。
事实上，第二种计算方法运行速度更快。
不同之处在于存储引擎的 CPU：从 2,766 毫秒下降到 1,203 毫秒，不到一半的时间。前两个查询与之前的查询相同；第三个 xmSQL 查询显示，这次没有计算销售金额：
SELECT
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name],
COUNT ( )
FROM 'Sales'
LEFT OUTER JOIN 'Date'
ON 'Sales'[Order Date]='Date'[Date]
LEFT OUTER JOIN 'Product'
ON 'Sales'[ProductKey]='Product'[ProductKey];
仍有改进的余地。测试 COUNTROWS 是否为零需要 DAX 引擎计算报告中包含的年、月和产品的每种组合的销售交易数量。该措施的唯一目的是将该数字与零进行比较，以返回 "真 "或 "假"。DAX 中的 ISEMPTY 函数经过优化，用于检查表中是否存在行。ISEMPTY 不计算行数；只有一行的存在意味着 ISEMPTY 返回 FALSE。我们在以下版本的度量中使用了这种方法。
测试 HasNoSales ISEMPTY
第三种方法使用与第二种方法相同的算法，这次使用 ISEMPTY 来检查 Sales 表中是否存在行：
DEFINE
MEASURE Sales[HasNoSales ISEMPTY] =
ISEMPTY ( Sales )
EVALUATE
FILTER (
SUMMARIZECOLUMNS (
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name],
"Has no sales", [HasNoSales ISEMPTY]
),
[Has no sales] = TRUE ()
)
ORDER BY
'Date'[Year],
'Date'[Month Number]
该度量做出了与以前相同的假设。我们希望ISEMPTY产生一个更好的执行计划。不幸的是，事实并非如此。
速度上的差别是无关紧要的。在xmSQL查询中有一个小的区别，但这不足以提高整体速度。第三个xmSQL查询显示引擎没有计算行数，因为检查是否有行就足够了:
SELECT
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name]
FROM 'Sales'
LEFT OUTER JOIN 'Date'
ON 'Sales'[Order Date]='Date'[Date]
LEFT OUTER JOIN 'Product'
ON 'Sales'[ProductKey]='Product'[ProductKey];
从xmSQL中消失的行计数。由于在VertiPaq中压缩数据的方式，计算表中的行非常快。它是如此之快，以至于是否执行计数之间没有明显的差异。但是，对于不同的数据分布或不同类型的压缩，计算行数的时间可能会变得相关。因此，与前两个版本相比，我们更喜欢第三个版本，因为它的实现产生了稍微简单一些的查询计划。
最后三个版本的度量使用不同的算法。我们的想法是利用DAX中的集合函数来检查它们的性能是比我们使用的基本函数更好还是更差。正如我们即将发现的那样，它们几乎总是更糟。然而，在做出任何决定之前，我们总是需要测试不同实现的性能。
测试HasNoSales INTERSECT
第四种方法使用了不同的算法。我们已经知道ISEMPTY是一个很好的函数，所以我们使用它来测试当前选择的产品集与销售的产品集相交是否产生空集。如果产品有销售额，它将包含在VALUES (product [ProductKey])中，当与VALUES (sales [ProductKey])相交时，它将成为结果的一部分。ISEMPTY检查INTERSECT的结果是否包含任何行:
DEFINE
MEASURE Sales[HasNoSales INTERSECT] =
ISEMPTY (
INTERSECT ( VALUES ( 'Product'[ProductKey] ), VALUES ( Sales[ProductKey] ) )
)
EVALUATE
FILTER (
SUMMARIZECOLUMNS (
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name],
"Has no sales", [HasNoSales INTERSECT]
),
[Has no sales] = TRUE ()
)
ORDER BY
'Date'[Year],
'Date'[Month Number]
我们知道公式引擎在DAX中计算集合函数。然而，也有可能查询计划显示了改进。不幸的是，事实并非如此。
存储引擎CPU时间显著增加，现在有4个xmSQL查询，而不是以前的3个。前两个xmSQL查询仍然检索产品和月份。第三个很有趣:
SELECT
'Product'[ProductKey],
'Product'[Product Name]
FROM 'Product';
该查询检索产品键和产品名称之间的关联。实际上，DAX查询按Product[Product Name]分组，但是度量正确地检索产品键集。因此，引擎将产品名称映射到产品键，增加了查询计划的复杂性。
最后一个查询与第三个版本非常相似，只是增加了检索年、月、产品名称和产品键的组合的复杂性——而HasNoSales ISEMPTY中使用的查询不需要产品键:
SELECT
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name],
'Sales'[ProductKey]
FROM 'Sales'
LEFT OUTER JOIN 'Date'
ON 'Sales'[Order Date]='Date'[Date]
LEFT OUTER JOIN 'Product'
ON 'Sales'[ProductKey]='Product'[ProductKey];
这个微小的差异足以增加整个查询的执行时间。公式引擎查询计划与以前的版本非常接近。
从分析中可以清楚地看出，集合函数的问题在于我们必须在代码中包含对Product[ProductKey]的引用。使用前三个公式，度量的DAX代码不依赖于特定的列。因此，优化器只使用SUMMARIZECOLUMNS函数中提供的列构建存储引擎查询。集合函数通过强制引擎检索产品键来增加复杂性。查询中的SUMMARIZECOLUMNS不太可能使用ProductKey(用户应该在可视化中使用该列)。因此，无论如何，基于集合函数的度量都应该是比较慢的。
测试HasNoSales EXCEPT
第五小节是前一小节的轻微变化。区别只是在使用EXCEPT而不是INTERSECT，这需要我们否定ISEMPTY:
DEFINE
MEASURE Sales[HasNoSales EXCEPT] =
NOT ISEMPTY (
EXCEPT ( VALUES ( 'Product'[ProductKey] ), VALUES ( Sales[ProductKey] ) )
)
EVALUATE
FILTER (
SUMMARIZECOLUMNS (
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name],
"Has no sales", [HasNoSales EXCEPT]
),
[Has no sales] = TRUE ()
)
ORDER BY
'Date'[Year],
'Date'[Month Number]
除了用于处理两个VALUES函数的结果的函数之外，这个公式与前一个公式几乎相同。因此，我们期望看到类似水平的表现。
xmSQL查询和公式引擎查询计划与使用INTERSECT的前一个版本几乎相同。因此，服务器计时基本上是相同的。
测试HasNoSales SELECTEDVALUE
要分析的最后一个度量使用IN操作符而不是set函数。它假设外部查询只计算一个产品的结果——也就是说，外部SUMMARIZECOLUMNS在产品级别进行分组:
DEFINE
MEASURE Sales[HasNoSales SELECTEDVALUE] =
NOT ( SELECTEDVALUE ( 'Product'[ProductKey] ) IN VALUES ( Sales[ProductKey] ) )
EVALUATE
FILTER (
SUMMARIZECOLUMNS (
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name],
"Has no sales", [HasNoSales SELECTEDVALUE]
),
[Has no sales] = TRUE ()
)
ORDER BY
'Date'[Year],
'Date'[Month Number]
尽管它看起来很接近以前的版本，但它的性能很糟糕。
存储引擎CPU飙升至45,672毫秒。原因是主xmSQL查询现在包含一个回调:
SELECT
'Date'[Year],
'Date'[Month Number],
'Product'[Product Name]
FROM 'Sales'
LEFT OUTER JOIN 'Date'
ON 'Sales'[Order Date]='Date'[Date]
LEFT OUTER JOIN 'Product'
ON 'Sales'[ProductKey]='Product'[ProductKey]
WHERE
[CallbackDataID ( VALUES ( Sales[ProductKey] ) ) ] ( PFDATAID ( 'Product'[Product Name] ) , PFDATAID ( 'Sales'[ProductKey] ) ) ;
这一度量非常缓慢，不值得进一步研究。
结论
是时候得出一些结论了。我们在表格中总结了我们的发现，突出了我们最喜欢的公式:
HasNoSales ISEMPTY似乎比HasNoSales COUNTROWS慢一点。但是，请注意，这种差异是无关紧要的，因为它远远低于不同执行之间的标准变化。ISEMPTY版本显示了一个比COUNTROWS版本稍微简单的xmSQL查询，这解释了我们的选择。
本文的重要结论并不是ISEMPTY版本是最好的。结论是，在选择一种算法之前，必须进行广泛的测试。在我们的算法组合中，最佳选项和最差选项之间的比率约为40，这意味着如果我们盲目地选择最差选项，我们可能会使用40倍于选择最佳选项时使用的CPU功率。
此外，这些是我们在演示数据库中的发现。如果由于大小、数据分布、压缩级别等原因，您在自己的模型上发现不同的结果，我们不会感到惊讶。当性能至关重要时，需要在使用度量进行生产之前执行大量测试，无论看起来多么简单。
