Banco de Dados

Tabelas

Usar nomes que remetem a sua funcionalidade e em maiúsculo.

Usar nomes em português.

Sempre preencher a descrição, informando o que armazena e se possível onde se aplica.

Campos

Usar nomes que remetem a sua funcionalidade e em maiúsculo.

No nome do campo complementar com sufixo “_” + abreviatura da tabela

 Exemplo: CODIGO_PED

Sempre preencher a descrição, informando o que armazena e se possível onde se aplica.

Armazenar strings curtas em VARCHAR, grande em BLOBs: Para armazenar dados de caracteres curtos, use VARCHARs, para armazenar textos longos, use BLOBs. Os Varchars são mais rápidos para os pequenos pedaços de dados porque eles são armazenados no registro e todo o registro é lido durante o mesmo ciclo IO e se o tamanho do registro for menor que 2/3 do tamanho da página do banco de dados, todo o registro é armazenado na mesma página de banco de dados. BLOBs são armazenados fora do registro, e exigem a rodada adicional de IO para lê-lo, e eles mostram a vantagem com a leitura e escrita de cordas longas. Use BIGINT para chaves primárias e exclusivas: Use o tipo BIGINT para chaves primárias e exclusivas auto-incrementadas e para identificadores de todos os tipos. Operações com BIGINT são as mais rápidas, e BIGINT tem capacidade suficiente para armazenar quase todos os intervalos de dados. Não use VARCHARs para chaves: Não use VARCHAR para identificadores a menos que seja realmente necessário - operações com eles são muito menos eficaz do que com colunas de inteiro. Especialmente evitar GUIDs, como identifica - devido à distribuição aleatória de valores GUID operações INSERT / UPDATE com chaves primárias / exclusivas GUIDs pode ser 20 vezes mais lento do que com inteiros.

ForeignKey

Ao criar ForeingKeys procure usar a opção on update “cascata”, quando há alguma troca de código ou referência, o banco de dados se encarregará de fazer as mudanças necessárias.

SQL

Usar palavras reservadas (select, where, order by, group by, sum, count, etc.) em minúsculos e o restante (nomes de tabelas e campos) em maiúsculo.

Cuidado com o tamanho máximo da SQL (64kB ou 65536 caracteres). Linha também tem limite

Faça quebra de linha após cada campo, relacionamento e condição.

Select

Não utilizar asterisco, descrever os campos das tabelas;

Cuidado com SubSelects, são bem mais lentos.

Evite ordenação (order by), ela faz com que utilize cache em disco tornando-se bem mais lenta, faça ordenação do ClientDataSet;

Evitar ordenação e agrupamento pelo index, além de ser mais suscetíveis e erros em manutenções em subselect pode ocorrer comportamentos estranhos.

Sempre adicionar o nome da tabela antes do nome dos campos, agiliza os comandos SQL.

Não utilizar “tab” após os nomes das tabelas, força um comando SQL extra;

 Exemplo: 
 select 
   PEDIDO.CODIGO_PED,
   PEDIDO.DATA_PED,
   PEDIDO.CLIENTE_PED,
   CLIENTE.NOME_CLI
 from PEDIDO
 left join CLIENTE on (CLIENTE.CODIGO_CLI = PEDIDO.CLIENTE_PED)
 where PEDIDO.CODIGO_PED = 3

Índices "Desligar" se você estiver usando IN com muitas constantes: Se você estiver usando a construção WHERE fieldX IN (Constant1, Constant2, ... ConstantN), e há um índice no fieldX, o Firebird usará um índice quantas vezes mais constantes na lista IN. Desabilite a pesquisa de índice, transformando campoX em expressão +0: WHERE campoX + 0 IN (Constant1, Constant2, ... ConstantN) ou, para strings, use fieldX || Substitua IN por JOIN: Evite usar consultas com aninhado WHERE IN (SELECT ... WHERE IN (SELECT .. WHERE IN ())), pode confundir otimizador do Firebird. Transforme aninhados INs em associações.

Limitar a busca de consultas SELECT: Sempre tente limitar a saída grande para consultas SELECT com FIRST ... SKIP ou ROWS cláusulas. Se a consulta não é projetada especificamente como um relatório (que exige que todos os registros sejam impressos / exportados), normalmente é suficiente para mostrar os 10-100 registros superiores. Procure apenas os registros necessários.

