15 Firebird-antipatronen

door Alexey Kovyazin, 14-jan-2025

Inleiding

Dit document beschrijft 15 veelvoorkomende anti-patterns bij het werken met Firebird-databases en biedt voor elk daarvan een oplossing.

1. Meerdere parallelle queries op MON$

Anti-pattern: Een zeer populaire fout - een OnConnect-trigger, een query op MON$ATTACHMENTS om de gegevens van de gebruiker op te halen voor auditdoeleinden, of het berekenen van het aantal verbindingen voor licentiedoeleinden.

Waarom is dit slecht?

• MON$-tabellen zijn virtuele tabellen die zijn opgeslagen in fbNN_mon_xx systeembestanden, met prestatiestatistieken, enz.

• Bestand >1Gb betekent dat je het te veel gebruikt

• Ze zijn uitsluitend bedoeld voor gebruik door systeembeheerders - dat wil zeggen, 1-2 parallelle queries, uitsluitend voor beheerders

• 200+ verbindingen met parallelle queries op MON$ zullen Firebird zeer aanzienlijk vertragen, en 500+ gelijktijdige queries zullen Firebird met hoge waarschijnlijkheid “laten hangen”

Oplossingen:

• Gebruik MON$ niet voor niet-administratieve taken, dat wil zeggen voor tellen of auditen, vermijd het gebruik ervan in OnConnect

• Voor auditdoeleinden:

• Gebruik Contextvariabelen zoals CURRENT_USER, CURRENT_TIMESTAMP, enz.

• Gebruik Audit - een native Firebird-functie, veel krachtiger dan triggers

• Voor licentiedoeleinden - gebruik de contextvariabelen van de gebruiker

2. Traag laden van dashboards

Anti-pattern: Het laden van uitgebreide dashboards of scoreborden die alle orders en facturen van de afgelopen maand of het afgelopen jaar optellen bij het opstarten van de applicatie, of het elke minuut of vaker bijwerken van bepaalde statistieken.

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

Waarom is dit slecht?

• Gebruikers moeten meerdere seconden wachten om bedrijfsbrede statistieken te zien voordat ze met hun eigenlijke werk kunnen beginnen

• Vanuit het oogpunt van Firebird - om voortdurend veel parallelle queries uit te voeren, enorme hoeveelheden gegevens op te halen, te sorteren/groeperen, zal Firebird intensief meerdere CPU-cores gebruiken, lezen van schijf, cache, geheugen dat is toegewezen voor sorteren (en soms gaat het sorteren naar schijf)

• Het is alsof je meerdere keren per minuut een rapport opbouwt!

Oplossingen:

1. Verminder het aantal gebruikers dat dashboards te zien krijgt:

• Meestal is een dashboard alleen nodig voor analisten en het management; sluit het uit van de algemene applicatielast

• Maak het laden van het dashboard bij het opstarten/voor een bepaald formulier optioneel, standaard uitgeschakeld

• Laad dashboardgegevens door expliciet op een knop te klikken, niet bij het opstarten (dat wil zeggen, maak er een rapport van)

2. Bereken dashboardgegevens met 1 proces volgens een schema (dat wil zeggen, een robot) en sla ze op in een eenvoudige tabel die klaar is om op te halen met een eenvoudige query

3. Gebruik triggers om gegevens te aggregeren en sla ze klaar voor gebruik op

4. Gebruik een replicadatabase om dashboardgegevens te berekenen (en ook alle zware rapporten)

3. Onnodige records laden

Anti-pattern: Alle gegevens zonder filtering in de grid laden bij het openen van een applicatie of formulier, ongeacht of het honderdduizenden records bevat.

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

Waarom is dit slecht?

• Ondanks dat de grid slechts 50 records toont, moeten gebruikers door duizenden records scrollen in plaats van de zoekfunctionaliteit te gebruiken

• In 99% van de gevallen hebben gebruikers een zeer nauwe subset van gegevens nodig: bijvoorbeeld de meest recente verkooprecords

• Vanuit het oogpunt van Firebird:

• Elk openen vereist het lezen, opslaan in cache en overdragen van duizenden records via het netwerk

• Als je de dataset open houdt (in Delphi), houdt Firebird buffers, gesorteerde records in tijdelijke ruimte (bij ORDER BY, GROUP BY, enz.) aan totdat de dataset wordt gesloten

Oplossingen:

1. Beperk het aantal records met FIRST/SKIP/ROWS

2. Beperk het aantal records met een criterium, bijvoorbeeld toon records die in de afgelopen 3 dagen zijn aangemaakt/gewijzigd

3. Sluit queries in het algemeen zo snel mogelijk.

4. Overmatig queryen bij scrollen

Anti-pattern: Queries uitvoeren bij scrollevenementen. Bijvoorbeeld bij het weergeven van gegevens in een grid of tabel, een afzonderlijke query uitvoeren VOOR ELK record, of als je het klassieke voorbeeld van master-detail scrollen in 2 grids zonder vertraging gebruikt.

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;

