Desempenho do INNER JOIN vs LEFT JOIN no SQL Server

Eu criei o comando SQL que usa o INNER JOIN para 9 tabelas, de qualquer forma esse comando leva muito tempo (mais de cinco minutos). Então meu pessoal me sugere para mudar INNER JOIN para LEFT JOIN porque o desempenho de LEFT JOIN é melhor, na primeira vez é apesar do que eu sei. Depois que eu mudei, a velocidade da consulta melhorou significativamente.

Eu gostaria de saber porque o LEFT JOIN é mais rápido que o INNER JOIN?

Meu comando SQL se parece com o seguinte: SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D e assim por diante

Atualização: Isso é breve do meu esquema.

 FROM sidisaleshdrmly a -- NOT HAVE PK AND FK INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK ON a.CompanyCd = b.CompanyCd AND a.SPRNo = b.SPRNo AND a.SuffixNo = b.SuffixNo AND a.dnno = b.dnno INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine ON a.CompanyCd = h.CompanyCd AND a.sprno = h.AcctSPRNo INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix ON c.CompanyCd = h.CompanyCd AND c.FSlipNo = h.FSlipNo AND c.FSlipSuffix = h.FSlipSuffix INNER JOIN coMappingExpParty d -- NO PK AND FK ON c.CompanyCd = d.CompanyCd AND c.CountryCd = d.CountryCd INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd ON b.CompanyCd = e.CompanyCd AND b.ProductSalesCd = e.ProductSalesCd LEFT JOIN coUOM i -- PK = UOMId ON h.UOMId = i.UOMId INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd ON a.CompanyCd = j.CompanyCd AND b.BFStatus = j.BFStatus AND b.ProductSalesCd = j.ProductSalesCd INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd ON e.ProductGroup1Cd = g1.ProductGroup1Cd INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd ON e.ProductGroup1Cd = g2.ProductGroup1Cd 

Um LEFT JOIN não é absolutamente mais rápido do que um INNER JOIN . Na verdade, é mais lento; por definição, uma junit externa ( LEFT JOIN ou RIGHT JOIN ) tem que fazer todo o trabalho de um INNER JOIN mais o trabalho extra de estender nulo os resultados. Também seria esperado que retornasse mais linhas, aumentando ainda mais o tempo total de execução simplesmente devido ao tamanho maior do conjunto de resultados.

(E mesmo que um LEFT JOIN fosse mais rápido em situações específicas devido a alguma confluência de fatores difícil de imaginar, ele não é funcionalmente equivalente a um INNER JOIN , então você não pode simplesmente replace todas as instâncias de um pelo outro!)

Muito provavelmente, seus problemas de desempenho estão em outro lugar, como não ter uma chave candidata ou chave estrangeira indexada corretamente. 9 mesas é muito para se juntar assim a desaceleração poderia literalmente ser quase em qualquer lugar. Se você postar seu esquema, poderemos fornecer mais detalhes.


Editar:

Refletindo mais sobre isso, eu poderia pensar em uma circunstância sob a qual um LEFT JOIN poderia ser mais rápido que um INNER JOIN , e é quando:

  • Algumas das tabelas são muito pequenas (digamos, menos de 10 linhas);
  • As tabelas não possuem índices suficientes para cobrir a consulta.

Considere este exemplo:

 CREATE TABLE #Test1 ( ID int NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL ) INSERT #Test1 (ID, Name) VALUES (1, 'One') INSERT #Test1 (ID, Name) VALUES (2, 'Two') INSERT #Test1 (ID, Name) VALUES (3, 'Three') INSERT #Test1 (ID, Name) VALUES (4, 'Four') INSERT #Test1 (ID, Name) VALUES (5, 'Five') CREATE TABLE #Test2 ( ID int NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL ) INSERT #Test2 (ID, Name) VALUES (1, 'One') INSERT #Test2 (ID, Name) VALUES (2, 'Two') INSERT #Test2 (ID, Name) VALUES (3, 'Three') INSERT #Test2 (ID, Name) VALUES (4, 'Four') INSERT #Test2 (ID, Name) VALUES (5, 'Five') SELECT * FROM #Test1 t1 INNER JOIN #Test2 t2 ON t2.Name = t1.Name SELECT * FROM #Test1 t1 LEFT JOIN #Test2 t2 ON t2.Name = t1.Name DROP TABLE #Test1 DROP TABLE #Test2 

Se você executar isso e visualizar o plano de execução, verá que a consulta INNER JOIN realmente custa mais do que o LEFT JOIN , porque satisfaz os dois critérios acima. É porque o SQL Server quer fazer uma correspondência de hash para o INNER JOIN , mas faz loops nesteds para o LEFT JOIN ; o primeiro é normalmente muito mais rápido, mas como o número de linhas é tão pequeno e não há índice para usar, a operação de hash acaba sendo a parte mais cara da consulta.

