如何在Daxstudio中检查哪个DAX查询具有更好的性能? [英] How to check in Daxstudio which DAX query has better performance?

问题描述

如何使用

和查询计划:

查询2号.

  EVALUATE加列(值(ThisTable [Product])，"FilterLookup"，MAXX(FILTER(SearchTable，SearchTable [Product] = ThisTable [Product])，SearchTable [类别])) 

统计:

查询计划:

问题与:

在不相关表中进行DAX查找第一个非空白值

您可以下载带有示例数据的pbix文件:

DAX查找前1个值.pbix

解决方案

您无法从DAX Studio真正看出来，当数据集如此小时，但是在大多数情况下，具有最简单查询计划的查询将是最快的.查询#1就是这种情况，它的确是您情况下最快的查询(忽略所有〜20 ms以下的时间测量-由于数据集太小，因此不可靠，这是不可靠的).

此外，我想补充说明的是，以下查询应该提供相同的结果，并且比两个查询都更简单，查询计划甚至更快:

  ADDCOLUMNS(这张表，"FilterLookup"，LOOKUPVALUE(SearchTable [Category]，SearchTable [Product]，ThisTable [Product])) 

编辑:我没有注意到"apple"在 SearchTable [Product] 列中出现了两次.这将导致上述对 LOOKUPVALUE(...)的调用失败，因为它无法为 SearchTable [Category] ​​找到明确的值.

How to check which of the two DAX queries has a better performance using Daxstudio. In the example the queries return exactly the same results. However the stats differ showing not clear hints. What usuful information can we grasp from the comparison of the two queries?

Comparison query stats summary:

+-------------------------+------------+---------+---------+
|                         |            | Query 1 | Query 2 |
+-------------------------+------------+---------+---------+
| Server timings          | Total      |       7 |       5 |
|                         | SE CPU     |       0 |       0 |
|                         | FE         |       6 |       4 |
|                         | SE         |       1 |       1 |
|                         | SE Queries |       3 |       2 |
|                         | SE Cashe   |       0 |       0 |
+-------------------------+------------+---------+---------+
| Query plan, no of lines | physical   |       7 |      28 |
|                         | logical    |      13 |       9 |
+-------------------------+------------+---------+---------+

• The second query is quicker but has a bushy and longer plan. 2 scans.
• The first query has longer server timings but cleaner and shorter query plan. 3 scans.

So the server timings favor the second query but its complex query plan raises concern. Knowing the stats and query plans what can we expect if the SearchTable had milion of rows? Shouldn't we like simpler query plans, since DAX optimization may change in the future in their favor?

Sample data. We have two tables SearchTable and ThisTable:

SearchTable = 
DATATABLE (
    "Category", STRING,
    "Product", STRING,
    "Amount", INTEGER,
    {
        { BLANK ()      , "apple"       , 1 },
        { "Fruits"      , "apple"       , 1 },  -- watch out for multiple apples!
        { "Yummy Fruits", "apple"       , 2 },
        { "Fruits"      , "banana"      , 4 },
        { "Fruits"      , "wolfberry"   , 5 },
        { "Fruits"      , "cherry"      , 3 },
        { "Vegetables"  , "carrot"      , 3 },
        { "Vegetables"  , "potato"      , 1 },
        { "Vegetables"  , "onion"       , 7 },
        { "Fruits"      , "cherry"      , 3 }        
    }
)
---
ThisTable = 
DATATABLE (
    "Product", STRING,
    {
        { "apple" },
        { "banana" },
        { "blackberry" },
        { "carrot" },
        { "cherry" },
        { "onion " },
        { "potato" },
        { "watermelon" },
        { "wolfberry" }
    }
)

Query no 1.

EVALUATE
ADDCOLUMNS (
    VALUES ( ThisTable[Product] ),
    "FilterLookup",
    VAR LookupKey = ThisTable[Product]
    RETURN
        CALCULATE ( MAX ( SearchTable[Category] ), SearchTable[Product] = LookupKey )
)

The query has these stats:

And query plan:

Query no 2.

EVALUATE
ADDCOLUMNS (
    VALUES ( ThisTable[Product] ),
    "FilterLookup", MAXX (
        FILTER ( SearchTable, SearchTable[Product] = ThisTable[Product] ),
        SearchTable[Category]
    )
)

Stats:

The query plan:

The question is related to:

DAX lookup first non blank value in unrelated table

You can download a pbix file with sample data:

DAX lookup top 1 value.pbix

解决方案

You can't really tell from DAX Studio, when the data sets are so small, but in most cases, the query with the least complex query plan will be fastest. This is the case for query #1 and that will indeed be the fastest query in your case (ignore all time measurements below ~20 ms - as it's unreliable, because the data sets are so small).

Also, I'd like to add that the following query should provide the same result, and be even faster, with an even simpler query plan than both of your queries:

ADDCOLUMNS(
    ThisTable,
    "FilterLookup",
        LOOKUPVALUE(SearchTable[Category], SearchTable[Product], ThisTable[Product])
)

Edit: I didn't notice that "apple" appears twice in the SearchTable[Product] column. This will cause the above call to LOOKUPVALUE(...) to fail, since it won't be able to find an unambiguous value for SearchTable[Category].

这篇关于如何在Daxstudio中检查哪个DAX查询具有更好的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！