Gustavo's profileGustavo Maia AguiarPhotosBlogListsMore  |  Tools Help |
|
|
July 24 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 -- Insere dez mil registros com valores aleatórios WHILE @i <= 10000 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 -- Retorna todos os registros de 4 formas diferentes 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).
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 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 -- Retorna todos os registros de 4 formas diferentes Após executar duas vezes o seguinte resultado é obtido:
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 -- Atualiza 1000 registros tornando nulo C1 -- Cria um índice sobre C1 -- Retorna todos os registros de 4 formas diferentes Após executar duas vezes (apenas o COUNT) o seguinte resultado é obtido:
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(*) [ ]s, Gustavo Comments (8)
TrackbacksThe trackback URL for this entry is: http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!658.trak Weblogs that reference this entry
|
|
|
+ou+COUNT('X')+s%c3%a3o+mais+perform%c3%a1ticos+que+COUNT(*)+%3f+-+Windows+Live&referrer=){kind=small}
