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. Múltiplas consultas paralelas a MON$

Anti-padrão: Um erro muito popular - trigger OnConnect, consulta a MON$ATTACHMENTS para selecionar os 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 a MON$ deixarão o Firebird muito mais lento de forma significativa, e 500+ consultas simultâneas “travarão” o Firebird com altas chances

Soluções:

• Não use MON$ para tarefas não administrativas, ou seja, para contar ou auditar, evite usá-las em 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 triggers

• Para fins de licenciamento - use 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 da aplicação, 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 precisam esperar vários segundos para ver estatísticas de toda a 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, classificá-los/agrupá-los, o Firebird usará intensivamente vários núcleos de CPU, lendo do disco, cache, memória dedicada à classificação (e às vezes a classificaçã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 os dashboards:

• Normalmente o Dashboard é necessário apenas para analistas e gestão, exclua-o da carga geral da aplicação

• 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 um 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, um robô) e armazene-os em uma tabela simples pronta para ser recuperada por uma consulta simples

3. Use triggers 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. Carregamento de registros desnecessários

Anti-padrão: Carregar todos os dados sem filtragem na grade ao abrir uma aplicação ou formulário, independentemente de conter centenas de milhares de registros.

procedure TDataForm.LoadAllRecords;
begin
 FDQuery1.SQL.Text := 'SELECT * FROM large_table';
 FDQuery1.Open;
 // Loads entire table into memory
 DBGrid1.DataSource.DataSet := FDQuery1;
end;

Por que isso é ruim?

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

• 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 exige leitura, armazenamento em cache e transferência de milhares de registros pela rede

• Se você mantém o dataset aberto (no 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 algum critério, por exemplo, mostrar registros criados/alterados nos ú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
 // query for each row
 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 recursos de CPU desnecessariamente

• 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 precisa ser preparada, executada, o 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 os detalhes da parte visível da grade

4. Adicione um atraso para executar a consulta de 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 os dados da grade automaticamente em intervalos mínimos em cada aplicação 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 isso acontece: atualizações automáticas para agendamentos, ou seleção de posições na fila, ou busca pelo “horário 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 nos dados (streaming ou triggers 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 adequada da transação específica, isso requer inúmeras operações de leitura, como resultado, a coleta de lixo se torna significativamente mais lenta.

Soluções:

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

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

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

7. Uso de 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 somente leitura separada para operações que não alteram dados

• O Firebird é um dos poucos bancos de dados que permitem 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. Uso de LIKE :param

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

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Por que isso é ruim?

Como LIKE permite busca 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 busca 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 busca nunca começa com um curinga %, prefira STARTING WITH em vez de LIKE:

WHERE fieldName STARTING WITH ?param1

2. Otimize buscas de string bidirecionais

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

-- Create reversed index
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- Query using both directions
WHERE fieldName STARTING WITH :param1
   OR reverse(fieldName) STARTING WITH reverse(:param2)

3. Implemente uma estratégia de busca progressiva

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

• Primeiro tente uma busca rápida por índice com STARTING WITH

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

4. Otimização de busca baseada em palavras

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

• Crie uma tabela separada de mapeamento palavra-ID

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

5. Para recursos abrangentes de busca de texto completo:

• Considere usar o IBSurgeon Full Text Search UDR

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

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

Por que isso é ruim?

• Manter transações abertas por longos períodos 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 incorporar os valores dos parâmetros diretamente no texto da consulta.

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

Por que isso é ruim?

• Esta prática reduz o desempenho para consultas repetidas

• Toda 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 errada: triggers/CHECKs em vez de Primary Key

Anti-padrão: Usar triggers ou CHECK em vez de Primary Keys para verificações de integridade do banco de dados.

Por que isso é ruim?

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

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

Soluções:

• Use primary keys

• 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 sequences (generators) para novos identificadores

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

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

Soluções:

-- Use generator/sequence!
CREATE GENERATOR gen_user_id;
-- Use generator for ID generation
INSERT INTO users (id, name)
VALUES (
 GEN_ID(gen_user_id, 1),
 'John Doe' );

13. Uso Ineficiente de GUID

Por que é ruim?

• Usar GUIDs gerados pelo sistema em vez de gen_uuid() pode impactar o desempenho do índice

• 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

Antipadrão: Usar campos calculados com SELECTs para outras tabelas diminui significativamente o desempenho de operações SELECT simples.

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 é ruim?

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

• Reforça os relacionamentos entre tabelas

• Faz sentido usar campos calculados apenas para cálculos leves com campos da 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 em Log

Antipadrão: Não suprima erros e avisos do Firebird sem registrar em log!

try
 FDQuery1.Open;
except
 // Silent failure
end;

Por que é 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
 // Comprehensive logging
 Logger.Error('Database connection failed: ' + E.Message);
 ShowMessage('Unable to connect to database. Please contact support.');
 // Log additional context
 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/