/ Banco de dados

Dicas e boas práticas para quem está começando com banco de dados - Parte 2

Dando continuidade a série de dicas. Nesta segunda e última parte do artigo, iremos falar sobre orientações simples, porém importantes, na construção de consultas, atualização ou remoção de dados.

E vamos as dicas...

1. Selecione somente o necessário

Procure citar na consulta somente os campos que deseja visualizar. Embora pareça óbvio, mas o uso do select * from pode custar caro para o banco. Imagine a seguinte situação: a tabela a ser consultada possui um campo que armazena dados binários. E, você não sabia disso. Para piorar a situação, vários registros da tabela possuem um anexo de 10MB.

Certamente, o I/O do disco do servidor vai subir um pouco quando você disparar a consulta. É claro que o tempo de resposta do servidor seria muito mais rápido se você não citasse a coluna onde está o binário.


Consultas onde existem mais de uma tabela envolvida, o uso do * também pode causar confusão, trazendo todos os campos de ambas as tabelas.

Dica: faça o uso de apelido para cada tabela. E cite somente as colunas que deseja consultar. Exemplo:

select a.col1, a.col2, b.col1 
from tab_a a join tab_b b on...

2. Prefira o EXISTS

Em uma consulta, temos diversas formas de aplicar filtros nos dados. E uma forma muito utilizada é o uso de subconsulta na cláusula WHERE. Temos duas formas de criar este filtro dinâmico.

A primeira forma é fazendo o uso do operador IN. Onde a consulta principal será filtrada conforme valores da subconsulta. Exemplo:

select a.col1, a.col2
from tabela_a a
where a.col3 in (select b.col1 from tabela_b b)

A segunda forma é fazendo o uso do operador EXISTS frente a cláusula IN. Onde a consulta principal será filtrada conforme os valores da subconsulta. Porém, neste caso a subconsulta possui relacionamento direto com a consulta principal. Observe o exemplo abaixo:

select a.col1, a.col2
from tabela_a a
where exists (select b.col1 from tabela_b b where b.col3 = a.col3)

Este relacionamento entre as duas tabelas que é o ponto principal ligado a performance. É muito importante que tais colunas sejam indexadas.

3. Funções de conversão/formatação

É muito comum fazer o uso de funções de conversão ou formatação de dados em um filtro de consulta. Entretanto, saiba que além do custo da conversão, tais transformações aplicadas neste momento podem fazer com que o banco deixe de utilizar um índice criado simplesmente pela troca do tipo de dado.

Outra situação que implica em aumento de custos são as conversões implícitas. Onde o tipo do dado aplicado no filtro difere do dado armazenado em banco. Em muitos cenários, o banco tenta resolver aplicando tais conversões.

Dica: se tal prática de transformação for utilizada com frequência. Deve-se estudar a possibilidade de armazenar o dado na forma mais adequada.

4. Consulta com DISTINCT

Embora muito útil em consultas, o uso do DISTINCT deve ser avaliado com cuidado. Principalmente em consultas que atingem grandes quantidades de dados. Em poucas palavras, a consulta com o DISTINCT tem o custo da seleção dos dados mais o agrupamento. Parece pouca coisa a primeira vista, mas para o banco é um belo de um trabalho.

5. Ordenar o resultado?

Assim como o DISTINCT, o ORDER BY deve ser utilizado com cautela. Caso não seja realmente necessário, ele deve ser evitado por questões de custos.

6. Configuração de linguagem, idioma e cultura

É importante que o banco de dados e a aplicação estejam conversando e fazendo o uso da mesma linguagem/idioma dando importância ao aspecto da globalização. A correta configuração da tabela de caracteres a ser utilizada pelo banco evita inúmeros transtornos com os dados imputados pela aplicação, facultando a necessidade de conversões e evitando erros que prejudiquem o negócio.

Dica: antes de criar um banco, certifique-se de que a linguaguem/idioma esteja configurada corretamente no servidor. A tabela de caracteres também é outro ponto importante a se ajustar.

7. Não utilize HAVING para filtrar dados

Se o filtro for aplicado em uma coluna existente na tabela, evite utilizar o HAVING para isso.

Não recomendado:

select col1, col2, count(*) as col3
from tabela
group by col1, col2
having col2 = 10

Recomendado:

select col1, col2, count(*) as col3
from tabela
where col2 = 10
group by col1, col2

8. Evite utilizar IN com muitos valores

Apesar de ser mais rápido escrevê-lo no momento da criação de uma instrução, este operador pode gerar alguns problemas quando utilizado com grandes quantidades de filtros informados de maneira estática. Estou falando de "centenas" de valores ou mais! Por exemplo:

select col5 from tabela_a where col1 in (1,2,5,30,40,41,42,43,55,70,80...)

O primeiro grande problema é o tamanho que a instrução pode atingir, gerando ao banco um certo esforço para a sua compilação. O segundo seria a limitação imposta por alguns bancos na quantidade de valores informados estaticamente no operador.

Entenda que, não quero recriminar o uso deste operador. Mas, alguns testes antecipados seriam bem-vindo para ver se esta forma de uso é a melhor escolha.

9. "Select from (Select...)"

Não é um macaco gordo, mas quebra um galho danado! É isso mesmo... esta possibilidade oferecida pela maioria dos grandes bancos é muito útil quando se deseja realizar consultas mais avançadas envolvendo o processamento dos dados. E este resultado serve de base para outra consulta. Agora imagine tudo isso em uma única instrução. Vamos lá, vou explicar de maneira mais simplificada e aplicando um cenário fictício.

