15 antipatterns Firebird

par Alexey Kovyazin, 14-Jan-2025

Introduction

Ce document décrit 15 anti-patrons courants lors de l’utilisation des bases de données Firebird et fournit des solutions pour chacun d’eux.

1. Requêtes parallèles multiples sur MON$

Anti-patron : Une erreur très répandue - déclencheur OnConnect, requête sur MON$ATTACHMENTS pour sélectionner les détails de l’utilisateur à des fins d’audit, ou calcul du nombre de connexions à des fins de licence.

Pourquoi est-ce mauvais ?

Les tables MON$ sont des tables virtuelles stockées dans les fichiers système fbNN_mon_xx, avec des statistiques de performance, etc.
Un fichier >1Go signifie que vous l’utilisez trop
Elles sont conçues uniquement pour l’usage des administrateurs système - c’est-à-dire 1-2 requêtes parallèles, exclusivement pour les administrateurs
200+ connexions avec des requêtes parallèles sur MON$ ralentiront très significativement Firebird, et 500+ requêtes simultanées « bloqueront » Firebird avec de fortes probabilités

Solutions :

N’utilisez pas MON$ pour des tâches non administratives, c’est-à-dire pour compter ou auditer, évitez de les utiliser dans OnConnect
À des fins d’audit :
Utilisez les variables de contexte comme CURRENT_USER, CURRENT_TIMESTAMP, etc.
Utilisez Audit - fonctionnalité native de Firebird, bien plus puissante que les déclencheurs
À des fins de licence - utilisez les variables de contexte de l’utilisateur

2. Chargement lent du tableau de bord

Anti-patron : Charger des tableaux de bord complets ou des tableaux de scores qui additionnent toutes les commandes et factures du dernier mois ou de l’année au démarrage de l’application, ou mettre à jour certaines métriques chaque minute ou plus souvent.

sql

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';

Pourquoi est-ce mauvais ?

Les utilisateurs doivent attendre plusieurs secondes pour voir les statistiques de l’entreprise avant de pouvoir commencer leur travail réel
Du point de vue de Firebird - pour exécuter constamment de nombreuses requêtes parallèles, récupérer des quantités massives de données, les trier/grouper, Firebird utilisera intensivement plusieurs cœurs de CPU, en lisant depuis le disque, le cache, la mémoire dédiée au tri (et parfois le tri se fait sur disque)
C’est comme construire un rapport plusieurs fois par minute !

Solutions :

Réduisez le nombre d’utilisateurs qui verront les tableaux de bord :

Habituellement, le tableau de bord n’est requis que pour les analystes et la direction, excluez-le de la charge générale de l’application
Rendez le chargement du tableau de bord au démarrage/pour certains formulaires optionnel, désactivé par défaut
Chargez les données du tableau de bord par un clic explicite sur un bouton, pas au démarrage (c’est-à-dire, faites-en un rapport)

Calculez les données du tableau de bord avec 1 processus selon un planning (c’est-à-dire un robot) et stockez-les dans une table simple prête à être récupérée par une requête simple
Utilisez des déclencheurs pour agréger les données et les stocker prêtes à l’emploi
Utilisez une base de données réplique pour calculer les données des tableaux de bord (et tous les rapports lourds aussi)

3. Chargement d’enregistrements inutiles

Anti-patron : Charger toutes les données sans filtrage dans la grille à l’ouverture d’une application ou d’un formulaire, qu’il contienne ou non des centaines de milliers d’enregistrements.

delphi

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

Pourquoi est-ce mauvais ?

Bien que la grille n’affiche que 50 enregistrements, les utilisateurs doivent faire défiler des milliers d’enregistrements au lieu d’utiliser la fonctionnalité de recherche
Dans 99% des cas, les utilisateurs ont besoin d’un sous-ensemble très restreint de données : les enregistrements de ventes les plus récents, par exemple
Du point de vue de Firebird :
Chaque ouverture nécessite la lecture, le stockage en cache et le transfert de milliers d’enregistrements via le réseau
Si vous gardez le dataset ouvert (en Delphi), Firebird conserve les tampons, les enregistrements triés dans l’espace temporaire (si ORDER BY, GROUP BY, etc.) jusqu’à la fermeture du dataset

Solutions :

Limitez le nombre d’enregistrements avec FIRST/SKIP/ROWS
Limitez le nombre d’enregistrements avec certains critères, par exemple, affichez les enregistrements créés/modifiés au cours des 3 derniers jours
En général, fermez les requêtes dès que possible.

4. Requêtes excessives lors du défilement

Anti-patron : Exécuter des requêtes sur les événements de défilement. Par exemple, lors de l’affichage de données dans une grille ou un tableau, exécuter une requête séparée POUR CHAQUE enregistrement, ou si vous utilisez l’exemple classique de défilement maître-détail dans 2 grilles sans délai.

delphi

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;

Pourquoi est-ce mauvais ?

