11 dicas de performance com SQL Server

Como todos que me conhecem pessoalmente sabem, gosto muito de trabalhar em projetos de melhoria de performance. Não sou nenhum especialista no assunto, mas acho desafiador pegar um sistema que está com problemas de velocidade na execução de seus algoritmos e de alguma forma torná-lo mais eficiente.

Quando tive a ideia do Busca Acelerada, além do desafio inerente a projetos envolvendo grandes quantidades de dados, estava a instigante necessidade dele ser rápido, para honrar o nome é claro. Conforme fomos adicionando mais e mais sites parceiros (hoje já passam de 80), mais e mais estados (hoje temos cobertura nacional) e mais e mais anúncios (hoje são quase 2 milhões), forçamos bastante os meios tradicionais de desenvolver aplicações em ASP.NET e nos deparamos com diversos problemas de performance e escala.

O que quero dizer com isso é que, geralmente, desenvolvemos programas em ASP.NET sem grandes preocupações com performance, do jeito mais produtivo. Este é o Microsoft way de programar, certo? O que se vende com o .NET é produtividade e baixa curva de aprendizado. E não há nada de errado nisso, principalmente para mais de 90% das necessidades de aplicações do mercado.

Entretanto, o Busca Acelerada está nesses 10% e costumo dizer que desenvolver esse tipo de software com sucesso é o que separa os ótimos programadores dos apenas bons. Visando ajudar outras pessoas que possam vir a passar por problemas de desempenho, criei uma série de posts aqui no blog sobre testes e ajustes de desempenho, incluindo Regex, desempenho web, desempenho de apps, desempenho de variáveis e por aí vai.

Hoje vamos falar de T-SQL (ou SQL da Microsoft como alguns citam) e em posts futuros pretendo abordar Entity Framework com Linq2Entities.

Aviso

Tudo o que vai ser descrito aqui é fruto pura e unicamente das minhas experiências pessoais.

Obviamente em certo ponto busquei referências, principalmente no MSDN, mas quero dizer que não possuo um embasamento teórico aprofundado em bancos de dados, pois não é minha área de especialização.

Com certeza muitos itens não se adequarão ao seu cenário, entenda que costumo (e gosto) de desenvolver softwares atípicos, e principalmente, fugir dos padrões de ERP, CRM, etc, softwares em que a maioria dos desenvolvedores trabalham todos os dias.

Desta forma, leia, use o que se sentir à vontade e ignore o resto. Aceito sugestões de outras dicas, pode deixar nos comentários por favor.

Dica #1: Bancos relacionais não são orientados a objetos!

Vamos começar do começo, ok?

Quando estiver modelando um banco relacional como o SQL Server, você vai estar também definindo boa parte das limitações de performance que seu banco terá durante sua vida útil. A modelagem correta das tabelas (veja bem, correta, não necessariamente normalizada) irá influenciar pesadamente no desempenho do seu sistema, principalmente considerando que a maior parte do tempo de resposta de um sistema é devido aos acessos ao banco de dados.

A regra aqui é: não confundam modelagem ER com um diagrama de classes ou algo do gênero.

Embora muitos desenvolvedores atuais sejam adeptos de teorias “pós-modernistas” de desenvolvimento Code-First, onde primeiro se criam as classes para então gerar as tabelas a partir delas (o que não há nada de errado especificamente), vale ressaltar que a mais elaborada biblioteca de classes, usando e abusando de regras como herança, não-repetição de código, especialização de classes, agregação e composição, não dará um bom banco de dados.

Seguem alguns exemplos.

Primeiro, vamos pegar como base o famigerado cadastro de clientes. Os clientes têm endereço certo?

Muitos programadores já pensam logo de cara em fazer uma tabela de endereços separada da tabela de clientes. Se em seu sistema existe a necessidade de múltiplos endereços para um mesmo cliente (comum em e-commerces), ok. Caso contrário, é inútil. E não vale a desculpa de “mas se eu tiver outra tabela, como fornecedor, que precise de endereço…”, pois a menos que alguns fornecedores morem no mesmo lugar que alguns clientes, não haverá ganho algum com essa abordagem. Isso se chama paternite, vamos falar dela mais tarde.

