15 Firebird antipatterna

Od strane Alekseja Kovina, 14. januara 2025.

Uvod

Ovaj dokument opisuje 15 uobičajenih anti-šablona pri radu sa Firebird bazama podataka i pruža rešenja za svaki od njih.

1. Višestruki paralelni upiti na MON$

Anti-šablon: Veoma popularna greška - okidač OnConnect, upit na MON$ATTACHMENTS za izbor korisničkih podataka u svrhe revizije, ili izračunavanje broja konekcija u svrhe licenciranja.

Zašto je loše?

• MON$ tabele su virtuelne tabele koje se čuvaju u fbNN_mon_xx sistemskim fajlovima, sa statistikama performansi, itd.

• Fajl >1Gb znači da ih previše koristite

• Dizajnirane su isključivo za upotrebu od strane sistemskih administratora - tj. 1-2 paralelna upita, isključivo za administratore

• 200+ konekcija sa paralelnim upitima na MON$ će značajno usporiti Firebird, a 500+ simultanih upita će “zaglaviti” Firebird sa velikom verovatnoćom

Rešenja:

• Ne koristite MON$ za neadministrativne zadatke, tj. za brojanje ili reviziju, izbegavajte njihovu upotrebu u OnConnect

• U svrhe revizije:

• Koristite kontekstualne promenljive kao što su CURRENT_USER, CURRENT_TIMESTAMP, itd

• Koristite Audit - nativnu Firebird funkcionalnost, mnogo moćniju od okidača

• U svrhe licenciranja - koristite korisničke kontekstualne promenljive

2. Sporo učitavanje kontrolne table

Anti-šablon: Učitavanje sveobuhvatnih kontrolnih tabli ili semafora koji sumiraju sve porudžbine i fakture za poslednji mesec ili godinu tokom pokretanja aplikacije, ili ažuriranje nekih metrika svakog minuta ili češće.

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

Zašto je loše?

• Korisnici moraju da čekaju nekoliko sekundi da vide statistiku cele kompanije pre nego što mogu da započnu svoj stvarni rad

• Sa stanovišta Firebird-a - da bi se stalno izvršavali mnogi paralelni upiti, preuzimala ogromna količina podataka, sortirala/grupisala, Firebird će intenzivno koristiti više CPU jezgara, čitajući sa diska, keša, memorije namenjene sortiranju (a ponekad sortiranje ide na disk)

• To je kao da gradite izveštaj nekoliko puta u minuti!

Rešenja:

1. Smanjite broj korisnika koji će videti kontrolne table:

• Obično je kontrolna tabla potrebna samo analitičarima i menadžmentu, isključite je iz opšteg opterećenja aplikacije

• Učinite učitavanje kontrolne table pri pokretanju/za neku formu opcionim, podrazumevano onemogućenim

• Učitajte podatke kontrolne table eksplicitnim klikom na dugme, ne pri pokretanju (tj. napravite je kao izveštaj)

2. Izračunajte podatke kontrolne table jednim procesom po rasporedu (tj. robot) i sačuvajte ih u jednostavnu tabelu spremnu za preuzimanje jednostavnim upitom

3. Koristite okidače za agregaciju podataka i čuvajte ih spremne za upotrebu

4. Koristite replika bazu podataka za izračunavanje podataka kontrolne table (i svih teških izveštaja takođe)

3. Učitavanje nepotrebnih zapisa

Anti-šablon: Učitavanje svih podataka bez filtriranja u mrežu pri otvaranju aplikacije ili forme, bez obzira na to da li sadrži stotine hiljada zapisa.

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

Zašto je loše?

• Iako mreža prikazuje samo 50 zapisa, korisnici moraju da skroluju kroz hiljade zapisa umesto da koriste funkcionalnost pretrage

• U 99% slučajeva korisnicima je potreban veoma uzak podskup podataka: na primer, najnoviji zapisi o prodaji

• Sa stanovišta Firebird-a:

• Svako otvaranje zahteva čitanje, čuvanje u kešu i prenos hiljada zapisa kroz mrežu

• Ako držite skup podataka otvorenim (u Delphi-ju), Firebird čuva baferе, sortirane zapise u privremenom prostoru (ako postoje ORDER BY, GROUP BY, itd) do zatvaranja skupa podataka

Rešenja:

1. Ograničite broj zapisa pomoću FIRST/SKIP/ROWS

2. Ograničite broj zapisa nekim kriterijumom, na primer, prikažite zapise kreirane/izmenjene tokom poslednja 3 dana

3. Uopšteno, zatvorite upite što je pre moguće.

4. Prekomerno upitovanje pri skrolovanju

Anti-šablon: Izvršavanje upita na događajima skrolovanja. Na primer, kada prikazujete podatke u mreži ili tabeli, izvršavate poseban upit ZA SVAKI zapis, ili ako koristite klasičan primer master-detail skrolovanja u 2 mreže bez odlaganja.

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;

