15 Antipadrões do Firebird

por Alexey Kovyazin, 14-Jan-2025

Introdução

Este documento descreve 15 anti-padrões comuns ao trabalhar com bancos de dados Firebird e fornece soluções para cada um deles.

1. Consultas paralelas múltiplas ao MON$

Anti-padrão: Um erro muito popular - gatilho OnConnect, consulta ao MON$ATTACHMENTS para selecionar detalhes do usuário para fins de auditoria, ou calcular o número de conexões para fins de licenciamento.

Por que isso é ruim?

• As tabelas MON$ são tabelas virtuais armazenadas em arquivos de sistema fbNN_mon_xx, com estatísticas de desempenho, etc.

• Arquivo >1Gb significa que você está usando demais

• Elas são projetadas apenas para uso de administradores de sistema - ou seja, 1-2 consultas paralelas, exclusivamente para administradores

• 200+ conexões com consultas paralelas ao MON$ diminuirão significativamente a velocidade do Firebird, e 500+ consultas simultâneas “travarão” o Firebird com grandes chances

Soluções:

• Não use MON$ para tarefas não administrativas, ou seja, para contar ou auditar, evite usá-las no OnConnect

• Para fins de auditoria:

• Use Variáveis de Contexto como CURRENT_USER, CURRENT_TIMESTAMP, etc.

• Use Audit - recurso nativo do Firebird, muito mais poderoso que gatilhos

• Para fins de licenciamento - use as variáveis de contexto do usuário

2. Carregamento Lento de Dashboard

Anti-padrão: Carregar dashboards ou painéis abrangentes que somam todos os pedidos e faturas do último mês ou ano durante a inicialização do aplicativo, ou atualizar algumas métricas a cada minuto ou com mais frequência.

SELECT
 SUM(total_sales) as yearly_sales,
 COUNT(DISTINCT customers) as customer_count,
 AVG(order_value) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-01';

Por que isso é ruim?

• Os usuários devem esperar vários segundos para ver estatísticas da empresa antes de poderem começar seu trabalho real

• Do ponto de vista do Firebird - para executar constantemente muitas consultas paralelas, recuperar grandes quantidades de dados, ordená-los/agrupá-los, o Firebird usará intensivamente múltiplos núcleos de CPU, lendo do disco, cache, memória dedicada para ordenação (e às vezes a ordenação vai para o disco)

• É como construir um relatório várias vezes por minuto!

Soluções:

1. Diminua o número de usuários que verão dashboards:

• Normalmente o Dashboard é necessário apenas para analistas e gerência, exclua-o do carregamento geral do aplicativo

• Torne o carregamento do dashboard na inicialização/para algum formulário opcional, desabilitado por padrão

• Carregue os dados do dashboard por clique explícito em botão, não na inicialização (ou seja, torne-o um relatório)

2. Calcule os dados do dashboard com 1 processo por agendamento (ou seja, robô) e armazene-os em uma tabela simples pronta para ser recuperada por consulta simples

3. Use gatilhos para agregar dados e armazená-los prontos para uso

4. Use um banco de dados réplica para calcular os dados dos dashboards (e todos os relatórios pesados também)

3. Carregando Registros Desnecessários

Anti-padrão: Carregar todos os dados sem filtro na grade ao abrir um aplicativo ou formulário, independentemente de conter centenas de milhares de registros.

procedure TDataForm.LoadAllRecords;
begin
 FDQuery1.SQL.Text := 'SELECT * FROM large_table';
 FDQuery1.Open;
 // Carrega a tabela inteira na memória
 DBGrid1.DataSource.DataSet := FDQuery1;
end;

Por que isso é ruim?

• Apesar da grade mostrar apenas 50 registros, os usuários devem rolar por milhares de registros em vez de usar a funcionalidade de pesquisa

• Em 99% dos casos, os usuários precisam de um subconjunto muito restrito de dados: os registros de vendas mais recentes, por exemplo