Exécuter une requête séparée POUR CHAQUE enregistrement dans une grille dynamique force Firebird à traiter des milliers de petites requêtes, consommant inutilement des ressources CPU
Du point de vue de Firebird :
De nombreuses (milliers par seconde) petites requêtes créeront une charge CPU significative, car même si la requête affiche 0ms dans les statistiques, elle doit être préparée, exécutée, le résultat transféré, etc.

Solutions :

Chargez plusieurs lignes à la fois en utilisant des opérations par lots
Améliorez la requête principale de la grille pour exécuter la requête détaillée dans le cadre de celle-ci
Ajoutez un bouton explicite pour charger les détails de la partie visible de la grille
Ajoutez un délai pour exécuter la requête afin de recevoir les détails, pour empêcher les requêtes immédiates pendant le défilement
N’activez pas le chargement des détails lors du défilement pour tous les utilisateurs par défaut

5. Rafraîchissements automatiques inutiles

Anti-patron : Rafraîchir automatiquement les données de la grille à intervalles minimaux dans chaque application cliente, avec cette fonctionnalité activée par défaut.

Pourquoi est-ce mauvais ?

Cela entraîne des centaines de connexions client exécutant des requêtes presque identiques pour récupérer les mêmes enregistrements
Où cela se produit : rafraîchissements automatiques pour les plannings, ou sélection pour les positions de file d’attente, ou recherche du « créneau le plus proche », etc.
Du point de vue de Firebird :
La combinaison du chargement des tableaux de bord et des événements de défilement : de nombreuses requêtes de taille moyenne créent une charge sur le système

Solutions :

Augmentez l’intervalle !
Implémentez des rafraîchissements explicites (déclenchés par l’utilisateur)
Utilisez des rafraîchissements sélectifs du jeu de données basés sur les changements réels des données (streaming ou déclencheurs ou événement+streaming)

6. Mises à jour fréquentes d’enregistrements

Anti-patron : Mettre à jour fréquemment le même enregistrement dans différentes transactions, créant de nombreuses versions d’enregistrement.

Pourquoi est-ce mauvais ?

Un enregistrement avec des dizaines de versions peut dégrader significativement les performances, un enregistrement avec des milliers peut devenir un bloqueur
Du point de vue de Firebird : la chaîne de versions d’enregistrement doit être reconstruite pour identifier la version appropriée de la transaction spécifique, cela nécessite de nombreuses opérations de lecture, en conséquence, la collecte des déchets devient significativement plus lente.

Solutions :

Migrez vers Firebird 4+, il y a une collecte des déchets intermédiaire
Ne gardez pas de transactions d’écriture de longue durée, faites la collecte des déchets appropriée
Pour Firebird <4, envisagez d’utiliser DELETE+INSERT au lieu de UPDATE

7. Utilisation de transactions d’écriture pour des sélections en lecture seule

Anti-patron : L’utilisation de transactions d’écriture pour des sélections en lecture seule entraîne des opérations excessives.

Pourquoi est-ce mauvais ?

L’utilisation de transactions d’écriture pour des sélections en lecture seule entraîne de nombreuses écritures inutiles de pages d’en-tête
L’utilisation de transactions d’écriture pour des opérations en lecture seule est inefficace (un grand TIP lors du commit crée une charge supplémentaire sur le serveur)

Solutions :

Utilisez une transaction en lecture seule séparée pour les opérations qui ne modifient pas les données
Firebird est l’une des rares bases de données qui permettent d’ouvrir plusieurs transactions dans le cadre d’une seule connexion
Les tables temporaires globales sont disponibles pour une utilisation dans des transactions en lecture seule

8. Utiliser LIKE :param

La requête suivante avec paramètre n’utilisera pas l’index pour le champ fieldName (même si l’index existe) :

sql

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Pourquoi est-ce mauvais ?

Puisque LIKE permet la recherche avec caractères génériques (%), qui peuvent remplacer n’importe quel nombre de symboles, Firebird ne peut pas déterminer à l’avance si la valeur du paramètre conviendra à une recherche par index.

Habituellement, les développeurs essaient de contourner cela en intégrant la valeur du paramètre dans le texte de la requête :

fieldName LIKE «Alex%» - possible d’utiliser l’index
fieldName LIKE «%Alex» - impossible d’utiliser l’index standard
fieldName LIKE «%Alex%» - impossible d’utiliser l’index du tout