Zašto je loše?

• Izvršavanje posebnog upita ZA SVAKI zapis u dinamičkoj mreži primorava Firebird da obrađuje hiljade sitnih upita, nepotrebno trošeći CPU resurse

• Sa stanovišta Firebird-a:

• Mnogi (hiljade u sekundi) mali upiti će stvoriti značajno CPU opterećenje, jer čak i ako upit pokazuje 0ms u statistici, potrebno ga je pripremiti, izvršiti, preneti rezultat, itd

Rešenja:

1. Učitajte više redova odjednom koristeći batch operacije

2. Poboljšajte glavni upit za mrežu da izvršava detaljni upit kao deo njega

3. Dodajte eksplicitno dugme za učitavanje detalja za vidljivi deo mreže

4. Dodajte odlaganje za izvršavanje upita za dobijanje detalja, da sprečite trenutne upite tokom skrolovanja

5. Ne omogućavajte učitavanje detalja pri skrolovanju za sve korisnike podrazumevano

5. Nepotrebna automatska osvežavanja

Anti-šablon: Automatsko osvežavanje podataka mreže u minimalnim intervalima u svakoj klijentskoj aplikaciji, sa ovom funkcijom omogućenom podrazumevano.

Zašto je loše?

• To rezultira stotinama klijentskih konekcija koje izvršavaju skoro identične upite za preuzimanje istih zapisa

• Gde se to dešava: automatska osvežavanja za rasporede, ili biranje za pozicije u redu, ili pretraga za “najbližim terminom”, itd

• Sa stanovišta Firebird-a:

• Kombinacija učitavanja kontrolnih tabli i događaja skrolovanja: mnogi upiti srednje veličine stvaraju opterećenje na sistem

Rešenja:

1. Povećajte interval!

2. Implementirajte eksplicitna (korisnički pokrenuta) osvežavanja

3. Koristite selektivna osvežavanja skupa podataka zasnovana na stvarnim promenama podataka (streaming ili okidači ili događaj+streaming)

6. Česta ažuriranja zapisa

Anti-šablon: Često ažuriranje istog zapisa u različitim transakcijama, stvarajući brojne verzije zapisa.

Zašto je loše?

• Zapis sa desetinama verzija može značajno degradirati performanse, zapis sa hiljadama može postati blokator

• Sa stanovišta Firebird-a: lanac verzija zapisa treba rekonstruisati da bi se identifikovala odgovarajuća verzija za specifičnu transakciju, to zahteva brojne operacije čitanja, kao rezultat, sakupljanje smeća postaje značajno sporije.

Rešenja:

1. Migrirajte na Firebird 4+, postoji intermedijarno sakupljanje smeća

2. Ne držite dugotrajne transakcije za pisanje, radite pravilno sakupljanje smeća

3. Za Firebird <4, razmotrite korišćenje DELETE+INSERT umesto UPDATE

7. Korišćenje transakcija za pisanje za selekte samo za čitanje

Anti-šablon: Korišćenje transakcija za pisanje za selekte samo za čitanje dovodi do prekomernih operacija.

Zašto je loše?

• Korišćenje transakcija za pisanje za selekte samo za čitanje dovodi do mnogih nepotrebnih upisa zaglavlja stranica

• Korišćenje transakcija za pisanje za operacije samo za čitanje je neefikasno (veliki TIP tokom commit-a stvara dodatno opterećenje na server)

Rešenja:

• Koristite odvojenu transakciju samo za čitanje za operacije koje ne menjaju podatke

• Firebird je jedna od retkih baza podataka koja dozvoljava otvaranje nekoliko transakcija u okviru jedne konekcije

• Globalne privremene tabele su dostupne za korišćenje u transakcijama samo za čitanje

8. Korišćenje LIKE :param

Sledeći upit sa parametrom neće koristiti indeks za fieldName (čak i ako indeks postoji):

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Zašto je loše?

Pošto LIKE dozvoljava pretragu sa džokerima (%), koji mogu zameniti bilo koji broj simbola, Firebird ne može unapred da utvrdi da li će vrednost parametra biti pogodna za pretragu indeksa.

Obično programeri pokušavaju da to zaobiđu ugrađivanjem vrednosti parametra u tekst upita:

• fieldName LIKE «Alex%» - moguće je koristiti indeks

• fieldName LIKE «%Alex» - nije moguće koristiti standardni indeks

• fieldName LIKE «%Alex%» - nije moguće uopšte koristiti indeks

