Início / dba / Perguntas / 331316
Accepted
Skary
Asked: 2023-09-18 21:58:33 +0800 CST

Ajuste de desempenho da consulta SQL Server, com LEFT OUTER JOIN vs INNER JOIN

  • 772

No meu cenário, preciso otimizar um procedimento armazenado, que usei para importar dados para o banco de dados de um banco de dados Exchange. Estou muito confuso porque a solução com INNER JOIN é inferior à solução LEFT OUTER JOIN, basicamente parece que a maneira como verifico a existência da "relação" causa uma enorme lentidão.

Para ser mais claro, eu tenho um Final_DB com poucas tabelas, uma para os artigos (tbl_ana_Articles), uma para atributos de artigos, também conhecidas como características (tbl_ana_Characteristics), e algumas outras tabelas. Em outro Exchange_DB obtenho relação entre artigos e atributos/características (é usado para atualizar periodicamente relações no Final_DB).

A tabela com relações, fornecida pelo banco de dados Exchange, precisa ser não dinâmica primeiro, antes de ser útil (não é uma tabela de junção cruzada quando a consigo, ela se torna uma junção cruzada após a não dinâmica).

Então basicamente eu escrevo a consulta dessa maneira:

WITH ExchangeArticleCode_CharacteristicCode AS (
  SELECT [ACODAR], SUBSTRING([TNCAR],5,2) AS [TNCAR] , [TVALOR]
  FROM [EXCHANGE_DB].[dbo].[ANART00F]
  UNPIVOT
  (
   [TVALOR]
   FOR [TNCAR] in ([ACAR01], ACAR02, ACAR03, ACAR04 , ACAR05 , ACAR06 , ACAR07 , ACAR08 , ACAR09 , ACAR10 , ACAR11 , ACAR12 , ACAR13 , ACAR14 , ACAR15 , ACAR16 , ACAR17 , ACAR18 , ACAR19 , ACAR20)
  ) AS [UNPIVOT]
  WHERE [TVALOR] IS NOT NULL AND [TVALOR] != ''
)

SELECT Characteristic.[ID]        AS [ID_CHARACTERISTIC]
       ,Article.[ID]               AS [ID_ARTICLE]
       ,Characteristic.[ID_FILTER] AS [ID_FILTER]
FROM ExchangeArticleCode_CharacteristicCode
LEFT OUTER JOIN [dbo].[tbl_ana_Articles] AS Article 
 ON (ExchangeArticleCode_CharacteristicCode.ACODAR collate Latin1_General_CI_AS) = Article.CODE
LEFT OUTER JOIN [dbo].[tbl_ana_Characteristics] AS Characteristic 
 ON (ExchangeArticleCode_CharacteristicCode.TNCAR collate Latin1_General_CI_AS) + '_' + (ExchangeArticleCode_CharacteristicCode.TVALOR collate Latin1_General_CI_AS) = Characteristic.ID_ERP
WHERE Characteristic.[IS_ACTIVE] = 1

Esta solução é surpreendentemente rápida, mas tem problemas, às vezes há lixo no banco de dados do Exchange, então a junção esquerda não corresponde aos códigos e na tabela de resultados recebo alguns NULL. Se eu tentar evitar o NULL, substituindo LEFT OUTER JOIN por INNER JOIN ou adicionando uma verificação (IS NOT NULL) na condição where, a consulta se tornará muito lenta e pesada para ser executada. Não está claro para mim por que e como evitar isso.

Aqui está o plano de execução da consulta rápida: https://www.brentozar.com/pastetheplan/?id=ryMBHCryp

Aqui está o plano de execução da consulta lenta: https://www.brentozar.com/pastetheplan/?id=SywALRS16

Para ser justo, parece-me que a consulta lenta tem um loop aninhado caro, mas por que o INNER JOIN é traduzido nesse loop aninhado?

sql-server

2 respostas

  1. Best Answer

    problemas

    Existem alguns problemas com este código que podem ser facilmente resolvidos com uma tabela temporária. Em particular, a construção de chaves de junção de valores e de múltiplas colunas em tempo de execução muitas vezes levará a escolhas de planos estranhas e estimativas de cardinalidade ruins.

    Também fiz um pequeno ajuste em sua wherecláusula para procurar linhas que contenham pelo menos um único caractere. A verificação de strings não nulas e não vazias não é necessária, pois nulos não podem corresponder a valores.

    Você também pode achar útil um índice clusterizado na tabela temporária, seja ACODAR, TNCAR_TVALORno formato TNCAR_TVALOR, ACODAR.

    WITH 
    ExchangeArticleCode_CharacteristicCode AS 
