Piores Práticas – Uso do COUNT(*)

Olá Pessoal,

Duas das minhas recentes postagens trataram a respeito de JOINS (Será que LEFT OUTER JOIN, RIGHT OUTER JOIN, *= e =* são sinônimos ? e OUTER JOIN com mais de duas tabelas ? Será que está mesmo "certo" ?). A razão desses posts terem sido escritos deu-se por conta de uma consultoria que realizei em um importante órgão do governo. Na ocasião da migração do SQL Server 2000 para o SQL Server 2005, alguns problemas aconteceram e mesmo fugindo um pouco ao escopo, me encarreguei de fazer uma documentação sobre o funcionamento desses operadores em certas "circunstâncias". Ainda na mesma consultoria, apareceu algumas outras "surpresas" relacionadas ao COUNT(*). Não me surpreendi, pois, já havia visto a "surpresa" em outro órgão público que trabalhei e que também surpreendeu alguns analistas. Não é praga de órgão público não, pois, vejo isso acontecendo todos os dias em vários fóruns e comunidades e lógico em empresas privadas também. Vejamos através de um script baseado no clássico OLTP de pedidos. Há algumas práticas de desnormalização na tabela de ItensPedido que não constituem a melhor prática, mas que são irrelevantes para esse artigo.

CREATE TABLE Clientes (
    ClienteCodigo INT,
    ClienteNome VARCHAR(80),
    Cidade VARCHAR(50))

CREATE TABLE Pedidos (
    PedidoCodigo INT, ClienteCodigo INT,
    PedidoData SMALLDATETIME, PedidoFrete SMALLMONEY)

CREATE TABLE ItensPedido (
    ItemSeq INT, PedidoCodigo INT, ProdutoNome VARCHAR(50),
    Quantidade INT, PrecoUnitario SMALLMONEY)

INSERT INTO Clientes VALUES (1,‘Wendell’,‘Goiânia’)
INSERT INTO Clientes VALUES (2,‘Bianca’,‘São Paulo’)
INSERT INTO Clientes VALUES (3,‘Gabriel’,‘Rio de Janeiro’)
INSERT INTO Clientes VALUES (4,‘Carol’,‘Porto Alegre’)

INSERT INTO Pedidos VALUES (1,1,‘20090328’,5.89)
INSERT INTO Pedidos VALUES (2,1,‘20090329’,15.54)
INSERT INTO Pedidos VALUES (3,1,‘20090330’,25.80)
INSERT INTO Pedidos VALUES (4,2,‘20090329’,15.00)
INSERT INTO Pedidos VALUES (5,2,‘20090331’,35.00)
INSERT INTO Pedidos VALUES (6,3,‘20090327’,0.00)
INSERT INTO Pedidos VALUES (7,3,‘20090330’,5.63)

INSERT INTO ItensPedido VALUES (01,1,‘Sabonete’,1,5.25)
INSERT INTO ItensPedido VALUES (02,1,‘Shampoo’,2,23.42)
INSERT INTO ItensPedido VALUES (03,1,‘Condicionador’,1,15.33)
INSERT INTO ItensPedido VALUES (04,2,‘MP5 Player’,2,250.67)
INSERT INTO ItensPedido VALUES (05,2,‘Mouse’,1,95.99)
INSERT INTO ItensPedido VALUES (06,3,‘Introdução a Banco de Dados’,1,50.00)
INSERT INTO ItensPedido VALUES (07,3,‘XML – Teoria e Prática’,1,42.00)
INSERT INTO ItensPedido VALUES (08,3,‘Destimificando SOA’,1,78.00)
INSERT INTO ItensPedido VALUES (09,3,‘PHP e MySQL’,1,89.00)
INSERT INTO ItensPedido VALUES (10,3,‘Visual Studio – Técnicas Avançadas’,1,125.00)
INSERT INTO ItensPedido VALUES (11,4,‘Whisky Blue Label’,1,659.00)
INSERT INTO ItensPedido VALUES (12,4,‘Vodka Stolichnaya Black – 1000 ml’,2,69.99)
INSERT INTO ItensPedido VALUES (13,5,‘Nintendo Wii’,1,1399.00)
INSERT INTO ItensPedido VALUES (14,6,‘Academia Particular Acadmix Premium’,1,899.99)
INSERT INTO ItensPedido VALUES (15,6,‘Bicicleta Mountain Bike Caloi 10 Aro 26 12v’,1,639)
INSERT INTO ItensPedido VALUES (16,6,‘Esteira Elétrica Premium Caloi CLE30’,2,1549.00)
INSERT INTO ItensPedido VALUES (17,6,‘Barraca Indy Nautika’,1,369.00)
INSERT INTO ItensPedido VALUES (18,6,‘Piscina Master Oval 2600 Litros’,2,419.99)
INSERT INTO ItensPedido VALUES (19,6,‘Piscina Master 7.800L’,1,514.00)
INSERT INTO ItensPedido VALUES (20,7,‘Camaleão – BOX 17 CDs – Ney Matogrosso’,1,329.90)