Você pode ver o mesmo efeito escrevendo um programa em sua linguagem de programação favorita para realizar um grande número de pesquisas em uma lista com 5 elementos, em comparação com uma tabela de hash com 5 elementos. Por causa do tamanho, a versão da tabela de hash é realmente mais lenta. Mas aumentá-lo para 50 elementos, ou 5000 elementos, e a versão da lista diminui para um rastreamento, porque é O (N) vs. O (1) para o hashtable.

Mas altere essa consulta para estar na coluna ” ID vez de ” Name e você verá uma história muito diferente. Nesse caso, ele faz loops nesteds para ambas as consultas, mas a versão INNER JOIN é capaz de replace uma das varreduras de índice clusterizadas por uma busca – o que significa que isso será literalmente uma ordem de magnitude mais rápida com um grande número de linhas.

Então a conclusão é mais ou menos o que eu mencionei vários parágrafos acima; isso é quase certamente um problema de indexação ou cobertura de índice, possivelmente combinado com uma ou mais tabelas muito pequenas. Essas são as únicas circunstâncias sob as quais o SQL Server pode, às vezes, escolher um plano de execução pior para um INNER JOIN que um LEFT JOIN .

Há um cenário importante que pode levar a que uma junit externa seja mais rápida que uma junit interna que ainda não foi discutida.

Ao usar uma junit externa, o otimizador está sempre livre para descartar a tabela associada externa do plano de execução se as colunas de junit forem a PK da tabela externa e nenhuma das colunas for selecionada na tabela externa. Por exemplo, SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY e B.KEY é o PK para B. Ambos Oracle (acredito que eu estava usando o release 10) e Sql Server (usei o 2008 R2) retire a tabela B do plano de execução.

O mesmo não é necessariamente verdadeiro para uma junit interna: SELECT A.* FROM A INNER JOIN B ON A.KEY=B.KEY pode ou não exigir B ​​no plano de execução dependendo de quais restrições existem.

Se A.KEY é uma chave estrangeira anulável referenciando B.KEY, então o otimizador não pode descartar B do plano porque ele deve confirmar que existe uma linha B para cada linha A.

Se A.KEY é uma chave estrangeira obrigatória fazendo referência a B.KEY, o otimizador fica livre para abandonar B do plano porque as restrições garantem a existência da linha. Mas só porque o otimizador pode derrubar a tabela do plano, não significa que será. O SQL Server 2008 R2 NÃO elimina B do plano. O Oracle 10 NÃO descarta B do plano. É fácil ver como a junit externa superará a junit interna no SQL Server nesse caso.

Este é um exemplo trivial e não prático para uma consulta independente. Por que se juntar a uma mesa se você não precisa?

Mas isso pode ser uma consideração muito importante ao projetar vistas. Freqüentemente, é criada uma visão de “tudo” que une tudo o que um usuário pode precisar relacionado a uma tabela central. (Especialmente se houver usuários ingênuos fazendo consultas ad-hoc que não entendem o modelo relacional) A visualização pode include todas as colunas relevantes de várias tabelas. Mas os usuários finais podem acessar apenas colunas de um subconjunto das tabelas dentro da visualização. Se as tabelas forem unidas com junções externas, o otimizador pode (e não) descartar as tabelas desnecessárias do plano.

É fundamental certificar-se de que a visualização usando junções externas forneça os resultados corretos. Como Aaronaught disse – você não pode replace cegamente OUTER JOIN para INNER JOIN e esperar os mesmos resultados. Mas há momentos em que pode ser útil por motivos de desempenho ao usar exibições.

Uma última nota – eu não testei o impacto no desempenho em relação ao acima, mas em teoria parece que você deve ser capaz de replace com segurança um INNER JOIN com um OUTER JOIN se você também adicionar a condição IS NOT NULL para a cláusula where.

Se tudo funcionar como deveria, não deveria, MAS todos nós sabemos que tudo não funciona da maneira que deveria, especialmente quando se trata do otimizador de consulta, cache de plano de consulta e statistics.

Primeiro, sugiro reconstruir o índice e as statistics e, em seguida, limpar o cache do plano de consulta apenas para ter certeza de que isso não está estragando tudo. No entanto, eu tive problemas mesmo quando isso foi feito.

Eu experimentei alguns casos em que uma junit à esquerda foi mais rápida que uma junit interna.

O motivo subjacente é o seguinte: Se você tiver duas tabelas e ingressar em uma coluna com um índice (em ambas as tabelas). A junit interna produzirá o mesmo resultado, não importa se você fizer um loop pelas inputs no índice na tabela um e corresponder ao índice na tabela dois, como se você fizesse o contrário: faça um loop sobre as inputs no índice na tabela dois e combine com o índice na tabela um. O problema é que quando você tem statistics enganosas, o otimizador de consultas usará as statistics do índice para localizar a tabela com menos inputs correspondentes (com base em seus outros critérios). Se você tem duas tabelas com 1 milhão em cada, na tabela 1 você tem 10 linhas correspondentes e na tabela 2 você tem 100.000 linhas correspondentes. A melhor maneira seria fazer uma varredura de índice na tabela um e combinar 10 vezes na tabela dois. O inverso seria uma varredura de índice que faz um loop de mais de 100.000 linhas e tenta corresponder a 100.000 vezes e apenas 10 são bem-sucedidas. Portanto, se as statistics não estiverem corretas, o otimizador pode escolher a tabela e o índice incorretos para fazer o loop.

