15 antipattern di Firebird

di Alexey Kovyazin, 14-gen-2025

Introduzione

Questo documento illustra 15 anti-pattern comuni quando si lavora con database Firebird e fornisce una soluzione per ciascuno di essi.

1. Query parallele multiple su MON$

Anti-pattern: Un errore molto diffuso: trigger OnConnect, query su MON$ATTACHMENTS per selezionare i dettagli dell’utente a scopo di audit, oppure calcolare il numero di connessioni per scopi di licenza.

Perché è negativo?

• Le tabelle MON$ sono tabelle virtuali memorizzate nei file di sistema fbNN_mon_xx, con statistiche sulle prestazioni, ecc.

• Un file >1Gb significa che la stai usando troppo

• Sono progettate solo per l’uso da parte degli amministratori di sistema, cioè 1-2 query parallele, esclusivamente per gli amministratori

• 200+ connessioni con query parallele su MON$ rallenteranno Firebird in modo molto significativo, e 500+ query simultanee “bloccheranno” Firebird con alte probabilità

Soluzioni:

• Non usare MON$ per attività non amministrative, cioè per contare o fare audit, evita di usarle in OnConnect

• Per scopi di audit:

• Usa le variabili di contesto come CURRENT_USER, CURRENT_TIMESTAMP, ecc.

• Usa Audit, funzionalità nativa di Firebird, molto più potente dei trigger

• Per scopi di licenza, usa le variabili di contesto dell’utente

2. Caricamento lento della dashboard

Anti-pattern: Caricare dashboard o scoreboard complete che sommano tutti gli ordini e le fatture dell’ultimo mese o anno all’avvio dell’applicazione, oppure aggiornare alcune metriche ogni minuto o più spesso.

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

Perché è negativo?

• Gli utenti devono attendere diversi secondi per vedere le statistiche aziendali prima di poter iniziare il proprio lavoro effettivo

• Dal punto di vista di Firebird, per eseguire costantemente molte query parallele, recuperare enormi quantità di dati, ordinarli/raggrupparli, Firebird utilizzerà intensamente più core CPU, leggendo da disco, cache, memoria dedicata all’ordinamento (e talvolta l’ordinamento finisce su disco)

• È come costruire un report più volte al minuto!

Soluzioni:

1. Riduci il numero di utenti che vedranno le dashboard:

• Di solito la Dashboard è richiesta solo da analisti e management, escludila dal carico generale dell’applicazione

• Rendi opzionale il caricamento della dashboard all’avvio/per qualche form, disabilitato per impostazione predefinita

• Carica i dati della dashboard tramite un click esplicito su un pulsante, non all’avvio (cioè, rendila un report)

2. Calcola i dati della dashboard con 1 processo pianificato (cioè un robot) e memorizzali in una tabella semplice pronta per essere recuperata con una query semplice

3. Usa i trigger per aggregare i dati e memorizzarli pronti all’uso

4. Usa un database replica per calcolare i dati delle dashboard (e anche tutti i report pesanti)

3. Caricamento di record non necessari

Anti-pattern: Caricare tutti i dati senza filtri nella griglia all’apertura di un’applicazione o di un form, indipendentemente dal fatto che contenga centinaia di migliaia di record.

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

Perché è negativo?

• Nonostante la griglia mostri solo 50 record, gli utenti devono scorrere migliaia di record invece di usare la funzionalità di ricerca

• Nel 99% dei casi gli utenti hanno bisogno di un sottoinsieme molto ristretto di dati: i record di vendita più recenti, per esempio

• Dal punto di vista di Firebird:

• Ogni apertura richiede la lettura, la memorizzazione in cache e il trasferimento di migliaia di record attraverso la rete

• Se mantieni il dataset aperto (in Delphi), Firebird mantiene buffer, record ordinati nello spazio temporaneo (se ORDER BY, GROUP BY, ecc.) fino alla chiusura del dataset

Soluzioni:

1. Limita il numero di record con FIRST/SKIP/ROWS

2. Limita il numero di record con qualche criterio, per esempio mostrando i record creati/modificati negli ultimi 3 giorni

3. In generale, chiudi le query il prima possibile.

4. Query eccessive durante lo scroll

Anti-pattern: Eseguire query sugli eventi di scroll. Per esempio, quando si visualizzano dati in una griglia o tabella, eseguendo una query separata PER OGNI record, oppure se stai usando il classico esempio di scroll master-detail in 2 griglie senza ritardo.

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;

Perché è negativo?

