15 antywzorców Firebird

Autor: Alexey Kovyazin, 14 stycznia 2025

Wprowadzenie

Ten dokument opisuje 15 typowych antywzorców podczas pracy z bazami danych Firebird i podaje rozwiązania dla każdego z nich.

1. Wiele równoległych zapytań do MON$

Antywzorzec: Bardzo popularny błąd — wyzwalacz OnConnect, zapytanie do MON$ATTACHMENTS w celu wybrania danych użytkownika do celów audytu lub obliczenia liczby połączeń do celów licencyjnych.

Dlaczego to jest złe?

• Tabele MON$ to tabele wirtualne przechowywane w plikach systemowych fbNN_mon_xx, zawierające statystyki wydajności itp.

• Plik >1 GB oznacza, że używasz go zbyt intensywnie

• Są przeznaczone wyłącznie do użytku administratorów systemu — czyli 1–2 równoległe zapytania, wyłącznie dla administratorów

• 200+ połączeń z równoległymi zapytaniami do MON$ znacznie spowolni Firebird, a 500+ jednoczesnych zapytań z dużym prawdopodobieństwem „zawiesi” Firebird

Rozwiązania:

• Nie używaj MON$ do zadań nieadministracyjnych, tj. do zliczania lub audytu, unikaj ich używania w OnConnect

• Do celów audytu:

• Używaj zmiennych kontekstowych, takich jak CURRENT_USER, CURRENT_TIMESTAMP itp.

• Używaj Audit — natywnej funkcji Firebird, znacznie potężniejszej niż wyzwalacze

• Do celów licencyjnych — używaj zmiennych kontekstowych użytkownika

2. Wolne ładowanie dashboardu

Antywzorzec: Ładowanie rozbudowanych dashboardów lub tablic wyników, które sumują wszystkie zamówienia i faktury za ostatni miesiąc lub rok podczas uruchamiania aplikacji, albo aktualizowanie niektórych metryk co minutę lub częściej.

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

Dlaczego to jest złe?

• Użytkownicy muszą czekać kilka sekund, aby zobaczyć statystyki całej firmy, zanim będą mogli rozpocząć swoją właściwą pracę

• Z punktu widzenia Firebird — aby stale uruchamiać wiele równoległych zapytań, pobierać ogromne ilości danych, sortować/grupować je, Firebird będzie intensywnie wykorzystywać wiele rdzeni CPU, odczytywać z dysku, pamięci podręcznej, pamięci przeznaczonej na sortowanie (a czasami sortowanie trafia na dysk)

• To jak budowanie raportu kilka razy na minutę!

Rozwiązania:

1. Zmniejsz liczbę użytkowników, którzy będą widzieć dashboardy:

• Zazwyczaj dashboard jest wymagany tylko dla analityków i kadry zarządzającej, wyklucz go z ogólnego obciążenia aplikacji

• Uczyń ładowanie dashboardu przy starcie/dla niektórych formularzy opcjonalnym, domyślnie wyłączonym

• Ładuj dane dashboardu po jawnym kliknięciu przycisku, a nie przy starcie (tj. zrób z tego raport)

2. Obliczaj dane dashboardu w 1 procesie według harmonogramu (tj. robot) i przechowuj je w prostej tabeli gotowej do pobrania prostym zapytaniem

3. Używaj wyzwalaczy do agregowania danych i przechowuj je gotowe do użycia

4. Używaj bazy repliki do obliczania danych dashboardu (i wszystkich ciężkich raportów)

3. Ładowanie niepotrzebnych rekordów

Antywzorzec: Ładowanie wszystkich danych bez filtrowania do siatki przy otwieraniu aplikacji lub formularza, niezależnie od tego, czy zawiera setki tysięcy rekordów.

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

Dlaczego to jest złe?

• Mimo że siatka pokazuje tylko 50 rekordów, użytkownicy muszą przewijać tysiące rekordów zamiast korzystać z funkcji wyszukiwania

