15 Firebird-Antipatterns

von Alexey Kovyazin, 14-Jan-2025

Einleitung

Dieses Dokument beschreibt 15 häufige Anti-Patterns bei der Arbeit mit Firebird-Datenbanken und bietet für jedes eine Lösung.

1. Mehrere parallele Abfragen auf MON$

Anti-Pattern: Ein sehr verbreiteter Fehler – ein OnConnect-Trigger, eine Abfrage auf MON$ATTACHMENTS, um Benutzerdetails für Audit-Zwecke auszuwählen, oder die Berechnung der Anzahl der Verbindungen für Lizenzierungszwecke.

Warum ist das schlecht?

• MON$-Tabellen sind virtuelle Tabellen, die in den Systemdateien fbNN_mon_xx gespeichert sind, mit Performance-Statistiken usw.

• Eine Datei >1 GB bedeutet, dass Sie sie zu stark nutzen

• Sie sind ausschließlich für die Nutzung durch Systemadministratoren gedacht – d. h. 1–2 parallele Abfragen, ausschließlich für Administratoren

• 200+ Verbindungen mit parallelen Abfragen auf MON$ verlangsamen Firebird sehr erheblich, und 500+ gleichzeitige Abfragen werden Firebird mit hoher Wahrscheinlichkeit „aufhängen“

Lösungen:

• Verwenden Sie MON$ nicht für nicht-administrative Aufgaben, z. B. zum Zählen oder Auditieren, und vermeiden Sie ihre Verwendung in OnConnect

• Für Audit-Zwecke:

• Verwenden Sie Kontextvariablen wie CURRENT_USER, CURRENT_TIMESTAMP usw.

• Verwenden Sie Audit – eine native Firebird-Funktion, die viel leistungsfähiger als Trigger ist

• Für Lizenzierungszwecke – verwenden Sie die Kontextvariablen des Benutzers

2. Langsames Laden des Dashboards

Anti-Pattern: Das Laden umfassender Dashboards oder Scoreboards, die alle Bestellungen und Rechnungen des letzten Monats oder Jahres summieren, beim Start der Anwendung oder das Aktualisieren einiger Metriken jede Minute oder häufiger.

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

Warum ist das schlecht?

• Benutzer müssen mehrere Sekunden warten, um unternehmensweite Statistiken zu sehen, bevor sie ihre eigentliche Arbeit beginnen können

• Aus Sicht von Firebird – um ständig viele parallele Abfragen auszuführen und riesige Datenmengen abzurufen, zu sortieren/zu gruppieren, wird Firebird intensiv mehrere CPU-Kerne nutzen, von der Festplatte lesen, Cache und für die Sortierung reservierten Speicher verwenden (und manchmal geht die Sortierung auf die Festplatte)

• Es ist, als würde man mehrmals pro Minute einen Bericht erstellen!

Lösungen:

1. Reduzieren Sie die Anzahl der Benutzer, die Dashboards sehen:

• In der Regel wird ein Dashboard nur von Analysten und dem Management benötigt; schließen Sie es aus der allgemeinen Anwendungslast aus

• Machen Sie das Laden des Dashboards beim Start/für ein bestimmtes Formular optional und standardmäßig deaktiviert

• Laden Sie Dashboard-Daten per explizitem Button-Klick, nicht beim Start (d. h. machen Sie es zu einem Bericht)

2. Berechnen Sie Dashboard-Daten mit einem Prozess nach Zeitplan (d. h. einem Roboter) und speichern Sie sie in einer einfachen Tabelle, die für den Abruf per einfacher Abfrage bereitsteht

3. Verwenden Sie Trigger, um Daten zu aggregieren und sie zur Nutzung bereitzustellen

4. Verwenden Sie eine Replikatdatenbank, um Dashboard-Daten (und auch alle aufwendigen Berichte) zu berechnen

3. Laden unnötiger Datensätze

Anti-Pattern: Beim Öffnen einer Anwendung oder eines Formulars alle Daten ohne Filterung in das Grid laden, unabhängig davon, ob es Hunderttausende von Datensätzen enthält.

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

Warum ist das schlecht?

• Obwohl das Grid nur 50 Datensätze anzeigt, müssen Benutzer durch Tausende von Datensätzen scrollen, anstatt die Suchfunktion zu nutzen