Uma vez que as tabelas estejam devidamente populadas, algumas consultas com o uso do COUNT(*) podem ser formuladas

— Contar o total de Clientes
SELECT COUNT(*) As TotalClientes FROM Clientes;

— Contar o total de Pedidos
SELECT COUNT(*) As TotalPedidos FROM Pedidos;

— Contar o total de Itens
SELECT COUNT(*) As TotalItens FROM ItensPedido;

— Contar quantos Itens existem em cada Pedido
SELECT PedidoCodigo, COUNT(*) As TotalItensPedido FROM ItensPedido
GROUP BY PedidoCodigo

Não é preciso tentar descobrir o que há errado. Simplesmente não há nada de errado. As consultas estão perfeitamente elaboradas e retornam os resultados corretos. Alguns podem pensar que o COUNT(*) poderia ser substituído por COUNT(1) ou COUNT(0) como já vi alguns programadores fazerem. Isso é sem dúvida uma falácia, pois, substituir COUNT(*) por expressões como COUNT(0) ou COUNT(1) definitivamente não melhoram o desempenho. Não é esse o ponto onde reside o problema e sem dúvidas as consultas apresentadas não contém nada de errado.

A atenção deve voltar-se para as consultas abaixo:

— Retornar o total de Pedidos por Cliente (Alternativa 1)
SELECT ClienteNome, COUNT(*) As TotalPedidos
FROM Clientes As C
INNER JOIN Pedidos As P ON C.ClienteCodigo = P.ClienteCodigo
GROUP BY ClienteNome;

— Retornar o total de Pedidos por Cliente (Alternativa 2)
SELECT ClienteNome, COUNT(*) As TotalPedidos
FROM Clientes As C
LEFT OUTER JOIN Pedidos As P ON C.ClienteCodigo = P.ClienteCodigo
GROUP BY ClienteNome;

Se a primeira utiliza o operador INNER JOIN e a segunda opta pelo operador LEFT OUTER JOIN é possível que diferenças sejam apresentadas (e nesse caso são apresentadas). O resultado das consultas é expresso abaixo:

Uso do INNER JOIN

ClienteNome

TotalPedidos

Bianca

2

Gabriel

2

Wendell

3

Uso do LEFT OUTER JOIN

ClienteNome

TotalPedidos

Bianca

2

Carol

1

Gabriel

2

Wendell

3

Como era previsto, o operador INNER JOIN só retornou os clientes que possuem pedidos, ou seja, aqueles clientes cujo ClienteCodigo esteja presente tanto na tabela de Clientes quanto na tabela de pedidos. O uso do operador LEFT OUTER JOIN retornou todos os clientes, ou seja, todos os registros da esquerda (Clientes) quer tenham ou não correspondentes na tabela de Pedidos através da coluna ClienteCodigo.

Se os resultados forem analisados, há uma fato muito curioso no uso do LEFT OUTER JOIN. De fato os quatro clientes foram retornados (Bianca, Carol, Gabriel e Wendell) como era de se esperar, mas será que as quantidades estão de fato corretas ? Se o uso do INNER JOIN exclui a Carol do resultado é de se esperar que ela não tenha nenhum pedido (e de fato não tem), mas se for observado o LEFT OUTER JOIN inclui Carol como se ela tivesse um pedido (mesmo não tendo). As consultas abaixo mostram essa contradição.

