15 antipatternů Firebird

od Alexey Kovyazin, 14. ledna 2025

Úvod

Tento dokument popisuje 15 běžných anti-vzorů při práci s databázemi Firebird a nabízí řešení pro každý z nich.

1. Mnoho paralelních dotazů na MON$

Anti-vzor: Velmi oblíbená chyba – trigger OnConnect, dotaz na MON$ATTACHMENTS pro výběr údajů o uživateli pro účely auditu nebo výpočet počtu připojení pro účely licencování.

Proč je to špatné?

• Tabulky MON$ jsou virtuální tabulky, které jsou uloženy v systémových souborech fbNN_mon_xx, obsahují statistiky výkonu atd.

• Soubor >1 Gb znamená, že je používáte příliš mnoho

• Jsou navrženy pouze pro použití systémovými administrátory – tj. 1–2 paralelní dotazy, výhradně pro administrátory

• 200+ připojení s paralelními dotazy na MON$ velmi výrazně zpomalí Firebird a 500+ simultánních dotazů Firebird „zavěsí“ s vysokou pravděpodobností

Řešení:

• Nepoužívejte MON$ pro neadministrativní úlohy, tj. pro počítání nebo audit, vyhněte se jejich použití v OnConnect

• Pro účely auditu:

• Používejte kontextové proměnné jako CURRENT_USER, CURRENT_TIMESTAMP atd.

• Používejte Audit – nativní funkci Firebird, mnohem výkonnější než triggery

• Pro účely licencování – používejte kontextové proměnné uživatele

2. Pomalé načítání dashboardu

Anti-vzor: Načítání rozsáhlých dashboardů nebo přehledů, které sčítají všechny objednávky a faktury za poslední měsíc nebo rok při spuštění aplikace, nebo aktualizace některých metrik každou minutu nebo častěji.

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

Proč je to špatné?

• Uživatelé musí čekat několik sekund, než uvidí celofiremní statistiky, než mohou začít se svou skutečnou prací

• Z pohledu Firebird – aby bylo možné neustále spouštět mnoho paralelních dotazů, načítat obrovská množství dat, třídit/ seskupovat je, bude Firebird intenzivně využívat více jader CPU, číst z disku, cache, paměti vyhrazené pro třídění (a někdy třídění probíhá na disku)

• Je to jako sestavovat report několikrát za minutu!

Řešení:

1. Snižte počet uživatelů, kteří uvidí dashboardy:

• Dashboard obvykle vyžadují pouze analytici a vedení, vylučte jej z obecného zatížení aplikace

• Načítání dashboardu při spuštění/pro některý formulář udělejte volitelným, ve výchozím nastavení vypnutým

• Načítejte data dashboardu explicitním kliknutím na tlačítko, ne při spuštění (tj. udělejte z něj report)

2. Vypočítejte data dashboardu jedním procesem podle plánu (tj. robotem) a uložte je do jednoduché tabulky připravené k načtení jednoduchým dotazem

3. Použijte triggery k agregaci dat a uložte je připravené k použití

4. Použijte replikační databázi pro výpočet dat dashboardu (a také všech náročných reportů)

3. Načítání nepotřebných záznamů

Anti-vzor: Načítání všech dat bez filtrování do gridu při otevření aplikace nebo formuláře, bez ohledu na to, zda obsahuje stovky tisíc záznamů.

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

Proč je to špatné?

• Přestože grid zobrazuje pouze 50 záznamů, uživatelé musí procházet tisíce záznamů místo použití funkce vyhledávání

• V 99 % případů uživatelé potřebují velmi úzkou podmnožinu dat: například nejnovější záznamy prodeje

• Z pohledu Firebird:

• Každé otevření vyžaduje čtení, ukládání do cache a přenos tisíců záznamů po síti

• Pokud ponecháte dataset otevřený (v Delphi), Firebird drží buffery, setříděné záznamy v dočasném prostoru (pokud ORDER BY, GROUP BY atd.) až do zavření datasetu

Řešení:

1. Omezte počet záznamů pomocí FIRST/SKIP/ROWS

2. Omezte počet záznamů nějakým kritériem, například zobrazte záznamy vytvořené/změněné za poslední 3 dny

3. Obecně zavírejte dotazy co nejdříve.

4. Nadměrné dotazování při scrollování

Anti-vzor: Spouštění dotazů při událostech scrollování. Například při zobrazování dat v gridu nebo tabulce provádění samostatného dotazu PRO KAŽDÝ záznam, nebo pokud používáte klasický příklad scrollování master-detail ve 2 gridech bez zpoždění.

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;

