Início / dba / Perguntas / 162174
Accepted
Pரதீப்
Asked: 2017-01-26 03:30:08 +0800 CST

Agregação em Outer Apply vs Left Join vs Tabela Derivada

  • 772

Considere a seguinte configuração. Existem três tabelas envolvidas #CCP_DETAILS_TEMP, PeriodeACTUALS_DETAILS

#CCP_DETAILS_TEMPterá 50000registros, ACTUALS_DETAILSpode ter 5000000registros e periodtabela terá 2000registros

Detalhes do índice:

CREATE UNIQUE CLUSTERED INDEX IX_CCP_DETAILS_TEMP
  ON #CCP_DETAILS_TEMP (CCP_DETAILS_SID)

CREATE NONCLUSTERED INDEX IXN_ACTUALS_DETAILS_PERIOD_SID_RS_MODEL_SID_CCP_DETAILS_SID_QUANTITY_INCLUSION
  ON ACTUALS_DETAILS (PERIOD_SID, CCP_DETAILS_SID, RS_MODEL_SID, QUANTITY_INCLUSION)
  INCLUDE( SALES, QUANTITY, DISCOUNT) 

CREATE UNIQUE CLUSTERED INDEX IX_PERIOD
  ON PERIOD (PERIOD_SID)

Eu tenho um requisito para o qual escrevi três maneiras diferentes de alcançar o resultado. Agora quero saber qual é melhor.

Todas as três consultas estão sendo executadas mais ou menos no mesmo tempo. Preciso de alguns conselhos de especialistas sobre qual deles terá melhor desempenho. Existe alguma desvantagem em qualquer uma das abordagens

Abordagem 1: Outer Apply

Tempo gasto: 4615 Milli Seconds

SELECT c.CCP_DETAILS_SID,
       A.PERIOD_SID,
       SALES,
       QUANTITY
FROM   #CCP_DETAILS_TEMP c
       CROSS JOIN (SELECT PERIOD_SID
                   FROM   BPIGTN_GAL_APP_DEV_ARM..PERIOD
                   WHERE  PERIOD_SID BETWEEN 577 AND 624)A
       OUTER apply (SELECT Sum(SALES),
                           Sum(QUANTITY)
                    FROM   [DBO].[ACTUALS_DETAILS] ad
                    WHERE  a.PERIOD_SID = ad.PERIOD_SID
                           AND ad.CCP_DETAILS_SID = c.CCP_DETAILS_SID
                           AND QUANTITY_INCLUSION = 'Y') oa (sales, quantity)

insira a descrição da imagem aqui

Estatísticas da consulta:

Tabela 'PERÍODO'. Contagem de varredura 1, leituras lógicas 2, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela '#CCP_DETAILS_TEMP'. Contagem de varredura 16, leituras lógicas 688, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'Mesa de trabalho'. Contagem de varredura 16, leituras lógicas 807232, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'ACTUALS_DETAILS'. Contagem de varredura 1200000, leituras lógicas 3859053, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'Mesa de trabalho'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'Mesa de trabalho'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tempos de execução do SQL Server: tempo de CPU = 36796 ms, tempo decorrido = 4615 ms.

Tempos de execução do SQL Server: tempo de CPU = 0 ms, tempo decorrido = 0 ms.

Abordagem 2: Left Join

Tempo gasto: 4293 Milli Seconds

SELECT c.CCP_DETAILS_SID,
       A.PERIOD_SID,
       Sum(SALES),
       Sum(QUANTITY)
FROM   #CCP_DETAILS_TEMP c
       CROSS JOIN (SELECT PERIOD_SID
                   FROM   BPIGTN_GAL_APP_DEV_ARM..PERIOD
                   WHERE  PERIOD_SID BETWEEN 577 AND 624) a
       LEFT JOIN [ACTUALS_DETAILS] ad
              ON a.PERIOD_SID = ad.PERIOD_SID
                 AND ad.CCP_DETAILS_SID = c.CCP_DETAILS_SID
                 AND QUANTITY_INCLUSION = 'Y'
GROUP  BY c.CCP_DETAILS_SID,
          A.PERIOD_SID

insira a descrição da imagem aqui

Estatísticas da consulta:

Tabela 'ACTUALS_DETAILS'. Contagem de varredura 17, leituras lógicas 37134, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'PERÍODO'. Contagem de varredura 1, leituras lógicas 2, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela '#CCP_DETAILS_TEMP'. Contagem de varredura 16, leituras lógicas 688, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'Mesa de trabalho'. Contagem de varredura 16, leituras lógicas 807232, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'Arquivo de trabalho'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'Mesa de trabalho'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tempos de execução do SQL Server: tempo de CPU = 7983 ms, tempo decorrido = 4293 ms.

Tempos de execução do SQL Server: tempo de CPU = 0 ms, tempo decorrido = 0 ms.

Abordagem 3: agregando a primeira e a junção à esquerda:

Tempo gasto: 4200 Milli Seconds

SELECT c.CCP_DETAILS_SID,
       A.PERIOD_SID,
       SALES,
       QUANTITY
