Mitos do SQL Server – Fazer junções via INNER JOIN é mais rápido que na cláusula WHERE ? (Parte I)

Bom Dia Pessoal,

Eu já perdi as contas de quantas vezes aparecem dúvidas em relação ao uso do INNER JOIN vs a junção da cláusula WHERE. Só de fórum e aulas já respondi pelo menos umas vinte vezes. Recentemente me deparei com esse assunto em duas ocasiões diferentes no mesmo dia. A primeira foi no grupo SQL Server DF e a segunda foi no módulo 3 (Optimizing Queries for Performance) do curso 2784A – Tuning and Optimizing Queries Using Microsoft SQL Server 2005 que atualmente estou ministrando. Uma vez que ambas as construções retornem o mesmo resultado, há de fato muitas especulações sobre as mesmas. Qual seria mais rápida ? E se há uma mais rápida a que se atribui um melhor desempenho ? Seria alguma delas uma construção em deuso ? Vejamos então mais um mito a ser investigado.

Um pouco de história e dos padrões ANSI SQL

Não pretendo me alongar muito na explicação (essa história eu deixo para os meus alunos do 2778), mas falemos um pouco sobre a evolução do padrão ANSI SQL com o foco especial na questão dos JOINs. As primeiras implementações de bancos de dados relacionais datam de 1970 e no final daquela década sua adoção estava em franca expansão em relação aos seus antecessores (hierárquico e rede). A implementação relacional tinha seus fundamentos claramente definidos usando as tabelas e relações como seus símbolos fundamentais, mas a linguagem para consultá-los estava longe de ser algo aceito e padronizado por parte dos fabricantes. Cada um simplesmente lançava seus produtos e as variações da SQL para consultá-los, mas sem nenhum padrão ou regra a ser seguida. Se as coisas continuassem nesse ritmo seria questão de tempo até que a linguagem perdesse suas características e cada fabricante tivesse sua própria SQL sem qualquer familiaridade com a SQL dos concorrentes.

Em 1986, foi realizada a primeira reunião para padronização da SQL pelo ANSI. Acredito que essa reunião deva ter sido uma reunião apenas para colocar os fabricantes uns de frente para os outros e dizer "Vamos padronizar ?". Sim, esse aperto de mãos foi um passo muito importante, mas havia muita coisa para ser definida e por isso em 1989 foi feita uma revisão para padronizar alguns comandos importantes da linguagem. Esse padrão ficou relativamente famoso sendo conhecido como SQL-89 (exatamente no mesmo ano em que a Microsoft lançava o SQL Server).

Embora o padrão tenha sido fundamental, o padrão não contemplava algo muito importante. Ninguém havia falado nada sobre junções entre tabelas. É inconcebível algo nesse sentido hoje (acredito até que alguém deva ter pensado nisso minutos depois da reunião ter acabado), mas o negócio é expressar a junção entre tabelas de uma forma mais convencional e nasceu daqui a idéia de juntar as colunas na cláusula WHERE. Ex: SELECT <Campos> FROM A, B WHERE A.Coluna = B.Coluna

O uso da junção na clásula WHERE podia atender a quase todas as necessidades, mas em muito pouco tempo, alguém percebeu a necessidade das junções do tipo "OUTER". Como o padrão não falava nada, os fabricantes começaram a implementar suas extensões por conta própria e ampliar seus dialetos. Não é à toa que a ORACLE começou com o =+ e a Microsoft com o =*. Nos três anos que se seguiram, o ANSI começou a trabalhar para contemplar um padrão que atendesse a essas necessidades e instituiu em 1992 formalmente os operadores de junção (JOINs) de tabelas como o INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER e CROSS.

Tirando o SQL Server e o ORACLE que confiavam nas extensões proprietárias, o uso dos demais JOINs veio mesmo em ótima hora (não imagino o OUTER via aplicação performático ainda mais antes de 1992). Mas no caso do INNER, ficava a opção de utilizar o estilo antigo (89) ou o estilo novo (92).

Estilo Construção
ANSI 89 SELECT <Campos> FROM A, B
WHERE A.Coluna = B.Coluna
ANSI 92 SELECT <Campos> FROM A
INNER JOIN B ON A.Coluna = B.Coluna

