Contacts
IBSurgeon
Cette page a été traduite automatiquement. Lisez l'original en anglais. English

Bibliothèque IBSurgeon

15 Antipatrones de Firebird

by Alexey Kovyazin, 14-Jan-2025

Introduction

Ce document décrit 15 anti-modèles courants lors du travail avec des bases de données Firebird et propose des solutions pour chacun d’eux.

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

Anti-modèle : Une erreur très courante - déclencher OnConnect, interroger MON$ATTACHMENTS pour sélectionner les détails de l’utilisateur à des fins d’audit, ou calculer le 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 >1 Go 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 vers MON$ ralentiront Firebird de manière très significative, 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

  • Pour les besoins d’audit :

  • Utilisez des variables de contexte comme CURRENT_USER, CURRENT_TIMESTAMP, etc.

  • Utilisez Audit - fonctionnalité native de Firebird, beaucoup plus puissante que les triggers

  • Pour les besoins de licence - utilisez les variables de contexte de l’utilisateur

2. Chargement lent des tableaux de bord

Anti-modèle : Charger des tableaux de bord ou des tableaux de scores complets 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 des statistiques à l’échelle 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/regrouper, Firebird utilisera intensivement plusieurs cœurs CPU, la lecture depuis le disque, le cache, la mémoire dédiée au tri (et parfois le tri va sur le disque)

  • C’est comme construire un rapport plusieurs fois par minute !

Solutions :

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

  • Généralement, le tableau de bord n’est nécessaire que pour les analystes et la direction, excluez-le du chargement général de l’application

  • Rendez le chargement du tableau de bord au démarrage/pour un formulaire optionnel, désactivé par défaut

  • Chargez les données du tableau de bord par un clic de bouton explicite, pas au démarrage (c’est-à-dire, faites-en un rapport)

  1. Calculez les données du tableau de bord avec 1 processus selon un calendrier (c’est-à-dire un robot) et stockez-les dans une table simple prête à être récupérée par une requête simple

  2. Utilisez des triggers pour agréger les données et les stocker prêtes à l’emploi

  3. Utilisez une base de données répliquée pour calculer les données des tableaux de bord (et tous les rapports lourds aussi)

3. Chargement d’enregistrements inutiles

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

delphi 
procedure TDataForm.LoadAllRecords;
begin
 FDQuery1.SQL.Text := 'SELECT * FROM large_table';
 FDQuery1.Open;
 // Charge toute la table en mémoire
 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 :

  1. Limitez le nombre d’enregistrements avec FIRST/SKIP/ROWS

  2. Limitez le nombre d’enregistrements avec certains critères, par exemple, affichez les enregistrements créés/modifiés au cours des 3 derniers jours

  3. En général, fermez les requêtes dès que possible.

4. Requêtes excessives lors du défilement

Anti-modèle : Exécuter des requêtes lors des événements de défilement. Par exemple, lors de l’affichage de données dans une grille ou un tableau, effectuer 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
 // requête pour chaque ligne
 FDQuery2.SQL.Text :=
 'SELECT additional_info FROM details ' +
 'WHERE id = ' + IntToStr(CurrentRowId);
 FDQuery2.Open;
end;

Pourquoi est-ce mauvais ?

  • Effectuer 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 0 ms dans les statistiques, elle nécessite d’être préparée, exécutée, le résultat transféré, etc.

Solutions :

  1. Chargez plusieurs lignes à la fois en utilisant des opérations par lots

  2. Améliorez la requête principale de la grille pour exécuter la requête détaillée dans le cadre de celle-ci

  3. Ajoutez un bouton explicite pour charger les détails de la partie visible de la grille

  4. 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

  5. N’activez pas le chargement des détails au défilement pour tous les utilisateurs par défaut

5. Actualisations automatiques inutiles

Anti-modèle : Actualiser les données de la grille automatiquement à 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 clientes exécutant des requêtes presque identiques pour récupérer les mêmes enregistrements

  • Où cela se produit : actualisations automatiques pour les plannings, ou sélection pour les positions de file d’attente, ou recherche de “créneau le plus proche”, etc.

  • Du point de vue de Firebird :

  • Combinaison de chargement de tableaux de bord et d’événements de défilement : de nombreuses requêtes de taille moyenne créent une charge sur le système

Solutions :

  1. Augmentez l’intervalle !

  2. Implémentez des actualisations explicites (déclenchées par l’utilisateur)

  3. Utilisez des actualisations sélectives de l’ensemble de données basées sur les changements réels des données (streaming ou triggers ou événement+streaming)