Esse pensamento leva a um aumento na complexidade de queries simples como trazer todos os dados de um cliente e preste atenção, quando falo em complexidade não estou falando de ser difícil de programar, mas custoso para o banco de dados em tempo de processamento, acesso à disco. Toda vez que quiser trazer todos os dados de um cliente, terá de usar um JOIN, o que é desnecessário se você modelar o banco otimizado para as consultas que vai fazer (também falaremos disso mais tarde).

Eu dei um exemplo tosco, de composição, mas um caso mais grave é o de querer usar herança em tabelas. Criar uma tabela Pessoa, com colunas comuns às tabelas Cliente e Fornecedor, por exemplo, levará novamente a trabalho desnecessário na construção e execução de consultas. Não há problema em ter colunas repetidas em tabelas diferentes, o problema é quando se tem dados repetidos em tabelas diferentes.

Não quero me estender muito neste item, vamos voltar nele mais tarde.

Dica #2: Use JOINs, mas não muito!

Na maioria dos casos em que tenha que juntar dados de duas ou mais tabelas em uma mesma consulta, dê preferência ao uso de JOINs ao invés de subconsultas e tabelas aninhadas. Usando esta dica com a dos índices mais abaixo, lhe darão um ganho muito grande em performance.

Entretanto, o uso de múltiplos JOINs em uma mesma consulta podem acabar com sua performance devido ao excesso de cruzamento entre diferentes tabelas, neste caso, dê uma olhada no item sobre desnormalização de tabelas, mais adiante.

Dica #3: Índices!

Boa parte dos problemas de desempenho se resolvem com índices bem construídos. Assim como os livros, que são um conjunto de capítulos com muitas páginas cada, os bancos são conjuntos de tabelas como muitos registros cada. Em ambos nós precisamos ter índices, que são estruturas de apoio para encontrar rapidamente o que estamos procurando.

Note que índices e chaves são coisas diferentes, embora a chaves primárias sejam os únicos índices clusterizados das tabelas. A ideia aqui não é ensinar como construir índices (use o SQL Server Management Studio e não terá problemas), é dar dicas sobre porque construi-los e quando fazê-lo.

Primeiro, toda tabela deve ter um índice clusterizado, que é o índice que rege a ordenação das linhas da tabela. Ou seja, a chave primária. Fora as regras tradicionais de chaves primárias, como não poder haver repetição, dê preferência por usar um campo numérico como chave, pois os índices trabalham melhor como estes campos (i.e. as consultas serão mais rápidas). Este é o mais básico e elementar dos índices e ele é criado mesmo que você não saiba disso.

Segundo, crie índices nas suas chaves estrangeiras. Não necessariamente em todas, afinal o uso excessivo de índices prejudica a performance dos INSERTs, UPDATEs e DELETEs, mas nas FKs que você costuma fazer JOIN.

Terceiro, crie índices para garantir integridade. Quando você cria um índice para uma coluna (ou grupo delas) e diz que ele é UNIQUE, você não apenas garante que não haja elementos repetidos em sua tabela, como aumenta significativamente a performance de consultas que envolvam as colunas do índice. Isso porque quando o motor de consulta do SQL Server sabe que um campo não se repete, ele vai parar de procurar por mais ocorrências daquele campo tão logo encontre a primeira ocorrência, entende?

Quarto, dê manutenção nos seus índices periodicamente. Conforme você vai inserindo, removendo e atualizando elementos da sua tabela, seus índices vão ficando fragmentados, o que pode prejudicar a performance de uma maneira até pior do que a ausência de índices. Desta forma, lembre-se de dar um Rebuild nos seus índices quando eles estiverem fragmentados, preferencialmente em horários de pouco acesso ao seu sistema para evitar problemas com os usuários.

Dica #4: Como guardar Strings no banco

O que tem a ver performance e Strings no banco?

Tudo!

A forma como você armazena String no banco influenciam o tamanho que a tabela terá, o tempo de consultas a estes campos e até mesmo questões estratégicas como chaves naturais com alta performance.

Tudo começa entendendo os tipos de dados para texto e para que serve cada um.

O CHAR é o mais básico e representa um vetor de caracteres de tamanho fixo, não interessa o que você salva em um CHAR(10), ele terá 10 caracteres de comprimento, preenchendo o restante com espaços em branco ou cortando sua palavra na décima posição (dependendo da configuração do banco pode gerar erro). Use CHAR sempre que souber o tamanho exato de um campo, pois ele possui o melhor desempenho de todos. Como exemplo gosto sempre de citar a coluna CPF em cadastro de clientes, que sempre possui 11 caracteres de comprimento.