• In 99 % der Fälle benötigen Benutzer nur eine sehr kleine Teilmenge der Daten: beispielsweise die neuesten Verkaufsdatensätze

• Aus Sicht von Firebird:

• Jedes Öffnen erfordert das Lesen, Speichern im Cache und Übertragen von Tausenden von Datensätzen über das Netzwerk

• Wenn Sie das Dataset (in Delphi) geöffnet halten, behält Firebird Puffer und sortierte Datensätze im temporären Speicher (bei ORDER BY, GROUP BY usw.) bis zum Schließen des Datasets

Lösungen:

1. Begrenzen Sie die Anzahl der Datensätze mit FIRST/SKIP/ROWS

2. Begrenzen Sie die Anzahl der Datensätze durch bestimmte Kriterien, z. B. Anzeige der in den letzten 3 Tagen erstellten/geänderten Datensätze

3. Schließen Sie Abfragen im Allgemeinen so schnell wie möglich.

4. Übermäßige Abfragen beim Scrollen

Anti-Pattern: Ausführen von Abfragen bei Scroll-Ereignissen. Zum Beispiel, wenn Daten in einem Grid oder einer Tabelle angezeigt werden, eine separate Abfrage FÜR JEDEN Datensatz auszuführen, oder wenn Sie das klassische Beispiel des Master-Detail-Scrollens in 2 Grids ohne Verzögerung verwenden.

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;

Warum ist das schlecht?

• Eine separate Abfrage FÜR JEDEN Datensatz in einem dynamischen Grid zwingt Firebird, Tausende winziger Abfragen zu verarbeiten, was unnötig CPU-Ressourcen verbraucht

• Aus Sicht von Firebird:

• Viele (Tausende pro Sekunde) kleine Abfragen erzeugen eine erhebliche CPU-Last, denn selbst wenn eine Abfrage in der Statistik 0 ms anzeigt, muss sie vorbereitet, ausgeführt und das Ergebnis übertragen werden usw.

Lösungen:

1. Laden Sie mehrere Zeilen auf einmal mit Batch-Operationen

2. Erweitern Sie die Hauptabfrage für das Grid, um die Detailabfrage als Teil davon auszuführen

3. Fügen Sie einen expliziten Button hinzu, um Details für den sichtbaren Teil des Grids zu laden

4. Fügen Sie eine Verzögerung hinzu, bevor die Abfrage zum Abrufen der Details ausgeführt wird, um sofortige Abfragen während des Scrollens zu verhindern

5. Aktivieren Sie das Laden von Details beim Scrollen nicht standardmäßig für alle Benutzer

5. Unnötige automatische Aktualisierungen

Anti-Pattern: Automatisches Aktualisieren von Grid-Daten in minimalen Intervallen in jeder Client-Anwendung, wobei diese Funktion standardmäßig aktiviert ist.

Warum ist das schlecht?

• Dies führt dazu, dass Hunderte von Client-Verbindungen nahezu identische Abfragen ausführen, um dieselben Datensätze abzurufen

• Wo das passiert: automatische Aktualisierungen für Zeitpläne oder Select für Warteschlangenpositionen oder Suche nach dem „nächsten Slot“ usw.

• Aus Sicht von Firebird:

• Die Kombination aus dem Laden von Dashboards und On-Scroll-Ereignissen: viele mittelgroße Abfragen erzeugen Last auf dem System

Lösungen:

1. Erhöhen Sie das Intervall!

2. Implementieren Sie explizite (vom Benutzer ausgelöste) Aktualisierungen

3. Verwenden Sie selektive Aktualisierungen des Datasets basierend auf tatsächlichen Datenänderungen (Streaming oder Trigger oder Event+Streaming)

6. Häufige Datensatzaktualisierungen

Anti-Pattern: Häufiges Aktualisieren desselben Datensatzes in verschiedenen Transaktionen, wodurch zahlreiche Datensatzversionen entstehen.

Warum ist das schlecht?

• Ein Datensatz mit Dutzenden von Versionen kann die Performance erheblich beeinträchtigen, ein Datensatz mit Tausenden kann zum Blocker werden