6. Mises à jour fréquentes d’enregistrements

Anti-modèle : 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, et par conséquent, le garbage collection devient significativement plus lent.

Solutions :

  1. Migrez vers Firebird 4+, il y a un garbage collection intermédiaire

  2. Ne gardez pas de longues transactions en écriture ouvertes, faites un garbage collection approprié

  3. Pour Firebird <4, envisagez d’utiliser DELETE+INSERT au lieu d’UPDATE

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

Anti-modèle : Utiliser des transactions en écriture pour des sélections en lecture seule entraîne des opérations excessives.

Pourquoi est-ce mauvais ?

  • Utiliser des transactions en écriture pour des sélections en lecture seule entraîne de nombreuses écritures inutiles de pages d’en-tête

  • Utiliser des transactions en écriture pour des opérations en lecture seule est inefficace (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 permet d’ouvrir plusieurs transactions dans le cadre d’une seule connexion

  • Les tables temporaires globales sont disponibles pour une utilisation dans les transactions en lecture seule

8. Utilisation de LIKE :param

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

sql 
SELECT * FROM Table1 WHERE fieldName LIKE :param1

Pourquoi est-ce mauvais ?

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

Habituellement, les développeurs essaient de contourner ce problème 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» - pas possible d’utiliser l’index standard

  • fieldName LIKE «%Alex%» - pas possible d’utiliser l’index du tout

Cela entraîne 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 modèles de préfixe ou de suffixe connus, utilisez un index inversé :

sql 
-- Créer un index inversé
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- Requête utilisant les deux 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/fin/milieu (mais pas simultanément) :

  • Essayez d’abord une recherche indexée rapide avec STARTING WITH

  • Si aucun résultat n’est trouvé, utilisez une recherche LIKE plus lente en secours

4. Optimisation de la recherche par mots

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

  • Créez une table de correspondance séparée mot-ID

  • Recherchez dans la table de correspondance au lieu du texte original

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

  • Envisagez d’utiliser IBSurgeon Full Text Search UDR

  • Cette solution open-source fournit des fonctionnalités avancées de recherche de texte

9. Non-fermeture des transactions pour les opérations en lecture seule

Pourquoi est-ce mauvais ?

  • Garder les transactions ouvertes pendant de longues périodes pourrait forcer Firebird à maintenir de nombreuses versions arrière pour les transactions snapshot potentielles

Solutions :

  • Utilisez des transactions en lecture seule lorsque c’est possible, et fermez les transactions en é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-modèle : É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 chronophage 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 : triggers/CHECK au lieu de la clé primaire

Anti-modèle : Utiliser des triggers 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 que la validation de la clé primaire utilise le mode spécial pour lire la version actuelle de l’enregistrement, quel que soit le niveau d’isolation de la transaction de l’utilisateur.

  • Faire des vérifications de clé primaire avec des triggers dans les transactions utilisateur augmente la possibilité de doublons 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-modèle : Utiliser MAX(id)+1 pour les nouveaux identifiants est peu 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 de séquences (générateurs) pour les nouveaux identifiants

  • MAX(id)+1 ne garantit pas l’unicité avec des 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 
-- Utilisez un générateur/séquence !
CREATE GENERATOR gen_user_id;
-- Utilisez le générateur pour la génération d'ID
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 affecter les performances des index

- Un GUID généré par le système est hautement aléatoire


**Solutions :**

- Utiliser la fonction gen\_uuid()

- Envisager d'utiliser BIGINT à la place

- Dans la version 6, il y aura UUID v7


## 14. Champs calculés inefficaces

**Anti-modèle :** L'utilisation de champs calculés avec des SELECT vers d'autres tables diminue considérablement les performances des opérations 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)));

Pourquoi est-ce mauvais ?

  • Les champs calculés sont calculés à la volée, et ils ne sont pas destiné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 les 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-modèle : Ne supprimez pas les erreurs et avertissements Firebird sans journalisation !

delphi 
try
 FDQuery1.Open;
except
 // Échec silencieux
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
 // Journalisation complète
 Logger.Error('Échec de la connexion à la base de données : ' + E.Message);
 ShowMessage('Impossible de se connecter à la base de données. Veuillez contacter le support.');
 // Journaliser le contexte supplémentaire
 Logger.LogStackTrace(E);
 end;
end;

Coordonnées