O VARCHAR representa vetores de caracteres com tamanho indefinido. Você define o limite de caracteres permitidos no campo, mas somente o que você guardar nele é que ocupará espaço. A flexibilidade tem o seu preço: o tempo de consulta a campos VARCHAR é maior que campos CHAR, isso porque em um campo de tamanho fixo é fácil ler o conteúdo no disco pois basta saber onde ele começa e calcular o seu final pelo limite do campo. No caso do VARCHAR não se tem certeza de quantos caracteres está se usando no campo e isso gera testes adicionais por parte do motor de consulta.

Já o TEXT é para casos específicos em que se tem milhares de caracteres em um único campo. Com eficiência extremamente duvidosa, deve-se tomar cuidado caso queira realizar consultas nestes tipos de campo (procurando por um trecho em um texto com milhares de caracteres). Nestes casos recomenda-se habilitar o Full-Text Search no SQL Server. Não tenho propriedade para falar a respeito e sugiro procurar no MSDN.

Mas e as versões NCHAR, NVARCHAR e NTEXT?

Pois bem, o N é de National, que indica suporte maior à diferentes idiomas e conjuntos de caracteres. Especialmente útil para garantir um armazenamento mais compatível de caracteres de diferentes idiomas. Isso não chega a ser um diferencial na maioria dos sistemas tradicionais mas lembre-se disso e de uma outra palavra chamada COLLATION quando estiver projetando sistemas multi-linguagem.

Uma última dica sobre campos de texto é a respeito de índices sobre eles.

Sabemos que campos de texto são os mais problemáticos de serem usados em WHEREs mas isto pode ser contornado com a criação correta de índices. Se o seu campo possui tamanho fixo (CHAR) e não pode ser repetido (novamente cito o CPF como exemplo), não pense duas vezes antes de criar um índice para ele. Consultas a este campo serão estupidamente mais velozes desta forma. Em casos que se usa campos de texto como parâmetro para ordenação, a construção de índices é muito boa também pois os índices são ordenados de maneira independente do restante da tabela.

Dica #5: Deixe a normalização para os trabalhos da faculdade!

Soa grosseiro, mas em projetos de uso intenso de dados temos de pensar dessa forma. Nada contra a forma acadêmica de projetar bancos, muito pelo contrário, um projeto bem estruturado de um grande ERP ou CRM é digno de colocar em um quadro na parede. Em alguns casos nem caberia em uma única parede, hehehehe. A questão aqui é que quanto mais se divide um banco em tabelas, mais JOINs serão necessários para juntar os dados novamente. Inversamente, quanto mais dados colocarmos em uma mesma tabela mais demorada serão as consultas nesta tabela.

E agora José?

O negócio aqui é analisar cada caso e usar cada técnica de acordo com a sua necessidade para aquele cenário. Ou seja, se os JOINs a um grupo de tabelas estão sendo usados com muita frequência ou até mesmo sempre tem de ser consultados juntos, considere a hipótese de transformar tudo em um tabelão. Como sei que isso soa estranho para muita gente, experimente ao menos criar uma View e notará a diferença.

Como exemplo gosto de citar a tabela de Endereco e de Estado. No Brasil os estados podem ser representados com apenas 2 caracteres, e criando um campo UF no endereço ao invés de um IDEstado tornará seu sistema muito mais eficiente do que fazer JOINs o tempo todo entre Endereco e Estado. Inversamente evite tabelas genéricas usando campos de tipo para definir a categoria do registro armazenado, pois isso geralmente costuma inflar demais as tabelas sem necessidade.

Outra coisa, ao contrário do que muitos acreditam, nem sempre criar chaves estrangeiras para todos os campos em comum entre tabelas é um bom negócio. Os relacionamentos servem para garantir integridade referencial e possuem como efeito colateral o aumento no tempo dos INSERTs, DELETEs e UPDATEs nas tabelas relacionadas. Então pense bem antes de sair criando chaves a torto e a direito.

Dica #6: Views…sim, você leu certo!

As views sofrem bullying desde a chegada das stored procedures no ano 2000. O pessoal acha que stored procedures são melhores que views pois são mais flexíveis, não permitindo apenas SELECTs. Costumam também achar que não há diferença entre um SELECT comum e uma VIEW.

