Oi! Seja bem-vindo!
Hoje trago a você um teste que para identificarmos quanto é “pouco e muito para algoritmos de JOIN” no SQL Server: Nested Loops, Merge Join e Hash Match. Creio que você vai gostar desse artigo, pois esse tipo de informação nos ajuda no planejamento de bancos de dados com mais performance.
O que é um algoritmo de JOIN
Join é o ato de combinar registros de uma tabela com seus correspondentes em outra. Considerando o exemplo abaixo, podemos combinar as tabelas “Vendas” e “Produtos” através das colunas “Vendas.Produto” e “Produto.ID”, conforme as flechas (observar colunas em negrito).
Quando juntamos as duas tabelas temos linhas de dados mais completas, e podemos inclusive fazer cálculos, a exemplo da coluna “Total da Venda”, que é “vendas.quantidade” x “produtos.valor”.
Quando falamos JOINs, sabemos que o SQL trabalha com 3 algoritmos, e a opção de qual utilizar está relacionada ao volume de dados a serem processados, conforme tabela abaixo:
Algoritmo de Join | Volume de dados |
---|---|
Nested Loops | Pequeno |
Merge Join | Médio |
Hash Match | Grande |
A pergunta que fica é: Quanto é pouco ou muito para algoritmos de JOIN?
Quanto é Pouco e Muito para algoritmos de JOIN
Para responder a essa pergunta, montei o script abaixo, onde você pode fazer o teste simplesmente alterando o número de registros a serem processados pelo JOIN na variável @registros.
------------------------------------------------------------------ -- Loop, merge ou hash: Quanto é "muito"/"pouco" nas escolhas do Execution Plan ------------------------------------------------------------------ -- Nested Loops: Volumes pequenos -- Merge Sort: Volumes médios -- Hash Match: Volumes grandes -- Quanto é muito e pouco? -------------------------------------------------- -- Nested Loops, Merge Sort ou Hash Match? -------------------------------------------------- /* OBS: O comportamento pode mudar de acordo com a versão do SQL select @@version Meus resultados: Versões: 12.0.2000.8 (X64) e 13.0.4001.0 (X64) Cenário: Índice Clustered VS coluna não indexada Loop : até 117 (117 vs. 117) Merge : 118 até 2466 Hash : 2467 ou mais */ -- Volume do join: declare @registros int = 117 -- Criar carga de testes if object_id('tempdb.dbo.#TabelaA') is not null drop table #TabelaA if object_id('tempdb.dbo.#TabelaB') is not null drop table #TabelaB create table #TabelaA (id_A int primary key clustered) create table #TabelaB (id_B int primary key clustered, id_A int) ;with cte as ( select 1 as linha union all select linha + 1 as linha from cte where linha < @registros ) insert into #TabelaA output inserted.id_A, inserted.id_A into #TabelaB select linha from cte option (maxrecursion 0) -- Verificar o algoritmo escolhido select @registros volume, A.id_A, B.id_B from #TabelaA A join #TabelaB B on B.id_A = A.id_A
Para o cenário descrito no script de testes, obtive os seguintes valores:
Algoritmo de Join | Volume de dados |
---|---|
Nested Loops | até 117 (117 vs. 117) |
Merge Join | 118 até 2466 |
Hash Match | 2467 ou mais |
CONCLUSÃO
Gostei bastante de trazer esse teste a você porque sabendo como o plano de execução se comporta, nós certamente teremos muito mais subsidio para planejar e criar bancos de dados mais performáticos.
Um bom salário e plano de carreiras começam com pequenas coisas, e essa é uma delas.
Abraço do seu amigo Josué 🙂