Proč je to špatné?

• Provádění samostatného dotazu PRO KAŽDÝ záznam v dynamickém gridu nutí Firebird zpracovávat tisíce drobných dotazů, což zbytečně spotřebovává prostředky CPU

• Z pohledu Firebird:

• Mnoho (tisíce za sekundu) malých dotazů vytvoří významné zatížení CPU, protože i když dotaz ve statistikách ukazuje 0 ms, vyžaduje přípravu, provedení, přenos výsledku atd.

Řešení:

1. Načítejte více řádků najednou pomocí dávkových operací

2. Rozšiřte hlavní dotaz pro grid tak, aby prováděl detailní dotaz jako svou součást

3. Přidejte explicitní tlačítko pro načtení detailů pro viditelnou část gridu

4. Přidejte zpoždění pro provedení dotazu k získání detailů, abyste zabránili okamžitým dotazům během scrollování

5. Nepovolujte načítání detailů při scrollování pro všechny uživatele ve výchozím nastavení

5. Zbytečné automatické obnovy

Anti-vzor: Automatická obnova dat gridu v minimálních intervalech v každé klientské aplikaci, přičemž tato funkce je ve výchozím nastavení povolena.

Proč je to špatné?

• Výsledkem jsou stovky klientských připojení spouštějících téměř identické dotazy k načtení stejných záznamů

• Kde k tomu dochází: automatické obnovy rozvrhů, výběr pozic ve frontě, hledání „nejbližšího slotu“ atd.

• Z pohledu Firebird:

• Kombinace načítání dashboardů a událostí scrollování: mnoho středně velkých dotazů vytváří zatížení systému

Řešení:

1. Zvětšete interval!

2. Implementujte explicitní (uživatelem spuštěné) obnovy

3. Používejte selektivní obnovy datové sady založené na skutečných změnách dat (streaming nebo triggery nebo event+streaming)

6. Časté aktualizace záznamů

Anti-vzor: Častá aktualizace stejného záznamu v různých transakcích, což vytváří mnoho verzí záznamu.

Proč je to špatné?

• Záznam s desítkami verzí může výrazně zhoršit výkon, záznam s tisíci verzemi se může stát blokátorem

• Z pohledu Firebird: řetězec verzí záznamu musí být rekonstruován, aby se identifikovala správná verze pro konkrétní transakci, což vyžaduje mnoho čtecích operací, v důsledku čehož je garbage collection výrazně pomalejší.

Řešení:

1. Přejděte na Firebird 4+, kde je průběžná garbage collection

2. Neudržujte dlouho běžící zapisovatelné transakce, provádějte řádnou garbage collection

3. Pro Firebird <4 zvažte použití DELETE+INSERT místo UPDATE

7. Používání zapisovatelných transakcí pro read-only selecty

Anti-vzor: Používání zapisovatelných transakcí pro read-only selecty vede k nadměrným operacím.

Proč je to špatné?

• Používání zapisovatelných transakcí pro read-only selecty vede k mnoha zbytečným zápisům hlavičkových stránek

• Používání zapisovatelných transakcí pro read-only operace je neefektivní (velký TIP při commitu vytváří dodatečné zatížení serveru)

Řešení:

• Pro operace, které nemění data, používejte samostatnou read-only transakci

• Firebird je jednou z mála databází, které umožňují otevřít několik transakcí v rámci jednoho připojení

• Globální dočasné tabulky (Global Temporary tables) jsou dostupné pro použití v read-only transakcích

8. Použití LIKE :param

Následující dotaz s parametrem nepoužije index pro fieldName (i když index existuje):

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Proč je to špatné?

Protože LIKE umožňuje vyhledávání se zástupnými znaky (%), které mohou nahradit libovolný počet symbolů, Firebird nemůže předem určit, zda bude hodnota parametru vhodná pro indexové vyhledávání.

Vývojáři se obvykle snaží toto obejít vložením hodnoty parametru do textu dotazu:

• fieldName LIKE «Alex%» – možné použít index

• fieldName LIKE «%Alex» – není možné použít standardní index

• fieldName LIKE «%Alex%» – není možné použít index vůbec