Cela conduit à d’autres problèmes (voir #10 ci-dessous).

Solutions :

1. Utilisez STARTING WITH pour les préfixes de chaîne connus

Lorsque votre valeur de recherche ne commence jamais par un caractère générique %, préférez STARTING WITH à LIKE :

sql

WHERE fieldName STARTING WITH ?param1

2. Optimisez les recherches de chaînes bidirectionnelles

Pour les chaînes avec des motifs de préfixe ou de suffixe connus, utilisez un index inversé :

sql

-- 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. Implémentez une stratégie de recherche progressive

Pour les chaînes qui apparaissent au début/à la fin/au milieu (mais pas simultanément) :

Essayez d’abord une recherche indexée rapide avec STARTING WITH
Si aucun résultat n’est trouvé, revenez à la recherche LIKE plus lente

4. Optimisation de la recherche basée sur les mots

Lors de la recherche de mots complets (délimités par des espaces, des virgules, etc.) :

Créez une table de mappage mot-ID séparée
Recherchez via la table de mappage au lieu du texte original

5. Pour des capacités de recherche en texte intégral complètes :

Envisagez d’utiliser IBSurgeon Full Text Search UDR
Cette solution open-source fournit des fonctionnalités avancées de recherche textuelle

9. Ne pas fermer les transactions pour les opérations en lecture seule

Pourquoi est-ce mauvais ?

Garder les transactions ouvertes pendant des périodes prolongées pourrait forcer Firebird à maintenir de nombreuses versions antérieures pour les transactions snapshot potentielles

Solutions :

Utilisez des transactions en lecture seule lorsque c’est possible, et fermez les transactions d’écriture dès que possible
Utilisez des versions modernes de Firebird (4+) pour réduire l’impact des chaînes de versions d’enregistrements
Implémentez un sweep approprié

10. Problèmes de paramétrage des requêtes

Anti-patron : Éviter les requêtes préparées et le paramétrage, en intégrant plutôt les valeurs des paramètres directement dans le texte de la requête.

delphi

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

Pourquoi est-ce mauvais ?

Cette pratique réduit les performances pour les requêtes répétées
Chaque requête avec des valeurs de paramètres intégrées doit être préparée comme nouvelle
La préparation peut être longue et coûteuse en temps pour les grandes tables
Complique l’analyse des problèmes
Il est difficile de regrouper les requêtes par texte
Crée des vulnérabilités d’injection SQL

Solutions :

delphi

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

11. Vérification d’intégrité incorrecte : déclencheurs/CHECK au lieu de clé primaire

Anti-patron : Utiliser des déclencheurs ou CHECK au lieu de clés primaires pour les vérifications d’intégrité de la base de données.

Pourquoi est-ce mauvais ?

Cela ignore le fait que la validation de clé primaire utilise le mode spécial pour lire la version actuelle de l’enregistrement, indépendamment du niveau d’isolation de la transaction de l’utilisateur.
Effectuer des vérifications de PK avec des déclencheurs dans les transactions utilisateur augmente la possibilité de duplication et complique inutilement la logique

Solutions :

Utilisez des clés primaires
Évitez les vérifications d’intégrité redondantes
Gardez la logique de la base de données simple

12. Génération d’ID avec MAX()

Anti-patron : Utiliser MAX(id)+1 pour les nouveaux identifiants n’est pas fiable et inefficace.

sql

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

Pourquoi est-ce mauvais ?

Utiliser MAX(id)+1 au lieu des séquences (générateurs) pour les nouveaux identifiants
MAX(id)+1 ne garantit pas l’unicité avec les paramètres de transaction courants - deux transactions parallèles pourraient recevoir la même valeur MAX()
La combinaison de Max()+1 et CHECK(select if unique) ne fonctionne pas non plus !

Solutions :

sql

-- 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. Utilisation inefficace des GUID

Pourquoi est-ce mauvais ?

L’utilisation de GUID générés par le système au lieu de gen_uuid() peut impacter les performances des index
Un GUID généré par le système est fortement randomisé

Solutions :

Utiliser la fonction gen_uuid()
Envisager plutôt d’utiliser BIGINT
Dans la version 6, il y aura UUID v7

14. Champs calculés inefficaces

Anti-pattern : L’utilisation de champs calculés avec des SELECT vers d’autres tables diminue significativement les performances des opérations SELECT simples.

sql

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

Pourquoi est-ce mauvais ?

Les champs calculés sont calculés à la volée, et ils ne sont pas censés implémenter une logique complexe, et peuvent considérablement compliquer les efforts d’optimisation
Cela renforce les relations entre les tables
Il est judicieux d’utiliser des champs calculés uniquement pour des calculs légers avec les champs de la table, comme la concaténation

Solutions :

sql

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. Suppression des erreurs sans journalisation

Anti-pattern : Ne supprimez pas les erreurs et avertissements de Firebird sans les journaliser !

delphi

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

Pourquoi est-ce mauvais ?

Masquer les erreurs empêche un diagnostic et un débogage appropriés. Une journalisation correcte des erreurs est cruciale pour comprendre et résoudre rapidement les problèmes.

Solutions :

delphi

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;

Informations de contact

Envoyez vos questions à [email protected]
Devenez Firebird Supporter (à partir de 10 EUR/mois) et participez à des webinaires avancés fermés !
https://store.firebirdsql.org/p/firebird-associate-donation-eur/