DAX-查询计划,有四个:
- DAX VertiPaq Logical Plan
- DAX VertiPaq Physical Plan
- DAX DirectQuery Algebrizer Tree
- DAX DirectQuery Logical Plan
运算符类型:
Plan Type
|
Operator Type
|
Description
|
Logical Plan
|
ScaLogOp
|
Scalar Logical Operator
Outputs a scalar value of type numeric, string, Boolean, etc.
|
RelLogOp
|
Relational Logical Operator
Outputs a table of columns and rows.
| |
Physical Plan
|
LookupPhyOp
|
Lookup Physical Operator
Given a current row as input, calculates and returns a scalar value.
|
IterPhyOp
|
Iterator Physical Operator
Given a current row as an optional input, returns a sequence of rows.
|
实际上,物理计划还有第三个: SPOOL,它是执行子树的内存结果节点。
共同属性:
1, LOGICAL PLAN - SCALOGOP
DEPENDONCOLS:
它是逻辑运算符依靠的查询树左边的列。用于确认MEASURE是否正确依靠行环境。
DATA TYPE:
6个类型,加 BLANK.
DOMINANTVALUE:
如果它是NONE,就是 DENSE,不是, 就是 SPARSE。类似MDX 的 BLOCK AND CELL-BY-CELL MODE。
2,LOGICAL PLAN - RELLOGOP
DEPENDON\COLS:
同上。
RANGE OF COLUMN NUMBERS:
DAX 使用 BEGIN-COLUMN-NAME END-COLUMN-NAME。
REQUIREDCOLS:
它是 DEPENDONCOLS 和 RANGE COLUMNS 的合集。
3,PHYSICAL PLAN - LOOKUPPHYOP
LOOKUPCOLS:
遍历器提供的列用于计算单值。
DATA TYPE:
同上。
4,PHYSICAL PLAN - ITERPHYOP
LOOKUPCOLS:
同上。
ITERCOLS:
遍历器输出列。
一个特殊的属性: SPOOL_ITERONLY<>
它就是一个遍历器,从内存SPOOL中得到行。
VERTIPAQ 运算符
Logical Operators
|
Description
|
Example
|
Scan_Vertipaq
|
This RelLogOp is the foundation of all other Vertipaq logical operators. It represents a basic Vertipaq query that joins a root table with related tables following many-to-one relationships, determines which rows are retrieved by testing against Vertiscan predicates, and groups the resultset by output columns.
|
evaluate ‘Product’
|
GroupBy_Vertipaq
|
This RelLogOp renames columns and adds rollup columns to a Vertipaq query.
|
evaluate summarize(‘Product’, rollup(‘Product Category’[Product Category Name], ‘Product’[Product Name]))
|
Filter_Vertipaq
|
This RelLogOp adds a Verticalc predicate to a Vertipaq query.
|
evaluate filter('Product', right([Product Name], 4) = "Tire")
|
Sum_Vertipaq
|
This ScaLogOp adds a SUM aggregation to a Vertipaq query.
|
evaluate row("x", sum('Internet Sales'[Sales Amount]))
|
Min_Vertipaq
|
This ScaLogOp adds a MIN aggregation to a Vertipaq query.
|
evaluate row("x", min('Internet Sales'[Sales Amount]))
|
Max_Vertipaq
|
This ScaLogOp adds a MAX aggregation to a Vertipaq query.
|
evaluate row("x", max('Internet Sales'[Sales Amount]))
|
Count_Vertipaq
|
This ScaLogOp adds a COUNT aggregation to a Vertipaq query.
|
evaluate row("x", countrows('Internet Sales'))
|
DistinctCount_Vertipaq
|
This ScaLogOp adds a DISTINCTCOUNT aggregation to a Vertipaq query.
|
evaluate row("x", distinctcount('Internet Sales'[Due Date]))
|
Average_Vertipaq
|
This ScaLogOp adds an AVERAGE aggregation to a Vertipaq query.
|
evaluate row("x", average('Internet Sales'[Sales Amount]))
|
Stdev.S_Vertipaq
|
This ScaLogOp adds a STDEV.S aggregation to a Vertipaq query.
|
evaluate row("x", stdev.s('Internet Sales'[Sales Amount]))
|
Stdev.P_Vertipaq
|
This ScaLogOp adds a STDEV.P aggregation to a Vertipaq query.
|
evaluate row("x", stdev.p('Internet Sales'[Sales Amount]))
|
Var.S_Vertipaq
|
This ScaLogOp adds a VAR.S aggregation to a Vertipaq query.
|
evaluate row("x", var.s('Internet Sales'[Sales Amount]))
|
Var.P_Vertipaq
|
This ScaLogOp adds a VAR.P aggregation to a Vertipaq query.
|
evaluate row("x", var.p('Internet Sales'[Sales Amount]))
|
Physical Operators
|
|
|
VertipaqResult
|
This IterPhyOp iterates over the resultset returned by a Vertipaq query.
|
You can find this operator in the physical plan tree after running any of the above queries.
|
当执行简单的运算,速度最快,如果复杂的,
需要 DAX FE 的帮助,速度变慢。由于 FE 是单线程的,如果计算使用 VERTIPAQ ENGINE,多线程,批量处理,快很多。
因此,查询计划如果使用VERTIPAQ 运算符,是非常好的。
几点注意:
1,SCAN_VERTIPAQ是其他的基础
2,SPOOL 可以被查询,通过: SPOOL LOOKUPPHYOP, SPOOL_ITERONLY, SPOOL_LOOKUPONLY, SPOOL_SLICEINDEX ITERPHYOPS.
一个非常重要的特征就是: #RECORDS, VERTIPAQ ENGINE多少记录返回 ,决定计划优劣的重要标志。
3,如果使用 FE,物理计划树并不体现。
4,SCAN_VERTIPAQ的特殊属性,主要包括:TABLE, BLANK ROW, JOINCOLS, SEMIJOINCOLS,
最后,比较两个查询:
A:
evaluate
addcolumns(
'Date',
"x",
sumx(
filter('Internet Sales', [Order Date] <= [Date]),
[Sales Amount]
)
)
B:
evaluate
addcolumns(
'Date',
"x",
calculate(
sum('Internet Sales'[Sales Amount]),
'Internet Sales'[Order Date] <= earlier([Date]),
all('Date')
)
)
那一个好呢?
如果检查查询计划,就会发现:
A: FE 使用 SUMX AND FILTER;
B: FE 仅用 FILTER,SUMX 由 VERTIPAQ ENGINE 的 SUM 完成。
毫无疑问, 计划 B 好于 A.