Arquivo da categoria: Mitos do SQL Server

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

Mitos do SQL Server – Será que a opção IGNORE_DUP_KEY permite entradas duplicadas na chave primária e índices únicos ?

Boa Noite Pessoal,

Seguindo tempestivas semanas de trabalho, algumas aulas sendo ministradas, provas de certificação marcadas e diversas outras pendências está realmente complicado manter a postagem semanal além de comparecer nos fóruns e até responder os e-mails e dúvidas de SQL Server (estou tentando colocar tudo em dia). Ontem apresentei uma aula sobre índices e notei que em uma das transparências aparecia a opção Ignore_Dup_Key. A idéia era meramente citar a opção, mas esse nome leva a interpretações equivocadas. Para não perder o hábito, resolvi discorrer um pouco mais sobre essa opção que ao contrário do que parece não permite a entrada de registros duplicados em uma chave primária, uma constraint unique ou um índice unique.

A relação entre constraints de unicidade e índices

Existem duas constraints relacionadas a unicidade (ou como alguns dizem, a integridade de entidade). A primeira é a chave primária e a segunda é a unique constraint. Normalmente a primeira opção é mais conhecida, pois, além de possibilitar unicidade é obrigatória para permitir relacionamentos. A segunda possui essas características embora não seja destinada a esse fim. Ela normalmente é utilizada para garantir a unicidade das chaves secundárias. Tanto a chave primária quanto a unique constraint são critérios lógicos e não físicos. Isso significa que uma chave primária ou uma unique constraint nada tem a ver com a unicidade dos registros diretamente. Para que essa unicidade possa ser garantida do ponto de vista físico, a criação dessas estruturas lógicas faz com que fisicamente índices sejam implementados. Ex:

— Cria uma tabela
CREATE TABLE Clientes (
    ID INT NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    CPF CHAR(11) NOT NULL,

CONSTRAINT PK_Clientes PRIMARY KEY (ID),
CONSTRAINT UQ_CPF UNIQUE (CPF))

— Verifica os índices existentes
EXEC sp_helpindex ‘Clientes’

— Verifica as constraints existentes
EXEC sp_helpconstraint ‘Clientes’

Como pode ser observado, a criação de uma chave primária e uma constraint unique provocou a criação de dois índices unique (mesmo que não de forma explícita). A criação desses índices é a implementação física que possibilita que restrições lógicas como a chave primária e a unique constraint possam ser garantidas. Não importa se a constraint é uma chave primária ou uma unique constraint, pois, fisicamente é criado um índice único. A ligação entre as constraints e os índices é indissiociável. Não é possível excluir os índices se as constraints existirem. A exclusão das constraints provoca a exclusão dos índices.

Os efeitos da opção IGNORE_DUP_KEY

O script abaixo remove as constraints previamente criadas e adiciona a propriedade IGNORE_DUP_KEY nas constraints. Essas propriedades não são pertinentes as constraints em si, mas são aplicáveis aos índices que serão criados por conta das constraints.

— Retira as Constraints da tabela de clientes
ALTER TABLE Clientes DROP CONSTRAINT PK_Clientes
ALTER TABLE Clientes DROP CONSTRAINT UQ_CPF

— Adiciona as constraints com a opção IGNORE_DUP_KEY
ALTER TABLE Clientes ADD CONSTRAINT PK_Clientes
    PRIMARY KEY (ID) WITH (IGNORE_DUP_KEY = ON)

ALTER TABLE Clientes ADD CONSTRAINT UQ_CPF
    UNIQUE (CPF) WITH (IGNORE_DUP_KEY = ON)

Agora que a chave primária foi criada com a opção de ignorar registros repetidos, resta checar se isso é semelhante a permitir registros duplicados.

— Insere um cliente com o ID 1 e com o ID 2
INSERT INTO Clientes VALUES (1, ‘Rodrigo Borges Freitas’, ‘70036580923’)
INSERT INTO Clientes VALUES (2, ‘Iara Mendes Oliveira’, ‘20136789102’)

— Insere um novo cliente com o ID 1 (já existente)
INSERT INTO Clientes VALUES (1, ‘Natasha Gomes Siqueira’, ‘32205631997’)

O conjunto de comandos retorna a seguintes sequência de mensagens no SQL Server Management Studio:

(1 row(s) affected)

(1 row(s) affected)
Duplicate key was ignored.

(0 row(s) affected)

Os dois primeiros INSERTs foram contemplados na tabela, mas já o terceiro INSERT foi simplesmente “ignorado” e por isso a mensagem “(0 row(s) affected)”. Impedir a duplicação de chaves primárias é um requisito inviolável para permitir relacionamentos, pois, se chaves primárias pudessem ser duplicadas e houvesse dois registros com o ID igual a 1, qual seria o sentido das tabelas que tivessem uma FK apontando para esse ID ? Seria ilógico.

A unique constraint não é utilizada em relacionamentos e sobre certos aspectos menos restritiva. Será que a opção IGNORE_DUP_KEY possibilitaria a duplicação ?

