15 antipatternů Firebirdu

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 poskytuje řešení pro každý z nich.

1. Více paralelních dotazů na MON$

Anti-vzor: Velmi častá 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 licenční účely.

Proč je to špatné?

• Tabulky MON$ jsou virtuální tabulky, které jsou uloženy v systémových souborech fbNN_mon_xx, s výkonnostními statistikami atd.

• Soubor >1Gb znamená, že jej používáte příliš často

• Jsou určeny pouze pro použití správci systému - tj. 1-2 paralelní dotazy, výhradně pro administrátory

• 200+ připojení s paralelními dotazy na MON$ výrazně zpomalí Firebird a 500+ souběžný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 Firebirdu, mnohem výkonnější než triggery

• Pro licenční účely - používejte kontextové proměnné uživatele

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

Anti-vzor: Načítání komplexní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í celopodnikové statistiky, než mohou začít svou skutečnou práci

• Z pohledu Firebirdu - aby mohl neustále spouštět mnoho paralelních dotazů, získávat obrovské množství dat, třídit/seskupovat je, Firebird bude intenzivně využívat více jader CPU, čtení z disku, mezipaměti, paměti určené pro třídění (a někdy třídění jde na disk)

• Je to jako vytvářet report několikrát za minutu!

Řešení:

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

• Obvykle je Dashboard vyžadován pouze pro analytiky a management, vyřaďte jej z obecného načítání aplikace

• Načítání dashboardu při spuštění/pro určitý formulář udělejte volitelné, ve výchozím stavu zakázané

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

2. Počítejte data dashboardu 1 procesem podle plánu (tj. robot) a ukládejte je do jednoduché tabulky připravené k načtení jednoduchým dotazem

3. Používejte triggery pro agregaci dat a jejich ukládání připravené k použití

4. Používejte repliku databáze pro výpočet dat dashboardu (a všech těžkých reportů také)

3. Načítání zbytečných záznamů

Anti-vzor: Načítání všech dat bez filtrování do mřížky 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;
 // Načte celou tabulku do paměti
 DBGrid1.DataSource.DataSet := FDQuery1;
end;

Proč je to špatné?

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

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

• Z pohledu Firebirdu:

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

• Pokud necháte dataset otevřený (v Delphi), Firebird drží buffery, setříděné záznamy v dočasném prostoru (pokud je 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é během posledních 3 dnů

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

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

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

procedure TForm1.GridScrolled(Sender: TObject);
begin
 // dotaz pro každý řádek
 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řížce nutí Firebird zpracovávat tisíce malých dotazů, zbytečně zatěžuje CPU

• Z pohledu Firebirdu:

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

Řešení:

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

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

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

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

5. Nezapínejte načítání detailů při rolování pro všechny uživatele ve výchozím stavu

5. Zbytečné automatické obnovování

Anti-vzor: Automatické obnovování dat mřížky v minimálních intervalech v každé klientské aplikaci, s touto funkcí zapnutou ve výchozím stavu.

Proč je to špatné?

• Výsledkem jsou stovky klientských připojení spouštějících téměř identické dotazy pro získání stejných záznamů

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

• Z pohledu Firebirdu:

• Kombinace načítání dashboardů a událostí rolování: mnoho středně velkých dotazů vytváří zátěž na systém

Řešení:

1. Zvyšte interval!

2. Implementujte explicitní (uživatelem spouštěné) obnovování

3. Používejte selektivní obnovování datové sady na základě skutečných změn dat (streaming nebo triggery nebo událost+streaming)

6. Časté aktualizace záznamů

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

Proč je to špatné?

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

• Z pohledu Firebirdu: řetězec verzí záznamů musí být rekonstruován pro identifikaci správné verze pro konkrétní transakci, vyžaduje mnoho čtecích operací, a v důsledku toho je garbage collection výrazně pomalejší.

Řešení:

1. Migrujte na Firebird 4+, kde je průběžná garbage collection

2. Nedržte 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žití zapisovatelných transakcí pro read-only selecty

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

Proč je to špatné?

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

• Použití zapisovatelných transakcí pro read-only operace je neefektivní (velký TIP při commitu vytváří další zátěž na server)

Řešení:

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

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

• Globální dočasné tabulky jsou k dispozici pro použití v read-only transakcích

8. Použití LIKE :param

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

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Proč je to špatné?

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

Obvykle se vývojáři snaží obejít to 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žijte STARTING WITH pro známé předpony řetězců

Když vaše vyhledávací hodnota nikdy nezačíná zástupným znakem %, preferujte STARTING WITH před LIKE:

WHERE fieldName STARTING WITH ?param1

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

Pro řetězce se známými vzory předpon nebo přípon použijte reverzní index:

-- Vytvoření reverzního indexu
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- Dotaz používající oba směry
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í s STARTING WITH

• Pokud nejsou nalezeny žádné výsledky, použijte 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 tabulku mapování slov na ID

• Vyhledávejte přes mapovací tabulku místo původního textu

5. Pro komplexní full-textové vyhledávání:

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

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

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

Proč je to špatné?

• Dlouho otevřené transakce mohou donutit Firebird udržovat mnoho zpětných verzí pro potenciální snapshot transakce

Řešení:

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

• Používejte moderní verze Firebirdu (4+) pro 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 pro opakované dotazy

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

• Příprava může být dlouhá a časově náročná pro velké tabulky

• 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/CHECK místo primárního klíče

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

Proč je to špatné?

• Ignoruje to, ž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 transakcí uživatele.

• Provádění kontrol PK pomocí triggerů v uživatelských transakcích zvyšuje možnost duplicit 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žití 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žití 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 získat stejnou hodnotu MAX()

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

Řešení:

-- Použijte generátor/sekvenci!
CREATE GENERATOR gen_user_id;
-- Použijte generátor pro generování ID
INSERT INTO users (id, name)
VALUES (
 GEN_ID(gen_user_id, 1),
 'John Doe' );

## 13. Neefektivní použití GUID

**Proč je to špatně?**

- Použití systémem generovaných GUID místo gen\_uuid() může ovlivnit výkon indexů

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


**Řešení:**

- Použijte funkci gen\_uuid()

- Zvažte použití BIGINT

- Ve verzi 6 bude k dispozici UUID v7


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

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

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

Proč je to špatně?

• Vypočítávaná pole se počítají za běhu a nejsou určena k implementaci složité logiky, což může výrazně zkomplikovat optimalizační úsilí

• Posiluje vazby mezi tabulkami

• Použití vypočítávaných polí dává smysl pouze pro nenáročné výpočty s poli tabulky, jako je 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čení chyb bez logování

Anti-vzor: Nepotlačujte chyby a varování Firebirdu bez logování!

try
 FDQuery1.Open;
except
 // Tiché selhání
end;

Proč je to špatně?

• Skrývání chyb znemožňuje správnou diagnostiku a ladění. Správné 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
 // Komplexní logování
 Logger.Error('Připojení k databázi selhalo: ' + E.Message);
 ShowMessage('Nelze se připojit k databázi. Kontaktujte prosím podporu.');
 // Logování dalšího kontextu
 Logger.LogStackTrace(E);
 end;
end;

Kontaktní informace

• Pošlete své dotazy na [email protected]

• Staňte se podporovatelem Firebirdu (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/