• W 99% przypadków użytkownicy potrzebują bardzo wąskiego podzbioru danych: na przykład najnowszych rekordów sprzedaży

• Z punktu widzenia Firebird:

• Każde otwarcie wymaga odczytu, przechowywania w pamięci podręcznej i transferu tysięcy rekordów przez sieć

• Jeśli utrzymujesz zestaw danych otwarty (w Delphi), Firebird utrzymuje bufory, posortowane rekordy w przestrzeni tymczasowej (jeśli ORDER BY, GROUP BY itp.) aż do zamknięcia zestawu danych

Rozwiązania:

1. Ogranicz liczbę rekordów za pomocą FIRST/SKIP/ROWS

2. Ogranicz liczbę rekordów według pewnych kryteriów, na przykład pokazuj rekordy utworzone/zmienione w ciągu ostatnich 3 dni

3. Ogólnie rzecz biorąc, zamykaj zapytania tak szybko, jak to możliwe.

4. Nadmierne odpytywanie przy przewijaniu

Antywzorzec: Wykonywanie zapytań przy zdarzeniach przewijania. Na przykład podczas wyświetlania danych w siatce lub tabeli wykonywanie osobnego zapytania DLA KAŻDEGO rekordu lub jeśli używasz klasycznego przykładu przewijania master-detail w 2 siatkach bez opóźnienia.

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;

Dlaczego to jest złe?

• Wykonywanie osobnego zapytania DLA KAŻDEGO rekordu w dynamicznej siatce zmusza Firebird do przetwarzania tysięcy drobnych zapytań, niepotrzebnie zużywając zasoby CPU

• Z punktu widzenia Firebird:

• Wiele (tysiące na sekundę) małych zapytań spowoduje znaczne obciążenie CPU, ponieważ nawet jeśli zapytanie pokazuje 0 ms w statystykach, wymaga przygotowania, wykonania, przesłania wyniku itp.

Rozwiązania:

1. Ładuj wiele wierszy naraz za pomocą operacji wsadowych

2. Rozszerz główne zapytanie dla siatki, aby wykonywało zapytanie szczegółowe jako jego część

3. Dodaj jawny przycisk do ładowania szczegółów dla widocznej części siatki

4. Dodaj opóźnienie wykonania zapytania w celu otrzymania szczegółów, aby zapobiec natychmiastowym zapytaniom podczas przewijania

5. Nie włączaj ładowania szczegółów przy przewijaniu dla wszystkich użytkowników domyślnie

5. Niepotrzebne automatyczne odświeżanie

Antywzorzec: Automatyczne odświeżanie danych siatki w minimalnych odstępach czasu w każdej aplikacji klienckiej, z tą funkcją włączoną domyślnie.

Dlaczego to jest złe?

• Powoduje to setki połączeń klienckich wykonujących niemal identyczne zapytania w celu pobrania tych samych rekordów

• Gdzie to występuje: automatyczne odświeżanie harmonogramów, wybór pozycji w kolejce, wyszukiwanie „najbliższego terminu” itp.

• Z punktu widzenia Firebird:

• Połączenie ładowania dashboardów i zdarzeń przewijania: wiele średnich zapytań tworzy obciążenie systemu

Rozwiązania:

1. Zwiększ interwał!

2. Wdróż jawne (wyzwalane przez użytkownika) odświeżanie

3. Używaj selektywnego odświeżania zestawu danych w oparciu o rzeczywiste zmiany danych (streaming lub wyzwalacze lub zdarzenie+streaming)

6. Częste aktualizacje rekordów

Antywzorzec: Częste aktualizowanie tego samego rekordu w różnych transakcjach, tworzące liczne wersje rekordów.

Dlaczego to jest złe?

• Rekord z dziesiątkami wersji może znacznie pogorszyć wydajność, rekord z tysiącami może stać się blokerem

