15 antipatrones de Firebird

por Alexey Kovyazin, 14-ene-2025

Introducción

Este documento describe 15 anti-patrones comunes al trabajar con bases de datos Firebird y proporciona soluciones para cada uno de ellos.

1. Múltiples consultas paralelas a MON$

Anti-patrón: Un error muy popular: trigger OnConnect, consulta a MON$ATTACHMENTS para seleccionar los datos del usuario con fines de auditoría, o calcular el número de conexiones con fines de licenciamiento.

¿Por qué es malo?

• Las tablas MON$ son tablas virtuales que se almacenan en archivos de sistema fbNN_mon_xx, con estadísticas de rendimiento, etc.

• Un archivo >1Gb significa que lo estás usando demasiado

• Están diseñadas únicamente para uso de administradores de sistemas, es decir, 1-2 consultas paralelas, exclusivamente para administradores

• 200+ conexiones con consultas paralelas a MON$ ralentizarán Firebird de forma muy significativa, y 500+ consultas simultáneas “colgarán” Firebird con altas probabilidades

Soluciones:

• No uses MON$ para tareas no administrativas, es decir, para contar o auditar; evita usarlas en OnConnect

• Para fines de auditoría:

• Usa variables de contexto como CURRENT_USER, CURRENT_TIMESTAMP, etc.

• Usa Audit, una funcionalidad nativa de Firebird, mucho más potente que los triggers

• Para fines de licenciamiento, usa las variables de contexto del usuario

2. Carga lenta del Dashboard

Anti-patrón: Cargar dashboards o scoreboards completos que suman todos los pedidos y facturas del último mes o año durante el inicio de la aplicación, o actualizar algunas métricas cada minuto o con mayor frecuencia.

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 qué es malo?

• Los usuarios deben esperar varios segundos para ver estadísticas de toda la empresa antes de poder comenzar su trabajo real

• Desde el punto de vista de Firebird, para ejecutar constantemente muchas consultas paralelas, recuperar grandes cantidades de datos, ordenarlos/agruparlos, Firebird utilizará intensivamente múltiples núcleos de CPU, leyendo de disco, caché, memoria dedicada a la ordenación (y a veces la ordenación va a disco)

• ¡Es como construir un informe varias veces por minuto!

Soluciones:

1. Disminuir el número de usuarios que verán los dashboards:

• Normalmente el Dashboard solo es necesario para analistas y la dirección; exclúyelo de la carga general de la aplicación

• Haz que la carga del dashboard al inicio/para algún formulario sea opcional, deshabilitada por defecto

• Carga los datos del dashboard mediante un clic explícito en un botón, no al inicio (es decir, conviértelo en un informe)

2. Calcular los datos del dashboard con 1 proceso según una programación (es decir, un robot) y almacenarlos en una tabla simple lista para ser recuperada mediante una consulta simple

3. Usar triggers para agregar datos y almacenarlos listos para su uso

4. Usar una base de datos réplica para calcular los datos del dashboard (y también todos los informes pesados)

3. Cargar registros innecesarios

Anti-patrón: Cargar todos los datos sin filtrar en la grilla al abrir una aplicación o formulario, independientemente de si contiene cientos de miles 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 qué es malo?

• A pesar de que la grilla solo muestra 50 registros, los usuarios deben desplazarse por miles de registros en lugar de usar la funcionalidad de búsqueda

• En el 99% de los casos los usuarios necesitan un subconjunto muy reducido de datos: los registros de ventas más recientes, por ejemplo

• Desde el punto de vista de Firebird:

• Cada apertura requiere leer, almacenar en caché y transferir miles de registros a través de la red

• Si mantienes el dataset abierto (en Delphi), Firebird mantiene buffers, registros ordenados en espacio temporal (si hay ORDER BY, GROUP BY, etc.) hasta el cierre del dataset

Soluciones:

1. Limitar el número de registros con FIRST/SKIP/ROWS

2. Limitar el número de registros con algún criterio, por ejemplo, mostrar registros creados/modificados durante los últimos 3 días

3. En general, cerrar las consultas lo antes posible.

4. Consultas excesivas al hacer scroll

Anti-patrón: Ejecutar consultas en eventos de scroll. Por ejemplo, al mostrar datos en una grilla o tabla, realizar una consulta separada POR CADA registro, o si estás usando el ejemplo clásico de scroll maestro-detalle en 2 grillas sin retardo.

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 qué es malo?

