Normalmente, eu não recomendo o uso de dicas de junção por todos os motivos padrão. Recentemente, no entanto, descobri um padrão em que quase sempre encontro uma junção de loop forçada para ter um desempenho melhor. Na verdade, estou começando a usar e recomendo tanto que queria uma segunda opinião para ter certeza de que não estou perdendo nada. Aqui está um cenário representativo (o código muito específico para gerar um exemplo está no final):
--Case 1: NO HINT
SELECT S.*
INTO #Results
FROM #Driver AS D
JOIN SampleTable AS S ON S.ID = D.ID
--Case 2: LOOP JOIN HINT
SELECT S.*
INTO #Results
FROM #Driver AS D
INNER LOOP JOIN SampleTable AS S ON S.ID = D.ID
SampleTable tem 1 milhão de linhas e seu PK é ID.
A tabela temporária #Driver tem apenas uma coluna, ID, nenhum índice e 50 mil linhas.
O que eu sempre encontro é o seguinte:
Caso 1: NO HINT
Index Scan na SampleTable
Hash Join
Maior duração (média de 333 ms)
Maior CPU (média de 331 ms)
Baixas leituras lógicas (4714)
Caso 2: LOOP JOIN HINT
Index Seek on SampleTable
Loop Join
Menor duração (média de 204ms, 39% menos)
CPU mais baixa (média de 206, 38% menos)
Leituras lógicas muito mais altas (160015, 34X mais)
A princípio, as leituras muito mais altas do segundo caso me assustaram um pouco porque diminuir as leituras costuma ser considerado uma medida decente de desempenho. Mas quanto mais penso sobre o que está realmente acontecendo, isso não me preocupa. Aqui está o meu pensamento:
SampleTable está contido em 4714 páginas, ocupando cerca de 36 MB. O caso 1 verifica todos eles, e é por isso que obtemos 4714 leituras. Além disso, ele deve executar 1 milhão de hashes, que consomem muita CPU e, em última análise, aumentam o tempo proporcionalmente. É todo esse hashing que parece aumentar o tempo no caso 1.
Agora considere o caso 2. Ele não está fazendo nenhum hash, mas em vez disso está fazendo 50.000 buscas separadas, que é o que está aumentando as leituras. Mas quão caras são as leituras comparativamente? Pode-se dizer que, se essas forem leituras físicas, pode ser muito caro. Mas lembre-se de 1) apenas a primeira leitura de uma determinada página pode ser física e 2) mesmo assim, o caso 1 teria o mesmo ou pior problema, pois é garantido que atingirá todas as páginas.
Levando em conta o fato de que ambos os casos precisam acessar cada página pelo menos uma vez, parece ser uma questão de qual é mais rápido, 1 milhão de hashes ou cerca de 155.000 leituras na memória? Meus testes parecem dizer o último, mas o SQL Server escolhe consistentemente o primeiro.
Pergunta
Então, de volta à minha pergunta: devo continuar forçando essa dica LOOP JOIN quando o teste mostra esses tipos de resultados ou estou perdendo alguma coisa na minha análise? Estou hesitante em ir contra o otimizador do SQL Server, mas parece que ele muda para usar uma junção de hash muito antes do que deveria em casos como esses.
Atualização 2014-04-28
Fiz mais alguns testes e descobri que os resultados que estava obtendo acima (em uma VM com 2 CPUs) não podiam ser replicados em outros ambientes (tentei em 2 máquinas físicas diferentes com 8 e 12 CPUs). O otimizador se saiu muito melhor nos últimos casos, a ponto de não haver esse problema pronunciado. Acho que a lição aprendida, que parece óbvia em retrospecto, é que o ambiente pode afetar significativamente o funcionamento do otimizador.
Planos de Execução
Plano de Execução Caso 1
Plano de Execução Caso 2
Código para gerar caso de exemplo
------------------------------------------------------------
-- 1. Create SampleTable with 1,000,000 rows
------------------------------------------------------------
CREATE TABLE SampleTable
(
ID INT NOT NULL PRIMARY KEY CLUSTERED
, Number1 INT NOT NULL
, Number2 INT NOT NULL
, Number3 INT NOT NULL
, Number4 INT NOT NULL
, Number5 INT NOT NULL
)
--Add 1 million rows
;WITH
Cte0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
Cte1 AS (SELECT 1 AS C FROM Cte0 AS A, Cte0 AS B),--4 rows
Cte2 AS (SELECT 1 AS C FROM Cte1 AS A ,Cte1 AS B),--16 rows
Cte3 AS (SELECT 1 AS C FROM Cte2 AS A ,Cte2 AS B),--256 rows
Cte4 AS (SELECT 1 AS C FROM Cte3 AS A ,Cte3 AS B),--65536 rows
Cte5 AS (SELECT 1 AS C FROM Cte4 AS A ,Cte2 AS B),--1048576 rows
FinalCte AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS Number FROM Cte5)
INSERT INTO SampleTable
SELECT Number, Number, Number, Number, Number, Number
FROM FinalCte
WHERE Number <= 1000000
------------------------------------------------------------
-- Create 2 SPs that join from #Driver to SampleTable.
------------------------------------------------------------
GO
IF OBJECT_ID('JoinTest_NoHint') IS NOT NULL DROP PROCEDURE JoinTest_NoHint
GO
CREATE PROC JoinTest_NoHint
AS
SELECT S.*
INTO #Results
FROM #Driver AS D
JOIN SampleTable AS S ON S.ID = D.ID
GO
IF OBJECT_ID('JoinTest_LoopHint') IS NOT NULL DROP PROCEDURE JoinTest_LoopHint
GO
CREATE PROC JoinTest_LoopHint
AS
SELECT S.*
INTO #Results
FROM #Driver AS D
INNER LOOP JOIN SampleTable AS S ON S.ID = D.ID
GO
------------------------------------------------------------
-- Create driver table with 50K rows
------------------------------------------------------------
GO
IF OBJECT_ID('tempdb..#Driver') IS NOT NULL DROP TABLE #Driver
SELECT ID
INTO #Driver
FROM SampleTable
WHERE ID % 20 = 0
------------------------------------------------------------
-- Run each test and run Profiler
------------------------------------------------------------
GO
/*Reg*/ EXEC JoinTest_NoHint
GO
/*Loop*/ EXEC JoinTest_LoopHint
------------------------------------------------------------
-- Results
------------------------------------------------------------
/*
Duration CPU Reads TextData
315 313 4714 /*Reg*/ EXEC JoinTest_NoHint
309 296 4713 /*Reg*/ EXEC JoinTest_NoHint
327 329 4713 /*Reg*/ EXEC JoinTest_NoHint
398 406 4715 /*Reg*/ EXEC JoinTest_NoHint
316 312 4714 /*Reg*/ EXEC JoinTest_NoHint
217 219 160017 /*Loop*/ EXEC JoinTest_LoopHint
211 219 160014 /*Loop*/ EXEC JoinTest_LoopHint
217 219 160013 /*Loop*/ EXEC JoinTest_LoopHint
190 188 160013 /*Loop*/ EXEC JoinTest_LoopHint
187 187 160015 /*Loop*/ EXEC JoinTest_LoopHint
*/
Há um custo inicial para uma junção de hash (criar a tabela de hash, que também é uma operação de bloqueio), mas a junção de hash tem o menor custo teórico por linha dos três tipos de junção física suportados pelo SQL Server, ambos em termos de E/S e CPU. A junção de hash realmente se destaca com uma entrada de compilação relativamente pequena e uma entrada de teste grande. Dito isso, nenhum tipo de junção física é 'melhor' em todos os cenários.
Cada busca requer a navegação em uma b-tree até a raiz, o que é computacionalmente caro em comparação com uma única investigação de hash. Além disso, o padrão geral de IO para o lado interno de uma junção de loops aninhados é aleatório, em comparação com o padrão de acesso sequencial da entrada de varredura do lado da sonda para uma junção hash. Dependendo do subsistema de E/S físico subjacente, as leituras sequenciais podem ser mais rápidas do que as leituras aleatórias. Além disso, o mecanismo de leitura antecipada do SQL Server funciona melhor com E/S sequencial, emitindo leituras maiores.
O otimizador de consulta do SQL Server faz várias suposições. Uma delas é que o primeiro acesso a uma página feito por uma consulta resultará em um IO físico (a 'suposição de cache frio'). A chance de uma leitura posterior vir de uma página já lida na memória pela mesma consulta é modelada, mas isso não passa de um palpite.
A razão pela qual o modelo do otimizador funciona dessa maneira é que geralmente é melhor otimizar para o pior caso (é necessário E/S físico). Muitas deficiências podem ser cobertas pelo paralelismo e pela execução de coisas na memória. Os planos de consulta que o otimizador produziria se presumisse que todos os dados estavam na memória poderiam ter um desempenho muito ruim se essa suposição fosse inválida.
O plano produzido usando a suposição de cache frio pode não funcionar tão bem quanto se um cache quente fosse assumido, mas seu desempenho de pior caso geralmente será superior.
Você deve ter muito cuidado ao fazer isso por dois motivos. Primeiro, as dicas de junção também forçam silenciosamente a ordem de junção física para corresponder à ordem escrita da consulta (como se você também tivesse especificado
OPTION (FORCE ORDER). Isso limita severamente as alternativas disponíveis para o otimizador e pode nem sempre ser o que você deseja.OPTION (LOOP JOIN)força loops aninhados joins para a consulta, mas não impõe a ordem de junção por escrito.Em segundo lugar, você está supondo que o tamanho do conjunto de dados permanecerá pequeno e a maioria das leituras lógicas virá do cache. Se essas suposições se tornarem inválidas (talvez com o tempo), o desempenho diminuirá. O otimizador de consulta embutido é muito bom em reagir a mudanças nas circunstâncias; remover essa liberdade é algo em que você deve pensar muito.
No geral, a menos que haja um motivo convincente para forçar as junções de loops, eu o evitaria. Os planos padrão geralmente estão bem próximos do ideal e tendem a ser mais resilientes diante de mudanças nas circunstâncias.
50.000 linhas unidas em uma tabela de um milhão de linhas parece ser muito para qualquer tabela sem um índice.
É difícil dizer exatamente o que fazer neste caso, já que ele está tão isolado do problema que você está realmente tentando resolver. Eu certamente espero que não seja um padrão geral em seu código onde você está se juntando a muitas tabelas temporárias não indexadas com quantidades significativas de linhas.
Tomando o exemplo apenas pelo que diz, por que não colocar um índice em #Driver? O D.ID é verdadeiramente único? Em caso afirmativo, isso é semanticamente equivalente a uma instrução EXISTS, que pelo menos permitirá ao SQL Server saber que você não deseja continuar pesquisando S por valores duplicados de D:
Resumindo, para esse padrão, eu não usaria uma dica de LOOP. Eu simplesmente não usaria esse padrão. Eu faria um dos seguintes, em ordem de prioridade, se possível: