15 Firebird-antipatronen

door Alexey Kovyazin, 14-jan-2025

Inleiding

Dit document beschrijft 15 veelvoorkomende anti-patronen bij het werken met Firebird-databases en biedt voor elk daarvan oplossingen.

1. Meerdere parallelle queries naar MON$

Anti-patroon: Een zeer veelgemaakte fout - trigger OnConnect, query naar MON$ATTACHMENTS om gebruikersgegevens te selecteren voor auditdoeleinden, of het aantal verbindingen berekenen 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 u het te veel gebruikt
Ze zijn uitsluitend bedoeld voor gebruik door systeembeheerders - d.w.z. 1-2 parallelle queries, uitsluitend voor beheerders
200+ verbindingen met parallelle queries naar MON$ zullen Firebird aanzienlijk vertragen, en 500+ gelijktijdige queries zullen Firebird met grote kans “laten hangen”

Oplossingen:

Gebruik MON$ niet voor niet-administratieve taken, d.w.z. om te tellen of te auditen, vermijd 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. Langzaam laden van dashboards

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

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

Waarom is dit slecht?

Gebruikers moeten enkele seconden wachten om bedrijfsbrede statistieken te zien voordat ze aan hun daadwerkelijke werk kunnen beginnen
Vanuit Firebird-oogpunt - om voortdurend veel parallelle queries uit te voeren, grote hoeveelheden gegevens op te halen, te sorteren/groeperen, zal Firebird intensief meerdere CPU-kernen gebruiken, lezen van schijf, cache, geheugen dat is toegewezen voor sorteren (en soms gaat sorteren naar schijf)
Het is alsof u meerdere keren per minuut een rapport opbouwt!

Oplossingen:

Verminder het aantal gebruikers dat dashboards zal zien:

Meestal is een Dashboard alleen nodig voor analisten en management, sluit het uit van de algemene applicatiebelasting
Maak het laden van het dashboard bij opstarten/voor een bepaald formulier optioneel, standaard uitgeschakeld
Laad dashboardgegevens door een expliciete knopklik, niet bij het opstarten (d.w.z. maak er een rapport van)

Bereken dashboardgegevens met 1 proces volgens schema (d.w.z. robot) en sla ze op in een eenvoudige tabel die klaar is om op te halen met een eenvoudige query
Gebruik triggers om gegevens te aggregeren en ze gebruiksklaar op te slaan
Gebruik een replicadatabase om dashboardgegevens te berekenen (en ook alle zware rapporten)

3. Laden van onnodige records

Anti-patroon: Het laden van alle gegevens zonder filter in het raster bij het openen van een applicatie of formulier, ongeacht of het honderdduizenden records bevat.

delphi

procedure TDataForm.LoadAllRecords;
begin
 FDQuery1.SQL.Text := 'SELECT * FROM large_table';
 FDQuery1.Open;
 // Laadt de hele tabel in het geheugen
 DBGrid1.DataSource.DataSet := FDQuery1;
end;

Waarom is dit slecht?

Ondanks dat het raster 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 beperkte subset van gegevens nodig: de meest recente verkooprecords, bijvoorbeeld
Vanuit Firebird-oogpunt:
Elke opening vereist het lezen, opslaan in cache en overdracht van duizenden records via het netwerk
Als u de dataset openhoudt (in Delphi), houdt Firebird buffers, gesorteerde records in tijdelijke ruimte (indien ORDER BY, GROUP BY, enz.) totdat de dataset wordt gesloten

Oplossingen:

Beperk het aantal records met FIRST/SKIP/ROWS
Beperk het aantal records met bepaalde criteria, bijvoorbeeld toon records die in de afgelopen 3 dagen zijn gemaakt/gewijzigd
Sluit queries in het algemeen zo snel mogelijk.

4. Overmatig queryen bij scrollen

Anti-patroon: Het uitvoeren van queries bij scrollgebeurtenissen. Bijvoorbeeld, bij het weergeven van gegevens in een raster of tabel, een afzonderlijke query uitvoeren VOOR ELK record, of als u het klassieke voorbeeld van master-detail scrollen in 2 rasters zonder vertraging gebruikt.

delphi

procedure TForm1.GridScrolled(Sender: TObject);
begin
 // query voor elke rij
 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 dynamisch raster dwingt Firebird om duizenden kleine queries te verwerken, waardoor onnodig CPU-bronnen worden verbruikt
Vanuit Firebird-oogpunt:
Veel (duizenden per seconde) kleine queries zullen aanzienlijke CPU-belasting veroorzaken, omdat zelfs als een query 0ms in statistieken toont, het vereist dat het wordt voorbereid, uitgevoerd, resultaat wordt overgedragen, enz.

Oplossingen:

Laad meerdere rijen tegelijk met behulp van batchbewerkingen
Verbeter de hoofdquery voor het raster om de gedetailleerde query als onderdeel ervan uit te voeren
Voeg een expliciete knop toe om details voor het zichtbare deel van het raster te laden
Voeg een vertraging toe om de query voor details uit te voeren, om onmiddellijke queries tijdens het scrollen te voorkomen
Schakel het laden van details bij scrollen niet standaard in voor alle gebruikers

5. Onnodige automatische verversingen

Anti-patroon: Het automatisch verversen van rastergegevens met minimale intervallen in elke clientapplicatie, met deze functie standaard ingeschakeld.

Waarom is dit slecht?

Dit resulteert in honderden clientverbindingen die bijna identieke queries uitvoeren om dezelfde records op te halen
Waar het gebeurt: automatische verversingen voor schema’s, of selectie voor wachtrijposities, of zoeken naar “dichtstbijzijnde slot”, enz.
Vanuit Firebird-oogpunt:
Combinatie van het laden van dashboards en scrollgebeurtenissen: veel middelgrote queries creëren belasting op het systeem

Oplossingen:

Verhoog het interval!
Implementeer expliciete (door de gebruiker geactiveerde) verversingen
Gebruik selectieve verversingen van de dataset op basis van daadwerkelijke gegevenswijzigingen (streaming of triggers of event+streaming)

6. Frequente recordupdates

Anti-patroon: Het frequent bijwerken van hetzelfde record in verschillende transacties, waardoor talloze recordversies ontstaan.

Waarom is dit slecht?

Een record met tientallen versies kan de prestaties aanzienlijk verslechteren, een record met duizenden kan een blokkering worden
Vanuit Firebird-oogpunt: 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:

Migreer naar Firebird 4+, daar is tussentijdse garbage collection
Houd geen langlopende schrijfbare transacties open, voer de juiste garbage collection uit
Voor Firebird <4, overweeg DELETE+INSERT te gebruiken in plaats van UPDATE

7. Schrijfbare transacties gebruiken voor alleen-lezen selects

Anti-patroon: Het gebruik van schrijfbare transacties voor alleen-lezen selects leidt tot overmatige bewerkingen.

Waarom is dit slecht?

Het gebruik van schrijfbare transacties voor alleen-lezen selects leidt tot veel onnodige schrijfbewerkingen van headerpagina’s
Het gebruik van schrijfbare transacties voor alleen-lezen bewerkingen is inefficiënt (grote TIP tijdens commit creëert extra belasting op de server)

Oplossingen:

Gebruik een afzonderlijke alleen-lezen transactie voor bewerkingen die geen gegevens wijzigen
Firebird is een van de weinige databases die het mogelijk maakt om meerdere transacties binnen één verbinding te openen
Globale tijdelijke tabellen zijn beschikbaar voor gebruik in alleen-lezen transacties

8. LIKE :param gebruiken

De volgende query met parameter zal geen index gebruiken voor het veldnaam (zelfs als er een index bestaat):

sql

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Waarom is dit slecht?

Aangezien LIKE wildcard-zoekopdrachten (%) toestaat, die een willekeurig aantal symbolen kunnen vervangen, kan Firebird niet van tevoren bepalen of de parameterwaarde geschikt zal zijn voor indexzoekopdracht.

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

fieldName LIKE «Alex%» - index mogelijk te gebruiken
fieldName LIKE «%Alex» - standaardindex niet mogelijk te gebruiken
fieldName LIKE «%Alex%» - index helemaal niet mogelijk te gebruiken

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

Oplossingen:

1. Gebruik STARTING WITH voor bekende stringvoorvoegsels

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

sql

WHERE fieldName STARTING WITH ?param1

2. Optimaliseer bidirectionele stringzoekopdrachten

Voor strings met bekende voorvoegsel- of achtervoegselpatronen, gebruik een omgekeerde index:

sql

-- Maak omgekeerde index
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- Query met beide richtingen
WHERE fieldName STARTING WITH :param1
   OR reverse(fieldName) STARTING WITH reverse(:param2)

3. Implementeer progressieve zoekstrategie

Voor strings die aan het begin/einde/midden verschijnen (maar niet tegelijkertijd):

Probeer eerst snelle geïndexeerde zoekopdracht met STARTING WITH
Als er geen resultaten worden gevonden, val dan terug op langzamere LIKE-zoekopdracht

4. Woordgebaseerde zoekoptimalisatie

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

Maak een aparte woord-ID-koppelingstabel
Zoek via de koppelingstabel in plaats van de originele tekst

5. Voor uitgebreide full-text zoekmogelijkheden:

Overweeg het gebruik van IBSurgeon Full Text Search UDR
Deze open-source oplossing biedt geavanceerde tekstzoekfunctionaliteit

9. Transacties niet sluiten voor alleen-lezen bewerkingen

Waarom is dit slecht?

Transacties lang openhouden kan Firebird dwingen om talloze 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 recordversieketens te verminderen
Implementeer een juiste sweep

10. Problemen met queryparameterisatie

Anti-patroon: Het vermijden van voorbereide queries en parameterisatie, in plaats daarvan parameterwaarden rechtstreeks in de querytekst inbedden.

delphi

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 ingebedde parameterwaarden moet als nieuw worden voorbereid
Voorbereiding kan lang en tijdrovend zijn voor grote tabellen
Bemoeilijkt probleemanalyse
Het is moeilijk om queries op tekst te groeperen
Creëert kwetsbaarheden voor SQL-injectie

Oplossingen:

delphi

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

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

Anti-patroon: Het gebruik van triggers of CHECK in plaats van Primary Keys voor database-integriteitscontroles.

Waarom is dit slecht?

Dit negeert dat Primary Key-validatie de speciale modus gebruikt om de huidige versie van het record te lezen, ongeacht het transactie-isolatieniveau van de gebruiker.
Het uitvoeren van PK-controles met triggers in gebruikers transacties vergroot de kans op duplicatie en bemoeilijkt de logica onnodig

Oplossingen:

Gebruik primary keys
Vermijd overbodige integriteitscontroles
Houd de databaselogica eenvoudig

12. ID-generatie met MAX()

Anti-patroon: Het gebruik van MAX(id)+1 voor nieuwe identificatoren is onbetrouwbaar en inefficiënt.

sql

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

Waarom is dit slecht?

Het gebruik van MAX(id)+1 in plaats van sequences (generators) voor nieuwe identificatoren
MAX(id)+1 garandeert geen uniciteit met gangbare transactieparameters - twee parallelle transacties kunnen dezelfde MAX()-waarde ontvangen
Combinatie van Max()+1 en CHECK(select if unique) werkt ook niet!

Oplossingen:

sql

-- Gebruik generator/sequence!
CREATE GENERATOR gen_user_id;
-- Gebruik generator voor ID-generatie
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 zeer willekeurig


**Oplossingen:**

- Gebruik de functie gen\_uuid()

- Overweeg het gebruik van BIGINT in plaats daarvan

- In versie 6 zal er UUID v7 zijn


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

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

Waarom is dit slecht?

Berekende velden worden ter plekke berekend en zijn niet bedoeld om complexe logica te implementeren, en kunnen optimalisatie-inspanningen aanzienlijk bemoeilijken
Het versterkt de relaties tussen tabellen
Het is zinvol om berekende velden alleen te gebruiken voor lichtgewicht berekeningen met velden van de tabel, zoals concatenatie

Oplossingen:

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. Foutonderdrukking zonder logboekregistratie

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

delphi

try
 FDQuery1.Open;
except
 // Stille fout
end;

Waarom is dit slecht?

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

Oplossingen:

delphi

try
 FDQuery1.Open;
except
 on E: Exception do
 begin
 // Uitgebreide logboekregistratie
 Logger.Error('Databaseverbinding mislukt: ' + E.Message);
 ShowMessage('Kan geen verbinding maken met de database. Neem contact op met de ondersteuning.');
 // Extra context loggen
 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/