Imagine uma empresa que deseja totalizar as vendas do mês e aplicar um acréscimo de 10% nas comissões de cada vendedor cuja a soma ultrapasse 100.000 (Cem mil). Fazendo o uso de "Select from (Select)", a consulta ficaria assim:

select resumo.id
   resumo.nome
   case
      when resumo.total_venda > 100000 then (resumo.total_comissao * 1.10)
      else resumo.total_comissao
   end as total_comissao
from (
      select v.id as id,
         v.nome as nome,
         sum(ve.valor_venda) as total_venda,
         sum(ve.valor_comissao) as total_comissao
      from vendedor v
      join venda ve on ve.id_vendedor = v.id
      where ve.data_venda between '01/07/19' and '31/07/19'
      group by v.id, v.nome
) resumo

A dica aqui é: começe a analise de dentro para fora. Note que o "from" possui uma consulta interna que recebe o apelido "resumo". E cada coluna desta consulta também recebe um apelido que será utilizado na consulta principal (externa). Assuma que "resumo" é uma tabela temporária e que pode ser utilizado regularmente pela consulta externa. De posse dos dados resumidos pela consulta interna, basta trazer as informações desejadas na consulta externa e aplicar as transformações necessárias.

Entretanto, saiba que, caso "resumo" venha ter grande quantidade de registros e estes venham a se relacionar com outras tabelas na consulta principal. Pode haver queda de performance devido a falta de índice.

É claro que existem outras formas mais resumidas de montar esta consulta e chegar ao mesmo resultado. Inclusive em uma única consulta. Mas, o objetivo aqui é explicar a forma de utilização do recurso.

10. Comentários em instruções ou scripts

Olhando para um script alheio, não é facil deduzir rapidamente o que ele faz na íntegra. E uma pessoa cautelosa, certamente irá analisá-lo antes mesmo de executá-lo em um ambiente de produção.

É inevitável que scripts contenham inúmeras instruções complexas ou que façam operações específicas envolvendo regras de negócio. E pensando no futuro ou em quem irá um dia precisar ajustá-lo, aconselho que, sempre que necessário, faça o uso de comentários ou blocos de comentários, postando informações que mostram para qual finalidade o script foi criado ou até mesmo orientações para ajudar a entendê-lo melhor. As duas formas mais comuns aceitas pela maioria dos bancos, são:

Bloco de comentário: */ Este script foi criado para ... /*
Neste caso, tudo que estiver entre "*/ ... /*" será ignorado na instrução.

Comentário simples: -- Filtro da coluna...
Neste caso, tudo que estiver a frente do "--" será ignorado na instrução.

Um exemplo de script contendo os dois casos:

/* Este script foi criado para ... /*
select col1, col2, col3 from tabela_a
union
select col1, col2, col3 from tabela_b
-- fim do script

11. Utilizando apelido em Update ou Delete

A prática de colocar apelido em tabelas é muito comum em Select. Entretanto, bancos como MS SQLServer, Oracle e Postgres disponibilizam este recurso para Update e Delete. Abaixo estão alguns exemplos práticos:

SQLServer:

update tabela_a
set col3 = b.col3
from tabela_a a
join tabela_b b on a.col1 = b.col1
where a.col2 = 20

Oracle:

update tabela_a a
set col3 = (select b.col3 from tabela_b b where b.col1 = a.col1)
where col2 = 20

Postgres:

-- update
update tabela_a
set col3 = b.col3
from tabela_b b 
where b.col1 = tabela_a.col1
and tabela_a.col2 = 20

-- delete
delete from tabela_a a 
using tabela_b b 
where a.col1 = b.col1;

12. Transacionar operações

É muito importante em operações de manutenção de dados (insert, update, delete) fazer o uso de transação em casos onde existem muitas instruções presentes no script. Isto garante que todas as operações compreendidas sejam concluídas sem erro. Desta forma, evitamos que parte das operações sejam executadas e outra não.
Abaixo estão três exemplos de transação para os bancos MS SQLServer, Oracle e Postgres:
SQL Server:

SET XACT_ABORT ON
GO
BEGIN TRANSACTION
  INSERT ...
  UPDATE ...
  DELETE ...
COMMIT TRANSACTION
--ROLLBACK TRANSACTION
GO

PostgreSQL:

BEGIN TRANSACTION;
  INSERT ...;
  UPDATE ...;
  DELETE ...;
COMMIT;
--ROLLBACK;

Oracle:

SET TRANSACTION NAME 'XXXX';
   INSERT ...;
   UPDATE ...;
   DELETE ...;
COMMIT;
--ROLLBACK;

Importante: evite transacionar grandes quantidades de registros. Isso pode provocar bloqueios indesejaveis em tabelas. Caso não seja possivel evitar, opte por horarios alternativos.

Conclusão

Embora o uso de ORM seja trivial e poupe um precioso tempo do desenvolvedor em escrever comandos SQL para o banco, em algum momento de sua vida, será necessário voltar aos primordios e por a mão na massa para escreve um comando SQL nativo. Seja para atender um caso especifico ou para melhorar a performance de uma consulta. As dicas apresentadas nos dois artigos visão auxiliar o dev nesta jornada.

Fica aqui o meu agradecimento. Obrigado pela paciencia até agora. Para acessar o primeiro artigo, clique aqui.

Referências

https://hackernoon.com/are-implicit-conversions-killing-your-sql-query-performance-70961e547f11
https://medium.com/alexandre-malavasi/25-dicas-e-boas-práticas-de-banco-de-dados-para-desenvolvedores-7a60bfc28f1f
RIGGS, Simon; CIOLLI, Gianni. PostgreSQL 10 Administration Cookbook. 1 ed. Birmingham, UK: Packt Publishing Ltd, 2017.