To vede k dalším problémům (viz #10 níže).

Řešení:

1. Používejte STARTING WITH pro známé prefixy řetězců

Pokud vaše hledaná hodnota nikdy nezačíná zástupným znakem %, upřednostněte STARTING WITH před LIKE:

WHERE fieldName STARTING WITH ?param1

2. Optimalizujte obousměrné vyhledávání v řetězcích

Pro řetězce se známými vzory prefixu nebo sufixu použijte reverzní 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. Implementujte progresivní strategii vyhledávání

Pro řetězce, které se vyskytují na začátku/konci/uprostřed (ale ne současně):

• Nejprve zkuste rychlé indexované vyhledávání pomocí STARTING WITH

• Pokud nejsou nalezeny žádné výsledky, přejděte na pomalejší vyhledávání LIKE

4. Optimalizace vyhledávání na základě slov

Při hledání celých slov (oddělených mezerami, čárkami atd.):

• Vytvořte samostatnou mapovací tabulku word-ID

• Vyhledávejte prostřednictvím mapovací tabulky místo původního textu

5. Pro komplexní možnosti full-textového vyhledávání:

• Zvažte použití IBSurgeon Full Text Search UDR

• Toto open-source řešení poskytuje pokročilou funkcionalitu textového vyhledávání

9. Nezavírání transakcí pro read-only operace

Proč je to špatné?

• Ponechávání transakcí otevřených po delší dobu může nutit Firebird udržovat mnoho back verzí pro potenciální snapshot transakce

Řešení:

• Kde je to možné, používejte read-only transakce a zapisovatelné transakce zavírejte co nejdříve

• Používejte moderní verze Firebird (4+) ke snížení dopadu řetězců verzí záznamů

• Implementujte řádný sweep

10. Problémy s parametrizací dotazů

Anti-vzor: Vyhýbání se připraveným dotazům a parametrizaci, místo toho vkládání hodnot parametrů přímo do textu dotazu.

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

Proč je to špatné?

• Tato praxe snižuje výkon u opakovaných dotazů

• Každý dotaz s vloženými hodnotami parametrů musí být připraven jako nový

• Příprava může být u velkých tabulek dlouhá a časově náročná

• Komplikuje analýzu problémů

• Je obtížné seskupovat dotazy podle textu

• Vytváří zranitelnosti SQL injection

Řešení:

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

11. Špatná kontrola integrity: triggery/CHECKy místo primárního klíče

Anti-vzor: Používání triggerů nebo CHECK místo primárních klíčů pro kontrolu integrity databáze.

Proč je to špatné?

• To ignoruje skutečnost, že validace primárního klíče používá speciální režim pro čtení aktuální verze záznamu bez ohledu na úroveň izolace transakce uživatele.

• Provádění kontrol PK pomocí triggerů v uživatelských transakcích zvyšuje možnost duplikace a zbytečně komplikuje logiku

Řešení:

• Používejte primární klíče

• Vyhněte se redundantním kontrolám integrity

• Udržujte databázovou logiku jednoduchou

12. Generování ID pomocí MAX()

Anti-vzor: Používání MAX(id)+1 pro nové identifikátory je nespolehlivé a neefektivní.

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

Proč je to špatné?

• Používání MAX(id)+1 místo sekvencí (generátorů) pro nové identifikátory

• MAX(id)+1 nezaručuje jedinečnost při běžných parametrech transakcí – dvě paralelní transakce by mohly obdržet stejnou hodnotu MAX()

• Kombinace Max()+1 a CHECK(select if unique) také nefunguje!

Řešení:

-- 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. Neefektivní používání GUID

Proč je to špatné?

• Používání systémem generovaných GUID namísto gen_uuid() může ovlivnit výkon indexů

• Systémem generovaný GUID je vysoce náhodný

Řešení:

• Používejte funkci gen_uuid()

• Zvažte použití BIGINT

• Ve verzi 6 bude UUID v7

14. Neefektivní vypočítávaná pole

Anti-pattern: Používání vypočítávaných polí s SELECTy do jiných tabulek výrazně snižuje výkon jednoduchých operací SELECT.

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

Proč je to špatné?

• Vypočítávaná pole se počítají za běhu, neměla by implementovat složitou logiku a mohou výrazně zkomplikovat optimalizaci

• Posiluje vztahy mezi tabulkami

• Smysl má používat vypočítávaná pole pouze pro nenáročné výpočty s poli dané tabulky, jako je například zřetězení

Řešení:

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. Potlačování chyb bez logování

Anti-pattern: Nepotlačujte chyby a varování Firebird bez logování!

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

Proč je to špatné?

• Skrývání chyb znemožňuje správnou diagnostiku a ladění. Řádné logování chyb je klíčové pro rychlé pochopení a řešení problémů.

Řešení:

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;

Kontaktní informace

• Své dotazy zasílejte na [email protected]

• Staňte se Firebird Supporter (od 10 EUR/měsíc) a zúčastněte se uzavřených pokročilých webinářů!

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