• Realizar una consulta separada POR CADA registro en una grilla dinámica obliga a Firebird a procesar miles de consultas diminutas, consumiendo recursos de CPU innecesariamente

• Desde el punto de vista de Firebird:

• Muchas (miles por segundo) consultas pequeñas crearán una carga significativa de CPU, porque incluso si la consulta muestra 0ms en las estadísticas, requiere ser preparada, ejecutada, transferir el resultado, etc.

Soluciones:

1. Cargar múltiples filas a la vez usando operaciones por lotes

2. Mejorar la consulta principal de la grilla para ejecutar la consulta de detalle como parte de ella

3. Agregar un botón explícito para cargar los detalles de la parte visible de la grilla

4. Agregar un retardo para ejecutar la consulta de obtención de detalles, para evitar consultas inmediatas durante el scroll

5. No habilitar la carga de detalles al hacer scroll para todos los usuarios por defecto

5. Refrescos automáticos innecesarios

Anti-patrón: Refrescar los datos de la grilla automáticamente a intervalos mínimos en cada aplicación cliente, con esta funcionalidad habilitada por defecto.

¿Por qué es malo?

• Esto da como resultado cientos de conexiones de clientes ejecutando consultas casi idénticas para recuperar los mismos registros

• Dónde ocurre: refrescos automáticos de horarios, o select para posiciones en cola, o búsqueda del “turno más cercano”, etc.

• Desde el punto de vista de Firebird:

• La combinación de carga de dashboards y eventos de scroll: muchas consultas de tamaño medio crean carga en el sistema

Soluciones:

1. ¡Aumentar el intervalo!

2. Implementar refrescos explícitos (disparados por el usuario)

3. Usar refrescos selectivos del conjunto de datos basados en cambios reales de los datos (streaming o triggers o evento+streaming)

6. Actualizaciones frecuentes de registros

Anti-patrón: Actualizar frecuentemente el mismo registro en diferentes transacciones, creando numerosas versiones del registro.

¿Por qué es malo?

• Un registro con docenas de versiones puede degradar significativamente el rendimiento; un registro con miles puede convertirse en un bloqueador

• Desde el punto de vista de Firebird: la cadena de versiones del registro debe reconstruirse para identificar la versión adecuada de la transacción específica, lo que requiere numerosas operaciones de lectura; como resultado, la recolección de basura se vuelve significativamente más lenta.

Soluciones:

1. Migrar a Firebird 4+, que tiene recolección de basura intermedia

2. No mantener transacciones de escritura de larga duración; realizar la recolección de basura adecuada

3. Para Firebird <4, considerar usar DELETE+INSERT en lugar de UPDATE

7. Usar transacciones de escritura para selects de solo lectura

Anti-patrón: Usar transacciones de escritura para selects de solo lectura conduce a operaciones excesivas.

¿Por qué es malo?

• Usar transacciones de escritura para selects de solo lectura conduce a muchas escrituras innecesarias de páginas de cabecera

• Usar transacciones de escritura para operaciones de solo lectura es ineficiente (un TIP grande durante el commit crea carga adicional en el servidor)

Soluciones:

• Usar una transacción de solo lectura separada para operaciones que no cambian datos

• Firebird es una de las pocas bases de datos que permiten abrir varias transacciones en el marco de una única conexión

• Las tablas temporales globales están disponibles para su uso en transacciones de solo lectura

8. Usar LIKE :param

La siguiente consulta con parámetro no usará el índice para fieldName (incluso si el índice existe):

SELECT * FROM Table1 WHERE fieldName LIKE :param1

¿Por qué es malo?

Dado que LIKE permite búsqueda con comodines (%), que pueden reemplazar cualquier número de símbolos, Firebird no puede determinar de antemano si el valor del parámetro será adecuado para la búsqueda por índice.

Normalmente los desarrolladores intentan evitarlo incrustando el valor del parámetro en el texto de la consulta:

• fieldName LIKE «Alex%» - es posible usar el índice

• fieldName LIKE «%Alex» - no es posible usar el índice estándar

• fieldName LIKE «%Alex%» - no es posible usar el índice en absoluto