• Z punktu widzenia Firebird: łańcuch wersji rekordu musi zostać zrekonstruowany, aby zidentyfikować właściwą wersję dla konkretnej transakcji, wymaga to licznych operacji odczytu, w rezultacie zbieranie śmieci staje się znacznie wolniejsze.

Rozwiązania:

1. Migruj do Firebird 4+, tam jest pośrednie zbieranie śmieci

2. Nie utrzymuj długotrwałych transakcji zapisu, wykonuj właściwe zbieranie śmieci

3. Dla Firebird <4 rozważ użycie DELETE+INSERT zamiast UPDATE

7. Używanie transakcji zapisu do odczytu tylko do odczytu

Antywzorzec: Używanie transakcji zapisu do zapytań tylko do odczytu prowadzi do nadmiernych operacji.

Dlaczego to jest złe?

• Używanie transakcji zapisu do zapytań tylko do odczytu prowadzi do wielu niepotrzebnych zapisów stron nagłówka

• Używanie transakcji zapisu do operacji tylko do odczytu jest nieefektywne (duży TIP podczas zatwierdzania tworzy dodatkowe obciążenie serwera)

Rozwiązania:

• Używaj osobnej transakcji tylko do odczytu dla operacji, które nie zmieniają danych

• Firebird jest jedną z niewielu baz danych, które pozwalają otworzyć kilka transakcji w ramach jednego połączenia

• Globalne tabele tymczasowe są dostępne do użycia w transakcjach tylko do odczytu

8. Używanie LIKE :param

Następujące zapytanie z parametrem nie będzie używać indeksu dla fieldName (nawet jeśli indeks istnieje):

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Dlaczego to jest złe?

Ponieważ LIKE pozwala na wyszukiwanie z symbolami wieloznacznymi (%), które mogą zastąpić dowolną liczbę znaków, Firebird nie może z góry określić, czy wartość parametru będzie odpowiednia do wyszukiwania indeksowego.

Zazwyczaj programiści próbują obejść to, osadzając wartość parametru w tekście zapytania:

• fieldName LIKE «Alex%» — możliwe użycie indeksu

• fieldName LIKE «%Alex» — niemożliwe użycie standardowego indeksu

• fieldName LIKE «%Alex%» — niemożliwe użycie indeksu w ogóle