— Insere um novo cliente com o ID 3, porém com CPF repetido
INSERT INTO Clientes VALUES (3, ‘Natasha Gomes Siqueira’, ‘70036580923’)

Após a execução desse cadastro, a mensagem é a mesma que a anterior. O título “(0 row(s) affected)” e uma instrução SELECT podem confirmar que o terceiro registro não foi inserido. Nada mais lógico, pois, as restrições não se devem às particularidades de cada constraint já que ambas criam um índice único e rejeitam registros repetidos da mesma forma embora não retornem nenhuma mensagem de erro. Será que o mesmo se mantêm para inserções em conjunto e não em linha ?

— Cria uma tabela com a mesma estrutura porém sem restrições
CREATE TABLE ClientesTmp (
    ID INT NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    CPF CHAR(11) NOT NULL)

— Insere quatro registros
INSERT INTO ClientesTmp VALUES (1,‘Bruno Morais Jr.’,‘45233310738’)
INSERT INTO ClientesTmp VALUES (3,‘Carla Martins Praxedes’,‘20136789102’)
INSERT INTO ClientesTmp VALUES (4,‘Eugênio Silva Batista’,‘58017439501’)
INSERT INTO ClientesTmp VALUES (5,‘Clístenes Jorge Moura’,‘58017439501’)

— Tenta inserir todos os registros de ClientesTMP em Clientes
— A cláusula OUTPUT mostra o registro inserido

INSERT INTO Clientes (ID, Nome, CPF)
OUTPUT INSERTED.*
SELECT ID, Nome, CPF FROM ClientesTmp

Embora a tabela ClientesTmp tivesse 4 registros, apenas um registro foi inserido na tabela Clientes que conforme mostrado pela cláusula OUTPUT foi o ID 4. Os demais foram simplesmente ignorados. Não é difícil descobrir o porquê. O ID 1 já existia na tabela Clientes. O ID 3 possui um CPF que já está sendo utilizado pelo ID 2 na tabela Clientes e o ID 4 e 5 possuem o mesmo CPF. No momento em que o ID 4 foi inserido, o ID 5 passa a representar um CPF em repetição e por isso foi desconsiderado. Em outras palavras, o uso da opção IGNORE_DUP_KEY faz com que todos os registros sejam inseridos, descartando-se apenas os duplicados. Alguns podem achar que esse já era o comportamento padrão mesmo sem a opção IGNORE_DUP_KEY. Será mesmo ?

— Retira as Constraints da tabela de clientes
ALTER TABLE Clientes DROP CONSTRAINT PK_Clientes
ALTER TABLE Clientes DROP CONSTRAINT UQ_CPF

— Adiciona as constraints sem a opção IGNORE_DUP_KEY
ALTER TABLE Clientes ADD CONSTRAINT PK_Clientes PRIMARY KEY (ID)
ALTER TABLE Clientes ADD CONSTRAINT UQ_CPF UNIQUE (CPF)

— Exclui o ID 4 da tabela Clientes para tentar carregá-lo novamente
DELETE FROM Clientes WHERE ID = 4

— Tenta inserir todos os registros de ClientesTMP em Clientes
INSERT INTO Clientes (ID, Nome, CPF)
SELECT ID, Nome, CPF FROM ClientesTmp

Dessa vez o efeito não foi exatamente o mesmo. Embora o ID 4 fosse o único que pudesse ser inserido em Clientes a partir de ClientesTmp, o fato de haver outros rejeitos rejeitáveis fez com que toda a operação fosse abortada.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_Clientes’. Cannot insert duplicate key in object ‘dbo.Clientes’.
The statement has been terminated.

O teste da unique constraint sequer foi realizado, pois, o primeiro registro (ID 1) já provoca uma violação da chave primária PK_Clientes.

Após todos esses testes está mais que comprovodo que a opção IGNORE_DUP_KEY ignora os registros duplicados, mas isso em hipótese nenhuma significa que as duplicações são permitidas apenas porque essa opção foi especificada. A única coisa que essa opção faz é ignorar as duplicações descartando-as sem levantar uma exceção. Existem momentos em que isso é desejável, mas também existem outros que não. O fato é que o SQL Server, assim como qualquer banco de dados relacional sério não permitirá repetições em chaves primárias, constraints uniques, ou melhor dizendo em índices unique. Se alguém dissemina essa informação, está apenas disseminando mais um desses mitos não comprovados que vimos por aí.

[ ]s,

Gustavo

Mitos do SQL Server – Será que COUNT(1) ou COUNT(‘X’) são mais performáticos que COUNT(*) ?

Bom Dia Pessoal,

Primeiro eu gostaria de agradecer os vários feedbacks que tive sobre a recém categoria "Mitos – SQL Server". A idéia de criar essa categoria surgiu meio que de brincadeira e achei que seria apenas mais um post dos vários que faço e não possuem comentários. Ao contrário do que imaginava, me surpreendi com os feedbacks, comentários e pedidos de novos mitos a serem desmistificados. É esse tipo de retorno que realmente motiva a elaborar artigos, webcasts, vídeos e continuar contribuindo com a comunidade.