Blasfêmia!

A estes eu digo que deveriam conhecer as maravilhosas Indexed Views. Diferente das views tradicionais que tem apenas a vantagem de serem pré-processadas (igual às stored procedures), as indexed views possuem performance semelhante a tabelas nativas, com a diferença de conterem somente os dados que interessam para determinada regra de negócio.

Sério, vale a pena perder uns 15 minutos procurando no MSDN por Indexed Views, consegui reduzir drasticamente o tempo de consultas de minutos para segundos.

Dica #7: Não seja preguiçoso, evite os atalhos!

Por mais que seja chato escrever comandos completos de SQL, quanto mais preciso e completo for seu comando, mais veloz será sua interpretação pelo motor de busca e consequentemente sua consulta terá melhor desempenho.

Sim, estou falando de milissegundos, mas não esqueça que a cada 1000 milissegundos economizados, é 1 segundo a menos de encheção de paciência! A dica aqui é bem simples, não tenha preguiça ao escrever as queries, você o fará uma vez, mas elas serão executadas milhares de vezes, então vale a pena. Digite completamente o nome de tabelas, incluindo o schema (dbo geralmente), evite algumas palavras-chave as quais cito mais tarde e retorne somente as colunas que serão utilizadas.

Você encontra a maneira certa de escrever consultas quando gera scripts SQL pelo Management Studio, note como até o uso de colchetes em volta das palavras é proposital, para evitar problemas com espaços em branco, entre outras preocupações que você também deveria ter.

Dica #8: LOCK ou NOLOCK? Eis a questão…

Poucos conhecem as palavras-chave LOCK e NOLOCK.

Se voltar aos fundamentos das consultas em bancos ER descobrirá que em diversos momentos as tabelas são bloqueadas para garantir que os conceitos de ACID sejam respeitados (Atomicidade, Consistência, Integridade e Durabilidade).  Não lembro exatamente mas geralmente atualizações bloqueiam as linhas da tabela que estão sendo atualizadas, os deletes bloqueiam a tabela inteira entre outros bloqueios. O bloqueio em si afeta outras operações de escrita na mesma tabela/linha/coluna (dependendo do LOCK) e em algumas ocasiões também bloqueia os SELECTs.

Por padrão todo SELECT respeita os LOCKs das tabelas, como se você sempre escrevesse SELECT * FROM Tabela WITH(LOCK).

Mas o que acontece quando você usa NOLOCK?

O motor de busca irá ignorar se a tabela está bloqueada ou não, o que pode gerar inconsistências na consulta mas que ao mesmo tempo aumenta a velocidade da consulta pois ignora verificações de LOCK. As inconsistências só são um risco se seu sistema realiza muitos DELETEs e INSERTs, o que pode gerar retornos de consulta que não correspondem exatamente ao estado atual do banco (não terá a última linha adicionada ou removida, entende?).

Entretanto, no caso do Busca Acelerada, onde o uso intenso de buscas requer o máximo de performance e cujos dados apenas são inseridos durante a madrugada, não existe esse perigo, sendo que durante o dia apenas alguns updates são realizados em campos que nem mesmo são exibidos aos usuários.

Claro, é um cenário um tanto específico, mas não custa nada saber desse recurso também, não é mesmo?

Dica #9: Keywords que você deve usar

A regra é que não existem balas de prata. Entretanto, existem diversas palavras-chave que em 90% dos casos podem resolver grandes problemas de performance e às vezes são ignoradas pelos desenvolvedores. Brevemente não esqueça de:

TOP – retorna um número limitado de registros, muito útil para trazer somente a quantidade de dados que faz sentido para sua aplicação. A menos que queira todas as ocorrências de uma condição consultada, use TOP 1, 5, 10, etc conforme o número de registros que queira retornar.

DISTINCT – retorna os elementos sem repeti-los, muito útil para eliminar repetições de valores em uma mesma coluna e consequentemente dados inúteis sendo transferidos pela rede. Se transferência de dados não são uma preocupação sua, então ignore essa dica, pois o processamento no servidor se torna maior mesmo.

COUNT – retorna a quantidade de elementos para uma dada condição (WHERE). Se quer saber apenas se um elemento existe na tabela, ou quantos elementos de um tipo, use COUNT ao invés de mandar retornar todos os dados dos registros que atendem à sua condição.