Agora pensemos no ano de 1989, diversos analistas e programadores acostumados a fazer a junção através do predicado na cláusula WHERE (A.Coluna = B.Coluna) e milhares de aplicações críticas em funcionamento. A cultura da junção na cláusula WHERE fatalmente faria com que os programadores experientes da época repassassem aos mais novos o uso da cláusula WHERE e não do INNER JOIN e estes por sua vez tenderiam a repetir o feito quando se tornassem os mais experientes. Naquela época ninguém falava em abstração, reusabilidade, stored procedures, padrões ORM, etc (só mesmos o mundo acadêmico e olhe lá). Trocar todas as cláusulas WHERE por INNER JOIN seria um trabalho hercúlio além de em termos práticos não trazer nada mais do que a possibilidade de dizer: "Minha aplicação está no padrão ANSI mais novo" o que não trás nenhum retorno frente ao custo envolvido em uma refatoração de código.

Outra dificuldade é a própria simplicidade que uma cláusula WHERE fornece para iniciantes. Claro que após mexer com bancos de dados por quase uma década, o entendimento de como um JOIN está profundamente enraizado e nem com fortes doses de cachaça eu acho que poderia me confundir, mas pensemos em alguém que nunca teve qualquer tipo de contato com a SQL e ainda está dando os primeiros passos ? A construção WHERE "cola" mais fácil, pois, é a tradução literal do que se pretende "Combine A com B onde A.Criterio seja igual a B.Criterio". A palavra JOIN não é difícil, mas pode assustar um pouco mais do que uma cláusula WHERE.

A idéia de algo mais novo sempre carrega consigo a idéia de melhoria justamente para corrigir algum dificuldade ou limitação vigente. Sim, na maioria das situações, o que é mais novo têm de representar uma vantagem em relação ao que é mais velho seja na facilidade de uso, no desempenho, no custo, etc. Implementações mais novas podem complementar as mais antigas ou simplesmente negá-las, mas de alguma forma têm de ser superiores e isso não é diferente com o ANSI 92 já que ele apresenta diversos comandos e operadores indiscutivelmente úteis, mas ausentes na revisão de 1989.

Se o mais novo é necessariamente superior em relação ao mais velho, não seria de estranhar a idéia de que talvez as implementações do ANSI 92 fossem mais performáticas, mas será que isso realmente tem fundamento ? Uma das primeiras coisas que se aprende em relação à SQL é que trata-se de uma linguagem declarativa, ou seja, diz-se o que se deseja, mas não se informa como retornar. Se isso é verdade, então a diferença de desempenho está diretamente ligada na forma e nos processos de recuperação de dados e não propriamente no que se deseja recuperar. Como as construções ANSI 89 e ANSI 92 declaram o mesmo objetivo (uma via WHERE e outra via JOIN), a diferença não ficaria por conta da declaração em si, mas por conta do que acontece nos bastidores.

O parser, o otimizador e a semântica

A construção de um parser de comandos dentro de um banco de dados deve ser um trabalho bastante complicado. Como bolar uma inteligência para verificar em um comando a relação de tabelas exatas, colunas, condições, etc além de retirar coisas inúteis para o processamento como múltiplos espaços, quebras de linhas, comentários, etc ? Não é um trabalho simples com certeza (haja IF e expressões regulares). Se o PARSER entrega o que deve ser feito, pensemos na quantidade de avaliações que devem ser feitas por um otimizador de consulta ? Como pensar em todas as possibilidades de se retornar um consulta, escolher a "melhor" e retornar ? É um grande desafio, pois, se o otimizador simplesmente escolhesse a melhor alternativa de todas em todas as situações, a conclusão dessa escolha poderia demorar tanto que o tempo final seria maior do que um escolha não tão otimizada. São muitas considerações e com certeza muita inteligência dentro de um otimizador de consultas de um banco de dados relacional.

