Histórico da Página
...
Boas práticas e dicas para construção de uma consulta sql:
1. Ao escrever uma cláusula WHERE, sempre colocar a cláusula mais restritiva antes. Qual é a condição mais restritiva? A condição na cláusula WHERE de uma instrução que retorna o menor número de linhas de dados.
Expandir | ||
---|---|---|
| ||
SELECT COUNT(1) from ppessoa where (sexo = 'M') -- 348260 linhas SELECT COUNT(1) from ppessoa WHERE (GRAUINSTRUCAO = '5') -- 966 linhas |
...
Obs.: Na maioria das vezes durante a análise do plano de execução o próprio sgbd* faz essa troca internamente para aprimorar sua própria busca, contudo essa ação gera custos para ele o ideal é não deixar essa tarefa para ele e garantir o melhor resultado de sua consulta.
2. Evitar cláusulas WHERE que utilizam colunas não indexadas ou solicitar a criação do índice para a equipe de banco de dados. É muito importante avaliar quais colunas da tabela serão apresentadas no resultado bem como quais colunas participam da cláusula WHERE, caso tais colunas estejam dentro de um índice ‘não clusterizado’, será o caminho mais performático para sua consulta.Exemplo:
Expandir | ||
---|---|---|
| ||
Na tabela em questão temos um índice ‘nonclustered’ com as colunas chapa, anocomp, mescomp, nroperiodo e codevento. Ao montar a consulta podemos ver que imediatamente o banco de dados decidiu usá-lo para determinar a execução da consulta:
Veja o plano de execução estimado entre as duas consultas, a diferença entre a estimativa simplesmente por ter modificado as colunas a serem retornadas: Obs.: Caso exista colunas na cláusula select ou na where que não estejam neste índice, será necessário usar o índice da chave primária, não sendo tão performático. Ou seja, sempre dê preferência para colunas associadas aos índices ‘não clusterizados’. |
3.Não esquecer de colocar (NOLOCK) nas sentenças (exceto quando se aplica exceção)Exemplo:
Expandir | ||
---|---|---|
| ||
SELECT CHAPA, ANOCOMP, MESCOMP, NROPERIODO, CODEVENTO FROMPFFINANC (NOLOCK) WHERE CODEVENTO = ‘abcd’ |
Obs.: Caso exista alguma atualização sendo executada para o CODEVENTO = ‘abcd’ em outra tela’, sua consulta não precisa aguardar a conclusão dessa atualização para que assim apresente os valores pedidos. O uso desse recurso só não poderá ser útil se para a sua consulta, seja expressamente necessário ter o valor mais atualizado da tabela, neste caso terá que aguardar a atualização ser concluída para depois receber tais informações. Esta abordagem não se aplica em Oracle, pois ele não permite “leitura suja”, ele possui outra abordagem para nível de isolamento.
4. No SELECT, trazer apenas as colunas necessárias;
Exemplo:
Vimos este estudo no segundo tópico deste documento, escolher as colunas prioritárias que estão citadas nos índices ‘não clusterizados’ para depois adotar as demais colunas que serão cobertas pelo índice ‘clusterizado’ referente a chave primária. Caso exista alguma coluna elegível a ser associada a algum índice ‘não clusterizado’, entre em contato conosco para avaliarmos a sugestão e apresentarmos ao produto tal oportunidade de melhoria.
Obs.: é muito importante não usar o “SELECT * FROM” nas suas consultas, sempre avaliar se precisa de todas colunas da tabela para sua análise. Pode ser que uma das colunas que seriam desnecessárias para sua pesquisa possua dados que ocuparam megas, gigas de seus recursos gerando lentidão em todo o banco de dados e sistema desnecessariamente.
5. Para Sub-Selects na cláusula WHERE, considere utilizar EXISTS ao invés de IN;
Expandir | ||
---|---|---|
|
...
A cláusula EXISTS trabalha como um comando de decisão, caso o valor seja encontrado segundo a consulta apontada pela EXISTS, então a consulta principal será executada. Neste exemplo só vou buscar a lista de pessoas caso exista pelo menos uma pessoa com a data de nascimento dia 24/04/69: SELECT * FROM PPESSOA (NOLOCK) WHERE EXISTS ( SELECT TOP 1 'OK' FROM PPESSOA (NOLOCK) WHERE DTNASCIMENTO = '1969-04-24' ) Geram o mesmo número de linhas com dois conceitos aplicados: Obs. Uma sugestão para o uso da cláusula IN, por exemplo, tenho uma lista de palavras que preciso fixá-las no filtro, neste caso tente usar a seguinte semântica: Antes: SELECT * FROM PPESSOA WHERE APELIDO IN ('EDSON','DANIEL','ANDRE') Depois: SELECT * FROM PPESSOA WHERE APELIDO IN (SELECT * FROM ( VALUES ('EDSON'),('DANIEL'),('ANDRE') ) MINHALISTA(APELIDO)) Este ganho será evidente principalmente se a lista tiver muitos valores, no exemplo abaixo foi inserida uma lista de 673 valores e já podemos ver o ganho de 2%, sendo mais expressivo dependendo do conteúdo da tabela e o tipo de dados filtrados: |
6. Between no lugar de Or/And dependendo do caso? Esta é uma dúvida que sempre aparece quando precisamos usar essa cláusula, vamos entender:
SELECT * FROM PFFINANC WHERE VALOR BETWEEN 100 AND 200
...
SELECT VALOR FROM PFFINANC WHERE VALOR BETWEEN 0 AND 200 → neste caso o valor zero está incluso.
7. Order by é caro no sql.
O uso desse recurso precisa ser bem avaliado, precisa pensar se a tela a qual o conteúdo da tabela vai ser apresentada tem recursos de ordenação, caso consiga é melhor buscar toda tabela sem organizar e fazê-lo pela aplicação. O uso do ‘order by’ é bem válido quando temos uma tabela muito grande e precisamos pegar somente alguns registros ordenados, por exemplo o uso da cláusula TOP N com o complemento do ‘order by’. Em outras palavras, não vale a pena buscar milhões de registros do banco sendo que preciso buscar os 3 primeiros com valor maior ou menor:
...
- Ao fazer um insert com o resultado de um select, nunca use o order by nesse select:
INSERT INTO TABELA1
SELECT SELECT COLUNA1, COLUNA2 FROM TABELA2 ORDER BY COLUNA1 DESC
8. De preferência em usar JOIN no lugar de UNION. Vamos entender as diferenças entre estas cláusulas para conseguir aplicá-las sem perder a performance e objetivo da consulta:
No caso do ‘Union’, serve principalmente para unificar linhas entre duas tabelas que possuem mesmas colunas e unificar as linhas que são idênticas entre ambas. Veja que o objectivo foi atingido listando todas as linhas das tabelas eliminando as repetições:
...
No caso do ‘Join’, estamos falando de junções horizontais as quais podemos mesclar informações de ambas tabelas. Nesta cláusula usamos o conceito de conjuntos (união, intersecção e diferença), precisamos que ao menos uma coluna seja equivalente em ambas tabelas para gerar nossa expressão.
9. Não use Distinct, tente utilizar Group By no lugar. Sabemos que cada um tem seu propósito e dependendo da situação não poderemos substituir um pelo outro. Contudo se houver oportunidade de fazê-lo, tente aplicar,
...
conforme exemplo abaixo:
Expandir | ||
---|---|---|
| ||
Obs.: Sempre analise o ‘plano de execução’ entre as duas abordagens, optando em adotar o item que gerou menor custo.
...