• Aus Sicht von Firebird: Die Kette der Datensatzversionen muss rekonstruiert werden, um die richtige Version der jeweiligen Transaktion zu identifizieren; dies erfordert zahlreiche Leseoperationen, wodurch die Garbage Collection erheblich langsamer wird.

Lösungen:

1. Migrieren Sie auf Firebird 4+, dort gibt es eine intermediäre Garbage Collection

2. Halten Sie keine lang laufenden schreibbaren Transaktionen aufrecht, führen Sie die ordnungsgemäße Garbage Collection durch

3. Für Firebird <4 sollten Sie DELETE+INSERT anstelle von UPDATE in Betracht ziehen

7. Verwendung von Schreibtransaktionen für schreibgeschützte Selects

Anti-Pattern: Die Verwendung von Schreibtransaktionen für schreibgeschützte Selects führt zu übermäßigen Operationen.

Warum ist das schlecht?

• Die Verwendung von Schreibtransaktionen für schreibgeschützte Selects führt zu vielen unnötigen Schreibvorgängen von Header-Seiten

• Die Verwendung von Schreibtransaktionen für schreibgeschützte Operationen ist ineffizient (eine große TIP beim Commit erzeugt zusätzliche Last auf dem Server)

Lösungen:

• Verwenden Sie eine separate schreibgeschützte Transaktion für Operationen, die keine Daten ändern

• Firebird ist eine der wenigen Datenbanken, die das Öffnen mehrerer Transaktionen im Rahmen einer einzigen Verbindung ermöglichen

• Globale temporäre Tabellen (Global Temporary Tables) stehen für die Verwendung in schreibgeschützten Transaktionen zur Verfügung

8. Verwendung von LIKE :param

Die folgende Abfrage mit Parameter verwendet keinen Index für das Feld fieldName (auch wenn ein Index existiert):

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Warum ist das schlecht?

Da LIKE eine Wildcard-Suche (%) erlaubt, die eine beliebige Anzahl von Symbolen ersetzen kann, kann Firebird nicht im Voraus bestimmen, ob der Parameterwert für eine Indexsuche geeignet sein wird.

Üblicherweise versuchen Entwickler, dies zu umgehen, indem sie den Parameterwert in den Abfragetext einbetten:

• fieldName LIKE «Alex%» – Indexverwendung möglich

• fieldName LIKE «%Alex» – keine Verwendung des Standardindex möglich

• fieldName LIKE «%Alex%» – keine Indexverwendung überhaupt möglich