Dica #10: Keywords que você deve evitar

Da mesma forma que mesmo as palavras acima Não são garantia de sucesso na escrita de boas consultas SQL, as palavras abaixo nem sempre são vilãs. Cito elas aqui apenas para que se lembre de sempre ponderar sobre sua utilização, para evitar surpresas quando seu sistema começar a ser utilizado de verdade, afinal, quando só você está testando o sistema tudo funciona com boa performance, não é mesmo?!

LIKE – compara um trecho de texto dentro de um bloco maior. Se tiver de usar o LIKE evite usar mais de um coringa (%) para que a perda de performance não seja tão grande. Outra dica que pode ajudar é o uso de índices no campo de texto onde precisará usar likes, como citado no tópico anterior sobre índices. Se você está se perguntando como fazem os buscadores como o Google, tenha a certeza de que eles não usam LIKE em uma grande tabela que possuía a web inteira dentro. O segredo aqui está em estruturas de dados específicas para armazenar textos de forma distribuída como índices invertidos, matrizes de dispersão, árvores de conhecimento, entre outras. O buraco é muito mais embaixo que um mero LIKE!

IN – se você quer retornar os registros cujas condições são múltiplos identificadores, como todos os empregados cujos IDs sejam 1,3,7,45,100, você irá usar um IN, certo?

O problema aqui é que o IN é interpretado pelo motor de busca como uma junção de ORs, ou seja, ‘WHERE ID IN (1,3,7,45,100)’ é a mesma coisa que ‘WHERE ID=1 OR ID=3 OR ID=7 OR ID=45 OR ID=100’. Isto Não chega a ser um problema em uma consulta com poucas dezenas de valores no IN, mas tome cuidado quando você chega nas centenas deles. Já experimentei outras abordagens com tabelas temporárias, tabelas em memória, entre outras, com resultados semelhantes. A única solução foi encontrada nos algoritmos que precedem a consulta ao banco, fugindo do uso intensivo de IN.

* – o famigerado asterisco deve ser evitado pelo simples motivo de que é muito fácil retornar mais colunas do que o necessário usando esse recurso. Outro problema é que o interpretador SQL deve buscar no esquema da tabela as colunas que terão de ser retornadas, antes de realizar a consulta propriamente dita. No final das contas é uma economia porca de tempo e que não traz benefício algum nem mesmo a curto prazo.

Dica #11: Use variáveis locais em Stored Procedures

Tive de atualizar esse post quando descobri a solução para um problema que há muito tempo estava me incomodando. Eu tenho uma Stored Procedure no Busca Acelerada que traz 4000 registros acima de um determinado ID. Acontece que essa procedure funciona muito rapidamente para as primeiras 250 mil iterações que preciso dela, aproximadamente, com um tempo entre 0s e 3s. Quando passa disso, ela fica extremamente lenta, na casa dos 300s. A mesma procedure.

Depois de muito bater a cabeça, descobrir que não era culpa de um único JOIN que tenho. Descobrir que o índice que criei baseado na única condição da procedure não resolveu nada. Cheguei a uma conclusão incrível que me fez reabrir este post: use variáveis locais em procedures.

Eu não inventei isso, descobri no StackOverflow, mais especificamente neste post, que se você quer evitar o chamado “variable sniffing” (uma busca constante pelo valor dos parâmetros de uma procedure) você deve copiar os parâmetros para variáveis locais, logo no início da procedure, como citado na melhor resposta do link que coloquei. Embora pareça bobo, eu vi isso funcionando na prática. Minha procedure caiu de 300s para o patamar normal de 0s a 3s.

Incrível. Recomendo testar caso tenha alguma procedure com performance ruim e sem motivo aparente.

Conclusões

Acredito que muitas dessas dicas podem ajudar diversos programadores que estão com problemas de performance em seus bancos.

É muito fácil encontrar informações na Internet de como modelar bancos e criar consultas, mas poucos se importam em fazê-lo da melhor maneira ou poucos mostram em como melhorar o desempenho de bancos e consultas já existentes.

Espero que isto traga resultados para você leitor do blog, e permita que construa sistemas cada vez melhores, pois para mim ajudaram bastante.

Concorda? Não concorda? Sabe mais alguma coisa que não listei? Manda ver nos comentários!