• Do ponto de vista do Firebird:

• Cada abertura requer leitura, armazenamento em cache e transferência de milhares de registros pela rede

• Se você mantiver o dataset aberto (em Delphi), o Firebird mantém buffers, registros ordenados em espaço temporário (se ORDER BY, GROUP BY, etc.) até o fechamento do dataset

Soluções:

1. Limite o número de registros com FIRST/SKIP/ROWS

2. Limite o número de registros com alguns critérios, por exemplo, mostre registros criados/alterados durante os últimos 3 dias

3. Em geral, feche as consultas o mais rápido possível.

4. Consultas Excessivas ao Rolar

Anti-padrão: Executar consultas em eventos de rolagem. Por exemplo, ao exibir dados em uma grade ou tabela, executar uma consulta separada PARA CADA registro, ou se você estiver usando o exemplo clássico de rolagem mestre-detalhe em 2 grades sem atraso.

procedure TForm1.GridScrolled(Sender: TObject);
begin
 // consulta para cada linha
 FDQuery2.SQL.Text :=
 'SELECT additional_info FROM details ' +
 'WHERE id = ' + IntToStr(CurrentRowId);
 FDQuery2.Open;
end;

Por que isso é ruim?

• Executar uma consulta separada PARA CADA registro em uma grade dinâmica força o Firebird a processar milhares de consultas minúsculas, consumindo desnecessariamente recursos de CPU

• Do ponto de vista do Firebird:

• Muitas (milhares por segundo) consultas pequenas criarão carga significativa de CPU, porque mesmo que a consulta mostre 0ms nas estatísticas, ela requer ser preparada, executada, resultado transferido, etc.

Soluções:

1. Carregue várias linhas de uma vez usando operações em lote

2. Aprimore a consulta principal da grade para executar a consulta detalhada como parte dela

3. Adicione um botão explícito para carregar detalhes da parte visível da grade

4. Adicione atraso para executar a consulta para receber detalhes, para evitar consultas imediatas durante a rolagem

5. Não habilite o carregamento de detalhes ao rolar para todos os usuários por padrão

5. Atualizações Automáticas Desnecessárias

Anti-padrão: Atualizar dados da grade automaticamente em intervalos mínimos em cada aplicativo cliente, com esse recurso habilitado por padrão.

Por que isso é ruim?

• Isso resulta em centenas de conexões de clientes executando consultas quase idênticas para recuperar os mesmos registros

• Onde acontece: atualizações automáticas para agendas, ou seleção para posições de fila, ou busca por “slot mais próximo”, etc.

• Do ponto de vista do Firebird:

• Combinação de carregamento de dashboards e eventos de rolagem: muitas consultas de tamanho médio criam carga no sistema

Soluções:

1. Aumente o intervalo!

2. Implemente atualizações explícitas (acionadas pelo usuário)

3. Use atualizações seletivas do conjunto de dados com base em mudanças reais de dados (streaming ou gatilhos ou evento+streaming)

6. Atualizações Frequentes de Registros

Anti-padrão: Atualizar frequentemente o mesmo registro em transações diferentes, criando inúmeras versões de registro.

Por que isso é ruim?

• Um registro com dezenas de versões pode degradar significativamente o desempenho, um registro com milhares pode se tornar um bloqueador

• Do ponto de vista do Firebird: a cadeia de versões de registro deve ser reconstruída para identificar a versão correta da transação específica, requer inúmeras operações de leitura, como resultado, a coleta de lixo se torna significativamente mais lenta.

Soluções:

1. Migre para Firebird 4+, há coleta de lixo intermediária

2. Não mantenha transações de escrita de longa duração abertas, faça a coleta de lixo adequada

3. Para Firebird <4, considere usar DELETE+INSERT em vez de UPDATE

7. Usando transações de escrita para selects somente leitura

Anti-padrão: Usar transações de escrita para selects somente leitura leva a operações excessivas.

Por que isso é ruim?