Especifique menos número de colunas em SELECT com ORDER BY / GROUP BY: Reduza o número de colunas e sua largura de resumo em consultas com ORDER BY / GROUP BY tanto na parte SELECT (ou seja, campos a serem mostrados) e na cláusula ORDER BY. Firebird mescla as colunas das cláusulas SELECT e ORDER BY / GROUP BY e classifica-as na memória (ou, se a memória não é suficiente, no disco). Portanto, se houver um VARCHAR longo em SELECT, o tamanho dos arquivos de classificação pode ser muito grande (muitos gigabytes). Reduzir o número de campos apenas para aqueles que devem ser classificados e junção tardia com grandes campos a serem mostrados pode extremamente (x3-x10) aumentar a velocidade de uma consulta com ORDER BY / GROUP BY.

Use tabelas derivadas para otimizar SELECT com ORDER BY / GROUP BY: Outra maneira de otimizar consulta SQL com classificação é usar tabelas derivadas para evitar operações de classificação desnecessárias.

Ao invés de:

 SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_N 
 FROM T 
 ORDER BY FIELD2

Use a seguinte modificação:

 SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_N 
 FROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2 
 JOIN T ON T.FIELD_KEY = T2.FIELD_KEY

Excluir colunas BLOB dos grandes SELECTs: Excluir colunas BLOB dos grandes SELECTs. Use um tipo de ligação tardia com sub-seleciona para mostrar seletivamente informações de BLOBs (por exemplo, mostrar o conteúdo do documento).

Use funções analíticas (janela) no Firebird 3.0: Se você estiver executando o SELECT com saída simultânea de alguma coluna e função agregada para ele, use funções de janela (analíticas) - é mais rápido do que a subconsulta ou 2 consultas.

Por exemplo:

 select 
   id, 
   salário, 
   salário / (select sum(salário) do empregado) porcentagem 
 from empregado

substituir com:

 select 
   id, 
   salário, salário / soma (salário) OVER () percentual
 from empregado

- WHERE CURRENT OF: A maneira mais rápida de processar registros obtidos pelo cursor no PSQL é a cláusula 'where current of <>'. É mais rápido do que 'where rb$db_key = :v_db_key' e muito mais rápido do que pesquisa com uma chave primária ou estrangeira.

Relacionamento

Use primeiro os relacionamentos mais fortes (n x n antes de 1 x 1)

Use LEFT JOIN da maneira correta: Se você estiver usando junções LEFT OUTER, coloque explicitamente tabelas na associação do menor para o maior.

Use HASH JOINs: No Firebird 3.0, no caso de juntar tabelas grandes e pequenas, o HASH JOIN pode ser muito mais rápido do que o normal juntar que usa "loop aninhado" com índice. Para fazer com que o otimizador do Firebird use a junção HASH, use +0 na condição de junção: T1 JOIN T2 ON T1.FIELD1 + 0 = T2.FIELD2 + 0. Verifique o resultado da otimização antes de colocá-lo na produção!

Condições

Quando quiser fazer uma comparação que sempre retorne falso, não coloque "where (1 = 0)" ou algo do tipo, pois gasta-se mais tempo para sua execução. Coloque "where (campochave = -1)" (lembrando que o campochave nunca será negativo), pois assim o motor do banco de dados responderá mais rapidamente, visto que usará o índice primário para a resolução do comando.

Pelo mesmo motivo, não acrescente cláusulas sempre verdadeiras "where (0=0)", pois apesar de simples, será executada uma comparação a cada registro. Se você estiver listando uma grande quantidade de registros verá o quanto a performance será afetada.

Inserções/atualizações em massa

Desativar índices: Se você inserir ou atualizar muitos registros (mais de 25% da tabela), desative os índices para a tabela onde os registros são inseridos e reativá-los após a inserção ou atualização. A operação de reconstrução de índice pode ser mais rápida do que muitas atualizações do índice.

Use tabelas temporárias globais: Para acelerar inserções e atualizações, use tabelas temporárias globais para inserções em massa dos grandes conjuntos de registros e, em seguida, transferir registros para a tabela permanente. Pode ser muito eficaz inserir registros no GTT, pré-processá-los e, em seguida, mover para a tabela persistente.