Esto conduce a otros problemas (ver #10 más abajo).

Soluciones:

1. Usar STARTING WITH para prefijos de cadena conocidos

Cuando tu valor de búsqueda nunca comienza con un comodín %, prefiere STARTING WITH sobre LIKE:

WHERE fieldName STARTING WITH ?param1

2. Optimizar búsquedas de cadenas bidireccionales

Para cadenas con patrones de prefijo o sufijo conocidos, usar un índice invertido:

-- 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. Implementar una estrategia de búsqueda progresiva

Para cadenas que aparecen al inicio/final/medio (pero no simultáneamente):

• Primero intentar una búsqueda rápida por índice con STARTING WITH

• Si no se encuentran resultados, recurrir a la búsqueda más lenta con LIKE

4. Optimización de búsqueda basada en palabras

Cuando se buscan palabras completas (delimitadas por espacios, comas, etc.):

• Crear una tabla separada de mapeo palabra-ID

• Buscar a través de la tabla de mapeo en lugar del texto original

5. Para capacidades completas de búsqueda de texto completo:

• Considerar usar IBSurgeon Full Text Search UDR

• Esta solución de código abierto proporciona funcionalidad avanzada de búsqueda de texto

9. No cerrar transacciones para operaciones de solo lectura

¿Por qué es malo?

• Mantener transacciones abiertas durante períodos prolongados podría obligar a Firebird a mantener numerosas versiones anteriores para posibles transacciones de snapshot

Soluciones:

• Usar transacciones de solo lectura donde sea posible, y cerrar las transacciones de escritura lo antes posible

• Usar versiones modernas de Firebird (4+) para reducir el impacto de las cadenas de versiones de registros

• Implementar un sweep adecuado

10. Problemas de parametrización de consultas

Anti-patrón: Evitar las consultas preparadas y la parametrización, incrustando en su lugar los valores de los parámetros directamente en el texto de la consulta.

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

¿Por qué es malo?

• Esta práctica reduce el rendimiento para consultas repetidas

• Cada consulta con valores de parámetros incrustados debe prepararse como nueva

• La preparación puede ser larga y consumir mucho tiempo para tablas grandes

• Complica el análisis de problemas

• Es difícil agrupar consultas por texto

• Crea vulnerabilidades de inyección SQL

Soluciones:

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

11. Verificación de integridad incorrecta: triggers/CHECK en lugar de Primary Key

Anti-patrón: Usar triggers o CHECK en lugar de Primary Keys para las verificaciones de integridad de la base de datos.

¿Por qué es malo?

• Esto ignora que la validación de Primary Key utiliza el modo especial para leer la versión actual del registro, independientemente del nivel de aislamiento de la transacción del usuario.

• Realizar verificaciones de PK con triggers en transacciones de usuario aumenta la posibilidad de duplicación y complica innecesariamente la lógica

Soluciones:

• Usar claves primarias

• Evitar verificaciones de integridad redundantes

• Mantener la lógica de la base de datos simple

12. Generación de ID con MAX()

Anti-patrón: Usar MAX(id)+1 para nuevos identificadores no es confiable ni eficiente.

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

¿Por qué es malo?

• Usar MAX(id)+1 en lugar de secuencias (generadores) para nuevos identificadores

• MAX(id)+1 no garantiza la unicidad con parámetros de transacción comunes: dos transacciones paralelas podrían recibir el mismo valor de MAX()

• ¡La combinación de Max()+1 y CHECK(select if unique) tampoco funciona!

Soluciones:

-- 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 qué es malo?

• Usar GUIDs generados por el sistema en lugar de gen_uuid() puede afectar el rendimiento del índice

• El GUID generado por el sistema está altamente aleatorizado

Soluciones:

• Usar la función gen_uuid()

• Considerar usar BIGINT en su lugar

• En la versión 6 habrá UUID v7

14. Campos calculados ineficientes

Antipatrón: Usar campos calculados con SELECTs a otras tablas disminuye significativamente el rendimiento de las operaciones 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 qué es malo?

• Los campos calculados se calculan al vuelo, y no están pensados para implementar lógica compleja, y pueden complicar significativamente los esfuerzos de optimización

• Refuerza las relaciones entre tablas

• Tiene sentido usar campos calculados solo para cálculos ligeros con campos de la tabla, como la concatenación

Soluciones:

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. Supresión de errores sin registro

Antipatrón: ¡No suprimir los errores y advertencias de Firebird sin registrarlos!

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

¿Por qué es malo?

• Ocultar errores impide un diagnóstico y depuración adecuados. El registro adecuado de errores es crucial para comprender y resolver los problemas rápidamente.

Soluciones:

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;

Información de contacto

• Envíe sus preguntas a [email protected]

• Conviértase en Firebird Supporter (desde EUR10/mes) y participe en webinars avanzados cerrados!

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