Eu havia planejado um outro post para essa semana falando de ferramentas para o SQL Server, mas em virtude dos ótimos feedbacks resolvi adiá-lo um pouco. Hoje falarei sobre mais um desses mitos e lendas urbanas que muito se repete, mas pouco se investiga. Quando comecei a aprender SQL (faz um tempinho já), um analista me disse que quando fosse utilizar o COUNT, não usasse o COUNT(*), mas sim o COUNT(1) ou alguma outra expressão como COUNT(‘X’), COUNT(0), etc. O importante era manter o conteúdo do COUNT o menor possível já que é melhor contar um valor fixo para cada linha do que utilizar o * que supostamente leria a tabela inteira e posteriormente faria a contagem. Como todo aprendiz, nada mais sensato que observar e aprender com os mais experientes. Se alguém experiente me disse que era da forma X, como poderia um aprendiz discordar ?

O tempo passou e fui notando que não só o analista, mas muitos outros analistas, desenvolvedores, etc realmente acreditam que o COUNT(1) é bem mais performático que o COUNT(*) pelo mesmo motivo. O COUNT(1) irá gerar uma coluna com o valor para todas as linhas e fazer a contagem enquanto que o COUNT(*) irá recuperar todas as linhas e todos os campos da tabela (afinal é *) para posteriormente fazer a contagem. Para pequenas tabelas isso é irrelevante, pois, outros fatores como rede, disco, etc vão ocupar boa parte do tempo, já para grandes tabelas, o negócio é utilizar o COUNT(1). Vejo essa afirmação tão forte em alguns lugares que um dia desses me deparei com o seguinte comentário em uma comunidade voltada para tunning de SQL (algumas pequenas adaptações foram feitas para preservar a identidade do autor).

"Após diálogo com o consultor da Empresa Tunning SQL Associates, ficou esclarecido que usar de (1) no lugar de (*) é mais performático. Quando se usa (*) o SGBD XPTO monta as colunas, mesmo não trazendo valor. Já a utilização do (1) faz com que o banco XPTO não monte a estrutura das colunas e sim retornar apenas o valor (1)."

O resto do post tinha os devidos esclarescimentos, mas eu fico imaginando o que será que alguém leigo irá pensar quando ler uma afirmação dessas. É muito factível de acreditar, pois, faz todo o sentido. Vejamos se o que parece fazer sentido realmente está correto. O script abaixo cria uma tabela com 33 colunas e popula a mesma com 10.000 registros. Exagerei em alguns tipos de dados para realmente tornar o registro largo.

— Cria a tabela
CREATE TABLE T (
    ID BIGINT IDENTITY(1,1), VERSAO TIMESTAMP,
    UG UNIQUEIDENTIFIER DEFAULT NEWID(),
    C01 BIGINT, C02 BIGINT, C03 BIGINT, C04 BIGINT, C05 BIGINT,
    C06 BIGINT, C07 BIGINT, C08 BIGINT, C09 BIGINT, C10 BIGINT,
    C11 BIGINT, C12 BIGINT, C13 BIGINT, C14 BIGINT, C15 BIGINT,
    C16 BIGINT, C17 BIGINT, C18 BIGINT, C19 BIGINT, C20 BIGINT,
    C21 BIGINT, C22 BIGINT, C23 BIGINT, C24 BIGINT, C25 BIGINT,
    C26 BIGINT, C27 BIGINT, C28 BIGINT, C29 BIGINT, C30 BIGINT)

— Insere dez mil registros com valores aleatórios
DECLARE @i INT
SET @i = 1

WHILE @i <= 10000
BEGIN
    INSERT INTO
T (
        C01, C02, C03, C04, C05, C06, C07, C08, C09, C10,
        C11, C12, C13, C14, C15, C16, C17, C18, C19, C20,
        C21, C22, C23, C24, C25, C26, C27, C28, C29, C30)
    VALUES (
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()))
    SET @i = @i + 1
END

Para tornar as coisas realmente interessantes, farei uma consulta com quatro variações (0, 1, ‘X’ e *) para fins comparativos.

— Ativa as medições de tempo e IO
SET STATISTICS TIME ON
SET STATISTICS IO ON

— Retorna todos os registros de 4 formas diferentes
SELECT COUNT(‘X’) FROM T
SELECT COUNT(1) FROM T
SELECT COUNT(0) FROM T
SELECT COUNT(*) FROM T

Após executar o batch duas vezes obtive o seguinte resultado: (é necessário executar duas vezes para que as haja igualdade no cachê de dados e no plano de execução).

Consulta Tempo IO Necessário
COUNT(‘X’) 06ms Table ‘T’. Scan count 1, logical reads 371, physical reads 0
COUNT(1) 10ms Table ‘T’. Scan count 1, logical reads 371, physical reads 0
COUNT(0) 83ms Table ‘T’. Scan count 1, logical reads 371, physical reads 0
COUNT(*) 14ms Table ‘T’. Scan count 1, logical reads 371, physical reads 0