• Usar transações de escrita para selects somente leitura leva a muitas gravações desnecessárias de páginas de cabeçalho

• Usar transações de escrita para operações somente leitura é ineficiente (TIP grande durante o commit cria carga adicional no servidor)

Soluções:

• Use uma transação separada somente leitura para operações que não alteram dados

• O Firebird é um dos poucos bancos de dados que permite abrir várias transações no âmbito de uma única conexão

• Tabelas temporárias globais estão disponíveis para uso em transações somente leitura

8. Usar LIKE :param

A seguinte consulta com parâmetro não usará índice para o campo (mesmo que o índice exista):

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Por que isso é ruim?

Como LIKE permite pesquisa com curinga (%), que pode substituir qualquer número de símbolos, o Firebird não pode determinar antecipadamente se o valor do parâmetro será adequado para pesquisa por índice.

Normalmente os desenvolvedores tentam contornar isso incorporando o valor do parâmetro no texto da consulta:

• fieldName LIKE «Alex%» - possível usar índice

• fieldName LIKE «%Alex» - não é possível usar índice padrão

• fieldName LIKE «%Alex%» - não é possível usar índice de forma alguma

Isso leva a outros problemas (veja #10 abaixo).

Soluções:

1. Use STARTING WITH para prefixos de string conhecidos

Quando seu valor de pesquisa nunca começa com um curinga %, prefira STARTING WITH em vez de LIKE:

WHERE fieldName STARTING WITH ?param1

2. Otimize pesquisas bidirecionais de strings

Para strings com padrões de prefixo ou sufixo conhecidos, use índice invertido:

-- Criar índice invertido
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- Consulta usando ambas as direções
WHERE fieldName STARTING WITH :param1
   OR reverse(fieldName) STARTING WITH reverse(:param2)

3. Implemente estratégia de pesquisa progressiva

Para strings que aparecem no início/fim/meio (mas não simultaneamente):

• Primeiro tente pesquisa indexada rápida com STARTING WITH

• Se nenhum resultado for encontrado, recorra à pesquisa LIKE mais lenta

4. Otimização de Pesquisa Baseada em Palavras

Ao pesquisar palavras completas (delimitadas por espaços, vírgulas, etc.):

• Crie uma tabela separada de mapeamento palavra-ID

• Pesquise através da tabela de mapeamento em vez do texto original

5. Para recursos abrangentes de pesquisa de texto completo:

• Considere usar IBSurgeon Full Text Search UDR

• Esta solução de código aberto fornece funcionalidade avançada de pesquisa de texto

9. Não fechar transações para operações somente leitura

Por que isso é ruim?

• Manter transações abertas por períodos prolongados pode forçar o Firebird a manter inúmeras versões anteriores para possíveis transações de snapshot

Soluções:

• Use transações somente leitura onde for possível e feche transações de escrita o mais rápido possível

• Use versões modernas do Firebird (4+) para reduzir o impacto das cadeias de versões de registros

• Implemente sweep adequado

10. Problemas de Parametrização de Consultas

Anti-padrão: Evitar consultas preparadas e parametrização, em vez disso incorporando valores de parâmetros diretamente no texto da consulta.

FDQuery1.SQL.Text :=
 'SELECT * FROM users WHERE name = ''' +
 EditUsername.Text + '''';
FDQuery1.Open;

Por que isso é ruim?

• Essa prática reduz o desempenho para consultas repetidas

• Cada consulta com valores de parâmetros incorporados deve ser preparada como nova

• A preparação pode ser longa e demorada para tabelas grandes

• Complica a análise de problemas

• É difícil agrupar consultas por texto

• Cria vulnerabilidades de injeção de SQL

Soluções:

FDQuery1.SQL.Text :=
 'SELECT * FROM users WHERE name = :username';
FDQuery1.ParamByName('username').AsString :=
 EditUsername.Text;
FDQuery1.Open;

11. Verificação de Integridade Incorreta: gatilhos/CHECKs em vez de Chave Primária

Anti-padrão: Usar gatilhos ou CHECK em vez de Chaves Primárias para verificações de integridade do banco de dados.

Por que isso é ruim?

• Isso ignora que a validação de Chave Primária usa o modo especial para ler a versão atual do registro, independentemente do nível de isolamento de transação do usuário.

• Fazer verificações de PK com gatilhos em transações de usuário aumenta a possibilidade de duplicação e complica desnecessariamente a lógica

Soluções:

• Use chaves primárias

• Evite verificações de integridade redundantes

• Mantenha a lógica do banco de dados simples

12. Geração de ID com MAX()

Anti-padrão: Usar MAX(id)+1 para novos identificadores é não confiável e ineficiente.

INSERT INTO users (id, name)
VALUES ((SELECT MAX(id) + 1 FROM users),
 'John Doe');

Por que isso é ruim?

• Usar MAX(id)+1 em vez de sequências (generators) para novos identificadores

• MAX(id)+1 não garante unicidade com parâmetros de transação comuns - duas transações paralelas poderiam receber o mesmo valor MAX()

• A combinação de Max()+1 e CHECK(select if unique) também não funciona!

Soluções:

-- Use generator/sequência!
CREATE GENERATOR gen_user_id;
-- Use generator para geração de ID
INSERT INTO users (id, name)
VALUES (
 GEN_ID(gen_user_id, 1),
 'John Doe' );

## 13. Uso Ineficiente de GUID

**Por que isso é ruim?**

- Usar GUIDs gerados pelo sistema em vez de gen\_uuid() pode impactar o desempenho do índice

- O GUID gerado pelo sistema é altamente aleatório


**Soluções:**

- Use a função gen\_uuid()

- Considere usar BIGINT em vez disso

- Na versão 6 haverá UUID v7


## 14. Campos Calculados Ineficientes

**Anti-padrão:** Usar campos calculados com SELECTs em outras tabelas diminui significativamente o desempenho de operações SELECT simples.

```sql hljs
CREATE TABLE orders (
 id INTEGER,
 total_amount COMPUTED BY (
 (SELECT SUM(item_price) FROM order_items
 WHERE order_items.order_id = orders.id)));

Por que isso é ruim?

• Campos calculados são calculados em tempo de execução, e não devem ser usados para implementar lógica complexa, podendo complicar significativamente os esforços de otimização

• Isso fortalece os relacionamentos entre tabelas

• Faz sentido usar campos calculados apenas para cálculos leves com campos da própria tabela, como concatenação

Soluções:

CREATE TABLE orders (
 id INTEGER PRIMARY KEY,
 cached_total_amount DECIMAL(10,2));

CREATE TRIGGER update_order_total
BEFORE INSERT OR UPDATE ON orders
AS
BEGIN
 NEW.cached_total_amount = (
 SELECT SUM(item_price)
 FROM order_items
 WHERE order_items.order_id = NEW.id
 );
END;

15. Supressão de Erros Sem Registro

Anti-padrão: Não suprima erros e avisos do Firebird sem registrar!

try
 FDQuery1.Open;
except
 // Falha silenciosa
end;

Por que isso é ruim?

• Ocultar erros impede o diagnóstico e a depuração adequados. O registro adequado de erros é crucial para entender e resolver problemas rapidamente.

Soluções:

try
 FDQuery1.Open;
except
 on E: Exception do
 begin
 // Registro abrangente
 Logger.Error('Falha na conexão com o banco de dados: ' + E.Message);
 ShowMessage('Não foi possível conectar ao banco de dados. Entre em contato com o suporte.');
 // Registrar contexto adicional
 Logger.LogStackTrace(E);
 end;
end;

Informações de Contato

• Envie suas perguntas para [email protected]

• Torne-se um Firebird Supporter (a partir de EUR10/mês) e participe de webinars avançados exclusivos!

• https://store.firebirdsql.org/p/firebird-associate-donation-eur/