— Retornar o total de Pedidos por Cliente (Apenas a Carol)
SELECT ClienteNome, COUNT(*) As TotalPedidos
FROM Clientes As C
LEFT OUTER JOIN Pedidos As P ON C.ClienteCodigo = P.ClienteCodigo
WHERE ClienteNome = ‘Carol’
GROUP BY ClienteNome;

— Retornar todos os pedidos de Carol
SELECT * FROM Pedidos
WHERE ClienteCodigo IN
(SELECT ClienteCodigo FROM Clientes WHERE ClienteNome = ‘Carol’)

Como pode a primeira consulta mostrar que Carol possui um pedido e a segunda consulta não retornar os pedidos de Carol ? É sem dúvida um paradoxo no qual inclina-se a acreditar que apenas uma das consultas pode de fato estar correta. Na verdade o erro não reside no paradoxo ou em um hipotético BUG mas sim na forma que a consulta foi construída. Eis a armadilha que o COUNT(*) pode ocultar.

O resultado pode ser revelador mas por que ele acontece ? A resposta é bem simples, vejamos por etapas.

— Retornar todos os clientes e seus pedidos (existentes ou não)
SELECT ClienteNome, PedidoCodigo
FROM Clientes As C
LEFT OUTER JOIN Pedidos As P ON C.ClienteCodigo = P.ClienteCodigo;

O resultado é exposto abaixo:

ClienteNome

PedidoCodigo

Wendell

1

Wendell

2

Wendell

3

Bianca

4

Bianca

5

Gabriel

6

Gabriel

7

Carol

NULL

Considerando que o LEFT OUTER JOIN foi utilizado é esperado que todos os clientes e seus pedidos sejam retornados e no caso dos clientes sem pedidos, o NULL é apresentado para a coluna PedidoCodigo. A presença do COUNT(*) irá fazer o que esse operador se dispõe a fazer, ou seja, contar o total de linhas existentes para cada clientes. Qual seria então o total de linhas para cada cliente observando-se a tabela acima ? Podemos ver que Wendell possui 3 linhas, Bianca 2 linhas, Gabriel 2 linhas e Carol possui uma linha.

Pode parecer errado mas está correto. Sem dúvida Carol possui uma linha. Não se trata de um pedido já que PedidoCodigo é nulo, mas se trata de uma linha e o uso do COUNT(*) se propõe a contar linhas e não pedidos. A contagem de linhas está correta e o paradoxo citado anteriormente é perfeitamente compreensível. Carol possui uma linha, mas não possui pedidos.

A "surpresa" está demonstrada e a solução torna-se óbvia, ou seja, não utilize expressões do tipo COUNT(*). Sempre que possível especifique os campos que você se propõe a contar.

— Retornar o total de Pedidos por Cliente (Apenas a Carol)
SELECT ClienteNome, COUNT(PedidoCodigo) As TotalPedidos
FROM Clientes As C
LEFT OUTER JOIN Pedidos As P ON C.ClienteCodigo = P.ClienteCodigo
GROUP BY ClienteNome;

Agora que a consulta teve as devidas adaptações, segue o resultado "correto":

ClienteNome

PedidoCodigo

Bianca

2

Carol

0

Gabriel

2

Wendell

3

A contagem dessa vez foi feita sobre um campo específico (PedidoCodigo) e como Carol não possui pedidos não pode ter uma contagem diferente de zero como é mostrado no resultado. Isso porque a consulta se propôs a contar o total de pedidos (COUNT sobre PedidoCodigo) e não o total de linhas (COUNT sobre o *). Pode parecer "inocente", mas fica explícito que COUNT(*) e COUNT(Coluna) nem sempre tem o mesmo resultado.

Pode ser que o leitor desse artigo ponha a mão na cabeça e pense "Puxa quantos relatórios errados eu já fiz nessa vida" ou ainda "Amanhã eu tenho que corrigir a consulta do relatório do diretor" mas antes que pensamentos como esse venham a mente não se desespere. Essa divergência só ocorre se algumas condições forem obedecidas.

 