Em termos de IO houve um empate, mas a questão do tempo favorece o uso do COUNT(‘X’) e do COUNT(1). É preciso ter cautela com essa medição, pois, embora pareça, o COUNT(‘X’) não é o mais rápido de todos como demonstra a estatística. Seria muito pouco provável que os tempos dessem exatamente os mesmos, pois, vale lembrar que a execução da consulta levará em conta muitos detalhes de distribuição de recursos como o quantum da CPU e o uso do discos por outras tarefas do sistema operacional. Se pelo menos estivéssemos falando de uma consulta que leva 1ms e outra que leva 1s ou que na média os tempos batessem, poderíamos fazer conclusões, mas apenas por essa curta estatística não é possível afirmar quem é o mais rápido (até porque os tempos podem diferir em outras execuções). Vejamos o plano de execução da consulta:

O que pode ser observado é que as quatro consultas são exatamente as mesmas. Dentro de um mesmo batch, cada uma levou 25% e os custos de IO foram exatamente os mesmos, o que permite (com excelente precisão) afirmar que as consultas são idênticas. Foi necessário ler toda a tabela (Table Scan) para posteriormente fazer a contagem (e isso inclui todas as colunas). Os resultados já permitem desmentir parcialmente o mito de que o COUNT(1) é mais eficaz que o COUNT(*) por exemplo. Alguns pontos ainda merecem ser esclarescidos nesse exemplo.

O primeiro ponto é que de forma nenhuma um banco de dados armazena os dados em um formato estilo Excel ou bloco de notas, ou seja, com as colunas organizadas adequadamente e os registros representando uma linha exata da planilha com a devida quebra. O armazenamento dos dados em um banco de dados normalmente obedece a formatos proprietários e nem de longe está próximo de ser uma planilha na qual as demais colunas pudessem ser simplesmente ignoradas para a contagem.

Outro ponto a se considerar é que os bancos de dados em geral fazem leituras por bloco e não por registros (embora possam impor bloqueios em nível de registro). Os blocos da tabela contém todas as colunas e se ele for requisitado, todas as colunas deverão estar presentes. Afinal não é possível ir ao disco, e recuperar um pedaço do bloco descartando as demais colunas (até porque como disse, o bloco não é uma planilha ou arquivo texto). Essas duas características fazem com que realmente seja impossível que o COUNT(1) seja superior ao COUNT(*) em termos de desempenho tornando inválida a suposição de que qualquer COUNT é mais performático que o COUNT(*).

Ainda que o COUNT(1) contasse uma coluna virtual com o valor 1, seria necessário ler todos os blocos da tabela e posteriormente montar a coluna virtual para efetuar a contagem. Se todas colunas já foram recuperadas do disco e a contagem dos registros já pode ser feita, qual seria a vantagem de ter o passo adicional da montagem da coluna virtual com o valor 1 ? Simplesmente nenhuma. O uso do COUNT(1) ao pé da letra seria inclusive mais lento e por isso que o otimizador de consulta simplesmente o ignora e produz exatamente o mesmo plano que o COUNT(*). Ainda que fosse utilizado algo como COUNT(Coluna), o efeito prático seria o mesmo.

A influência dos índices em expressões do tipo COUNT

Como será que os índices podem influenciar em uma situação desse tipo ? Alguns dirão que se o COUNT for feito sobre a coluna do índice haverá um melhor desempenho. Será que os índices podem de fato desempatar e tornar o COUNT(*) mais lento ? Nada melhor do que um teste prático. O script a seguir cria um índice sobre a coluna ID.

— Cria um índice sobre a coluna ID
CREATE INDEX IX_ID ON T (ID)

Agora que existe um índice sobre a coluna ID, vejamos algumas variações do COUNT e seus resultados.

— Ativa as medições de tempo e IO
SET STATISTICS TIME ON
SET STATISTICS IO ON

— Retorna todos os registros de 4 formas diferentes
SELECT COUNT(‘X’) FROM T
SELECT COUNT(1) FROM T
SELECT COUNT(ID) FROM T
SELECT COUNT(*) FROM T

Após executar duas vezes o seguinte resultado é obtido:

Consulta Tempo IO Necessário
COUNT(‘X’) 41ms Table ‘T’. Scan count 1, logical reads 26, physical reads 0
COUNT(1) 11ms Table ‘T’. Scan count 1, logical reads 26, physical reads 0
COUNT(ID) 11ms Table ‘T’. Scan count 1, logical reads 26, physical reads 0
COUNT(*) 113ms Table ‘T’. Scan count 1, logical reads 26, physical reads 0

Como disse anteriormente, a estatística de tempo é importante, mas ela varia de acordo com alguns fatores e nesse caso ela sozinha não é conclusiva. Vejamos o plano de execução.

