15 antywzorców Firebird

autor: Alexey Kovyazin, 14 sty 2025

Wprowadzenie

Ten dokument opisuje 15 typowych antywzorców podczas pracy z bazami danych Firebird oraz przedstawia 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 pobrania danych użytkownika do celów audytowych lub obliczenia liczby połączeń do celów licencyjnych.

Dlaczego to jest złe?

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

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

• Są przeznaczone wyłącznie do użytku administratorów systemu - tj. 1-2 równoległych zapytań, wyłącznie dla administratorów

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

Rozwiązania:

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

• Do celów audytowych:

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

• Używaj Audytu - natywnej funkcji Firebird, znacznie potężniejszej niż wyzwalacze

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

2. Wolne ładowanie pulpitu nawigacyjnego

Antywzorzec: Ładowanie rozbudowanych pulpitów nawigacyjnych lub tablic wyników sumujących wszystkie zamówienia i faktury z ostatniego miesiąca lub roku podczas uruchamiania aplikacji, lub 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ąć faktyczną pracę

• Z punktu widzenia Firebird - aby stale wykonywać wiele równoległych zapytań, pobierać ogromne ilości danych, sortować/grupować je, Firebird będzie intensywnie używać wielu rdzeni CPU, czytając z dysku, pamięci podręcznej, pamięci przeznaczonej do sortowania (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ć pulpity nawigacyjne:

• Zazwyczaj pulpit nawigacyjny jest potrzebny tylko analitykom i kierownictwu, wyklucz go z ogólnego ładowania aplikacji

• Ustaw ładowanie pulpitu nawigacyjnego przy starcie/dla niektórych formularzy jako opcjonalne, domyślnie wyłączone

• Ładuj dane pulpitu nawigacyjnego po kliknięciu przycisku, nie przy starcie (tj. potraktuj to jako raport)

2. Obliczaj dane pulpitu nawigacyjnego za pomocą 1 procesu według harmonogramu (np. robota) i przechowuj je w prostej tabeli gotowej do pobrania prostym zapytaniem

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

4. Używaj repliki bazy danych do obliczania danych pulpitu nawigacyjnego (oraz wszystkich ciężkich raportów)

3. Ładowanie niepotrzebnych rekordów

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

procedure TDataForm.LoadAllRecords;
begin
 FDQuery1.SQL.Text := 'SELECT * FROM large_table';
 FDQuery1.Open;
 // Ładuje całą tabelę do pamięci
 DBGrid1.DataSource.DataSet := FDQuery1;
end;

Dlaczego to jest złe?

• Pomimo ż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 przesyłania tysięcy rekordów przez sieć

• Jeśli trzymasz 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 za pomocą kryteriów, na przykład pokazuj rekordy utworzone/zmienione w ciągu ostatnich 3 dni

3. Ogólnie zamykaj zapytania tak szybko, jak to możliwe.

4. Nadmierne zapytania podczas przewijania

Antywzorzec: Wykonywanie zapytań podczas zdarzeń 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
 // zapytanie dla każdego wiersza
 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 małych zapytań, niepotrzebnie zużywając zasoby CPU

• Z punktu widzenia Firebird:

• Wiele (tysiące na sekundę) małych zapytań stworzy znaczące obciążenie CPU, ponieważ nawet jeśli zapytanie pokazuje 0ms 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 szczegółowe zapytanie jako jego część

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

4. Dodaj opóźnienie do wykonywania zapytania pobierającego szczegóły, aby zapobiec natychmiastowym zapytaniom podczas przewijania

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

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?

• Skutkuje to setkami połączeń klienckich wykonujących prawie identyczne zapytania w celu pobrania tych samych rekordów

• Gdzie to występuje: automatyczne odświeżanie harmonogramów, zapytania o pozycje w kolejce, wyszukiwanie “najbliższego wolnego terminu” itp.

• Z punktu widzenia Firebird:

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

Rozwiązania:

1. Zwiększ odstęp czasu!

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

3. Używaj selektywnego odświeżania zestawu danych w oparciu o faktyczne zmiany danych (strumieniowanie lub wyzwalacze lub zdarzenia+strumieniowanie)

6. Częste aktualizacje rekordów

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

Dlaczego to jest złe?

• Rekord z dziesiątkami wersji może znacząco obniżyć wydajność, rekord z tysiącami może stać się blokerem

• Z punktu widzenia Firebird: łańcuch wersji rekordów musi zostać zrekonstruowany, aby zidentyfikować właściwą wersję dla konkretnej transakcji, wymaga to licznych operacji odczytu, w wyniku czego odśmiecanie staje się znacznie wolniejsze.

Rozwiązania:

1. Migruj do Firebird 4+, istnieje pośrednie odśmiecanie

2. Nie trzymaj długo działających transakcji zapisu, wykonuj właściwe odśmiecanie

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

7. Używanie transakcji zapisu do zapytań 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ą na otwarcie kilku 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 użyje indeksu dla pola fieldName (nawet jeśli indeks istnieje):

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Dlaczego to jest złe?

Ponieważ LIKE pozwala na wyszukiwanie z użyciem symboli wieloznacznych (%), 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 wartość wyszukiwania nigdy nie zaczyna się od symbolu wieloznacznego %, preferuj STARTING WITH zamiast LIKE:

WHERE fieldName STARTING WITH ?param1

2. Optymalizuj dwukierunkowe wyszukiwanie ciągów

Dla ciągów ze znanymi wzorcami prefiksów lub sufiksów, używaj odwróconego indeksu:

-- Utwórz odwrócony indeks
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- Zapytanie używające obu kierunków
WHERE fieldName STARTING WITH :param1
   OR reverse(fieldName) STARTING WITH reverse(:param2)

3. Wdróż strategię progresywnego 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 ma wyników, użyj 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łów na ID

• Wyszukuj przez tabelę mapowania zamiast oryginalnego tekstu

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

• 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?

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

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 odśmiecanie (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ść dla 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

• Komplikuje analizę problemów

• Trudno jest grupować zapytania według tekstu

• Tworzy podatności na wstrzykiwanie SQL

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 do odczytu bieżącej wersji rekordu, niezależnie od poziomu izolacji transakcji użytkownika.

• Wykonywanie kontroli klucza głównego 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 kontroli 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(select if unique) również nie działa!

Rozwiązania:

-- Użyj generatora/sekwencji!
CREATE GENERATOR gen_user_id;
-- Użyj generatora do generowania ID
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 systemowo generowanych GUID zamiast gen\_uuid() może wpływać na wydajność indeksów

- Systemowo generowany GUID jest wysoce losowy


**Rozwiązania:**

- Użyj funkcji gen\_uuid()

- Rozważ użycie BIGINT zamiast tego

- W wersji 6 będzie dostępny UUID v7


## 14. Nieefektywne pola wyliczane

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

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

Dlaczego to jest złe?

• Pola wyliczane są obliczane na bieżąco i nie są przeznaczone do implementacji złożonej logiki, co może znacząco skomplikować wysiłki optymalizacyjne

• Wzmacnia to zależności między tabelami

• Używanie pól wyliczanych ma sens tylko w przypadku lekkich obliczeń na polach 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łumij błędów i ostrzeżeń Firebird bez logowania!

try
 FDQuery1.Open;
except
 // Cicha porażka
end;

Dlaczego to jest złe?

• Ukrywanie błędów uniemożliwia prawidłową 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
 // Kompleksowe logowanie
 Logger.Error('Połączenie z bazą danych nie powiodło się: ' + E.Message);
 ShowMessage('Nie można połączyć się z bazą danych. Skontaktuj się z pomocą techniczną.');
 // Zaloguj dodatkowy kontekst
 Logger.LogStackTrace(E);
 end;
end;

Dane kontaktowe

• Wyślij swoje pytania na adres [email protected]

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

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