Início / dba / Perguntas / 112300
Accepted
Geoff Patterson
Asked: 2015-08-27 09:29:52 +0800 CST

Encontrando linhas distintas em duas tabelas: Full Outer Join mais eficiente que Union?

  • 772

Ao encontrar linhas distintas em duas tabelas onde não podemos necessariamente garantir que sejam pré-classificadas, é uma boa ideia usar um FULL OUTER JOINem vez de um UNION? Há alguma desvantagem nessa abordagem? Se for consistentemente mais rápido, por que o otimizador de consulta não escolhe o mesmo plano para UNION que FULL OUTER JOINusaria?

Conseguimos trazer uma consulta de produção específica de ~ 10 minutos para ~ 3 minutos reescrevendo a UNIONcomo um arquivo FULL OUTER JOIN. A UNIONparece ser a maneira mais intuitiva de escrever a lógica, mas ao explorar as duas opções, observei que FULL OUTER JOINé mais eficiente em termos de uso de memória e CPU.

Consulte os scripts a seguir se desejar executar uma versão simplificada e anônima de nossa consulta de produção:

Script de configuração

-- Create a 500K row table
SELECT TOP 500000 ROW_NUMBER() OVER (ORDER BY NEWID()) AS id, v1.number % 5 AS val
INTO #t1
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

-- Create a 5MM row table that will match some, but not all, of the 500K row table
SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY NEWID()) AS id, v1.number % 5 AS val
INTO #t2
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

-- Optionally, key both tables to see the impact it has on query plans and performance
-- Both queries end up with essentially the same plan and performance in this case
-- So that means that at least there is not a downside to using the FULL OUTER JOIN when the data is sorted
--ALTER TABLE #t1
--ADD UNIQUE CLUSTERED (id)
--ALTER TABLE #t2
--ADD UNIQUE CLUSTERED (id)

JUNÇÃO EXTERNA COMPLETA

O FULL OUTER JOINescolhe a menor das duas tabelas como o lado da construção de uma junção de hash, o que significa que o uso de memória é proporcional ao tamanho da tabela menor (500 mil linhas).

-- CPU time = 3058 ms,  elapsed time = 783 ms.
-- MaxUsedMemory: 29016 KB
-- Table '#t1'. Scan count 5, logical reads 1301, physical reads 0
-- Table '#t2'. Scan count 5, logical reads 12989, physical reads 0
SELECT COUNT(*), AVG(id), AVG(val)
FROM (
    SELECT COALESCE(t1.id, t2.id) AS id, COALESCE(t1.val, t2.val) AS val
    FROM #t1 t1
    FULL OUTER JOIN #t2 t2
        ON t2.id = t1.id
        AND t2.val = t1.val
) x
GO

insira a descrição da imagem aqui

UNIÃO

O UNIONcria uma tabela de hash para um agregado de hash no conjunto de dados geral, o que significa que o uso de memória é proporcional ao número total de linhas distintas (linhas de 5,4 MM neste caso; geralmente, pelo menos o número de linhas no maior dos duas mesas). O uso de memória é 10 vezes maior que o FULL OUTER JOIN, e tanto o tempo de CPU quanto o tempo decorrido também são mais lentos. Se eu escalasse isso até o ponto em que a agregação de hash não pudesse caber na concessão de memória de uma única consulta, a diferença de desempenho se tornaria enorme (como foi em nossa grande consulta de produção).

-- CPU time = 4651 ms,  elapsed time = 1188 ms.
-- MaxUsedMemory: 301600 KB
-- Table '#t1'. Scan count 5, logical reads 1301, physical reads 0
-- Table '#t2'. Scan count 5, logical reads 12989, physical reads 0
SELECT COUNT(*), AVG(id), AVG(val)
FROM (
    SELECT t1.id, t1.val
    FROM #t1 t1
    UNION 
    SELECT t2.id, t2.val
    FROM #t2 t2
) x

insira a descrição da imagem aqui

sql-server performance

1 respostas

  1. Best Answer

    A semântica das duas consultas não é a mesma - UNIONremove duplicatas, enquanto o FULL OUTER JOINnão irá:

    DECLARE @T1 AS table (id bigint NULL, val integer NULL);
DECLARE @T2 AS table (id bigint NULL, val integer NULL);

INSERT @T1 (id, val) VALUES (1, 1);
INSERT @T1 (id, val) VALUES (1, 1);
INSERT @T2 (id, val) VALUES (1, 1);
INSERT @T2 (id, val) VALUES (1, 1);

SELECT COALESCE(t1.id, t2.id) AS id, COALESCE(t1.val, t2.val) AS val
FROM @t1 t1
FULL OUTER JOIN @t2 t2
    ON t2.id = t1.id
    AND t2.val = t1.val;

SELECT t1.id, t1.val
FROM @t1 t1
UNION 
SELECT t2.id, t2.val
FROM @t2 t2;

    Resultado:

    ╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║  1 ║   1 ║
║  1 ║   1 ║
║  1 ║   1 ║
║  1 ║   1 ║
╚════╩═════╝

╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║  1 ║   1 ║
╚════╩═════╝

    Dito isto, o otimizador não conhece muitos FOJNtruques, então é sempre possível que haja uma maneira melhor de expressar a consulta do que o natural UNION. Somente transformações comumente úteis e sempre corretas são implementadas.

    Observe que, com uma restrição exclusiva apenas na tabela maior, o otimizador escolhe uma união de hash, sem a remoção de duplicatas cara na entrada da sonda, o que faz com que escolha Concat Union All no exemplo da pergunta:

    ALTER TABLE #t2 
ADD CONSTRAINT UQ2 
UNIQUE CLUSTERED (id);

SELECT COUNT(*), AVG(x.id), AVG(x.val)
FROM (
    SELECT t1.id, t1.val
    FROM #t1 t1
    UNION
    SELECT t2.id, t2.val
    FROM #t2 t2
) AS x;

    Plano de união de hash

    A FOJNreescrita pode ser útil nos casos em que você sabe que não pode haver duplicatas em cada conjunto de entrada, mas essa condição não é imposta com uma restrição ou índice exclusivo (particularmente na entrada grande).

    Se tal garantia de exclusividade existir e ainda assim o otimizador não selecionar uma Hash Union, você pode tentar uma OPTION (HASH UNION)dica para ver como ela se compara.

    • 9

relate perguntas