To dovodi do drugih problema (videti #10 ispod).

Rešenja:

1. Koristite STARTING WITH za poznate prefikse stringova

Kada vaša vrednost pretrage nikada ne počinje džokerom %, preferirajte STARTING WITH umesto LIKE:

WHERE fieldName STARTING WITH ?param1

2. Optimizujte dvosmerne pretrage stringova

Za stringove sa poznatim obrascima prefiksa ili sufiksa, koristite obrnuti indeks:

-- 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. Implementirajte strategiju progresivne pretrage

Za stringove koji se pojavljuju na početku/kraju/sredini (ali ne istovremeno):

• Prvo pokušajte brzu indeksiranu pretragu sa STARTING WITH

• Ako nema rezultata, pređite na sporiju LIKE pretragu

4. Optimizacija pretrage zasnovane na rečima

Kada tražite cele reči (ograničene razmacima, zapetama itd.):

• Napravite zasebnu tabelu mapiranja word-ID

• Pretražujte kroz tabelu mapiranja umesto kroz originalni tekst

5. Za sveobuhvatne mogućnosti pretrage punog teksta:

• Razmotrite korišćenje IBSurgeon Full Text Search UDR

• Ovo open-source rešenje pruža naprednu funkcionalnost pretrage teksta

9. Nezatvaranje transakcija za operacije samo za čitanje

Zašto je to loše?

• Držanje transakcija otvorenim duže vreme može primorati Firebird da održava brojne starije verzije za potencijalne snapshot transakcije

Rešenja:

• Koristite transakcije samo za čitanje gde je to moguće, i zatvorite transakcije za pisanje što je pre moguće

• Koristite moderne verzije Firebird (4+) da smanjite uticaj lanaca verzija zapisa

• Implementirajte pravilno sweep

10. Problemi sa parametrizacijom upita

Anti-pattern: Izbegavanje pripremljenih upita i parametrizacije, umesto toga ugrađivanje vrednosti parametara direktno u tekst upita.

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

Zašto je to loše?

• Ova praksa smanjuje performanse za ponovljene upite

• Svaki upit sa ugrađenim vrednostima parametara mora biti pripremljen kao novi

• Priprema može biti duga i vremenski zahtevna za velike tabele

• Otežava analizu problema

• Teško je grupisati upite po tekstu

• Stvara ranjivosti na SQL injekcije

Rešenja:

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

11. Pogrešna provera integriteta: trigeri/CHECK umesto primarnog ključa

Anti-pattern: Korišćenje trigera ili CHECK umesto primarnih ključeva za provere integriteta baze podataka.

Zašto je to loše?

• Ovo zanemaruje činjenicu da validacija primarnog ključa koristi specijalni režim za čitanje trenutne verzije zapisa, bez obzira na nivo izolacije transakcije korisnika.

• Vršenje PK provera pomoću trigera u korisničkim transakcijama povećava mogućnost duplikacije i nepotrebno komplikuje logiku

Rešenja:

• Koristite primarne ključeve

• Izbegavajte redundantne provere integriteta

• Održavajte logiku baze podataka jednostavnom

12. Generisanje ID-a pomoću MAX()

Anti-pattern: Korišćenje MAX(id)+1 za nove identifikatore je nepouzdano i neefikasno.

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

Zašto je to loše?

• Korišćenje MAX(id)+1 umesto sekvenci (generatora) za nove identifikatore

• MAX(id)+1 ne garantuje jedinstvenost sa uobičajenim parametrima transakcija - dve paralelne transakcije mogu dobiti istu MAX() vrednost

• Kombinacija Max()+1 i CHECK(select if unique) takođe ne funkcioniše!

Rešenja:

-- 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. Neefikasno korišćenje GUID-ova

Zašto je to loše?

• Korišćenje sistemski generisanih GUID-ova umesto gen_uuid() može uticati na performanse indeksa

• Sistemski generisan GUID je visoko randomizovan

Rešenja:

• Koristite funkciju gen_uuid()

• Razmotrite korišćenje BIGINT umesto toga

• U verziji 6 biće UUID v7

14. Neefikasna izračunata polja

Anti-pattern: Korišćenje izračunatih polja sa SELECT-ovima ka drugim tabelama značajno smanjuje performanse jednostavnih SELECT operacija.

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

Zašto je to loše?

• Izračunata polja se izračunavaju u letu, i nije predviđeno da implementiraju složenu logiku, i mogu značajno zakomplikovati napore optimizacije

• Jača veze između tabela

• Ima smisla koristiti izračunata polja samo za lagane proračune sa poljima tabele, kao što je konkatenacija

Rešenja:

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. Potiskivanje grešaka bez logovanja

Anti-pattern: Ne potiskujte Firebird greške i upozorenja bez logovanja!

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

Zašto je to loše?

• Skrivanje grešaka onemogućava pravilnu dijagnostiku i debagovanje. Pravilno logovanje grešaka je ključno za brzo razumevanje i rešavanje problema.

Rešenja:

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;

Kontakt informacije

• Pošaljite svoja pitanja na [email protected]

• Postanite Firebird Supporter (od 10 EUR/mesečno) i učestvujte u zatvorenim naprednim webinarima!

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