O COUNT(0) foi descartado porque o resultado é o mesmo do COUNT(1). Dessa vez, ao invés do COUNT(0) foi utilizada a coluna exatamente utilizada pelo índice e de forma semelhante ao exemplo anterior o resultado foi exatamente o mesmo. Ainda que a coluna ID seja utilizada no COUNT e a mesma possui um índice ela não não apresentou um desempenho superior às demais opções. O plano de execução demonstra que independente de COUNT(1), COUNT(*) ou COUNT(ID), todas as construções utilizaram o índice sobre ID.

Inicialmente poderia pensar-se que o COUNT(ID) deveria ser mais rápido. Um bloco de índice teria somente a coluna ID e alguns ponteiros enquanto um bloco de dados teria não somente a coluna ID como todas as demais colunas da tabela. Assim a contagem com base no ID devem varrer os blocos de índices (26 blocos no total) e as demais construções deveriam varrer os blocos de dados (371 blocos) sendo portanto mais lentas.

Essa tese até faz algum sentido, mas antítese é essencialmente bem simples quando a teste. Se a contagem de entradas nos blocos de índice e nos blocos de dados é exatamente a mesma, qual é o sentido de varrer os blocos de dados ? É bem mais simples ir nos blocos de índice. A contagem de ambas será sempre a mesma, pois, a coluna ID é preenchida em todos os registros e um COUNT no índice ou nos dados é o mesmo. O otimizador então irá preferir ler 26 blocos (Índice) do que 371 blocos (tabela).

O uso do índice funcionou, mas pressupõe que a coluna ID sempre estará preenchida. Será que o raciocínio se mantém para colunas que aceitem valores nulos ? O script abaixo faz uma demonstração

— Elimina o índice sobre ID
DROP INDEX T.IX_ID

— Atualiza 1000 registros tornando nulo C1
UPDATE T SET C01 = NULL WHERE ID <= 1000

— Cria um índice sobre C1
CREATE INDEX IX_ID ON T (C01)

— Retorna todos os registros de 4 formas diferentes
SELECT COUNT(‘X’) FROM T
SELECT COUNT(1) FROM T
SELECT COUNT(C01) FROM T
SELECT COUNT(*) FROM T

Após executar duas vezes (apenas o COUNT) o seguinte resultado é obtido:

Consulta Tempo IO Necessário
COUNT(‘X’) 04ms Table ‘T’. Scan count 1, logical reads 30, physical reads 0
COUNT(1) 16ms Table ‘T’. Scan count 1, logical reads 30, physical reads 0
COUNT(C01) 12ms Table ‘T’. Scan count 1, logical reads 30, physical reads 0
COUNT(*) 163ms Table ‘T’. Scan count 1, logical reads 30, physical reads 0

O tempo dispensa maiores comentários (embora seja tendencioso para condenar o *). O plano de execução é exposto abaixo:

E novamente o IO e o plano foi exatamente o mesmo. Ainda que o índice não seja sobre colunas obrigatórios, a leitura do índice foi realizada por todas as construções e elas "empataram". Todas leram as 30 páginas de índice ao invés de passar por 371 páginas da tabela (o que era esperado, pois, é bem mais eficiente). Ainda que o COUNT(1) não tenha sido superior ao COUNT(*) é necessário atentar-se para um detalhe. O plano de execução tem uma mensagem após a execução do COUNT(C01).

Warning: Null value is eliminated by an aggregate or other SET operation

Esse aviso só apareceu na consulta com a coluna C01 e embora o desempenho tenha sido o "mesmo", a mensagem indica que há valores nulos que foram eliminados durante a consulta. O resultado da consulta é o seguinte:

Embora o plano de execução tenha sido praticamente o mesmo, no caso da contagem com base na coluna do índice, valores nulos foram eliminados. Antes da criação do índice, a coluna C01 tinha 10.000 valores preenchidos assim como a tabela tinha 10.000 linhas. Entretanto, o UPDATE tornou nulo 1.000 desses registros e por isso a contagem retornou 9.000. Afinal existem 10.000 linhas, mas apenas 9.000 IDs. Ainda assim, o uso do índice é utilizado para todas as variações do COUNT. No caso de colunas nulas, há apenas uma etapa posterior que é a eliminação de valores nulos para fazer a contagem. Isso não torna o uso do COUNT(1), COUNT(0) ou COUNT(‘X’) mais ou menos performático que o COUNT(*) visto que ambas as construções não estão sujeitas aos mesmos comportamentos de uma coluna não nula.

Felizmente nessa mesma comunidade, ao final do post, houve um comentário muito sensato:

"Após consultar a obra de Peter Gultuzan (SQL Performance Tuning), constatei que dos 8 maiores SGBDs, entre eles Oracle, DB2 e SqlServer o uso dessas sentenças não apresentou diferencas de performance entre si… Após fazer um teste no banco Z2X não vi nenhum ganho digno de nota (nem mesmo 5%)."

