Início / dba / Perguntas / 229165
Accepted
Rick
Asked: 2019-02-08 11:09:29 +0800 CST

por que essa junção esquerda é mais rápida que uma junção interna?

  • 772

Então, estou ajustando essa consulta e tenho certeza de que, nesse caso, posso substituir uma junção interna por uma junção esquerda sem afetar os dados. No entanto, não sei ao certo por que isso é mais rápido. Aqui está a consulta:

SELECT DISTINCT  cl.NAME                            AS company_name,
                     cl.id                              AS company_id,
                     ep.Plan__c                           AS plan_id,
                     ep.Employee__c,
                     ep.id,
                     do.Subcategory__c,
                     ep.Plan_Type__c,
                     pt.SubType,
                     Sum((pt.[shares] * fvh.[ValuePerShare])) AS TotalValue,
                     ppe.Deferral_Option__c,
                     dt.Defer_type_Code,
                     do.Short_Code__c,
                     pt.ContributionYear
  FROM   dbo.ParticipantTrades pt WITH (NOLOCK) 
        INNER JOIN dbo.PayoutPathElection ppe WITH (NOLOCK) 
                ON pt.payoutPathElectionID = ppe.Id 
        INNER JOIN dbo.DeferralOption do WITH (NOLOCK) 
                ON ppe.Deferral_Option__c = do.id 
        INNER JOIN dbo.EmployeePlan ep WITH (NOLOCK) 
                ON pt.employeePlan = ep.Id 
        LEFT JOIN dbo.DeferralType dt WITH (NOLOCK) 
                ON pt.deferralType = dt.defID
        INNER JOIN dbo.Fnc_lastfundvalue('2019-01-30') AS fvh 
                ON pt.fund = fvh.Fund
        INNER JOIN dbo.Clients cl with (NOLOCK)
                ON ep.Company__c = cl.Id
  WHERE ep.Company__c = '0017000001WL1HfAAL' AND ep.Plan_Type__c  LIKE '%' AND pt.tradeDate <= '2019-01-30'
  Group by   cl.NAME,
             cl.id,
             ep.Plan__c,
             ep.Employee__c,
             ep.id,
             do.Subcategory__c,
             ep.Plan_Type__c,
             pt.SubType,
             ppe.Deferral_Option__c,
             dt.Defer_type_Code,
             do.Short_Code__c,
             pt.ContributionYear

O gargalo está na junção da função de valor da tabela (Fnc_lastfundvalue). Meu palpite de por que alterá-lo para uma junção à esquerda é mais rápido é que ele pode reordenar as junções e causa menos derramamento no tempdb? Aqui está o plano de consulta antes e depois de alterar INNER JOIN dbo.Fnc_lastfundvalue.. para LEFT JOIN dbo.Fnc_lastfundvalue..

Antes (29 segundos): https://www.brentozar.com/pastetheplan/?id=Bk1Y-WqEE

Depois (3 segundos): https://www.brentozar.com/pastetheplan/?id=B1hoW-544

NB: Os planos de execução acima são criados em uma caixa de desenvolvimento. O servidor de produção ainda está no SQL Server 2008.

sql-server performance

1 respostas

  1. Best Answer

    Parece que parte da diferença é porque o plano lento foi executado primeiro.

    O plano lento estava operando claramente em um cache frio, pois mostra leituras físicas adicionais e conseguiu acumular 15 segundos adicionais de PAGEIOLATCH_SHespera em comparação com o caso rápido.

    Parece que isso afetou tanto a execução do próprio TVF (que levou 5.5segundos em comparação com 1.35o caso rápido.) quanto o plano mais amplo usando o resultado dele.

    Você diz nos comentários que na segunda execução levou 11segundos. Isso ainda é 3 vezes mais lento que o plano rápido ( 3.446segundos), então não explica toda a diferença de desempenho.

    O principal problema que você está enfrentando é devido à baixa estimativa padrão para TVFs de várias instruções (suposição fixa de 100linhas nos níveis de compatibilidade 2014/2016 e 1em versões anteriores). Na realidade, seu TVF retorna 1,715linhas.

    No caso da junção interna, porque as junções internas são associativas e comutativas, elas podem ser reordenadas de forma flexível e movidas para a parte mais profunda da árvore. Isso faria sentido se uma linha fosse realmente retornada, pois poderia reduzir a contagem de linhas antecipadamente para as outras junções no plano.

    O plano de execução está abaixo. As anotações rosa são "Tempo Decorrido Real (ms)" do XML.

    insira a descrição da imagem aqui

    Por causa da estimativa de 1 linha, ele começa mal juntando dbo.ParticipantTradese selecionando um plano com loops e pesquisas aninhados. Esses loops aninhados têm um tempo decorrido de 13.976segundos (presumivelmente, a maior parte foi gasta aguardando a classificação de derramamento imediatamente upstream para solicitar linhas dele, com 3.26segundos ocupados nas próprias pesquisas e o IO associado aguarda as leituras físicas nesse operador).

    923,646as linhas são emitidas da junção versus uma estimativa 1423.18e essa estimativa incorreta se propaga para cima no plano por meio de 3 classificações e uma junção de hash se espalha à medida que avança (devido à subestimação da linha)

    Quando você adiciona o LEFT JOIN, ele não pode ser reordenado tão livremente e a junção acontece muito mais acima no plano (onde causaria menos danos no INNER JOINcaso problemático). A semântica da junção externa ajuda aqui de qualquer maneira.

    Embora a estimativa para o número de linhas que saem do TVF ainda seja 1, isso não afeta negativamente as estimativas para a junção na qual está diretamente envolvido (o SQL Server assume que a cardinalidade que sai dessa junção será a mesma do outra subárvore para a junção e isso é de fato o que acontece - uma junção externa não pode reduzir esse número, pois uma linha não unida ainda passaria - apenas NULLpara as fvhcolunas).

    Ainda há erros de estimativa de cardinalidade no plano de junção externa, mas não da mesma magnitude, e ele solicita uma concessão de memória suficiente para evitar derramamento em qualquer lugar.

    Esse problema de estimativa de cardinalidade foi resolvido na versão mais recente com execução intercalada . Enquanto isso (como você diz nos comentários, ele precisa ser compatível com 2008) você pode intercalar manualmente armazenando o resultado do TVF em uma #temptabela e juntando-se a ela para permitir que a contagem do resultado intermediário (e estatísticas da coluna) seja feita em conta.

    • 6

relate perguntas