Waarom is dit slecht?

• Het uitvoeren van een afzonderlijke query VOOR ELK record in een dynamische grid dwingt Firebird om duizenden kleine queries te verwerken, wat onnodig CPU-bronnen verbruikt

• Vanuit het oogpunt van Firebird:

• Veel (duizenden per seconde) kleine queries veroorzaken aanzienlijke CPU-belasting, omdat zelfs als een query 0ms in de statistieken toont, deze moet worden voorbereid, uitgevoerd, het resultaat moet worden overgedragen, enz.

Oplossingen:

1. Laad meerdere rijen tegelijk met batchbewerkingen

2. Breid de hoofdquery voor de grid uit om de detailquery als onderdeel daarvan uit te voeren

3. Voeg een expliciete knop toe om details te laden voor het zichtbare deel van de grid

4. Voeg een vertraging toe aan het uitvoeren van de query om details op te halen, om onmiddellijke queries tijdens het scrollen te voorkomen

5. Schakel het laden van details bij scrollen niet standaard in voor alle gebruikers

5. Onnodige automatische verversingen

Anti-pattern: Gridgegevens automatisch verversen met minimale tussenpozen in elke clientapplicatie, waarbij deze functie standaard is ingeschakeld.

Waarom is dit slecht?

• Dit resulteert in honderden clientverbindingen die bijna identieke queries uitvoeren om dezelfde records op te halen

• Waar dit gebeurt: automatische verversingen voor roosters, of selecteren voor wachtrijposities, of zoeken naar de “dichtstbijzijnde tijdslot”, enz.

• Vanuit het oogpunt van Firebird:

• Combinatie van het laden van dashboards en on-scroll-gebeurtenissen: veel middelgrote queries belasten het systeem

Oplossingen:

1. Vergroot het interval!

2. Implementeer expliciete (door de gebruiker geactiveerde) verversingen

3. Gebruik selectieve verversingen van de dataset op basis van daadwerkelijke gegevenswijzigingen (streaming of triggers of event+streaming)

6. Frequente recordupdates

Anti-pattern: Hetzelfde record frequent bijwerken in verschillende transacties, waardoor talrijke recordversies ontstaan.

Waarom is dit slecht?

• Een record met tientallen versies kan de prestaties aanzienlijk verslechteren; een record met duizenden kan een blokkade worden

• Vanuit het oogpunt van Firebird: de keten van recordversies moet worden gereconstrueerd om de juiste versie voor de specifieke transactie te identificeren; dit vereist talrijke leesbewerkingen, waardoor garbage collection aanzienlijk langzamer wordt.

Oplossingen:

1. Migreer naar Firebird 4+, daar is intermediate garbage collection

2. Houd geen langdurige schrijfbare transacties aan; voer de juiste garbage collection uit

3. Overweeg voor Firebird <4 om DELETE+INSERT te gebruiken in plaats van UPDATE

7. Schrijftransacties gebruiken voor alleen-lezen selects

Anti-pattern: Schrijftransacties gebruiken voor alleen-lezen selects leidt tot overmatige bewerkingen.

Waarom is dit slecht?

• Schrijftransacties gebruiken voor alleen-lezen selects leidt tot veel onnodige schrijfacties van headerpagina’s

• Schrijftransacties gebruiken voor alleen-lezen bewerkingen is inefficiënt (grote TIP tijdens commit veroorzaakt extra belasting op de server)

Oplossingen:

• Gebruik een afzonderlijke alleen-lezen transactie voor bewerkingen die gegevens niet wijzigen

• Firebird is een van de weinige databases die het mogelijk maakt om meerdere transacties te openen binnen één verbinding

• Global Temporary-tabellen zijn beschikbaar voor gebruik in alleen-lezen transacties

8. Gebruik LIKE :param

De volgende query met parameter zal geen index gebruiken voor fieldName (zelfs als de index bestaat):

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Waarom is dit slecht?

Omdat LIKE zoeken met jokertekens (%) toestaat, die een willekeurig aantal symbolen kan vervangen, kan Firebird niet vooraf bepalen of de parameterwaarde geschikt zal zijn voor een indexzoekopdracht.

Meestal proberen ontwikkelaars dit te omzeilen door de parameterwaarde in de querytekst in te sluiten:

• fieldName LIKE «Alex%» - index kan worden gebruikt

• fieldName LIKE «%Alex» - standaardindex kan niet worden gebruikt

• fieldName LIKE «%Alex%» - index kan helemaal niet worden gebruikt