Como podemos perceber, COUNT(1), COUNT(‘X’) ou COUNT(QualquerExpressaoQueNãoSejaUmaColuna) têm exatamente o mesmo comportamento, resultado e desempenho de uma instrução COUNT(*). Quando se utilizam colunas, algumas surpresas podem acontecer conforme demonstrado. O uso do COUNT(*), COUNT(1), etc também pode ter efeitos adversos quando em conjunto com OUTER JOINs. Isso não refere-se ao desempenho, mas aos resultados esperados. Maiores detalhes em:

Piores Práticas – Uso do COUNT(*)
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!538.entry

[ ]s,

Gustavo

Mitos do SQL Server – A ordem das tabelas influencia no desempenho de uma instrução SELECT ?

Olá Pessoal,

Para inaugurar a categoria "Mitos do SQL Server" em relação ao SQL Server, escolhi uma thread que vi nos fóruns de SQL Server do MSDN e do Technet intitulada "A ordem das joins pode afetar a performance de uma consulta no sql ?". Independente da resposta, estou certo que qualquer um que trabalha (ou já trabalhou) com SQL já teve essa dúvida (mesmo que nos primeiros contatos). E será que faz diferença ? Além de pensar na elaboração da consulta ainda é preciso pensar na ordem das tabelas para deixá-la mais performática ? Ao invés de simplesmente dar uma resposta e talvez perpetuar um mito, nada melhor que tentar encontrar a resposta de forma verídica. A seguir três versões da mesma consulta no Adventure Works.

— Ordem 1
— Employee, Contact, SalesOrderHeader
SELECT
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate,
    COUNT(S.SalesOrderID) As TotalVendas
FROM
    HumanResources.Employee As E
    INNER JOIN Person.Contact As C ON E.ContactID = C.ContactID
    INNER JOIN Sales.SalesOrderHeader As S ON E.EmployeeID = S.SalesPersonID
GROUP BY
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate

— Ordem 2
— Contact, Employee, SalesOrderHeader

SELECT
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate,
    COUNT(S.SalesOrderID) As TotalVendas
FROM
    Person.Contact As C
    INNER JOIN HumanResources.Employee As E ON C.ContactID = E.ContactID
    INNER JOIN Sales.SalesOrderHeader As S ON E.EmployeeID = S.SalesPersonID
GROUP BY
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate

— Ordem 3
— SalesOrderHeader, Contact, Employee

SELECT
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate,
    COUNT(S.SalesOrderID) As TotalVendas
FROM
    Sales.SalesOrderHeader As S
    INNER JOIN HumanResources.Employee As E ON E.EmployeeID = S.SalesPersonID
    INNER JOIN Person.Contact As C ON E.ContactID = C.ContactID
GROUP BY
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate

Como a consulta retorna exatamente os mesmos resultados, executei as três juntas duas vezes para que os planos de execução e páginas fiquem em memória e assim o resultado seja mais imparcial. Se apenas a primeira execução fosse considerada, talvez a segunda consulta se beneficiasse das páginas em memória da primeira consulta e talvez a terceira consulta tivesse a mesma vantagem em relação a segunda consulta.

A execução dessas três consultas em um mesmo BATCH com a análise do plano de execução, estatísticas de tempo e IO é mostrada a seguir:

Ordem Tempo IO necessário
Employee, Contact, SalesOrderHeader 223 ms Table ‘SalesOrderHeader’. Scan count 290, logical reads 641
Table ‘Contact’. Scan count 0, logical reads 607
Table ‘Employee’. Scan count 1, logical reads 9
Contact, Employee, SalesOrderHeader 266 ms Table ‘SalesOrderHeader’. Scan count 290, logical reads 641
Table ‘Contact’. Scan count 0, logical reads 607
Table ‘Employee’. Scan count 1, logical reads 9
SalesOrderHeader, Contact, Employee 211 ms Table ‘SalesOrderHeader’. Scan count 290, logical reads 641
Table ‘Contact’. Scan count 0, logical reads 607
Table ‘Employee’. Scan count 1, logical reads 9

O plano de execução das três consultas foi exatamento mesmo conforme a figura abaixo:

As estatísticas de tempo, o gasto de IO o percentual de 33% para cada consulta e o mesmo plano de execução evidenciam que as três consultas são exatamente idênticas tanto no resultado quando no desempenho. Pode-se argumentar que o tempo de execução tem ligeiras diferenças em ms e que isso talvez sugira que a terceira consulta seja a mais eficiente. Isso no entanto não é verdade. Esse tempo é obtido conforme a disponibilidade de recursos da máquina na ocasião da execução e a provável diferença pode ter se dado porque naquele momento uma fração mínima de quantum estava mais ou menos disponível para uma determinada consulta. Não há como o tempo delas ser exatamente o mesmo, mas devido a baixíssima variação e aos demais indicadores concluí-se que as consultas são exatamente as mesmas mudando apenas sua sintaxe.

