Início / dba / Perguntas / 176279
Accepted
Thomas Franz
Asked: 2017-06-15 07:15:41 +0800 CST

Limite para HASH JOINs

  • 772

Eu tenho uma consulta muito complexa (quem não tem :-)). Nesta consulta está um JOIN para uma tabela como esta (claro que a original tem mais algumas colunas):

CREATE TABLE dbo.tbl_detail (id bigint PRIMARY key, 
                   descr varchar(300), 
                   txt varchar(MAX),
                   descr_p varchar(300)
                  );

Há duas junções para esta tabela na consulta:

  • LEFT JOIN dbo.tbl_detail td1 ON td1.id = main.detail_id
  • LEFT JOIN dbo.tbl_detail td2 ON td2.id = main.second_id
  • A consulta retorna aproximadamente 200 mil linhas.
  • td1 gera todas as colunas
  • td2 gera todas as colunas, exceto [txt] ( varchar(max))

Problema:

  • td2 usa um HASH JOIN
  • mas td1 usa um NESTED LOOKUP (mais de 200k linhas; nenhum problema estatístico, pois as estimativas estão corretas)
  • quando eu removo a coluna [txt] da saída td1 ela usa um HASH JOIN também
  • quando eu altero a coluna para VARCHAR(5000) (ou menor) ela usa um HASH JOIN
  • quando eu altero a coluna para VARCHAR(8000) ela usa o NESTED LOOKUP novamente
  • OPTION (HASH JOIN)ou LEFT HASH JOINfuncionaria, mas produz um plano de consulta extremamente lento

Pergunta: Por que nem sempre usa o HASH JOIN? Existe um limite de comprimento (por coluna ou soma de todas as colunas de saída)?

PS: Microsoft SQL Server 2014 (SP2) Developer Edition

sql-server sql-server-2014

1 respostas

  1. Best Answer

    É importante lembrar que o otimizador de consulta não escolhe cada junção individualmente e independentemente de todo o resto da consulta. As junções têm propriedades diferentes, o que significa que um tipo de junção diferente pode ser melhor ou pior dependendo de outras junções ou operações no plano. Posso gerar dados de teste que apresentem comportamento semelhante ao que você vê aqui, mas a reprodução depende da memória disponível para o servidor.

    Coloque 10k linhas cada em duas tabelas:

    CREATE TABLE dbo.tbl_detail (
    id bigint PRIMARY key, 
    descr varchar(300), 
    descr_p varchar(300),
    txt_max varchar(MAX),
    txt_5k varchar(5000)
);

INSERT INTO dbo.tbl_detail WITH (TABLOCK)
SELECT TOP (10000)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('DESCR', 60)
, REPLICATE('descr_p', 40)
, NULL
, NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE TABLE dbo.OUTER_TABLE (
    id bigint primary key,
    id2 bigint
);

INSERT INTO dbo.OUTER_TABLE WITH (TABLOCK)
SELECT TOP (10000)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

    Aqui está a consulta para testar:

    SELECT o.*, d.id, d.descr, d.descr_p
FROM OUTER_TABLE o
LEFT OUTER JOIN tbl_detail d ON o.ID2 = d.ID
ORDER BY o.ID;

    Se eu adicionar d.txt_5kà SELECTlista, naturalmente recebo uma junção de hash:

    haxixe natural

    No entanto, se eu adicionar d.txt_maxà SELECTlista, naturalmente recebo uma junção de loop:

    laço natural

    A chave aqui é que uma junção de loop preserva a ordem da tabela externa. Isso significa que uma junção de loop aninhado pode evitar uma classificação explícita para algumas consultas. As consultas com uma junção de hash podem precisar fazer a classificação. O custo da classificação depende (entre outras coisas) do tamanho estimado dos dados. O otimizador de consulta estima o tamanho dos dados com base no número estimado de linhas e nos tipos de dados. Com a VARCHAR(5000)coluna obtenho um tamanho estimado de 27 MB e com a VARCHAR(MAX)coluna obtenho um tamanho estimado de 42 MB. Na minha máquina, espera-se que a classificação de 42 MB seja derramada no disco, o que torna o plano de junção de hash muito mais caro do que o plano de loop para a VARCHAR(MAX)coluna.

    Podemos ver isso mais claramente usando dicas para forçar os diferentes planos para que possamos compará-los. Aqui está a comparação para a VARCHAR(5000)consulta:

    5k comparar

    E a comparação para a VARCHAR(MAX)consulta:

    comparação máxima

    Claro, pode não ser por isso que você está vendo o comportamento que está vendo para sua consulta específica. Eu só queria fornecer um exemplo de por que alterar um tipo de dados pode alterar o tipo de junção. Vou assumir que há um problema causado pela junção de loop e que você realmente precisa que seja uma junção de hash. A OPTION (HASH JOIN)dica pode não ser uma boa solução alternativa porque forçará cada junção a ser uma junção de hash na consulta. A LEFT HASH JOINdica pode não ser uma boa solução alternativa porque implica em uma FORCE ORDERdica que significa que o otimizador de consulta não poderá alterar a ordem de junção. Talvez você possa mover a parte problemática da consulta para uma tabela temporária e aplicar as dicas necessárias à consulta menor. É realmente difícil dizer muito sem mais informações.

    Você também pode tentar uma FORCESCANdica para incentivar a junção de hash, embora eu deteste recomendá-la. Certifique-se de testar cuidadosamente:

    SELECT o.*, d.id, d.descr, d.descr_p, d.txt_max
FROM OUTER_TABLE o
LEFT OUTER JOIN tbl_detail d WITH (FORCESCAN) ON o.ID2 = d.ID 
ORDER BY o.ID
OPTION (MAXDOP 1);
    • 3

relate perguntas