Dies führt zu anderen Problemen (siehe #10 unten).

Lösungen:

1. Verwenden Sie STARTING WITH für bekannte String-Präfixe

Wenn Ihr Suchwert nie mit einem Wildcard % beginnt, bevorzugen Sie STARTING WITH gegenüber LIKE:

WHERE fieldName STARTING WITH ?param1

2. Optimieren Sie bidirektionale String-Suchen

Für Strings mit bekannten Präfix- oder Suffixmustern verwenden Sie einen umgekehrten Index:

-- 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. Implementieren Sie eine progressive Suchstrategie

Für Strings, die am Anfang/Ende/in der Mitte vorkommen (aber nicht gleichzeitig):

• Versuchen Sie zuerst eine schnelle indizierte Suche mit STARTING WITH

• Wenn keine Ergebnisse gefunden werden, greifen Sie auf die langsamere LIKE-Suche zurück

4. Wortbasierte Suchoptimierung

Bei der Suche nach vollständigen Wörtern (getrennt durch Leerzeichen, Kommas usw.):

• Erstellen Sie eine separate Wort-ID-Zuordnungstabelle

• Suchen Sie über die Zuordnungstabelle statt über den Originaltext

5. Für umfassende Volltextsuchfunktionen:

• Ziehen Sie die Verwendung von IBSurgeon Full Text Search UDR in Betracht

• Diese Open-Source-Lösung bietet erweiterte Textsuchfunktionen

9. Nicht-Schließen von Transaktionen für schreibgeschützte Operationen

Warum ist das schlecht?

• Wenn Transaktionen über längere Zeiträume offen gehalten werden, kann Firebird gezwungen sein, zahlreiche Back-Versionen für potenzielle Snapshot-Transaktionen aufrechtzuerhalten

Lösungen:

• Verwenden Sie schreibgeschützte Transaktionen, wo es möglich ist, und schließen Sie schreibbare Transaktionen so schnell wie möglich

• Verwenden Sie moderne Firebird-Versionen (4+), um die Auswirkungen von Ketten von Datensatzversionen zu reduzieren

• Implementieren Sie einen ordnungsgemäßen Sweep

10. Probleme bei der Abfrageparametrisierung

Anti-Pattern: Vermeidung vorbereiteter Abfragen und der Parametrisierung, stattdessen Einbetten der Parameterwerte direkt in den Abfragetext.

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

Warum ist das schlecht?

• Diese Praxis reduziert die Performance bei wiederholten Abfragen

• Jede Abfrage mit eingebetteten Parameterwerten muss als neue vorbereitet werden

• Die Vorbereitung kann bei großen Tabellen lang und zeitaufwendig sein

• Erschwert die Problemanalyse

• Es ist schwierig, Abfragen nach Text zu gruppieren

• Erzeugt SQL-Injection-Schwachstellen

Lösungen:

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

11. Falsche Integritätsprüfung: Trigger/CHECKs statt Primary Key

Anti-Pattern: Verwendung von Triggern oder CHECK statt Primary Keys für Datenbankintegritätsprüfungen.

Warum ist das schlecht?

• Dies ignoriert, dass die Primary-Key-Validierung den speziellen Modus verwendet, um die aktuelle Version des Datensatzes zu lesen, unabhängig vom Isolationslevel der Transaktion des Benutzers.

• PK-Prüfungen mit Triggern in Benutzertransaktionen erhöhen die Wahrscheinlichkeit von Duplikaten und verkomplizieren die Logik unnötig

Lösungen:

• Verwenden Sie Primary Keys

• Vermeiden Sie redundante Integritätsprüfungen

• Halten Sie die Datenbanklogik einfach

12. ID-Generierung mit MAX()

Anti-Pattern: Die Verwendung von MAX(id)+1 für neue Bezeichner ist unzuverlässig und ineffizient.

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

Warum ist das schlecht?

• Verwendung von MAX(id)+1 statt Sequenzen (Generatoren) für neue Bezeichner

• MAX(id)+1 garantiert bei üblichen Transaktionsparametern keine Eindeutigkeit – zwei parallele Transaktionen könnten denselben MAX()-Wert erhalten

• Die Kombination von Max()+1 und CHECK(select if unique) funktioniert ebenfalls nicht!

Lösungen:

-- 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. Ineffiziente GUID-Nutzung

Warum ist das schlecht?

• Die Verwendung systemgenerierter GUIDs anstelle von gen_uuid() kann die Indexleistung beeinträchtigen

• Systemgenerierte GUIDs sind stark zufällig

Lösungen:

• Verwenden Sie die Funktion gen_uuid()

• Erwägen Sie stattdessen die Verwendung von BIGINT

• In Version 6 wird es UUID v7 geben

14. Ineffiziente berechnete Felder

Anti-Pattern: Die Verwendung berechneter Felder mit SELECTs auf andere Tabellen verringert die Leistung einfacher SELECT-Operationen erheblich.

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

Warum ist das schlecht?

• Berechnete Felder werden zur Laufzeit berechnet, sollen keine komplexe Logik implementieren und können Optimierungsbemühungen erheblich erschweren

• Es verstärkt die Beziehungen zwischen Tabellen

• Es ist sinnvoll, berechnete Felder nur für leichte Berechnungen mit Feldern der Tabelle zu verwenden, wie z. B. Verkettung

Lösungen:

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. Fehlerunterdrückung ohne Protokollierung

Anti-Pattern: Unterdrücken Sie Firebird-Fehler und -Warnungen nicht ohne Protokollierung!

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

Warum ist das schlecht?

• Das Verbergen von Fehlern verhindert eine ordnungsgemäße Diagnose und Fehlersuche. Eine ordnungsgemäße Fehlerprotokollierung ist entscheidend, um Probleme schnell zu verstehen und zu beheben.

Lösungen:

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;

Kontaktinformationen

• Senden Sie Ihre Fragen an [email protected]

• Werden Sie Firebird Supporter (ab EUR10/Monat) und nehmen Sie an geschlossenen fortgeschrittenen Webinaren teil!

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