Esse resultado por si só já parece suficiente para refutar a hipótese de que a ordem das tabelas influencia no desempenho de uma instrução SELECT visto que as três consultas têm exatamente o mesmo desempenho. Sim, é o que parece, mas na verdade o que se concluiu até agora é que as três consultas têm exatamente o mesmo desempenho não porque a ordem não tenha diferença, mas sim porque não importa a ordem, o SQL Server compilou todas para fazer o JOIN na ordem que ele julgou melhor, ou seja, primeiro é feito o JOIN entre HumanResources.Employee com a tabela Person.Contacts e o resultado dessa junção é submetido a um novo JOIN contra Sales.SalesOrderHeader conforme a figura abaixo:

Para saber mesmo se a ordem das tabelas faz ou não diferença no desempenho de uma instrução SELECT, é necessário "sobrescrever" as idéias do otimizador através de HINTs. No caso da ordem das tabelas, isso pode ser feito com o HINT FORCE ORDER conforme o script a seguir:

— Ordem 1
— Employee, Contact, SalesOrderHeader
SELECT
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate,
    COUNT(S.SalesOrderID) As TotalVendas
FROM
    HumanResources.Employee As E
    INNER JOIN Person.Contact As C ON E.ContactID = C.ContactID
    INNER JOIN Sales.SalesOrderHeader As S ON E.EmployeeID = S.SalesPersonID
GROUP BY
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate
OPTION (FORCE ORDER)

— Ordem 2
— Contact, Employee, SalesOrderHeader

SELECT
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate,
    COUNT(S.SalesOrderID) As TotalVendas
FROM
    Person.Contact As C
    INNER JOIN HumanResources.Employee As E ON C.ContactID = E.ContactID
    INNER JOIN Sales.SalesOrderHeader As S ON E.EmployeeID = S.SalesPersonID
GROUP BY
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate
OPTION (FORCE ORDER)

— Ordem 3
— SalesOrderHeader, Contact, Employee

SELECT
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate,
    COUNT(S.SalesOrderID) As TotalVendas
FROM
    Sales.SalesOrderHeader As S
    INNER JOIN HumanResources.Employee As E ON E.EmployeeID = S.SalesPersonID
    INNER JOIN Person.Contact As C ON E.ContactID = C.ContactID
GROUP BY
    C.Title, C.FirstName, C.MiddleName, C.LastName, C.EmailAddress, C.Phone,
    E.NationalIDNumber, E.LoginID, E.HireDate
OPTION (FORCE ORDER)

A execução das consultas alteradas teve as seguintes estatísticas de tempo e IO:

Ordem Tempo IO necessário
Employee, Contact, SalesOrderHeader 201 ms Table ‘SalesOrderHeader’. Scan count 290, logical reads 641
Table ‘Contact’. Scan count 0, logical reads 607
Table ‘Employee’. Scan count 1, logical reads 9
Contact, Employee, SalesOrderHeader 264 ms Table ‘SalesOrderHeader’. Scan count 290, logical reads 641
Table ‘Employee’. Scan count 1, logical reads 9
Table ‘Contact’. Scan count 1, logical reads 41
SalesOrderHeader, Contact, Employee 213 ms Table ‘Worktable’. Scan count 0, logical reads 0
Table ‘Contact’. Scan count 1, logical reads 34
Table ‘Employee’. Scan count 1, logical reads 9
Table ‘SalesOrderHeader’. Scan count 1, logical reads 57

Antes de partir para os planos de execução, um certo detalhe merece atenção. A última consulta produziu uma tabela extra chamada Worktable. Além do acesso a Contact, Employee, e SalesOrderHeader também houve acesso a uma tabela denominada Worktable. Essa tabela representa uma armazenamento temporário (entenda-se uma tabela temporária criada pelo SQL Server) para que o resultado da consulta pudesse ser trabalhado de forma intermediária o que já é um indicativo de piora em relação às demais soluções. Os planos de execução são mostrados abaixo:

Plano de Execução – Consulta 1 (Employee, Contact, SalesOrderHeader)

Plano de Execução – Consulta 2 (Contact, Employee, SalesOrderHeader)

Plano de Execução – Consulta 3 (SalesOrderHeader, Contact, Employee)

Como pode-se notar de um total de 100%, a primeira consulta gasta 11%, a segunda 37% e a terceira 52%. Isso leva a crer que a ordem das tabelas realmente faz toda a diferença no desempenho de uma consulta. As diferenças são consideráveis levando-se em conta que a primeira consulta representa apenas 11% do gasto total e que a terceira consulta representa um esforço maior que as duas anteriores juntas.

A primeira consulta é mais eficiente porque lista as tabelas menores primeiro. A tabela Employees é especificada primeiro e por ser relativamente pequena pode ser colocada em memória e lida com muito mais facilidade. O JOIN entre Employees e Contact irá produzir um resultado com base nos critérios do JOIN e considerando que o relacionamento de Employees e Contact é de 1:1, é de se esperar que o resultado desse JOIN seja exatamente a quantidade de empregados existentes (290). Isso é mais eficiente que fazer o JOIN entre Contact e Employees (se Contact vier primeiro), pois, existem 19972 registros em Contact e colocá-la em memória para lê-la é bem mais dispendioso até porque desses 19972, apenas 290 seriam válidos, o que levaria a um esforço maior do que a primeira opção. Especificar a SalesOrderHeader em primeiro lugar seria a pior escolha, pois, ela conta com 31465 registros e representa a maior tabela.