A cada release de um banco de dados, mesmo que ela seja completamente reescrita, acho muito pouco provável que alguém reconstrua o otimizador do zero. Provavelmente seus algoritmos básicos serão apenas evoluídos de uma release para outras e alguns novos serão adicionados. Otimizadores de consultas tornam-se muito mais espertos a cada dia e sua margem de aceito beira cada vez mais a perfeição (é cada vez mais difícil de ganhar deles).

Agora vamos combinar um pouco as idéias da SQL com o otimizador de consultas e o pensamento dos fabricantes. Em 1989, a junção é apenas via cláusula WHERE e os fabricantes tinham de fazer isso ficar otimizado (e certamente fizeram). Em 1992, quando saiu o padrão novo, visualizou-se uma nova forma de relacionar as tabelas via JOIN e não mais via cláusula WHERE. Os parsers não tinham a menor idéia do que era INNER JOIN, e por isso levou um tempinho para que as releases seguintes contemplassem essas construções. Fato rapidamente superado, pois, ninguém quer ficar pra trás. Uma nova construção demanda um trabalho novo para o PARSER e um trabalho novo para o otimizador com certeza. Os fabricantes poderiam construir uma forma mais otimizada para o JOIN revendo um algoritmo melhor ou utilizar o mesmo algoritmo da clásula WHERE o que levaria os JOINs a serem necessariamente mais eficientes ou no mínimo terem o mesmo desempenho que a implementação anterior (WHERE).

De uma lado sintático, com certeza junções via JOIN ou cláusula WHERE não são iguais, pois a escrita das construções é diferente, mas de um ponto de vista semântico elas são idênticas, pois, tem de retornar a mesma coisa (é apenas uma questão de estilo). Se o otimizador de consultas tem de ser o mais esperto possível, a correto é produzir o melhor plano possível. Supondo que o JOIN seja mais performático que a cláusula WHERE, se o otimizador é pouco inteligente, o JOIN então seria processado mais rápido que a cláusula WHERE, mas se o otimizador for esperto, ele entenderia que a semântica das duas construções é a mesma e que ambas as construções vão apresentar o mesmo resultado e poderia simplesmente escolher o melhor algoritmo para processar a consulta o que faria com que ambas retornassem com o mesmo desempenho. Como os próprios fabricantes compõe o corpo técnico do ANSI e que naturalmente a comparação entre JOIN e WHERE seria grande, acredito que qualquer diferença entre o processamento de um JOIN ou uma junção via WHERE que houvesse seria automaticamente contemplada por eles nas releases subsequentes, e os otimizadores simplesmente escolheriam a maneira mais rápida independente da construção sintática. Se admitirmos que não há nenhuma diferença em termos de algoritmo, então as construções já ficariam empatadas de qualquer forma. Resumidamente falando, eles empatariam pela escolha semântica do otimizador em favor do melhor algoritmo, ou fatalmente empatariam pelo fato dos dois usarem o mesmo algorito. Não haveria como o desempenho ser diferente.

O plano lógico de execução

O plano lógico de execução de uma consulta informa a sequência de passos que devem ser seguidos para que a consulta seja processada. Ele representa uma guia geral, mas fisicamente falando, a ordem pode não ser necessariamente seguida, se o SGBD achar uma sequência mais eficiente (e normalmente acha) desde que o resultado obtido pelo plano lógico e pelo plano físico sejam ou mesmos, ou seja, os dois podem diferir na ordem dos passos, mas não irão diferir no resultado obtido. Não vou detalhar todas as etapas de um plano lógico, apenas as relevantes para o artigo em questão. Maiores detalhes podem ser encontrados no curso 2784A – Tuning and Optimizing Queries Using Microsoft SQL Server 2005, no livro Inside Microsoft® SQL Server™ 2005 T-SQL Querying (ou na versão 2008) ou no Training Kit da prova 70-442. Um detalhe importante é que a cada etapa processada tem como saída uma tabela virtual a ser utilizada como entrada para a etapa seguinte:

(5) SELECT <colunas>
(1) FROM <tabela à esquerda>
(3) <tipo de join> JOIN <tabelas à direita>
(2) ON <condições de JOIN>
(4) WHERE <condições de WHERE>

 

Vejamos a descrição das fases descritas:

Etapa Tabela Virtual Descrição
(1) FROM T1 Um produto cartesiano é realizado entre as duas primeiras tabelas
(2) ON T2 O filtro especificado no operador ON é aplicado na tabela virtual T1 e apenas os registros que obedecerem a essas condições são considerados
(3) JOIN T3 No caso do OUTER JOIN, os registros desconsiderados na etapa 2, são recolocados na consulta, pois o OUTER JOIN deve favorecer registros sem ocorrência (LEFT ou RIGHT). Caso haja mais de duas tabelas na instrução SQL, as etapas 1 a 3 são repetidas combinando a tabela virtual T1 com cada tabela à direita até que todas sejam contempladas
(4) WHERE T4 Os registros na tabela virtual T3 são submetidos aos critérios da cláusula WHERE e somente aqueles que obedecerem irão compor à tabela virtual T4.
(5) SELECT T5 As colunas que não foram especificadas na lista de colunas do SELECT é descartada

Para ficar um pouco mais fácil de entender cada etapa, utilizarei duas tabelas de exemplo:

Regiao
RegiaoID RegiaoNome
1 Centro Oeste
2 Sul
3 Sudeste
Estados
EstadoID RegiaoID EstadoNome
1 1 Distrito Federal
2 1 Goiás
3 3 Minas Gerais
4 2 Paraná
5 2 Santa Catarina
6 3 São Paulo

Para demonstrar o fluxo de etapas do plano de execução, vou utilizar uma instrução SELECT simples.

SELECT RegiaoNome, EstadoNome FROM Regiao As Reg
INNER JOIN Estados As Est ON Reg.RegiaoID = Est.RegiaoID

A primeira etapa (FROM) monta uma plano cartesiano entre as tabelas Regiao e Estado produzindo a primeira tabela virtual:

RegiaoID RegiaoNome EstadoID RegiaoID EstadoNome
1 Centro Oeste 1 1 Distrito Federal
1 Centro Oeste 2 1 Goiás
1 Centro Oeste 3 3 Minas Gerais
1 Centro Oeste 4 2 Paraná
1 Centro Oeste 5 2 Santa Catarina
1 Centro Oeste 6 3 São Paulo
2 Sul 1 1 Distrito Federal
2 Sul 2 1 Goiás
2 Sul 3 3 Minas Gerais
2 Sul 4 2 Paraná
2 Sul 5 2 Santa Catarina
2 Sul 6 3 São Paulo
3 Sudeste 1 1 Distrito Federal
3 Sudeste 2 1 Goiás
3 Sudeste 3 3 Minas Gerais
3 Sudeste 4 2 Paraná
3 Sudeste 5 2 Santa Catarina
3 Sudeste 6 3 São Paulo

A próxima tabela virtual deverá aplicar as condições especificadas no operador ON para encontrar as correspondências. Aproveito para colocar a mesma tabela, porém somente os registros em azul devem ser considerados, pois são os únicos onde Regiao.RegiaoID = Estados.RegiaoID

RegiaoID RegiaoNome EstadoID RegiaoID EstadoNome
1 Centro Oeste 1 1 Distrito Federal
1 Centro Oeste 2 1 Goiás
1 Centro Oeste 3 3 Minas Gerais
1 Centro Oeste 4 2 Paraná
1 Centro Oeste 5 2 Santa Catarina
1 Centro Oeste 6 3 São Paulo
2 Sul 1 1 Distrito Federal
2 Sul 2 1 Goiás
2 Sul 3 3 Minas Gerais
2 Sul 4 2 Paraná
2 Sul 5 2 Santa Catarina
2 Sul 6 3 São Paulo
3 Sudeste 1 1 Distrito Federal
3 Sudeste 2 1 Goiás
3 Sudeste 3 3 Minas Gerais
3 Sudeste 4 2 Paraná
3 Sudeste 5 2 Santa Catarina
3 Sudeste 6 3 São Paulo

A etapa 3 (JOIN) não terá o que fazer, pois, não há OUTER JOIN envolvido e também não há JOINs com mais de duas tabelas. A etapa 4 (WHERE) também não tem nada para fazer, pois, não há cláusula WHERE envolvida. Por fim, a instrução SELECT irá retirar as duas colunas RegiaoID e a coluna EstadoID da tabela virtual. Apenas as células em verde devem ser finalmente retornadas.

RegiaoID RegiaoNome EstadoID RegiaoID EstadoNome
1 Centro Oeste 1 1 Distrito Federal
1 Centro Oeste 2 1 Goiás
1 Centro Oeste 3 3 Minas Gerais
1 Centro Oeste 4 2 Paraná
1 Centro Oeste 5 2 Santa Catarina
1 Centro Oeste 6 3 São Paulo
2 Sul 1 1 Distrito Federal
2 Sul 2 1 Goiás
2 Sul 3 3 Minas Gerais
2 Sul 4 2 Paraná
2 Sul 5 2 Santa Catarina
2 Sul 6 3 São Paulo
3 Sudeste 1 1 Distrito Federal
3 Sudeste 2 1 Goiás
3 Sudeste 3 3 Minas Gerais
3 Sudeste 4 2 Paraná
3 Sudeste 5 2 Santa Catarina
3 Sudeste 6 3 São Paulo

Ao final da produção da última tabela virtual (a formada pelas células verdes), o result set final é finalmente devolvido ao usuário. Vejamos agora a produção das tabelas virtuais e do fluxo de etapas do plano lógico de execução utilizando a cláusula WHERE.

SELECT RegiaoNome, EstadoNome FROM Regiao As Reg, Estados As Est
WHERE Reg.RegiaoID = Est.RegiaoID

A primeira etapa (FROM) monta uma plano cartesiano entre as tabelas Regiao e Estado produzindo a primeira tabela virtual exatamente da mesma forma que o uso do INNER JOIN:

RegiaoID RegiaoNome EstadoID RegiaoID EstadoNome
1 Centro Oeste 1 1 Distrito Federal
1 Centro Oeste 2 1 Goiás
1 Centro Oeste 3 3 Minas Gerais
1 Centro Oeste 4 2 Paraná
1 Centro Oeste 5 2 Santa Catarina
1 Centro Oeste 6 3 São Paulo
2 Sul 1 1 Distrito Federal
2 Sul 2 1 Goiás
2 Sul 3 3 Minas Gerais
2 Sul 4 2 Paraná
2 Sul 5 2 Santa Catarina
2 Sul 6 3 São Paulo
3 Sudeste 1 1 Distrito Federal
3 Sudeste 2 1 Goiás
3 Sudeste 3 3 Minas Gerais
3 Sudeste 4 2 Paraná
3 Sudeste 5 2 Santa Catarina
3 Sudeste 6 3 São Paulo

A etapa 2 (ON) não terá nenhuma atividade, pois, não há nenhum tipo de JOIN envolvido. Isso também faz com que o otimizador ignore a etapa 3 (JOIN), pois, não há uso de OUTER JOIN ou de mais de duas tabelas envolvidas em JOINs. A etapa 4 (WHERE) irá aplicar as condições estipuladas na cláusula WHERE (Regiao.RegiaoID = Estados.RegiaoID). Coloquei a mesma tabela anterior, mas somente os registros em azul devem ser considerados, pois são os únicos que atendem o predicado da cláusula WHERE.

RegiaoID RegiaoNome EstadoID RegiaoID EstadoNome
1 Centro Oeste 1 1 Distrito Federal
1 Centro Oeste 2 1 Goiás
1 Centro Oeste 3 3 Minas Gerais
1 Centro Oeste 4 2 Paraná
1 Centro Oeste 5 2 Santa Catarina
1 Centro Oeste 6 3 São Paulo
2 Sul 1 1 Distrito Federal
2 Sul 2 1 Goiás
2 Sul 3 3 Minas Gerais
2 Sul 4 2 Paraná
2 Sul 5 2 Santa Catarina
2 Sul 6 3 São Paulo
3 Sudeste 1 1 Distrito Federal
3 Sudeste 2 1 Goiás
3 Sudeste 3 3 Minas Gerais
3 Sudeste 4 2 Paraná
3 Sudeste 5 2 Santa Catarina
3 Sudeste 6 3 São Paulo

A etapa 5 (SELECT) irá deixar apenas as colunas contempladas (RegiaoNome, EstadoNome) devolvendo o resultset para o usuário. Representei as células necessárias na cor verde.

RegiaoID RegiaoNome EstadoID RegiaoID EstadoNome
1 Centro Oeste 1 1 Distrito Federal
1 Centro Oeste 2 1 Goiás
1 Centro Oeste 3 3 Minas Gerais
1 Centro Oeste 4 2 Paraná
1 Centro Oeste 5 2 Santa Catarina
1 Centro Oeste 6 3 São Paulo
2 Sul 1 1 Distrito Federal
2 Sul 2 1 Goiás
2 Sul 3 3 Minas Gerais
2 Sul 4 2 Paraná
2 Sul 5 2 Santa Catarina
2 Sul 6 3 São Paulo
3 Sudeste 1 1 Distrito Federal
3 Sudeste 2 1 Goiás
3 Sudeste 3 3 Minas Gerais
3 Sudeste 4 2 Paraná
3 Sudeste 5 2 Santa Catarina
3 Sudeste 6 3 São Paulo

Se compararmos as tabelas virtuais produzidas pelo INNER JOIN e as tabelas virtuais produzidas pela cláusula WHERE é possível concluir que elas são exatamente as mesmas. Isso já era esperado, pois, pelo fluxo, as tarefas executadas foram a produção de um plano cartesiano, o filtro dos registros correspondentes, e a devolução de um resultset com as colunas informadas. Não consigo imaginar como uma construção baseada em JOIN consiga ser mais ou menos eficiente que uma cláusula WHERE se ambas fazem exatamente a mesma coisa e devolvem exatamente os mesmos resultados. E mesmo que fossem diferentes ? O otimizador escolheria a melhor alternativa para qualquer uma das construções, já que semanticamente são idênticas

A descoberta empírica (ou prova real)

Aqueles que pularam o artigo até esse parte poderão concluir através do empirismo que as construções são realmente idênticas em termos de desempenho. Aqueles que leram o artigo até aqui, verão apenas o plano físico se confirmando e endosando todo o entendimento das etapas do plano lógico para ambas as construções. Para realizar o script com razoável volume, fiz um script para popular algumas tabelas:

— Cria duas tabelas
CREATE TABLE Clientes (
    ClienteID INT,
    ClienteNome VARCHAR(50))

CREATE TABLE Pedidos (
    NumPedido INT,
    ClienteID INT, Valor MONEY)

— Insere mil clientes
DECLARE @i INT
SET @i = 1

WHILE @i <= 1000
BEGIN
    INSERT INTO
Clientes VALUES (@i, ‘Cliente’ + RIGHT(‘000’ + @i,4))
    SET @i = @i + 1
END

— Insere 100 mil pedidos (100 para cada cliente)
SET @i = 1

WHILE @i <= 100000
BEGIN
    INSERT INTO Pedidos VALUES (@i, ((@i – 1)/ 100) + 1, @i * 10)
    SET @i = @i + 1
END

Agora vejamos os planos de execução e seus comparativos em relação ao gasto de IO, CPU e tempo.

O plano é realmente idêntico e o fluxo de etapas seguiu na íntegra o plano lógico, ou seja, foi feita uma varredura entre as tabelas, ambas foram combinadas obecedendo-se os critérios previstos e posteriormente o SELECT foi informado. Interessante notar que a semântica é a mesma e que mesmo para a cláusula WHERE, o operador de JOIN foi colocado (INNER JOIN) o que realmente permite a conclusão de que ambas as construções refletem o mesmo plano.

(100000 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Pedidos’. Scan count 1, logical reads 325, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 1300 ms.

(100000 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Pedidos’. Scan count 1, logical reads 325, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 78 ms,  elapsed time = 1330 ms.

A diferença de tempo entre as execuções é 30ms. Isso não é conclusivo de que o INNER JOIN seja mais eficiente. Significa apenas que naquela ocasião, a CPU estava um pouco mais desocupada já que a consulta retorna relativamente rápida (menos de 2 segundos para 100 linhas). Provalmente se ambas rodarem sucessivas vezes, haverá um desvio em favor do INNER ou do WHERE. A leitura das páginas de dados constata que realmente eles são equivalentes, já que há a leitura de 325 páginas de pedidos e 4 páginas de clientes em ambas as consultas.

Alguns ainda costumam afirmar que o INNER pode ser mais veloz que o WHERE por conta das PKs e FKs quando existem. A presença de PKs (especialmente as clusterizadas) podem influenciar na recuperação de dados e cobertura de índices, mas essas vantagens vão favorecer as duas construções e não uma específica. Sinceramente não irei nem me dar o trabalho de detalhar esse teste, mas se alguém acha que realmente uma PK e uma FK pode fazer com que um INNER JOIN seja mais performático que a cláusula WHERE que faça o teste por conta própria (faço a gentileza de apenas colocar os scripts).

— Alterações na tabela de Clientes
ALTER TABLE Clientes ALTER COLUMN ClienteID INT NOT NULL
ALTER TABLE Clientes ADD CONSTRAINT PK_Cliente PRIMARY KEY (ClienteID)

— Alterações na tabela de Pedidos
ALTER TABLE Pedidos ALTER COLUMN NumPedido INT NOT NULL
ALTER TABLE Pedidos ADD CONSTRAINT PK_Pedidos PRIMARY KEY (NumPedido)
ALTER TABLE Pedidos ADD CONSTRAINT FK_Pedidos_Clientes FOREIGN KEY (ClienteID)
    REFERENCES Clientes (ClienteID)

Predicados Auxiliares

Uma variante dessa discussão é quanto a presença de predicados auxiliares e sua colocação no INNER JOIN ou na cláusula WHERE. Ex:

— Cria um índice na coluna Valor (aplique a criação de PKs e FKs)
CREATE INDEX IX_Valor ON Pedidos (Valor)

— Executa uma junção via JOIN (Predicado Completo no operador ON)
SELECT ClienteNome, NumPedido, Valor
FROM Clientes As CLI
INNER JOIN Pedidos As PED ON CLI.ClienteID = PED.ClienteID AND Valor <= 100

— Executa uma junção via JOIN (Predicado Parcial)
SELECT ClienteNome, NumPedido, Valor
FROM Clientes As CLI
INNER JOIN Pedidos As PED ON CLI.ClienteID = PED.ClienteID
WHERE Valor <= 100

— Executa uma junção via WHERE
SELECT ClienteNome, NumPedido, Valor
FROM Clientes As CLI, Pedidos As PED
WHERE CLI.ClienteID = PED.ClienteID AND Valor <= 100

Como a produção de um plano cartesiano antecede a cláusula WHERE é possível que algumas construções sugiram que o plano cartesiano será feito antes da cláusula WHERE desprezando assim a possibilidade de usar um índice. Essa afirmação seria válida para qualquer uma das três construções, mas como a segunda utiliza parte do filtro na cláusula ON e parte na cláusula WHERE essa especulação é maior nessa construção. O fato é que o plano lógico dá uma ordem geral, mas como eu havia dito, o otimizador pode optar por trocar a ordem de um plano físico caso ache um caminho mais eficiente. Não é a toa que todas as consultas fizeram o filtro com base no valor antes e produziram exatamente o mesmo plano conforme a figura abaixo:

E qual construção escolher ?

Acho que já está bem claro que não existem diferenças entre junções via JOINs ou via cláusula WHERE do ponto de vista de desempenho. Se ambas produzem o mesmo resultado, qual seriam as razões para preferir uma construção específica ? Em minha opinião sou completamente a favor do INNER JOIN em relação a junções na cláusula WHERE pelas seguintes razões:

  • Clareza de código: O uso de JOINs não polui o código. As junções na cláusula WHERE confundem, pois, não se sabe apenas com uma olhada o que é filtro e o que é junção. É muito ruim encontrar um instrução SQL com 20 condições variando entre junções e filtros.
  • Possibilidades de otimização: Apenas a junção via JOIN permite que se influencia no algoritmo de junção (Nested Loops, Merge Join ou Hash Join)
  • Evolução: Se o padrão ANSI92 especifica operadores de junção específicos não vejo porque não utilizá-los. Essa definição foi há oito anos atrás. Especificar junções em cláusula WHERE só demonstra uma certa resistência em evoluir e adotar notações mais modernas.
  • Flexibilidade: Suponha que uma junção seja feita via cláusula WHERE e que posteriormente o efeito INNER JOIN tenha de ser substituído por um OUTER JOIN ? Se a junção foi feita via JOIN basta trocar algumas palavras chaves. Se a junção foi feita via cláusula WHERE pode ser bem mais complicado

Acho que estão bastante detalhadas as questões relacionadas a desempenho entre junções no INNER JOIN e na cláusula WHERE. Sei que essa dúvida não morrerá aqui e certamente muitos outros aparecerão com ela. Irei sempre utilizar a comparação do chumbo e do algodão. Afinal qual será mais pesado ? Um quilo de chumbo ou algodão ? Aqueles que acharem que o de chumbo é mais pesado normalmente tendem a achar que o INNER JOIN é mais performático que a cláusula WHERE.

Cabe aqui uma última observação. A maioria das colocações expostas aqui são amplamente aplicadas a outros SGBDs e tudo funciona como descrito enquanto o INNER JOIN é utilizado. Com a presença de OUTER JOINs, as diferenças aparecem, mas não por conta de desempenho, mas porque junções do tipo OUTER podem diferir no resultado em relação à clausula WHERE e nesse caso a análise do desempenho torna-se pouco relevante, pois, se o resultado é diferente, não há o que comparar em desempenho. No caso do SQL Server, a extensão proprietária GROUP BY ALL também irá diferir os resultados e o desempenho (mesmo para comparações do tipo INNER JOIN). Felizmente essa extensão será retirada no futuro.

[ ]s,

Gustavo

8 Respostas para “Mitos do SQL Server – Fazer junções via INNER JOIN é mais rápido que na cláusula WHERE ? (Parte I)

  1. Fabiano Neves

    Grande Gustavo, como sempre colocações perfeitas e muito sobre SGBDs, … bela leitura para quem Ama banco de dados… Abraços…

  2. Fala Fabiano,Puxa ótimo ler esse seu comentário. Serve de incentivo pra fazer a parte II.[ ]s,

  3. Lucas De Matos

    Gustavo,
    Muito boa a explicação. Eu que estou iniciando em sql e pretendo ai ser um administrador, esse post foi muito útil pra mim e pra todos aqueles que chegarem a ver. Parabéns!

  4. tulioccalazans

    Gustavo,

    Acredito que a ordem das restrições pode influenciar no desempenho das Querys.
    Ex.:

    SELECT ClienteNome, NumPedido, Valor
    FROM Clientes As CLI, Pedidos As PED
    WHERE Valor <= 100 AND CLI.ClienteID = PED.ClienteID — Alterado

    Acredito que seria necessário realizar um estudo individual em cada consulta com uma árvore canônica para a construção de uma Query com melhor desempenho.

    Não sei até que ponto o SGBD pode alterar o plano de execução com relação a ordem das restrições.

    Tenho acompanhado o seu blog e gosto bastante dos posts, este seria um ponto muito interessante para a continuação deste assunto.

    Abraço e sucesso.

    • Oi Tulio,

      Até acredito que a ordem possa sim influenciar a estruturação de uma árvore (leia-se plano de execução), mas creio que os otimizadores de hoje em dia são bem espertos e conseguem alterar a ordem para encontrar a ordem mais eficiente. Se você pesquisar os planos de execução em SGBDs com o SQL Server, Oracle e DB2 verá que a ordem não faz diferença no resultado. O otimizador de consultas irá se encarregar de escolher a melhor ordem. Isso era verdade no passado quando os otimizadores não eram tão espertos, mas não creio que seja realidade atualmente. Entretanto, eis uma boa sugestão para um post. Com certeza uma dúvida muito comum

      [ ]s,

      Gustavo

  5. Muito Bom, ótima explicação!

  6. Otima explicação para quem trabalha e gosta d ebanco de dados, e ótima visão para entermos os porque de estudar Estruturas de Dados 2. Parabéns

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s