• Eseguire una query separata PER OGNI record in una griglia dinamica costringe Firebird a elaborare migliaia di piccole query, consumando inutilmente risorse CPU

• Dal punto di vista di Firebird:

• Molte (migliaia al secondo) piccole query creeranno un carico CPU significativo, perché anche se la query mostra 0ms nelle statistiche, richiede di essere preparata, eseguita, il risultato trasferito, ecc.

Soluzioni:

1. Carica più righe contemporaneamente usando operazioni batch

2. Migliora la query principale della griglia per eseguire la query di dettaglio come parte di essa

3. Aggiungi un pulsante esplicito per caricare i dettagli della parte visibile della griglia

4. Aggiungi un ritardo per eseguire la query per ricevere i dettagli, per evitare query immediate durante lo scroll

5. Non abilitare il caricamento dei dettagli durante lo scroll per tutti gli utenti per impostazione predefinita

5. Aggiornamenti automatici non necessari

Anti-pattern: Aggiornare automaticamente i dati della griglia a intervalli minimi in ogni applicazione client, con questa funzionalità abilitata per impostazione predefinita.

Perché è negativo?

• Questo comporta centinaia di connessioni client che eseguono query quasi identiche per recuperare gli stessi record

• Dove accade: aggiornamenti automatici per pianificazioni, o select per posizioni in coda, o ricerca dello “slot più vicino”, ecc.

• Dal punto di vista di Firebird:

• La combinazione di caricamento di dashboard ed eventi on-scroll: molte query di medie dimensioni creano carico sul sistema

Soluzioni:

1. Aumenta l’intervallo!

2. Implementa aggiornamenti espliciti (attivati dall’utente)

3. Usa aggiornamenti selettivi del dataset basati su cambiamenti effettivi dei dati (streaming o trigger o evento+streaming)

6. Aggiornamenti frequenti dei record

Anti-pattern: Aggiornare frequentemente lo stesso record in transazioni diverse, creando numerose versioni del record.

Perché è negativo?

• Un record con decine di versioni può degradare significativamente le prestazioni, un record con migliaia può diventare un blocco

• Dal punto di vista di Firebird: la catena di versioni del record deve essere ricostruita per identificare la versione corretta della transazione specifica, richiede numerose operazioni di lettura, di conseguenza la garbage collection diventa significativamente più lenta.

Soluzioni:

1. Migra a Firebird 4+, c’è la garbage collection intermedia

2. Non mantenere transazioni scrivibili di lunga durata, esegui una corretta garbage collection

3. Per Firebird <4, considera l’uso di DELETE+INSERT invece di UPDATE

7. Uso di transazioni scrivibili per select di sola lettura

Anti-pattern: Usare transazioni scrivibili per select di sola lettura porta a operazioni eccessive.

Perché è negativo?

• Usare transazioni scrivibili per select di sola lettura porta a molte scritture non necessarie delle pagine di intestazione

• Usare transazioni scrivibili per operazioni di sola lettura è inefficiente (un TIP grande durante il commit crea carico aggiuntivo sul server)

Soluzioni:

• Usa una transazione di sola lettura separata per le operazioni che non modificano i dati

• Firebird è uno dei pochi database che consentono di aprire diverse transazioni nell’ambito di una singola connessione

• Le tabelle Global Temporary sono disponibili per l’uso in transazioni di sola lettura

8. Uso di LIKE :param

La seguente query con parametro non utilizzerà l’indice per fieldName (anche se l’indice esiste):

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Perché è negativo?

Poiché LIKE consente la ricerca con caratteri jolly (%), che possono sostituire qualsiasi numero di simboli, Firebird non può determinare in anticipo se il valore del parametro sarà adatto per la ricerca tramite indice.

Di solito gli sviluppatori cercano di aggirarlo incorporando il valore del parametro nel testo della query:

• fieldName LIKE «Alex%» - possibile usare l’indice

• fieldName LIKE «%Alex» - non possibile usare l’indice standard

• fieldName LIKE «%Alex%» - non possibile usare affatto l’indice