Prowadzi to do innych problemów (patrz #10 poniżej).

Rozwiązania:

1. Używaj STARTING WITH dla znanych prefiksów ciągów

Gdy twoja wartość wyszukiwania nigdy nie zaczyna się od symbolu wieloznacznego %, preferuj STARTING WITH zamiast LIKE:

WHERE fieldName STARTING WITH ?param1

2. Optymalizuj wyszukiwanie ciągów dwukierunkowych

Dla ciągów o znanych wzorcach prefiksu lub sufiksu użyj indeksu odwróconego:

-- 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. Wdróż progresywną strategię wyszukiwania

Dla ciągów, które pojawiają się na początku/końcu/środku (ale nie jednocześnie):

• Najpierw spróbuj szybkiego wyszukiwania indeksowego z STARTING WITH

• Jeśli nie znaleziono wyników, wróć do wolniejszego wyszukiwania LIKE

4. Optymalizacja wyszukiwania opartego na słowach

Podczas wyszukiwania całych słów (oddzielonych spacjami, przecinkami itp.):

• Utwórz osobną tabelę mapowania słowo-ID

• Wyszukuj przez tabelę mapowania zamiast oryginalnego tekstu

5. Dla kompleksowych możliwości wyszukiwania pełnotekstowego:

• Rozważ użycie IBSurgeon Full Text Search UDR

• To rozwiązanie open-source zapewnia zaawansowaną funkcjonalność wyszukiwania tekstu

9. Niezamykanie transakcji dla operacji tylko do odczytu

Dlaczego to jest złe?

• Utrzymywanie transakcji otwartych przez dłuższy czas może zmusić Firebird do utrzymywania licznych wersji wstecznych dla potencjalnych transakcji snapshot

Rozwiązania:

• Używaj transakcji tylko do odczytu tam, gdzie to możliwe, i zamykaj transakcje zapisu tak szybko, jak to możliwe

• Używaj nowoczesnych wersji Firebird (4+), aby zmniejszyć wpływ łańcuchów wersji rekordów

• Wdróż właściwe sweep

10. Problemy z parametryzacją zapytań

Antywzorzec: Unikanie przygotowanych zapytań i parametryzacji, zamiast tego osadzanie wartości parametrów bezpośrednio w tekście zapytania.

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

Dlaczego to jest złe?

• Ta praktyka zmniejsza wydajność powtarzanych zapytań

• Każde zapytanie z osadzonymi wartościami parametrów musi być przygotowane jako nowe

• Przygotowanie może być długie i czasochłonne dla dużych tabel

• Utrudnia analizę problemów

• Trudno jest grupować zapytania według tekstu

• Tworzy luki w zabezpieczeniach przed SQL injection

Rozwiązania:

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

11. Błędne sprawdzanie integralności: wyzwalacze/CHECK zamiast klucza głównego

Antywzorzec: Używanie wyzwalaczy lub CHECK zamiast kluczy głównych do sprawdzania integralności bazy danych.

Dlaczego to jest złe?

• Ignoruje to fakt, że walidacja klucza głównego używa specjalnego trybu odczytu bieżącej wersji rekordu, niezależnie od poziomu izolacji transakcji użytkownika.

• Wykonywanie sprawdzeń PK za pomocą wyzwalaczy w transakcjach użytkownika zwiększa możliwość duplikacji i niepotrzebnie komplikuje logikę

Rozwiązania:

• Używaj kluczy głównych

• Unikaj zbędnych sprawdzeń integralności

• Utrzymuj logikę bazy danych prostą

12. Generowanie ID za pomocą MAX()

Antywzorzec: Używanie MAX(id)+1 dla nowych identyfikatorów jest zawodne i nieefektywne.

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

Dlaczego to jest złe?

• Używanie MAX(id)+1 zamiast sekwencji (generatorów) dla nowych identyfikatorów

• MAX(id)+1 nie gwarantuje unikalności przy typowych parametrach transakcji — dwie równoległe transakcje mogą otrzymać tę samą wartość MAX()

• Połączenie Max()+1 i CHECK(sprawdź czy unikalne) również nie działa!

Rozwiązania:

-- 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. Nieefektywne użycie GUID

Dlaczego to jest złe?

• Używanie GUID-ów generowanych przez system zamiast gen_uuid() może wpływać na wydajność indeksów

• GUID generowany przez system jest wysoce losowy

Rozwiązania:

• Używaj funkcji gen_uuid()

• Rozważ użycie BIGINT zamiast tego

• W wersji 6 będzie UUID v7

14. Nieefektywne pola obliczane

Antywzorzec: Używanie pól obliczanych z SELECT-ami do innych tabel znacząco obniża wydajność prostych operacji SELECT.

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

Dlaczego to jest złe?

• Pola obliczane są wyliczane w locie i nie powinny implementować złożonej logiki, a mogą znacząco komplikować działania optymalizacyjne

• Wzmacnia relacje między tabelami

• Sensowne jest używanie pól obliczanych tylko do lekkich obliczeń z polami tabeli, takich jak konkatenacja

Rozwiązania:

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. Tłumienie błędów bez logowania

Antywzorzec: Nie tłum błędów i ostrzeżeń Firebird bez logowania!

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

Dlaczego to jest złe?

• Ukrywanie błędów uniemożliwia właściwą diagnozę i debugowanie. Właściwe logowanie błędów jest kluczowe dla szybkiego zrozumienia i rozwiązywania problemów.

Rozwiązania:

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;

Informacje kontaktowe

• Wysyłaj swoje pytania na adres [email protected]

• Zostań Firebird Supporter (od 10 EUR/miesiąc) i uczestnicz w zamkniętych zaawansowanych webinarach!

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