Início / dba / Perguntas / 252661
Accepted
Gravitate
Asked: 2019-11-07 05:35:06 +0800 CST

O SQL Server só executa cálculos em uma lista SELECT uma vez?

  • 772

Tome o seguinte exemplo:

SELECT <CalculationA> As ColA,
       <CalculationB> As ColB,
       <CalculationA> + <CalculationB> As ColC
FROM TableA

CalculationA e CalculationB, cada um, seriam calculados duas vezes?
Ou o otimizador seria inteligente o suficiente para calculá-los uma vez e usar o resultado duas vezes?

Eu gostaria de realizar um teste para ver o resultado por mim mesmo, porém, não tenho certeza de como eu poderia verificar algo assim.

Minha suposição é que ele realizaria o cálculo duas vezes.
Nesse caso, dependendo dos cálculos envolvidos, seria melhor usar uma tabela derivada ou uma visualização aninhada? Considere o seguinte:

SELECT TableB.ColA,
       TableB.ColB,
       TableB.ColA + TableB.ColB AS ColC,
FROM(    
      SELECT <CalculationA> As ColA,
             <CalculationB> As ColB
      FROM TableA
    ) As TableB

Nesse caso, eu esperaria que os cálculos fossem realizados apenas uma vez?

Por favor, alguém pode confirmar ou refutar minhas suposições? Ou me instruir sobre como testar algo assim para mim?

Obrigado.

sql-server view

4 respostas

  1. Best Answer

    A maioria das informações que você precisará estará no plano de execução (e no XML do plano).

    Faça esta consulta:

    SELECT COUNT(val) As ColA,
       COUNT(val2) As ColB,
       COUNT(val) +  COUNT(val2) As ColC
FROM dbo.TableA;

    O plano de execução (aberto com sentryone plan explorer ) mostra por quais etapas ele passou:

    insira a descrição da imagem aqui

    Com a agregação de fluxo agregando os valores para EXPR1005 e EXPR1006

    insira a descrição da imagem aqui

    Se quisermos saber o que são, podemos obter as informações exatas sobre essas expressões do XML do plano de consulta:

    <ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="COUNT([Database].[dbo].[TableA].[val])">
<Aggregate AggType="COUNT_BIG" Distinct="false">

    Com a primeira computação escalar de computação ColA & ColB:

    insira a descrição da imagem aqui

    E o último escalar de computação sendo uma simples adição:

    insira a descrição da imagem aqui

    Isso é lê-lo à medida que os dados fluem, em teoria, você deve lê-lo da esquerda para a direita se estiver passando pela execução lógica.

    Nesse caso, EXPR1004 está chamando as outras expressões, EXPR1002& EXPR1003. Por sua vez, estes estão chamando EXPR1005& EXPR1006.

    CalculationA e CalculationB, cada um, seriam calculados duas vezes? Ou o otimizador seria inteligente o suficiente para calculá-los uma vez e usar o resultado duas vezes?

    Testes anteriores mostram que neste caso ColCé simplificado como uma adição dos cálculos que são definidos como ColA& ColB.

    Como resultado, ColA& ColBsão calculados apenas uma vez.

    Agrupando por 200 valores distintos

    Se estivermos agrupando por 200 valores distintos (val3) o mesmo é mostrado:

    SET STATISTICS IO, TIME ON;
SELECT SUM(val) As ColA,
       SUM(val2) As ColB,
       SUM(val) +  SUM(val2) As ColC
FROM dbo.TableA
GROUP BY val3;

    Agregando até esses 200 valores distintos emval3

    insira a descrição da imagem aqui

    realizando as somas em val & val2 e, em seguida, somando-as para ColC:

    insira a descrição da imagem aqui

    Mesmo se estivermos agrupando todos, exceto um valor não exclusivo, a mesma adição deve ser vista para o escalar de computação.

    Adicionando uma função a ColA e ColB

    Mesmo se mudarmos a consulta para isso:

    SET STATISTICS IO, TIME ON;
SELECT ABS(SUM(val)) As ColA,
       ABS(SUM(val2)) As ColB,
       SUM(val) +  SUM(val2) As ColC