Se o otimizador optar por otimizar a junit esquerda na ordem em que está escrito, ele terá um desempenho melhor do que a junit interna.

MAS, o otimizador também pode otimizar uma junit esquerda de maneira suboptimizada como uma semi-junit esquerda. Para escolher o que você quer, você pode usar a dica de ordem de força.

Tente ambas as consultas (aquela com junit interna e esquerda) com OPTION (FORCE ORDER) no final e poste os resultados. OPTION (FORCE ORDER) é uma dica de consulta que força o otimizador a criar o plano de execução com a ordem de associação fornecida na consulta.

Se o INNER JOIN começar a se apresentar tão rápido quanto o LEFT JOIN , é porque:

  • Em uma consulta composta inteiramente por INNER JOIN s, a ordem de junit não importa. Isso dá liberdade para que o otimizador de consultas ordene as junções como achar melhor, para que o problema dependa do otimizador.
  • Com o LEFT JOIN , esse não é o caso, pois a alteração da ordem de junit alterará os resultados da consulta. Isso significa que o mecanismo deve seguir a ordem de associação fornecida na consulta, que pode ser melhor que a otimizada.

Não sei se isso responde à sua pergunta, mas eu já estive em um projeto que apresentava consultas altamente complexas fazendo cálculos, o que bagunçou completamente o otimizador. Tivemos casos em que um FORCE ORDER reduzia o tempo de execução de uma consulta de 5 minutos para 10 segundos.

Ter feito uma série de comparações entre junções externas e internas à esquerda e não foram capazes de encontrar uma diferença consistente. Existem muitas variables. Estou trabalhando em um database de relatórios com milhares de tabelas muitos com um grande número de campos, muitas mudanças ao longo do tempo (versões de fornecedores e stream de trabalho local). Não é possível criar todas as combinações de índices de cobertura para atender às necessidades de uma ampla variedade de consultas e lidar com dados históricos. Ter visto consultas internas eliminam o desempenho do servidor porque duas tabelas grandes (milhões a dezenas de milhões de linhas) são unidas internamente, puxando um grande número de campos e nenhum índice de cobertura existe.

O maior problema, no entanto, não parece aparentar nas discussões acima. Talvez seu database seja bem projetado com gatilhos e processamento de transactions bem projetado para garantir bons dados. O meu frequentemente tem valores NULL onde eles não são esperados. Sim, as definições da tabela podem impor nulos, mas isso não é uma opção no meu ambiente.

Então a questão é … você desenha sua consulta apenas para velocidade, uma prioridade maior para o processamento de transactions que executa o mesmo código milhares de vezes por minuto. Ou você procura a precisão que uma junit externa esquerda fornecerá. Lembre-se de que as junções internas devem encontrar correspondências em ambos os lados, portanto, um NULL inesperado não apenas removerá dados das duas tabelas, mas possivelmente linhas inteiras de informações. E isso acontece muito bem, sem mensagens de erro.

Você pode ser muito rápido ao obter 90% dos dados necessários e não descobrir que as junções internas removeram informações silenciosamente. Às vezes, as associações internas podem ser mais rápidas, mas não acredito que alguém assuma essa suposição, a menos que tenha revisado o plano de execução. A velocidade é importante, mas a precisão é mais importante.

Seus problemas de desempenho são mais prováveis ​​devido ao número de junções que você está fazendo e se as colunas nas quais você está ingressando possuem índices ou não.

Na pior das hipóteses, você poderia facilmente fazer 9 varreduras de tabela inteiras para cada junit.

Associações externas podem oferecer desempenho superior quando usadas em visualizações.

Digamos que você tenha uma consulta que envolva uma visualização e essa visualização seja composta de 10 tabelas unidas. Digamos que sua consulta apenas use colunas de 3 dessas 10 tabelas.

Se essas 10 tabelas tivessem sido unidas internamente, o otimizador de consultas teria que juntá-las, mesmo que sua consulta não precisasse de 7 de 10 das tabelas. Isso porque as associações internas podem filtrar os dados, tornando-os essenciais para o cálculo.

Se essas 10 tabelas tivessem sido unidas externamente, o otimizador de consultas só uniria as que eram necessárias: 3 de 10 delas nesse caso. Isso ocorre porque as próprias associações não estão mais filtrando os dados e, portanto, as uniões não usadas podem ser ignoradas.

Fonte: http://www.sqlservercentral.com/blogs/sql_coach/2010/07/29/poor-little-misunderstood-views/

Eu encontrei algo interessante no SQL Server ao verificar se as junções internas são mais rápidas do que junções à esquerda.

Se você não include os itens da tabela unida à esquerda, na instrução select, a junit à esquerda será mais rápida que a mesma consulta com a junit interna.

Se você include a tabela unida à esquerda na instrução select, a junit interna com a mesma consulta será igual ou mais rápida que a junit esquerda.