(
    SELECT 
        ACODAR = 
            ACODAR COLLATE Latin1_General_CI_AS, 
        TNCAR = 
            SUBSTRING(TNCAR, 5, 2), 
        TVALOR,
        TNCAR_TVALOR = 
            SUBSTRING(TNCAR, 5, 2) + 
            '_' + 
            TVALOR COLLATE Latin1_General_CI_AS
    FROM EXCHANGE_DB.dbo.ANART00F
    UNPIVOT
    (
        TVALOR
        FOR TNCAR IN 
            (
                ACAR01, 
                ACAR02, 
                ACAR03, 
                ACAR04, 
                ACAR05, 
                ACAR06, 
                ACAR07, 
                ACAR08, 
                ACAR09, 
                ACAR10, 
                ACAR11, 
                ACAR12, 
                ACAR13, 
                ACAR14, 
                ACAR15, 
                ACAR16, 
                ACAR17, 
                ACAR18, 
                ACAR19, 
                ACAR20
            )
    ) AS [UNPIVOT]
    WHERE TVALOR LIKE '_%'
)
SELECT
    *
INTO #ExchangeArticleCode_CharacteristicCode
FROM ExchangeArticleCode_CharacteristicCode;

SELECT 
    ID_CHARACTERISTIC = 
        Characteristic.ID,
    ID_ARTICLE = 
        Article.ID,
    ID_FILTER = 
        Characteristic.ID_FILTER
FROM #ExchangeArticleCode_CharacteristicCode AS ExchangeArticleCode_CharacteristicCode
LEFT OUTER JOIN dbo.tbl_ana_Articles AS Article 
  ON ExchangeArticleCode_CharacteristicCode.ACODAR = Article.CODE
LEFT OUTER JOIN dbo.tbl_ana_Characteristics AS Characteristic 
  ON ExchangeArticleCode_CharacteristicCode.TNCAR_TVALOR = Characteristic.ID_ERP
WHERE Characteristic.[IS_ACTIVE] = 1;
    • 2

  2. A condição WHERE [TVALOR] IS NOT NULL AND [TVALOR] != ''na tabela não dinâmica é muito mais seletiva do que as estimativas do SQLServer.

    No plano rápido, ele estima que selecionará 216.056 linhas de 423.640, mas na verdade produz apenas 11.944 linhas.

    Com os LEFT JOINs, o Sqlserver inicia a partir de ExchangeArticleCode_CharacteristicCode, que é a tabela esquerda no LEFT JOIN, produzindo 11.944 linhas, depois as combina com as outras tabelas grandes, mas isso não aumenta o número de linhas, porque aparentemente essas linhas não não corresponder a mais de um artigo ou característica.

    Quando você muda para INNER JOIN(ou adiciona uma verificação NOT NULL, que informa ao otimizador que ele pode tratar o LEFT JOIN como um INNER JOIN), o Sqlserver pode reordenar as junções para o que ele acha que será mais eficiente.

    Ele pressupõe que a descentralização da tabela ANART00F multiplicará as linhas, então adia até o final.

    Ele assume corretamente que a junção entre ANART00F e a tabela Artigos será eficiente (produz 21182 linhas), mas depois as une com a tabela Característica. Como a condição de junção requer colunas não dinâmicas, esta é na verdade uma junção completa sem condições e produz 115 milhões de linhas. O resultado é então aumentado para se tornar 2 bilhões de linhas. Só então, o sqlserver aplica as condições where, reduzindo esses 2 bilhões para 11934 (enquanto o sqlserver esperava que eles reduzissem o conjunto de resultados para 165 milhões).

    Para corrigir isso, você pode tentar atualizar as estatísticas de todas as tabelas envolvidas, mas suspeito que os valores específicos dos registros que você está tentando selecionar e os não dinâmicos não podem ser adivinhados corretamente, mesmo por estatísticas atualizadas.

    Outra solução que você pode tentar é SET FORCEPLANforçar o otimizador de consulta a unir as tabelas na ordem especificada

    SET FORCEPLAN ON;

--  your query here

SET FORCEPLAN OFF;
    • 1

relate perguntas