Dit leidt tot andere problemen (zie #10 hieronder).

Oplossingen:

1. Gebruik STARTING WITH voor bekende tekenreeksprefixen

Wanneer je zoekwaarde nooit begint met een jokerteken %, geef dan de voorkeur aan STARTING WITH boven LIKE:

WHERE fieldName STARTING WITH ?param1

2. Optimaliseer bidirectionele tekenreekszoekopdrachten

Gebruik voor tekenreeksen met bekende prefix- of suffixpatronen een omgekeerde 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. Implementeer een progressieve zoekstrategie

Voor tekenreeksen die aan het begin/einde/midden voorkomen (maar niet tegelijkertijd):

• Probeer eerst een snelle geïndexeerde zoekopdracht met STARTING WITH

• Als er geen resultaten worden gevonden, val dan terug op een langzamere LIKE-zoekopdracht

4. Optimalisatie van zoeken op basis van woorden

Bij het zoeken naar volledige woorden (gescheiden door spaties, komma’s, enz.):

• Maak een afzonderlijke woord-ID-mappingtabel

• Zoek via de mappingtabel in plaats van de oorspronkelijke tekst

5. Voor uitgebreide full-text zoekmogelijkheden:

• Overweeg het gebruik van IBSurgeon Full Text Search UDR

• Deze open-sourceoplossing biedt geavanceerde tekstzoekfunctionaliteit

9. Transacties niet sluiten voor alleen-lezen bewerkingen

Waarom is dit slecht?

• Transacties langdurig open houden kan Firebird dwingen talrijke back-versies te onderhouden voor mogelijke snapshot-transacties

Oplossingen:

• Gebruik alleen-lezen transacties waar mogelijk, en sluit schrijfbare transacties zo snel mogelijk

• Gebruik moderne Firebird-versies (4+) om de impact van ketens van recordversies te verminderen

• Implementeer een correcte sweep

10. Problemen met queryparametrisering

Anti-pattern: Voorbereide queries en parametrisering vermijden, en in plaats daarvan parameterwaarden rechtstreeks in de querytekst insluiten.

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

Waarom is dit slecht?

• Deze praktijk vermindert de prestaties voor herhaalde queries

• Elke query met ingesloten parameterwaarden moet als nieuw worden voorbereid

• Voorbereiding kan lang duren en tijdrovend zijn voor grote tabellen

• Het bemoeilijkt probleemanalyse

• Het is moeilijk om queries op tekst te groeperen

• Het creëert SQL-injectiekwetsbaarheden

Oplossingen:

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

11. Verkeerde integriteitscontrole: triggers/CHECKs in plaats van Primary Key

Anti-pattern: Triggers of CHECK gebruiken in plaats van Primary Keys voor integriteitscontroles van de database.

Waarom is dit slecht?

• Dit negeert het feit dat Primary Key-validatie de speciale modus gebruikt om de huidige versie van het record te lezen, ongeacht het isolatieniveau van de transactie van de gebruiker.

• PK-controles met triggers in gebruikerstransacties vergroten de kans op duplicatie en compliceren de logica onnodig

Oplossingen:

• Gebruik primary keys

• Vermijd overbodige integriteitscontroles

• Houd databaselogica eenvoudig

12. ID-generatie met MAX()

Anti-pattern: MAX(id)+1 gebruiken voor nieuwe identifiers is onbetrouwbaar en inefficiënt.

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

Waarom is dit slecht?

• MAX(id)+1 gebruiken in plaats van sequences (generators) voor nieuwe identifiers

• MAX(id)+1 garandeert geen uniciteit met gangbare transactieparameters - twee parallelle transacties kunnen dezelfde MAX()-waarde ontvangen

• De combinatie van Max()+1 en CHECK(select if unique) werkt ook niet!

Oplossingen:

-- 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. Inefficiënt gebruik van GUID’s

Waarom is dit slecht?

• Het gebruik van door het systeem gegenereerde GUID’s in plaats van gen_uuid() kan de indexprestaties beïnvloeden

• Door het systeem gegenereerde GUID’s zijn sterk gerandomiseerd

Oplossingen:

• Gebruik de functie gen_uuid()

• Overweeg in plaats daarvan BIGINT te gebruiken

• In versie 6 komt UUID v7

14. Inefficiënte berekende velden

Anti-patroon: Het gebruik van berekende velden met SELECT’s naar andere tabellen vermindert de prestaties van eenvoudige SELECT-bewerkingen aanzienlijk.

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

Waarom is dit slecht?

• Berekende velden worden ter plekke berekend, ze zijn niet bedoeld om complexe logica te implementeren en kunnen optimalisatie-inspanningen aanzienlijk bemoeilijken

• Het versterkt de relaties tussen tabellen

• Het is alleen zinvol om berekende velden te gebruiken voor lichtgewicht berekeningen met velden van de tabel, zoals samenvoeging

Oplossingen:

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. Fouten onderdrukken zonder logging

Anti-patroon: Onderdruk Firebird-fouten en -waarschuwingen niet zonder logging!

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

Waarom is dit slecht?

• Het verbergen van fouten verhindert een juiste diagnose en debugging. Een goede foutregistratie is cruciaal om problemen snel te begrijpen en op te lossen.

Oplossingen:

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;

Contactinformatie

• Stuur uw vragen naar [email protected]

• Word Firebird Supporter (vanaf EUR10/maand) en neem deel aan besloten geavanceerde webinars!

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