É difícil elaborar uma regra geral, mas o que normalmente seria o mais recomendável para todos os casos é especificar as tabelas menores primeiro e as maiores por último.

Algumas considerações

Esse teste permite concluir que o mito é sim verdadeiro e que inegavelmente a ordem em que as tabelas são especificadas influencia sim no desempenho de uma consulta. Entretanto, isso não significa que seja necessário preocupar-se com isso. Se os otimizadores fossem os mesmos de vinte anos atrás realmente seria preciso uma atenção especial na ordem em que as tabelas são especificadas para elaborar consultas de alto desempenho. O pior é que à medida que novos registros são inseridos, alterados e excluídos, uma consulta que anteriormente tivesse um alto desempenho poderia representar uma escolha ruim, pois, as premissas de qual é a ordem correta estariam alteradas. Sem dúvida, seria um CAOS ter de se preocupar com a formulação da consulta e de revisões periódicas para verificar se a ordem não ficou "errada" por conta de alguma carga e (ou) expurgo de dados.

Por essas e outras que os otimizadores tiveram que adaptar-se e se tornarem mais inteligentes. Que a ordem faz diferença não há dúvida, mas também não há dúvida de que o otimizador irá avaliar exatamente qual é a melhor ordem. Não é preciso mais preocupar-se com a ordem "correta", pois, o otimizador já irá se encarregar disso automaticamente dispensando esse cuidado. Aqueles que têm a preocupação com a ordem e fazem disso um aspecto imprescindível da otimização das consultas são aqueles que ainda pensam como os otimizadores de vinte anos atrás e talvez nunca tenham testado as diferentes soluções para ver se nos tempos atuais isso ainda faz diferença (felizmente não).

Há quem julgue que talvez o uso do HINT com a ordem "correta" dispense o custo extra do otimizador avaliar a melhor ordem. Sinceramente não acho um argumento válido, visto que sobrescrever o otimizador, irá gerar um trabalho extra de avaliar constamente se o uso do HINT FORCE ORDER ainda será mais eficiente. Vale a pena lembrar que o ganho também é imperceptível. Se o exemplo for testado com o HINT FORCE ORDER na melhor ordem e sem o HINT, o desempenho é o mesmo.

Então não se preocupe com a ordem em que as tabelas são especificadas na cláusula SELECT. É melhor investir tempo em tentar otimizar outros aspectos como índices, bloqueios, predicados bem elaborados, etc.

[ ]s,

Gustavo

Nova Categoria – Mitos (Verdades e Mentiras sobre o SQL Server)

Boa Noite Pessoal,

A definição de um mito é uma explicação para algo desconhecido. Normalmente quando não se conhece o porquê de determinado assunto, busca-se uma explicação nas fontes de conhecimentos disponíveis para tentar compreender o porque de uma dada realidade. As fontes de conhecimento utilizadas na construção de um mito não necessariamente têm embasamento científico como é o caso dos ditos populares, opiniões baseadas puramente no senso comum ou ainda as velhas histórias de pescador. O mito pode parecer "verdade" por algum tempo, mas este pode ser quebrado em virtude da sua fragilidade devido a fontes de conhecimento pouco sólidas ou ainda mudança nas bases científicas que o embasam, ou seja, novas descobertas que tornam falsas algumas premissas que os mitos consideravam como verdadeiras.

Já há algum tempo, venho querendo escrever sobre alguns mitos a cerca do SQL Server. É impressionante, como algumas "verdades" são repassadas entre DBAs, analistas, programadores, etc que juram que determinado recurso, funcionalidade ou característica se comporta de tal forma quando na realidade tem um funcionamento completamente diferente. É engraçado ler alguns desses mitos como "instruções SQL são processadas de cima para baixo", "a ordem das tabelas influencia o desempenho", "os campos na cláusula WHERE devem ser especificados na ordem da chave", etc. O problema com esses mitos não é propriamente a desconstração que eles podem vir a provocar, mas sim, o efeito negativo, pois, se muitos passarem a acreditar, problemas podem ocorrer. Ao contrário de alguns exemplos na política, no mercado financeiro, na psicologia, etc quando o assunto é SQL Server, uma mentira repetida várias vezes será sempre uma mentira e talvez alguns desses mitos levem a limitações ou esforços desnecessários.

A idéia dessa categoria é analisar esses "mitos" que aparecem por aí e testá-los de fato para ver se consistem de fato uma verdade ou uma mentira. É possível que alguns deles mudem com o tempo, mas estaremos sempre analisando nas condições atuais.

Logo mais estarei postando alguns desses mitos para análise. Os interessados podem pesquisar por todos os mitos de SQL Server postados diretamente na categoria "Mitos" ou no link abaixo:

http://gustavomaiaaguiar.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=cat%3dMitos%2520do%2520SQL%2520Server

[ ]s,

Gustavo