FROM dbo.TableA

    As agregações ainda não serão calculadas duas vezes, estamos simplesmente adicionando a ABS()função ao conjunto de resultados da agregação, que é uma linha:

    insira a descrição da imagem aqui

    insira a descrição da imagem aqui

    É claro que executar SUM(ABS(ColA)& SUM(ABS(ColB))fará com que o otimizador não possa usar a mesma expressão para calcular ColC.

    Se você quiser se aprofundar quando isso acontecer, eu o levaria ao Query Optimizer Deep Dive - Parte 1 (até a Parte 4) de Paul White.

    Outra maneira de se aprofundar nas fases de execução da consulta é adicionando estas dicas:

    OPTION 
(
    RECOMPILE, 
    QUERYTRACEON 3604,
    QUERYTRACEON 8605
);

    Isso irá expor a árvore de entrada conforme criada pelo otimizador.

    A soma dos dois valores calculados anteriores para obter ColCé então traduzida para:

    AncOp_PrjEl COL: Expr1004 

ScaOp_Arithmetic x_aopAdd

    ScaOp_Identifier COL: Expr1002 

    ScaOp_Identifier COL: Expr1003

    Esta informação já está presente na Árvore de Entrada , mesmo antes da fase de simplificação, mostrando que o otimizador sabe imediatamente que não precisa realizar o mesmo cálculo duas vezes.

    • 41

  2. Se a primeira parte do cálculo for um cálculo real ( Col1 + Col2) e não uma função, os cálculos individuais serão executados para cada etapa de "cálculo".

    SELECT <CalculationA> As ColA,
       <CalculationB> As ColB,
       <CalculationA> + <CalculationB> As ColC
FROM TableA

    Se substituirmos <CalculationA>sua declaração por um cálculo válido usando ColAe ColBde uma tabela e repetirmos isso para cada <CalculationB>,...etapa subsequente, a tarefa real de calcular o resultado será executada para cada etapa individualmente.

    Para reproduzir minha instrução, cole os seguintes trechos de código no SQL Server Management Studio e execute. Certifique-se de ter ativado a opção Incluir Plano de Execução Real .

    Incluir Plano de Execução Real

    Ele cria um banco de dados, uma tabela, preenche a tabela e realiza um cálculo que produz um plano de execução.

    CRIAR BANCO DE DADOS Q252661
VAI
USAR Q252661
VAI

CRIAR TABELA dbo.Q252661_TabelaA (
    ColA INT,
    ColB INT,
    ColC INT,
    Frio INT)   

VAI

INSERIR EM Q252661_Tabela A
(
    Cola,
    ColB,
    ColC,
    Resfriado
)
VALORES
(
    1,
    2,
    3,
    4
),(
    2,
    4,
    8,
    16
)

VAI
SELECT ColA + ColB AS ColA,
        ColC + Frio AS ColB,
        ColA + ColB + ColC + Frio AS ColC
DE Q252661_Tabela A

VAI

    A consulta será executada e produzirá um plano de execução gráfico semelhante ao seguinte:

    Plano de Execução Gráfica de Valores ADDing Plano de Execução Gráfica de Valores ADDing

    Como na resposta de Randi, vamos nos concentrar no operador Compute Scalar .

    Se você clicar no Plano de Execução de Consulta no SSMS e clicar com o botão direito do mouse para mostrar o plano real:

    Mostrar XML do plano de execução...

    .. você encontrará o seguinte XML (com foco na parte Compute Scalar ):

              <ComputeScalar>
            <DefinedValues>
              <DefinedValue>
                <ColumnReference Column="Expr1003" />
                <ScalarOperator ScalarString="[Q252661].[dbo].[Q252661_TableA].[ColA]+[Q252661].[dbo].[Q252661_TableA].[ColB]">
                  <Arithmetic Operation="ADD">
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColA" />
                      </Identifier>
                    </ScalarOperator>
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColB" />
                      </Identifier>
                    </ScalarOperator>
                  </Arithmetic>
                </ScalarOperator>
              </DefinedValue>
              <DefinedValue>
                <ColumnReference Column="Expr1004" />
                <ScalarOperator ScalarString="[Q252661].[dbo].[Q252661_TableA].[ColC]+[Q252661].[dbo].[Q252661_TableA].[ColD]">
                  <Arithmetic Operation="ADD">
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColC" />
                      </Identifier>
                    </ScalarOperator>
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColD" />
                      </Identifier>
                    </ScalarOperator>
                  </Arithmetic>
                </ScalarOperator>
              </DefinedValue>
              <DefinedValue>
                <ColumnReference Column="Expr1005" />
                <ScalarOperator ScalarString="[Q252661].[dbo].[Q252661_TableA].[ColA]+[Q252661].[dbo].[Q252661_TableA].[ColB]+[Q252661].[dbo].[Q252661_TableA].[ColC]+[Q252661].[dbo].[Q252661_TableA].[ColD]">
                  <Arithmetic Operation="ADD">
                    <ScalarOperator>
                      <Arithmetic Operation="ADD">
                        <ScalarOperator>
                          <Arithmetic Operation="ADD">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColA" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColB" />
                              </Identifier>
                            </ScalarOperator>
                          </Arithmetic>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColC" />
                          </Identifier>
                        </ScalarOperator>
                      </Arithmetic>
                    </ScalarOperator>
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColD" />
                      </Identifier>
                    </ScalarOperator>
                  </Arithmetic>
                </ScalarOperator>
              </DefinedValue>
            </DefinedValues>
            <RelOp AvgRowSize="23" EstimateCPU="8.07E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="Table Scan" NodeId="1" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2">
              <OutputList>
                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColA" />
                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColB" />
                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColC" />
                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColD" />
              </OutputList>
              <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColA" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColB" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColC" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColD" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" IndexKind="Heap" Storage="RowStore" />
              </TableScan>
            </RelOp>
          </ComputeScalar>

    Assim, cada cálculo individual é executado repetidamente no caso de os valores serem recuperados de uma tabela real. O seguinte fragmento XML é do resumo acima:

                    <ScalarOperator ScalarString="[Q252661].[dbo].[Q252661_TableA].[ColA]+[Q252661].[dbo].[Q252661_TableA].[ColB]">
                  <Arithmetic Operation="ADD">

    Há cinco <Arithmetic Operation="ADD">etapas no plano de execução.

    Respondendo sua pergunta

    CalculationA e CalculationB, cada um, seriam calculados duas vezes?

    Sim, se os cálculos forem somas reais de colunas conforme o exemplo. O último cálculo seria a soma de CalculationA + CalculationB.

    Ou o otimizador seria inteligente o suficiente para calculá-los uma vez e usar o resultado duas vezes?

    Depende do que você está calculando. - Neste exemplo: sim. - Na resposta de Randi: não.

    Minha suposição é que ele realizaria o cálculo duas vezes.

    Você está certo para certos cálculos.

    Nesse caso, dependendo dos cálculos envolvidos, seria melhor usar uma tabela derivada ou uma visualização aninhada?

    Correto.

    Depois de terminar, você pode descartar seu banco de dados novamente:

    USE [master]
GO
DROP DATABASE Q252661
    • 5

  3. Como já existem boas respostas para a pergunta, vou me concentrar no aspecto DRY (DRY = não se repita).

    Eu me acostumei a usar CROSS APPLY, se eu tiver que fazer os mesmos cálculos na mesma consulta várias vezes (não esqueça GROUP BY / WHERE / ORDER BY, onde os mesmos cálculos tendem a ocorrer repetidamente).

    SELECT calc.ColA,
       calc.ColB,
       calc.ColA + calc.ColB AS ColC
  FROM TableA AS a
 CROSS APPLY (SELECT a.Org_A * 100 AS ColA
                   , a.Org_B / 100 AS ColB
             ) AS calc
 WHERE calc.ColB = @whatever
 ORDER BY calc.ColA

    Quando um cálculo depende de outro, não há motivo para não usar várias CROSS APPLYchamadas para calcular os resultados provisórios (o mesmo, quando você precisa usar o resultado final no WHERE/ ORDER BY)

    SELECT calc1.ColA,
       calc2.ColB,
       calc3.ColC
  FROM TableA AS a
 CROSS APPLY (SELECT a.Org_A    * 100        AS ColA) AS calc1
 CROSS APPLY (SELECT calc1.ColA * 100        AS ColB) AS calc2
 CROSS APPLY (SELECT calc1.ColA + calc2.ColB AS ColC) AS calc3
 WHERE calc.ColB = @whatever
 ORDER BY calc.ColA, calc3.ColC

    O ponto principal para fazer isso é que você tem que modificar/corrigir apenas uma linha de código quando encontrar um bug ou tiver que alterar algo em vez de várias ocorrências e não correr o risco de ter várias (um pouco diferente, já que você esqueceu de alterar uma ) do mesmo cálculo.

    PS: em relação à legibilidade CROSS APPLY, geralmente vencerá contra várias sub-seleções aninhadas (ou CTEs), principalmente quando os cálculos usam colunas de tabelas de origem diferentes ou você tem resultados provisórios.

    • 0

  4. Se ambas as Expressões forem determinísticas, o otimizador reutilizará os resultados calculados anteriormente e simplesmente os adicionará. Se qualquer expressão for não determinística, essa expressão DEVE ser recalculada.

    • -2

relate perguntas