Questo porta ad altri problemi (vedi #10 sotto).

Soluzioni:

1. Usa STARTING WITH per prefissi di stringa noti

Quando il tuo valore di ricerca non inizia mai con un carattere jolly %, preferisci STARTING WITH a LIKE:

WHERE fieldName STARTING WITH ?param1

2. Ottimizza le ricerche di stringhe bidirezionali

Per stringhe con prefissi o suffissi noti, usa un indice invertito:

-- 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. Implementa una strategia di ricerca progressiva

Per stringhe che compaiono all’inizio/alla fine/nel mezzo (ma non simultaneamente):

• Prima prova la ricerca veloce con indice tramite STARTING WITH

• Se non vengono trovati risultati, ricadi sulla ricerca più lenta con LIKE

4. Ottimizzazione della ricerca basata su parole

Quando si cercano parole complete (delimitate da spazi, virgole, ecc.):

• Crea una tabella separata di mappatura parola-ID

• Cerca attraverso la tabella di mappatura invece che nel testo originale

5. Per capacità complete di ricerca full-text:

• Considera l’uso di IBSurgeon Full Text Search UDR

• Questa soluzione open-source fornisce funzionalità avanzate di ricerca testuale

9. Non chiudere le transazioni per operazioni di sola lettura

Perché è negativo?

• Mantenere le transazioni aperte per periodi prolungati potrebbe costringere Firebird a mantenere numerose versioni precedenti per potenziali transazioni snapshot

Soluzioni:

• Usa transazioni di sola lettura dove possibile, e chiudi le transazioni scrivibili il prima possibile

• Usa versioni moderne di Firebird (4+) per ridurre l’impatto delle catene di versioni dei record

• Implementa una corretta sweep

10. Problemi di parametrizzazione delle query

Anti-pattern: Evitare query preparate e parametrizzazione, incorporando invece i valori dei parametri direttamente nel testo della query.

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

Perché è negativo?

• Questa pratica riduce le prestazioni per query ripetute

• Ogni query con valori dei parametri incorporati deve essere preparata come nuova

• La preparazione può essere lunga e dispendiosa in termini di tempo per tabelle grandi

• Complica l’analisi dei problemi

• È difficile raggruppare le query per testo

• Crea vulnerabilità di SQL injection

Soluzioni:

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

11. Controllo di integrità errato: trigger/CHECK invece di Primary Key

Anti-pattern: Usare trigger o CHECK invece delle Primary Key per i controlli di integrità del database.

Perché è negativo?

• Questo ignora il fatto che la validazione della Primary Key utilizza una modalità speciale per leggere la versione corrente del record, indipendentemente dal livello di isolamento della transazione dell’utente.

• Eseguire i controlli PK con trigger nelle transazioni utente aumenta la possibilità di duplicazione e complica inutilmente la logica

Soluzioni:

• Usa le primary key

• Evita controlli di integrità ridondanti

• Mantieni semplice la logica del database

12. Generazione di ID con MAX()

Anti-pattern: Usare MAX(id)+1 per nuovi identificatori non è affidabile e inefficiente.

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

Perché è negativo?

• Usare MAX(id)+1 invece delle sequence (generator) per nuovi identificatori

• MAX(id)+1 non garantisce l’unicità con parametri di transazione comuni: due transazioni parallele potrebbero ricevere lo stesso valore MAX()

• Anche la combinazione di Max()+1 e CHECK(select if unique) non funziona!

Soluzioni:

-- 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. Uso inefficiente dei GUID

Perché è negativo?

• L’uso di GUID generati dal sistema invece di gen_uuid() può influire sulle prestazioni degli indici

• Il GUID generato dal sistema è altamente randomizzato

Soluzioni:

• Utilizzare la funzione gen_uuid()

• Considerare l’uso di BIGINT

• Nella versione 6 ci sarà UUID v7

14. Campi calcolati inefficienti

Anti-pattern: L’uso di campi calcolati con SELECT su altre tabelle riduce significativamente le prestazioni delle operazioni SELECT semplici.

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

Perché è negativo?

• I campi calcolati vengono calcolati al volo, non dovrebbero implementare logica complessa e possono complicare notevolmente gli sforzi di ottimizzazione

• Rafforza le relazioni tra le tabelle

• Ha senso usare i campi calcolati solo per calcoli leggeri con i campi della tabella, come la concatenazione

Soluzioni:

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. Soppressione degli errori senza registrazione

Anti-pattern: Non sopprimere gli errori e gli avvisi di Firebird senza registrarli!

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

Perché è negativo?

• Nascondere gli errori impedisce una corretta diagnosi e il debug. Una corretta registrazione degli errori è fondamentale per comprendere e risolvere rapidamente i problemi.

Soluzioni:

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;

Informazioni di contatto

• Invia le tue domande a [email protected]

• Diventa Firebird Supporter (a partire da 10 EUR/mese) e partecipa ai webinar avanzati riservati!

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