Use a opção NO_AUTO_UNDO: Se você estiver executando muitos comandos DML (Update / Insert / Delete) nos quadros da mesma transação, o Firebird funde undo-log de cada comando com undo-log da transação. Para acelerar operações DML em massa, inicie a transação com a opção "NO AUTO UNDO", para não mesclar os undo-logs de cada comando com o undo-log da transação.

Índices

Evitar índices desnecessários: Use menos índices para tabelas com inserções e atualizações intensivas. Cada índice adiciona uma sobrecarga significativa para operações de inserção, atualização, exclusão e coleta de lixo - pode haver 3-4 páginas adicionais lidas e gravadas quando o único registro está sendo inserido / atualizado / apagado / limpo para cada índice.

UDF

Substituir UDFs com chamadas de funções incorporadas: Substituir chamadas UDF com chamadas de funções incorporadas. Muitas funções incorporadas foram adicionadas nas versões recentes do Firebird, que oferecem funcionalidade anteriormente disponível apenas em bibliotecas UDF. Substitua essas funções sempre que possível, uma vez que funções incorporadas funcionam até 3 vezes mais rápido do que UDFs.

Utilizar transacções só de leitura para operações de leitura

Conexões

Usar pool de conexão: Se as conexões de banco de dados ao banco de dados Firebird são curtas (é típico para sites), use pool de conexão - por exemplo, em PHP use ibase_pconnect em vez de ibase_connect

Use a opção LINGER no Firebird 3.0: Se as conexões de banco de dados forem curtas e você estiver usando o Firebird 3+, use a opção LINGER para manter a cache ativa durante o período de tempo especificado, mantendo as páginas usadas com freqüência no cache, mesmo que não haja outras conexões. Por exemplo, ALTER DATABASE SET LINGER TO 60 manterá o cache por 60 segundos após o final da última conexão.

Transações

Use transações de somente leitura para operações que não mudam de registro (isto é, SELECTs) com modo de isolamento = read committed. Essas transações não mantêm versões de registro da coleta de lixo e podem ser executadas indefinidamente: elas não afetam o desempenho do banco de dados.

Use transações curtas de gravação e se livrar de TODOS os long-running: Utilize transações curtas graváveis (para operações INSERT / UPDATE / DELETE): A transação escrita mais curta é, melhor. As transações curtas reter proporcionalmente menos número de versões de registro de coleta de lixo do que de longa duração. Infelizmente, mesmo a única transação de longa duração (a partir da ferramenta de desenvolvimento deixada aberta, por exemplo) pode aparafusar o efeito bom de todas as outras transações escrituráveis curtas. É por isso que você precisa monitorar a transação de longa execução e corrigir os locais apropriados no código-fonte.

Evite cadeias de registro longas: Evite situações em que um registro tem muitas versões de registro - o Firebird funciona muito mais devagar com cadeias de registros longas. Use a combinação de inserções e exclusão agendada de registros antigos em vez de várias atualizações do mesmo registro.

Prepare

Use PREPARE corretamente: Use declarações preparadas para executar consultas SQL onde apenas os parâmetros são alterados - por exemplo, preparar antes do loop de tais consultas. Prepare pode levar um tempo significativo (especialmente para grandes tabelas), e preparar a consulta apenas uma vez irá aumentar significativamente o desempenho global.

Commit

Não COMETE muitas vezes durante a operação de inserção / actualização em massa: No caso da operação INSERT / UPDATE / DELETE em massa, não confira a transação após cada alteração (isso pode acontecer se você estiver usando a opção auto commit no driver do banco de dados) - commit transações pelo menos após 1000 operações ou mais. Cada commit de transação executa várias operações de E/S de leitura/gravação no banco de dados, por isso, muitas vezes, compromete-se a diminuir o desempenho do banco de dados.

Monitoramento

Evite frequentemente consultas às tabelas de monitoramento: Não execute consultas com frequência nas tabelas de monitoramento do Firebird (MON$) - essas consultas consomem recursos significativos e podem diminuir consideravelmente o desempenho da lógica de negócios principal. Recomendamos executar MON$ consultas não muitas vezes de uma vez por minuto.

xxxx

Marcar funções PSQL apropriadas como DETERMINISTIC: Marque suas funções PSQL (no Firebird 3+) que não têm parâmetros e retornam valores constantes com a palavra-chave DETERMINISTIC. As funções determinísticas são calculadas e armazenadas em cache no escopo da consulta atual.