Otimizando expressões DAX duplicadas usando variáveis

 

Bom pessoal estamos de volta com mais um artigo.  Ele descreve como usar variáveis para otimizar o desempenho de expressões DAX contendo várias instâncias da mesma medida ou a mesma subexpressão.

No DAX, as variáveis devem ser amplamente utilizadas como forma de melhorar a legibilidade de uma expressão. No entanto, usar variáveis corretamente também é útil para o desempenho da consulta. Na verdade, isso sugere um melhor plano de execução caso a mesma variável ou subexpressão seja referenciada mais de uma vez no mesmo contexto de filtro.

Por exemplo, considere esta implementação simples do padrão de Segmentação Dinâmica em um modelo de dados do Power BI.

A tabela Segmentos define intervalos para três segmentos que serão utilizados para agrupar os clientes com base em seus gastos.

O padrão de segmentação dinâmica original foi escrito antes que as variáveis estivessem disponíveis. Assim, a primeira implementação da medida segmentada de vendas do cliente é a seguinte:


Sales[Customers Sales exp Optimized] := 
IF (
    ISCROSSFILTERED ( Segments ),
    SUMX (
        Segments,
        SUMX (
            Customer,
            VAR SalesAmount = CALCULATE ( SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) )
            RETURN
                IF (
                    SalesAmount >= Segments[MinValue]
                        && SalesAmount < Segments[MaxValue],
                    SalesAmount
                )
        )
    ),
    [Sales Amount]
)

 

 

O relatório obtido usando a medida Customer Sales mostra resultados coerentes e bem distribuídos.

No entanto, existem três referências à medida da quantidade de vendas (Sales Amount) – dentro dos mesmos contextos de filtro e linha – na iteração SUMX da tabela do Cliente. Em um mundo ideal, o mecanismo de consulta deve ser capaz de identificar que o resultado das três referências sempre será o mesmo para um determinado cliente. Isso pode ser verdade no futuro, mas este não é o caso em janeiro de 2018.

Usando o DAX Studio, é possível ver as consultas do mecanismo de armazenamento geradas pela medida de vendas do cliente no relatório anterior.

As duas linhas destacadas mostram um cache similar para cada consulta de mecanismo de armazenamento semelhantes. Ambos têm o mesmo número de linhas e o mesmo tamanho, e a parte inicial da consulta parece idêntica. No entanto, há diferenças nas duas consultas do mecanismo de armazenamento. A primeira consulta é a seguinte.

WITH
    $Expr0 := ( PFCAST ( 'Sales'[Quantity] AS  INT ) 
                * PFCAST ( 'Sales'[Net Price] AS  INT )  ) 
SELECT
    'Customer'[CustomerKey],
    SUM ( @$Expr0 )
FROM 'Sales'
    LEFT OUTER JOIN 'Customer' ON
        'Sales'[CustomerKey]='Customer'[CustomerKey]; 

A segunda consulta do motor apresenta uma condição de filtro adicional.

WITH
    $Expr0 := ( PFCAST ( 'Sales'[Quantity] AS  INT ) 
                * PFCAST ( 'Sales'[Net Price] AS  INT )  ) 
SELECT
    'Customer'[CustomerKey],
    SUM ( @$Expr0 )
FROM 'Sales'
    LEFT OUTER JOIN 'Customer' ON
        'Sales'[CustomerKey]='Customer'[CustomerKey]
WHERE
    'Customer'[CustomerKey] IN ( 
        13407, 9266, 17548, 7787, 3646, 17055, 12914, 18041, 7294, 
        3153..[18869 total values, not all displayed] 
    ) ;

Lembre-se de que a consulta do mecanismo de armazenamento mostra um tamanho de linha estimado para cada datacache. O número real está disponível no evento Spool_Iterator do plano de consulta física e corresponde a 18.869, que é o número real de clientes.

O resultado de ambas as imagens é idêntico. Há duas consultas feitas para o mecanismo de armazenamento para o mesmo resultado, porque o otimizador de consulta assume possíveis resultados diferentes para os dois argumentos da função IF. Uma análise mais profunda da consulta semântica deve permitir que o otimizador de consulta faça um trabalho melhor, mas isso não acontece, ainda.  Usando variáveis, é possível avaliar a medida do valor de vendas(Sales Amount) apenas uma vez para cada cliente, referenciando o valor da variável nos diferentes argumentos da função IF.

O código seguinte é a versão otimizada da medida de vendas do cliente(Customer Sales).

 

Sales[Customers Sales Optimized] := 
IF (
    ISCROSSFILTERED ( Segments ),
    SUMX (
        Segments,
        SUMX (
            Customer,
            VAR SalesAmount = [Sales Amount]
            RETURN
                IF (
                    SalesAmount &gt;= Segments[MinValue]
                        &amp;&amp; SalesAmount &lt; Segments[MaxValue],
                    SalesAmount
                )
        )
    ),
    [Sales Amount]
)

Usando esta medida, o plano de consulta física é mais curto e há um único datacache materializando o resultado do valor de vendas para cada cliente.

A linha destacada é o único datacache usado para calcular a quantidade de vendas(Sales Amount) para todos os clientes e não contém nenhuma condição de filtro.


WITH
    $Expr0 := ( PFCAST ( 'Sales'[Quantity] AS  INT ) 
                * PFCAST ( 'Sales'[Net Price] AS  INT )  ) 
SELECT
    'Customer'[CustomerKey],
    SUM ( @$Expr0 )
FROM 'Sales'
    LEFT OUTER JOIN 'Customer' ON
        'Sales'[CustomerKey]='Customer'[CustomerKey]; 

A menor materialização melhora o desempenho da consulta. Isso é pouco mensurável nos pequenos modelos de dados – disponíveis para download no final do artigo, mas a diferença é visível para modelos de dados com um grande número de clientes.

A mesma otimização de desempenho é possível quando a mesma subexpressão é avaliada várias vezes no mesmo contexto de avaliação. Por exemplo, esta é uma versão de vendas do cliente que não inclui referências de medida e repete o mesmo CALCULO várias vezes em vez disso.

 


Sales[Customers Sales exp] := 
IF (
    ISCROSSFILTERED ( Segments ),
    SUMX (
        Segments,
        SUMX (
            Customer,
            IF (
                CALCULATE ( SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) )
                    &gt;= Segments[MinValue]
                    &amp;&amp; CALCULATE ( SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) )
                         Segments[MaxValue],
                CALCULATE ( SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) )
            )
        )
    ),
    [Sales Amount]
)

A versão otimizada apresenta a mesma abordagem mostrada anteriormente. Há uma única avaliação da instrução CALCULATE, e seu resultado é reutilizado várias vezes referenciando a variável SalesAmount.


Sales[Customers Sales exp Optimized] := 
IF (
    ISCROSSFILTERED ( Segments ),
    SUMX (
        Segments,
        SUMX (
            Customer,
            VAR SalesAmount = CALCULATE ( SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) )
            RETURN
                IF (
                    SalesAmount &gt;= Segments[MinValue]
                        &amp;&amp; SalesAmount &lt; Segments[MaxValue],
                    SalesAmount
                )
        )
    ),
    [Sales Amount]
)

No DAX, as variáveis proporcionam uma melhor legibilidade do código e facilitam a geração de um plano de consulta mais otimizado, reduzindo as chances de computar a mesma subexpressão várias vezes.

Arquivos de exemplo:

Otimizando consultas DAX com variáveis (109 downloads)

 

 

 

fonte: sqlbi

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *