Verdades e Mitos sobre JOINs

Durante os meus trabalhos de query tuning é muito comum, por parte dos desenvolvedores, surgirem questões – e também mitos – relacionadas ao uso de JOIN.  O tema pode denotar a falsa impressão de que é assunto para iniciantes, mas, eu garanto: não é! Faço uso do que Itzik Ben-Gan escreveu no seu livro Inside Microsoft SQL Server 2008: T-SQL Querying:

O bom profissional é aquele que constrói o seu conhecimento a partir de fortes fundamentos, dominando o básico.

Sem mais delongas, vamos ao que interessa.

Mito 1 – Fazer JOIN na cláusula WHERE é menos performático do que na cláusula ON

Inicialmente quero destacar que fazer JOINs via WHERE é um estilo suportado pelo padrão ANSI SQL-89. Isso significa que JOIN via WHERE não é algo anti ANSI, pelo contrário, é um estilo de escrita devidamente padronizado. Já o uso de JOIN via cláusula ON foi formalmente regulado pelo ANSI SQL-92. Assim, ambos são válidos. A única diferença é que no WHERE não é possível implementar OUTER JOIN (somente INNER e CROSS) . A Microsoft, contudo, criou uma abordagem proprietária, não reconhecida pela ANSI, visando fazer OUTER na cláusula WHERE: o uso do “*=” e “=*”. Vale lembrar que essa abordagem não é mais suportada a partir do SQL Server 2012, o qual tem nível mínimo de compatibilidade 90.

Superada essa questão vamos então à próxima: existe diferença de performance? Vejamos os planos de execução de duas consultas, utilizando ambos os estilos.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”dXNlIE5vcnRod2luZApHTwotLSB2aWEgY2zDoXVzdWxhIE9OClNFTEVDVCBvLk9yZGVySUQsIG8uT3JkZXJEYXRlLCBjLkNvbnRhY3ROYW1lLCBlLkxhc3ROYW1lCmZyb20gZGJvLk9yZGVycyBvCmpvaW4gZGJvLkN1c3RvbWVycyBjIE9OIG8uQ3VzdG9tZXJJRCA9IGMuQ3VzdG9tZXJJRApKT0lOIGRiby5FbXBsb3llZXMgZSBPTiBvLkVtcGxveWVlSUQgPSBlLkVtcGxveWVlSUQ=” hover_enabled=”0″]dXNlIE5vcnRod2luZApHTwotLSB2aWEgY2zDoXVzdWxhIE9OClNFTEVDVCBvLk9yZGVySUQsIG8uT3JkZXJEYXRlLCBjLkNvbnRhY3ROYW1lLCBlLkxhc3ROYW1lCmZyb20gZGJvLk9yZGVycyBvCmpvaW4gZGJvLkN1c3RvbWVycyBjIE9OIG8uQ3VzdG9tZXJJRCA9IGMuQ3VzdG9tZXJJRApKT0lOIGRiby5FbXBsb3llZXMgZSBPTiBvLkVtcGxveWVlSUQgPSBlLkVtcGxveWVlSUQ=[/et_pb_dmb_code_snippet]
[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gdmlhIGNsw6F1c3VsYSBXSEVSRQpTRUxFQ1Qgby5PcmRlcklELCBvLk9yZGVyRGF0ZSwgYy5Db250YWN0TmFtZSwgZS5MYXN0TmFtZQpmcm9tIGRiby5PcmRlcnMgbywgZGJvLkN1c3RvbWVycyBjLCBkYm8uRW1wbG95ZWVzIGUKV0hFUkUgby5DdXN0b21lcklEID0gYy5DdXN0b21lcklECmFuZCBvLkVtcGxveWVlSUQgPSBlLkVtcGxveWVlSUQ=” hover_enabled=”0″]LS0gdmlhIGNsw6F1c3VsYSBXSEVSRQpTRUxFQ1Qgby5PcmRlcklELCBvLk9yZGVyRGF0ZSwgYy5Db250YWN0TmFtZSwgZS5MYXN0TmFtZQpmcm9tIGRiby5PcmRlcnMgbywgZGJvLkN1c3RvbWVycyBjLCBkYm8uRW1wbG95ZWVzIGUKV0hFUkUgby5DdXN0b21lcklEID0gYy5DdXN0b21lcklECmFuZCBvLkVtcGxveWVlSUQgPSBlLkVtcGxveWVlSUQ=[/et_pb_dmb_code_snippet]

Como se vê, os planos são exatamente os mesmos. Não há benefício de performance ao usar um estilo ou outro. Particularmente, prefiro escrever como no primeiro exemplo, pois, para mim, a query fica com uma leitura mais intuitiva. Prefiro deixar os filtros para a cláusula WHERE e os joins para a cláusula ON.

Mito 2 – Quanto mais filtros na cláusula ON mais rápida será a query

Essa é uma afirmação, às vezes, bastante defendida (equivocadamente). Inicialmente é importante compreender como logicamente o código de uma query é processado (não estou falando de geração de planos de execução). Segue o resumo da sequência lógica das fases de processamento do código fonte de uma query:

(5) SELECT

(1) FROM [CROSS – INNER – OUTER]

(2) WHERE

(3) GROUP BY

(4) HAVING

(6) ORDER BY

Note que primeiramente é processada a cláusula FROM e não o SELECT, o qual é a penúltima fase. A cláusula WHERE é a segunda a ser processada e assim sucessivamente.

Cada fase produz como resultado uma tabela virtual que é utilizada como entrada na fase seguinte. A fase 1 – FROM pode possuir subfases quando a query envolver JOIN ou outros operadores de tabela como PITVOT ou APPLY. Em se tratando de JOINs a sequência lógica das subfases do FROM é a seguinte:

J1 – CROSS JOIN: é a primeira das três subfases. O CROSS JOIN gera um produto cartesiano combinando todas as linhas entre as duas tabelas envolvidas (N linhas x M linhas). O resultado desta subfase é uma tabela virtual contendo a combinação das linhas.

J2 – Filtro ON (INNER JOIN): esta subfase, se na query foi utilizada um INNER JOIN, recupera somente as linhas que atendam ao predicado da cláusula ON. O predicado ON é aplicado sobre todas as linhas retornadas na tabela virtual da subfase anterior (J1). O resultado da subfase J2 – Filtro ON, é uma tabela virtual contendo exclusivamente as linhas que atendam ao predicado do ON.

J3 – OUTER JOIN: esta subfase, se na query foi utilizada um OUTER JOIN, adiciona à tabela virtual, produzida no passo anterior, as linhas da tabela preservada que não atenderam ao predicado do ON. Uma tabela é definida como preservada conforme o tipo de OUTER JOIN (Left, Right ou Full) utilizado. Por exemplo, num LEFT OUTER JOIN a tabela preservada é a da esquerda e, desta forma, as linhas que haviam sido desconsideradas na subfase anterior são agora adicionadas “de volta”, gerando como saída uma nova tabela virtual.

Com base no que acabamos de expor, depreendemos que o JOIN é processado antes do WHERE. Assim, vale destacar: não importa se o JOIN foi feito via cláusula ON ou se foi feito via cláusula WHERE, o SQL Server é “inteligente o bastante” para identificar a existência dos JOINs e processá-los antes dos filtros da query. Desta maneira, não faz diferença, para a performance da consulta, o local onde você escreve os filtros da sua query. Vamos clarificar as coisas através de um novo exemplo.

 

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”dXNlIE5vcnRod2luZApHTwotLSBJTk5FUiBKT0lOIGRlaXhhbmRvIG9zIGZpbHRyb3MgbmEgY2zDoXVzdWxhIFdIRVJFClNFTEVDVApvLk9yZGVySUQsIG8uT3JkZXJEYXRlLCBvLlNoaXBSZWdpb24sIG8uU2hpcENvdW50cnksCmMuQ29tcGFueU5hbWUsIGMuQ291bnRyeSwgZS5GaXJzdE5hbWUKRlJPTSBvcmRlcnMgbwpJTk5FUiBKT0lOIGRiby5FbXBsb3llZXMgZSBPTiBvLkVtcGxveWVlSUQgPSBlLkVtcGxveWVlSUQKSU5ORVIgSk9JTiBkYm8uQ3VzdG9tZXJzIGMgT04gby5DdXN0b21lcklEID0gYy5DdXN0b21lcklECndoZXJlIG8uU2hpcFJlZ2lvbiBJUyBOT1QgTlVMTApBTkQgby5TaGlwQ291bnRyeSA9ICdCcmF6aWwn” hover_enabled=”0″]dXNlIE5vcnRod2luZApHTwotLSBJTk5FUiBKT0lOIGRlaXhhbmRvIG9zIGZpbHRyb3MgbmEgY2zDoXVzdWxhIFdIRVJFClNFTEVDVApvLk9yZGVySUQsIG8uT3JkZXJEYXRlLCBvLlNoaXBSZWdpb24sIG8uU2hpcENvdW50cnksCmMuQ29tcGFueU5hbWUsIGMuQ291bnRyeSwgZS5GaXJzdE5hbWUKRlJPTSBvcmRlcnMgbwpJTk5FUiBKT0lOIGRiby5FbXBsb3llZXMgZSBPTiBvLkVtcGxveWVlSUQgPSBlLkVtcGxveWVlSUQKSU5ORVIgSk9JTiBkYm8uQ3VzdG9tZXJzIGMgT04gby5DdXN0b21lcklEID0gYy5DdXN0b21lcklECndoZXJlIG8uU2hpcFJlZ2lvbiBJUyBOT1QgTlVMTApBTkQgby5TaGlwQ291bnRyeSA9ICdCcmF6aWwn[/et_pb_dmb_code_snippet]
[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gSU5ORVIgSk9JTiBhcGxpY2FuZG8gb3MgZmlsdHJvcyBkaXJldGFtZW50ZSBuYSBjbMOhdXN1bGEgT04KU0VMRUNUCm8uT3JkZXJJRCwgby5PcmRlckRhdGUsIG8uU2hpcFJlZ2lvbiwgby5TaGlwQ291bnRyeSwKYy5Db21wYW55TmFtZSwgYy5Db3VudHJ5LCBlLkZpcnN0TmFtZQpGUk9NIG9yZGVycyBvCklOTkVSIEpPSU4gZGJvLkVtcGxveWVlcyBlCk9OIG8uRW1wbG95ZWVJRCA9IGUuRW1wbG95ZWVJRApJTk5FUiBKT0lOIGRiby5DdXN0b21lcnMgYwpPTiBvLkN1c3RvbWVySUQgPSBjLkN1c3RvbWVySUQKQU5EIG8uU2hpcFJlZ2lvbiBJUyBOT1QgTlVMTApBTkQgby5TaGlwQ291bnRyeSA9ICdCcmF6aWwn” hover_enabled=”0″]LS0gSU5ORVIgSk9JTiBhcGxpY2FuZG8gb3MgZmlsdHJvcyBkaXJldGFtZW50ZSBuYSBjbMOhdXN1bGEgT04KU0VMRUNUCm8uT3JkZXJJRCwgby5PcmRlckRhdGUsIG8uU2hpcFJlZ2lvbiwgby5TaGlwQ291bnRyeSwKYy5Db21wYW55TmFtZSwgYy5Db3VudHJ5LCBlLkZpcnN0TmFtZQpGUk9NIG9yZGVycyBvCklOTkVSIEpPSU4gZGJvLkVtcGxveWVlcyBlCk9OIG8uRW1wbG95ZWVJRCA9IGUuRW1wbG95ZWVJRApJTk5FUiBKT0lOIGRiby5DdXN0b21lcnMgYwpPTiBvLkN1c3RvbWVySUQgPSBjLkN1c3RvbWVySUQKQU5EIG8uU2hpcFJlZ2lvbiBJUyBOT1QgTlVMTApBTkQgby5TaGlwQ291bnRyeSA9ICdCcmF6aWwn[/et_pb_dmb_code_snippet]

Veja que os planos de execução são os mesmos para ambas as consultas. Portanto, não há diferença de performance. Particularmente, prefiro escrever como no primeiro exemplo, pois visualmente fica mais fácil separar o que é predicado de JOIN e o que é predicado de WHERE.

Contudo, é necessário ter muito cuidado ao utilizar OUTER JOIN. Filtros adicionados ao predicado da cláusula ON de um OUTER JOIN podem prejudicar a performance, além de produzir um resultado errado.

Exploremos um exemplo.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”dXNlIEFkdmVudHVyZVdvcmtzCmdvCi0tICoqKiBMaXN0YXIgb3MgcHJvZHV0b3MgZGEgY29yIHByZXRhIHF1ZSB0ZW5oYW0gb3UgbsOjbyBzdWJjYXRlZ29yaWEKLS0gTWFudMOpbSBvIGZpbHRybyBubyBXSEVSRQpzZWxlY3QgcC5Qcm9kdWN0SUQsIHAuTmFtZSBQcm9kdWN0TmFtZSwgcC5Db2xvciwgcy5OYW1lIFN1YmNhdGVnb3J5TmFtZQpGUk9NIFByb2R1Y3Rpb24uUHJvZHVjdCBwCkxFRlQgT1VURVIgSk9JTiBQcm9kdWN0aW9uLlByb2R1Y3RTdWJjYXRlZ29yeSBzCk9OIHAuUHJvZHVjdFN1YmNhdGVnb3J5SUQgPSBzLlByb2R1Y3RTdWJjYXRlZ29yeUlECldIRVJFIHAuQ29sb3IgPSAnYmxhY2snCi0tIFRyYXogbyBmaWx0cm8gcGFyYSBvIE9OIGRvIExFRlQgT1VURVIgSk9JTgpzZWxlY3QgcC5Qcm9kdWN0SUQsIHAuTmFtZSBQcm9kdWN0TmFtZSwgcC5Db2xvciwgcy5OYW1lIFN1YmNhdGVnb3J5TmFtZQpGUk9NIFByb2R1Y3Rpb24uUHJvZHVjdCBwCkxFRlQgT1VURVIgSk9JTiBQcm9kdWN0aW9uLlByb2R1Y3RTdWJjYXRlZ29yeSBzCk9OIHAuUHJvZHVjdFN1YmNhdGVnb3J5SUQgPSBzLlByb2R1Y3RTdWJjYXRlZ29yeUlECmFuZCBwLkNvbG9yID0gJ2JsYWNrJw==” hover_enabled=”0″]dXNlIEFkdmVudHVyZVdvcmtzCmdvCi0tICoqKiBMaXN0YXIgb3MgcHJvZHV0b3MgZGEgY29yIHByZXRhIHF1ZSB0ZW5oYW0gb3UgbsOjbyBzdWJjYXRlZ29yaWEKLS0gTWFudMOpbSBvIGZpbHRybyBubyBXSEVSRQpzZWxlY3QgcC5Qcm9kdWN0SUQsIHAuTmFtZSBQcm9kdWN0TmFtZSwgcC5Db2xvciwgcy5OYW1lIFN1YmNhdGVnb3J5TmFtZQpGUk9NIFByb2R1Y3Rpb24uUHJvZHVjdCBwCkxFRlQgT1VURVIgSk9JTiBQcm9kdWN0aW9uLlByb2R1Y3RTdWJjYXRlZ29yeSBzCk9OIHAuUHJvZHVjdFN1YmNhdGVnb3J5SUQgPSBzLlByb2R1Y3RTdWJjYXRlZ29yeUlECldIRVJFIHAuQ29sb3IgPSAnYmxhY2snCi0tIFRyYXogbyBmaWx0cm8gcGFyYSBvIE9OIGRvIExFRlQgT1VURVIgSk9JTgpzZWxlY3QgcC5Qcm9kdWN0SUQsIHAuTmFtZSBQcm9kdWN0TmFtZSwgcC5Db2xvciwgcy5OYW1lIFN1YmNhdGVnb3J5TmFtZQpGUk9NIFByb2R1Y3Rpb24uUHJvZHVjdCBwCkxFRlQgT1VURVIgSk9JTiBQcm9kdWN0aW9uLlByb2R1Y3RTdWJjYXRlZ29yeSBzCk9OIHAuUHJvZHVjdFN1YmNhdGVnb3J5SUQgPSBzLlByb2R1Y3RTdWJjYXRlZ29yeUlECmFuZCBwLkNvbG9yID0gJ2JsYWNrJw==[/et_pb_dmb_code_snippet]

Seguem os planos de execução de ambas as queries para comparação.

A query 1, com filtro no WHERE, retorna 93 linhas, enquanto que a query 2 retorna 504. Além do resultado divergente, a performance da segunda consulta é bem inferior à da primeira.

Neste ponto pergunto: qual query apresenta o resultado correto para o nosso exemplo? Ao trazer o filtro para a cláusula ON do LEFT OUTER JOIN na query 2, conforme já expliquei anteriormente, na subfase J2 – Filtro ON somente os produtos com cor preta são recuperados, mas, na subfase seguinte J3 – OUTER JOIN, as linhas que foram desconsideradas são adicionadas de volta (outer Rows). Assim, a segunda query não atende ao requisito especificado: listar os produtos da cor preta que tenham ou não subcategoria.

Conclusão

Não existe diferença de performance em fazer JOIN na cláusula WHERE ou na cláusula ON. No que concerne ao uso de filtros, também não há diferença de performance quando aplicados no WHERE ou totalmente no ON, ressalvando-se, evidentemente, os casos envolvendo OUTER JOIN.

 

Webcast Otimizando Consultas T-SQL

No dia 26/06/2012, às 21 horas, estarei apresentando um webcast abordando o tema Otimizando Consultas T-SQL. Neste webcast irei apresentar maneiras de como analisar a performance das consultas e deixar dicas e melhores práticas.

Aos interessados, segue o link para a inscrição:

https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032515701&culture=pt-BR