Risposta.

In SQL è spesso necessaria una filtrazione dinamica — ad esempio, nei report o nella ricerca, quando i filtri sono definiti dall'utente. Le principali approcci sono:

SQL dinamico — la stringa della query viene costruita al volo con condizioni solo per parametri non vuoti. È flessibile, ma richiede di prestare attenzione alla protezione contro le iniezioni SQL (utilizzare parametri/escape).
Filtrazione condizionale tramite OR — utilizzare la costruzione (@param IS NULL OR campo = @param), affinché le condizioni superflue non influenzino il risultato, se il parametro non è definito. Questo metodo è sicuro, ma a volte porta a un utilizzo subottimale degli indici.

Esempio di modello per la ricerca degli ordini:

SELECT * FROM Orders
WHERE
    (@CustomerID IS NULL OR CustomerID = @CustomerID)
    AND (@Status IS NULL OR Status = @Status)
    AND (@DateFrom IS NULL OR OrderDate >= @DateFrom)
    AND (@DateTo IS NULL OR OrderDate <= @DateTo);

Domanda insidiosa.

Domanda: Perché a volte le prestazioni peggiorano quando si utilizza l'approccio (@param IS NULL OR Column = @param) — anche in presenza di un indice sulla Colonna?

Risposta: Questo modello impedisce all'ottimizzatore di utilizzare l'indice in modo efficiente, poiché è coinvolta una variabile nella condizione, e la query si trasforma in una scansione completa della tabella anziché in una ricerca indicizzata. Su grandi volumi di dati, questo è critico.

Esempio:

WHERE (@name IS NULL OR name = @name)

Questa condizione non è la stessa di name = @name, il che non consente al DBMS di utilizzare facilmente l'indice.

Storia

Nel servizio di ricerca della base clienti sono stati implementati "filtri flessibili" tramite il modello (@par IS NULL OR Field = @par), e con l'aumento del volume degli ordini la velocità di ricerca è scesa da secondi a minuti — gli indici hanno smesso di funzionare, poiché l'ottimizzatore non era in grado di determinare un valore specifico.

Storia

Nel progetto e-commerce, la filtrazione è stata costruita tramite SQL dinamico senza parametri, sostituendo direttamente i valori. Le iniezioni hanno permesso agli utenti di ottenere liste di tutti i clienti, poiché il filtro era vulnerabile — le password sono dovute essere cambiate urgentemente.

Storia

I report BI erano costruiti tramite un modello parametrizzato senza considerare che le condizioni OR rimuovono le scansioni indicizzate. Su milioni di set di dati, i report "si sono bloccati", finché non abbiamo riscritto la filtrazione in un modo ottimale con condizioni obbligatorie e una logica separata per diverse combinazioni di parametri.

Come implementare correttamente ed efficacemente la logica condizionale per filtrare i dati in SQL, se i parametri di ricerca sono definiti dinamicamente (ad esempio, un filtro web con qualsiasi insieme di condizioni)?

Risposta.

Domanda insidiosa.