15 Firebird-Antimuster

von Alexey Kovyazin, 14. Januar 2025

Einleitung

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

1. Mehrere parallele Abfragen auf MON$

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

Warum ist das schlecht?

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

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

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

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

Lösungen:

• Verwenden Sie MON$ nicht für nicht-administrative Aufgaben, d. h. zum Zählen oder Auditieren; vermeiden Sie die 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 von Dashboards

Anti-Pattern: Laden umfassender Dashboards oder Anzeigetafeln, die alle Bestellungen und Rechnungen des letzten Monats oder Jahres summieren, beim Anwendungsstart, oder 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 Firebird-Sicht - um ständig viele parallele Abfragen auszuführen, große Datenmengen abzurufen, zu sortieren/zu gruppieren, wird Firebird intensiv mehrere CPU-Kerne nutzen, von der Festplatte lesen, Cache und Speicher für die Sortierung verwenden (und manchmal geht die Sortierung auf die Festplatte)

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

Lösungen:

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

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

• Machen Sie das Laden des Dashboards beim Start/für einige Formulare optional, standardmäßig deaktiviert

• Laden Sie Dashboard-Daten durch expliziten Button-Klick, nicht beim Start (d. h. machen Sie es wie einen Bericht)

2. Berechnen Sie Dashboard-Daten mit 1 Prozess nach Zeitplan (d. h. Roboter) und speichern Sie sie in einer einfachen Tabelle, die durch eine einfache Abfrage abrufbar ist

3. Verwenden Sie Trigger, um Daten zu aggregieren und sie gebrauchsfertig zu speichern

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

3. Laden unnötiger Datensätze

Anti-Pattern: Laden aller Daten ohne Filterung in das Raster beim Öffnen einer Anwendung oder eines Formulars, unabhängig davon, ob es Hunderttausende von Datensätzen enthält.

procedure TDataForm.LoadAllRecords;
begin
 FDQuery1.SQL.Text := 'SELECT * FROM large_table';
 FDQuery1.Open;
 // Lädt die gesamte Tabelle in den Speicher
 DBGrid1.DataSource.DataSet := FDQuery1;
end;

Warum ist das schlecht?

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

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

• Aus Firebird-Sicht:

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

• Wenn Sie das Dataset offen halten (in Delphi), hält Firebird Puffer, sortierte Datensätze im temporären Speicher (bei ORDER BY, GROUP BY usw.), bis das Dataset geschlossen wird

Lösungen:

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

2. Begrenzen Sie die Anzahl der Datensätze mit bestimmten Kriterien, z. B. zeigen Sie Datensätze an, die in den letzten 3 Tagen erstellt/geändert wurden

3. Schließen Sie Abfragen generell 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 Raster oder einer Tabelle angezeigt werden, eine separate Abfrage FÜR JEDEN Datensatz ausgeführt wird, oder wenn Sie das klassische Beispiel des Master-Detail-Scrollens in 2 Rastern ohne Verzögerung verwenden.

procedure TForm1.GridScrolled(Sender: TObject);
begin
 // Abfrage für jede Zeile
 FDQuery2.SQL.Text :=
 'SELECT additional_info FROM details ' +
 'WHERE id = ' + IntToStr(CurrentRowId);
 FDQuery2.Open;
end;

Warum ist das schlecht?

• Das Ausführen einer separaten Abfrage FÜR JEDEN Datensatz in einem dynamischen Raster zwingt Firebird, Tausende winziger Abfragen zu verarbeiten, was unnötig CPU-Ressourcen verbraucht

• Aus Firebird-Sicht:

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

Lösungen:

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

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

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

4. Fügen Sie eine Verzögerung hinzu, um die Abfrage zum Erhalten von Details auszuführen, 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 Rasterdaten 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 fast identische Abfragen ausführen, um dieselben Datensätze abzurufen

• Wo es vorkommt: automatische Aktualisierungen für Zeitpläne, oder Auswahl für Warteschlangenpositionen, oder Suche nach “nächstgelegenem Zeitfenster” usw.

• Aus Firebird-Sicht:

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

Lösungen:

1. Intervall erhöhen!

2. Explizite (benutzerausgelöste) Aktualisierungen implementieren

