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

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

  1. Sensacional Gustavo.Vou enviar esse artigo pra galera que trabalha dando manutenção em codígos, de um cliente.Parabéns velho… Nos vemos no TechEd 2009.Abs,Diego Nogare

  2. Vladimir Michel

    Cara, muito bom o artigo mesmo. Essa é uma questão bem comum de se ver por aí.Um ponto importante que acho que merece destaque é o cuidado que deve-se tomar no caso de utilizar o COUNT(coluna), pois como você mostrou, caso ela possua valores nulos, pode-se obter um resultado diferente do esperado.

  3. Muito bom Gustavo. Acho que esta série vai dar muito o que falar. Vejo muitos DBAs "Seniors" falando mitos mirabolantes.Só para acrescentar, o uso de select rows from sys.partitions where object_id = object_id(\’T\’) and index_id in (0,1) é bem mais rápido que o count na table. Claro que deve ser levando em consideração a possibilidade de valores nulos no indice, conforme você falou no artigo.Abraços

  4. Parabéns, Gustavo, mais um excelente artigo! Valeu!

  5. Olá Gustavo,Mais uma vez parabéns, muito bom mesmo.Pena que hoje em dia são muitos os profissionais não qualificados para levar adiante os mitos do SQL Server.E como Vladimir falou, realmente é um ponto importante que se deve ter cuidado com o COUNT(columns), podendo assim lhe retorna um resultado não desejável caso exista valores NULL.Nunca é de mais falar, muito bom mesmo, espero poder acompanhar seus novos pôsteres sobre os mitos do SQL Server.Atenciosamente,Heberton Melo

  6. Gustavo,Ótimo esse artigo! Parabéns! Qual será o próximo mito? Abçs

  7. Parabéns ótimo artigo!!!!

  8. parabéns ótimo artigo, achei interessante também a parte sobre a organização em blocos do sqlserver, se um dia você quiser escrever sobre isso acharia muito legal

  9. Amigo, realmente existe uma perda na performance na primeira vez que vc executa. O SGBD precisa realmente saber as colunas que serão envolvidas na consulta. Este é o momento de "parser" (análise da query). Após isso ele já tem o plano de execução pronto e não precisa gastar recursos para isso. Outra coisa, o seu exemplo foi muito simples, imagine uma tabela em que o usuário tem direitos para ler uma coluna da tabela e não tem de ler outra o count(*) daria erro. Seus teste sempre consideraram a segunda execução onde esse problema não ocorrerá mais. Pois se não houve alteração da query ele irá usar o mesmo plano de execução e, mais importante, não vai rever as permissões de coluna da tabela. O ideal é colocar uma coluna fixa.

  10. Olá Ricardo,Muito interessante o detalhe da permissão. Eu nunca tinha feito, mas realmente se não houver permissão em uma coluna o plano falha (já encontrei as explicações para esse comportamento). Concordo que sempre haverá uma perda de desempenho na primeira execução por conta do parser e da geração do plano (isso não só para o COUNT, mas para qualquer consulta).Entretanto fiquei com duas dúvidas em relação ao comentário.- Você defende que se houver alteração de permissões um mesmo plano será reaproveitado ?- A utilização de uma coluna ao invés do * é mais performático ?[ ]s,

  11. Fabiano Neves

    Na webcast que fiz (SQL Server Day) falando sobre o *… eu falei sobre o processo onde o QO expande as colunas… vou repetir o que eu disse… – Vamos ter bom senso, ganhar 1 nanosegundo que o SQL perde fazendo isso vai resolver o problema de performance?…Abraço

  12. Não amigo, eu não defendo que será reaproveitado. Nunca testei, mas acredito que a alteração de permissões invalide todos os planos de execução para o objeto envolvido. O que eu quis dizer é que a segunda vez é mais rápido pq os parsers não são mais realizados por isso a análise de performance deveria ser feita somente na primeira execução da query e não na segunda. A utilização de uma coluna ao invés do * previne erros na aplicação se uma coluna nova e sem permissão for criada. Lembrando sempre que se essa coluna permitir nulos estes não serão contados. De qualquer forma parabéns pela pesquisa precisamos sempre melhorar as querys do BD. Algumas pessoas, acostumadas com sistemas pouco exigentes não entendem que "tunning" é retirar de onde não se tem mais de onde tirar. Mudança grande de performance é correção de erro de programação ou projeto físico, não é tunning. Eu costumo dizer que tunning é a diferença entre o michael schumacher é o segundo lugar.Abraços.

  13. Fabiano Neves

    Não era minha intensão polemizar masssss…. que o ganho em trocar * pelas colunas vai ser irrsório é fato, contra isso não há argumentos…De qualquer forma, blz.. cada um na sua… enquanto a banda toca, eu continuo com meus sistemas poucos exigentes… :-)

  14. Leandro Morais de Oliveira

    Opa!.. acessei seu blog pela 1ª vez hj… e estou achando muito massa! Muito bom mesmo, 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