COUNT(*)

COUNT(X)

Explicação

INNER JOIN
(A e B)

Contagem de linhas ou registros correspondentes

Contagem de linhas ou registros correspondentes

Nesse caso o INNER JOIN só retornará registros que existam nas tabelas A e B e não há perigo da contagem de linhas divergir da contagem de correspondentes com base na coluna "X".

OUTER JOIN
(A e B)

Contagem de linhas

Contagem de registros correspondentes

Nesse caso o COUNT(*) fará a contagem de linhas enquanto o COUNT(X) fará a contagem de registros correspondentes com base na coluna "X". Para registros não correspondentes a contagem de linhas irá divergir da contagem de colunas. Vale a pena lembrar que se o operador for LEFT OUTER JOIN, a coluna "X" tem de ser da tabela à direita (no caso B). Se o operador for o RIGHT OUTER JOIN, a coluna "X" tem de ser da tabela à esquerda (no caso B). Utilizar uma coluna do mesmo lado do OUTER JOIN é sinônimo do COUNT(*). Alternativas como COUNT(1) ou COUNT(0) também tem o mesmo efeito do COUNT(*).

E quando o COUNT(*) é permitido ?

Normalmente é sempre bom evitá-lo e utilizar o nome de colunas. Não que usar o COUNT(*) incorra em problemas de desempenho, mas utilizar o nome de uma coluna pode evitar que "surpresas" aconteçam. Se o objetivo for contar o total de linhas de uma tabela ou caso as tabelas estejam relacionadas com INNER JOIN (ou via cláusula WHERE sem *= e =*) não há problema em utilizá-lo.

Espero que a prática do COUNT(*) seja muito bem pensada antes de utilizada. Tirando o fato de que o COUNT(*) dispensa a necessidade de informar o nome de uma coluna ele só representa desvantagens. Avalie bem antes de utilizá-lo para não se surpreender. Um relatório errado pode render muitas dores de cabeça.

[ ]s,

Gustavo

7 Respostas para “Piores Práticas – Uso do COUNT(*)

  1. Fabiano Neves

    Muito boa, leitura. Só uma observação, acho que aqui você quis dizer:"Alguns podem pensar que o COUNT(*) poderia ser substituído por COUNT(1) ou COUNT(0) como já vi alguns programadores fazerem. Isso é sem dúvida uma falácia, pois, substituir COUNT(*) por expressões como COUNT(0) ou COUNT(1) definitivamente NÃO melhoram o desempenho." Ou seja, faltou o não.

  2. Parabéns Gustavo, estou acompanhando seu blog desde Dezembro/2008 e estou gostando muito, conheci seu trabalho assistindo uma vídeo-aula sobre sql server 2005 em um curso que participei. Está de parabéns sua série sobre as Piores Práticas, a partir de agora vou começar a postar alguns comentários contribuindo para o seu trabalho assim como vc contribui para aqueles que leem seu blog.[]s

  3. Muito bom Gustavo, na realidade a consulta deve ser bem analisada para a utilização do COUNT. Como você demonstrou, um pequeno erro na hora de desenvolver a consulta poderia gerar um problema grande para a empresa a qual o desenvolvedor ou DBA trabalha.Grande abraço.Thiago Caserta.

  4. Oi Fabiano,Quando escrevi a frase pensei em você e no seu blog, pois, acho que você diria algo do tipo. Só que passei um pouco depressa demais. Obrigado pela dica. Já tá corrigida…Abs,

  5. Olá "Sem Nome",Que bom que o blog lhe agradou. Tento sempre que possível atualizá-lo com o melhor conteúdo que eu possa oferecer. Em breve postarei mais algumas "piores práticas". Lembre-se de conhecê-las mas nunca utilizá-las :)Abs,

  6. Oi Thiago,Pois é. Dá pra ter uma idéia dos problemas que isso pode causar. Já pensou se aparecesse pedidos que nunca foram feitos na mesa do presidente ? Complicado de explicar (rs).Abs,

  7. Parabéns Gustavo …. excelente post …. 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