3. Selektive Aktualisierungen des Datensatzes basierend auf tatsächlichen Datenänderungen verwenden (Streaming oder Trigger oder Ereignis+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 Leistung erheblich beeinträchtigen, ein Datensatz mit Tausenden kann zum Blocker werden

• Aus Firebird-Sicht: Die Kette der Datensatzversionen muss rekonstruiert werden, um die richtige Version für die spezifische Transaktion zu identifizieren; dies erfordert zahlreiche Leseoperationen, und als Ergebnis wird die Garbage Collection erheblich langsamer.

Lösungen:

1. Migrieren Sie zu Firebird 4+, dort gibt es die Zwischen-Garbage-Collection

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

3. Für Firebird <4 erwägen Sie die Verwendung von DELETE+INSERT anstelle von UPDATE

7. Verwendung von Schreibtransaktionen für reine Leseabfragen

Anti-Pattern: Die Verwendung von Schreibtransaktionen für reine Leseabfragen führt zu übermäßigen Operationen.

Warum ist das schlecht?

• Die Verwendung von Schreibtransaktionen für reine Leseabfragen führt zu vielen unnötigen Schreibvorgängen auf Header-Seiten

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

Lösungen:

• Verwenden Sie eine separate Nur-Lese-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 sind für die Verwendung in Nur-Lese-Transaktionen verfügbar

8. Verwendung von LIKE :param

Die folgende Abfrage mit Parameter wird den Index für fieldName nicht verwenden (auch wenn ein Index existiert):

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Warum ist das schlecht?

Da LIKE eine Wildcard-Suche (%) ermöglicht, die eine beliebige Anzahl von Zeichen ersetzen kann, kann Firebird nicht im Voraus bestimmen, ob der Parameterwert für die Indexsuche geeignet ist.

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

• fieldName LIKE «Alex%» - Index kann verwendet werden

• fieldName LIKE «%Alex» - Standardindex kann nicht verwendet werden

• fieldName LIKE «%Alex%» - Index kann überhaupt nicht verwendet werden

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

Lösungen:

1. Verwenden Sie STARTING WITH für bekannte Zeichenfolgenpräfixe

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

WHERE fieldName STARTING WITH ?param1

2. Optimieren Sie bidirektionale Zeichenfolgensuchen

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

-- Umgekehrten Index erstellen
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- Abfrage mit beiden Richtungen
WHERE fieldName STARTING WITH :param1
   OR reverse(fieldName) STARTING WITH reverse(:param2)

3. Implementieren Sie eine progressive Suchstrategie

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

• Versuchen Sie zuerst die schnelle indizierte Suche mit STARTING WITH

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

4. Wortbasierte Suchoptimierung

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

• Erstellen Sie eine separate Wort-ID-Zuordnungstabelle

• Suchen Sie über die Zuordnungstabelle anstelle des Originaltextes

5. Für umfassende Volltextsuchfunktionen:

• Erwägen Sie die Verwendung der IBSurgeon Full Text Search UDR

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

9. Nicht schließen von Transaktionen für reine Leseoperationen

Warum ist das schlecht?

• Das Offenhalten von Transaktionen über längere Zeiträume könnte Firebird zwingen, zahlreiche Back-Versionen für potenzielle Snapshot-Transaktionen zu pflegen

Lösungen:

• Verwenden Sie Nur-Lese-Transaktionen, wo möglich, und schließen Sie beschreibbare Transaktionen so schnell wie möglich

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

• Implementieren Sie einen ordnungsgemäßen Sweep

10. Probleme mit der Abfrageparametrisierung

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

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

Warum ist das schlecht?

• Diese Praxis reduziert die Leistung für wiederholte Abfragen

• Jede Abfrage mit eingebetteten Parameterwerten muss als neu vorbereitet werden

• Die Vorbereitung kann für große Tabellen langwierig 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 anstelle von Primärschlüsseln

Anti-Pattern: Verwendung von Triggern oder CHECK anstelle von Primärschlüsseln für Datenbankintegritätsprüfungen.

Warum ist das schlecht?

• Dies ignoriert, dass die Primärschlüsselvalidierung den speziellen Modus zum Lesen der aktuellen Version des Datensatzes verwendet, unabhängig von der Transaktionsisolationsstufe des Benutzers.

• Die Durchführung von PK-Prüfungen mit Triggern in Benutzertransaktionen erhöht die Möglichkeit von Duplikaten und verkompliziert die Logik unnötig

Lösungen:

• Verwenden Sie Primärschlüssel

• Vermeiden Sie redundante Integritätsprüfungen

• Halten Sie die Datenbanklogik einfach

12. ID-Erzeugung 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 anstelle von Sequenzen (Generatoren) für neue Bezeichner

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

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

Lösungen:

-- Generator/Sequenz verwenden!
CREATE GENERATOR gen_user_id;
-- Generator für die ID-Erzeugung verwenden
INSERT INTO users (id, name)
VALUES (
 GEN_ID(gen_user_id, 1),
 'John Doe' );

## 13. Ineffiziente Verwendung von GUIDs

**Warum ist das schlecht?**

- Die Verwendung systemgenerierter GUIDs anstelle von gen\_uuid() kann die Indexleistung beeinträchtigen

- Systemgenerierte GUIDs sind stark zufallsverteilt


**Lösungen:**

- Verwenden Sie die Funktion gen\_uuid()

- Ziehen Sie die Verwendung von BIGINT in Betracht

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

```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)));

Warum ist das schlecht?

• Berechnete Felder werden spontan berechnet und sind nicht dafür gedacht, komplexe Logik zu implementieren; sie können Optimierungsbemühungen erheblich erschweren

• Es verstärkt die Beziehungen zwischen Tabellen

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

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
 // Stiller Fehlschlag
end;

Warum ist das schlecht?

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

Lösungen:

try
 FDQuery1.Open;
except
 on E: Exception do
 begin
 // Umfassende Protokollierung
 Logger.Error('Datenbankverbindung fehlgeschlagen: ' + E.Message);
 ShowMessage('Verbindung zur Datenbank nicht möglich. Bitte kontaktieren Sie den Support.');
 // Zusätzlichen Kontext protokollieren
 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/