FROM   #CCP_DETAILS_TEMP c
       CROSS JOIN (SELECT PERIOD_SID
                   FROM   BPIGTN_GAL_APP_DEV_ARM..PERIOD
                   WHERE  PERIOD_SID BETWEEN 577 AND 624) a
       LEFT JOIN (SELECT CCP_DETAILS_SID,
                         PERIOD_SID,
                         Sum(SALES)    SALES,
                         Sum(QUANTITY) QUANTITY
                  FROM   [ACTUALS_DETAILS] ad
                  WHERE  QUANTITY_INCLUSION = 'Y'
                  GROUP  BY CCP_DETAILS_SID,
                            PERIOD_SID) ad
              ON a.PERIOD_SID = ad.PERIOD_SID
                 AND ad.CCP_DETAILS_SID = c.CCP_DETAILS_SID

insira a descrição da imagem aqui

Estatísticas da consulta:

Tabela 'ACTUALS_DETAILS'. Contagem de varredura 17, leituras lógicas 37134, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'Mesa de trabalho'. Contagem de varredura 16, leituras lógicas 807232, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'Arquivo de trabalho'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'PERÍODO'. Contagem de varredura 1, leituras lógicas 2, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela '#CCP_DETAILS_TEMP'. Contagem de varredura 16, leituras lógicas 688, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'Mesa de trabalho'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tempos de execução do SQL Server: tempo de CPU = 7731 ms, tempo decorrido = 4200 ms.

Tempos de execução do SQL Server: tempo de CPU = 0 ms, tempo decorrido = 0 ms.

sql-server sql-server-2012

1 respostas

  1. Best Answer

    Para perguntas futuras, poste os planos de execução reais usando Paste The Plan . Acho que consegui fazer engenharia reversa de todos os detalhes relevantes usando as capturas de tela e sua STATISTICSsaída, mas posso ter entendido algumas coisas erradas. Parece que seus planos estão sendo executados com um DOP de 16, cerca de 50.000 linhas são retornadas de #CCP_DETAILS_TEMP, e 24 linhas são retornadas de PERIOD.

    Em todos os três planos de consulta, a junção entre #CCP_DETAILS_TEMPe PERIODé executada da mesma maneira, tem a mesma STATISTICSsaída e serve como a tabela externa na junção para ACTUALS_DETAILS. Parece que o SQL Server está fazendo a coisa certa para essa junção e não é tão interessante, então vou pular essa parte. É irrelevante para sua comparação.

    O que é relevante é o padrão de acesso à tabela no ACTUALS_DETAILS. Todas as três consultas usam buscas de índice em seu índice de cobertura, mas as buscas de índice são executadas de maneira diferente. Na primeira consulta, 1.200.000 buscas são realizadas usando as colunas PERIOD_SIDe . CCP_DETAILS_SIDNa segunda e terceira consultas, 17 buscas são realizadas usando apenas PERIOD_SID. Acredito que todas as linhas são buscadas com PERIOD_SID BETWEEN 577 AND 624, de modo que a busca de índice pode efetivamente ser pensada como uma varredura de índice paralela que começa PERIOD_SID = 577e termina com PERIOD_SID = 624. Isso resulta em uma grande diferença no IO entre as consultas:

    Tabela 'ACTUALS_DETAILS'. Contagem de varredura 1200000, leituras lógicas 3859053, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Tabela 'ACTUALS_DETAILS'. Contagem de varredura 17, leituras lógicas 37134, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Há um grande benefício em não ler as mesmas páginas repetidamente. Embora seja verdade que a abordagem de pseudo-varredura pode tecnicamente ler páginas que não são necessárias, você faz muito menos IO em geral. Também acredito que a diferença de IO seja diretamente responsável pela grande diferença de tempo de CPU entre a primeira consulta e as outras duas consultas: 36796 ms vs 7731 ms. Enquanto a primeira consulta foi executada, ela manteve, em média, 9 CPUs totalmente ocupadas em comparação com menos de 2 CPUs ocupadas para a segunda e terceira consultas. Essa é uma grande desvantagem para a primeira consulta e você a notaria em um sistema ocupado ou se suas consultas fossem forçadas a executar com DOP mais baixo. Na minha experiência limitada comAPPLYPercebi que o otimizador de consulta do SQL Server tende a implementá-lo como uma junção de loop aninhado com buscas de índice. Isso deve ser considerado uma evidência anedótica e tenho certeza de que há exceções, mas explica o que você está vendo aqui.

    As consultas 2 e 3 implementam a junção ACTUALS_DETAILScomo uma junção de hash. Suponho que a ideia por trás do push GROUP BYpara a adtabela derivada era para que o SQL Server executasse a agregação antecipadamente e você se unisse a menos linhas e agregasse menos linhas. No entanto, o SQL Server reescreveu sua segunda consulta para executar a agregação antecipadamente. Você pode dizer porque os operadores de agregação de fluxo e correspondência de hash estão à direita do operador de correspondência de hash (junção externa direita) no segundo plano. Até onde posso dizer, o segundo e o terceiro planos de consulta são efetivamente os mesmos, embora o terceiro plano tenha alguns operadores de custo extra de 0%.

    Pessoalmente, eu não consideraria a diferença entre 4293 e 4200 ms de tempo decorrido ou 7983 e 7731 ms de tempo de CPU como estatisticamente significativa. É possível que, se você executar as consultas mais algumas vezes, a segunda consulta seja mais rápida que a terceira. Eu usaria o estilo de consulta que parece mais natural para você. Pessoalmente, eu usaria a terceira consulta porque ela representa melhor o que eu quero que o otimizador faça, que é realizar a agregação o mais cedo possível.

    • 5

relate perguntas