Odpowiedź.

W SQL często wymagane jest dynamiczne filtrowanie — na przykład w raportach lub wyszukiwaniu, gdy filtry są określane przez użytkownika. Główne podejścia:

Dynamiczny SQL — ciąg zapytania jest budowany na bieżąco z warunkami tylko dla niepustych parametrów. Jest to elastyczne, ale wymaga skupienia na ochronie przed atakami SQL Injection (używać parametrów/escapowania).
Warunkowe filtrowanie przez OR — używaj konstrukcji (@param IS NULL OR pole = @param), aby dodatkowe warunki nie wpływały na wynik, jeśli parametr nie jest określony. Ten sposób jest bezpieczny, ale czasami prowadzi do nieoptymalnego wykorzystania indeksów.

Przykład szablonu dla wyszukiwania zamówień:

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

Pytanie z haczykiem.

Pytanie: Dlaczego czasami wydajność pogarsza się przy użyciu podejścia (@param IS NULL OR Column = @param) — nawet przy istnieniu indeksu na Column?

Odpowiedź: Taki szablon przeszkadza optymalizatorowi w efektywnym wykorzystaniu indeksu, ponieważ w warunku bierze udział zmienna, a zapytanie staje się pełnym skanem tabeli zamiast wyszukiwania indeksu. Przy dużych wolumenach danych jest to krytyczne.

Przykład:

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

Ten warunek to nie to samo, co po prostu name = @name, co nie pozwala DBMS łatwo korzystać z indeksu.

Historia

W serwisie wyszukiwania bazy klientów wprowadzono "elastyczne filtry" przez szablon (@par IS NULL OR Field = @par), a przy wzroście wolumenu zamówień prędkość wyszukiwania spadła z sekund do minut — indeksy przestały działać, ponieważ optymalizator nie mógł określić konkretnej wartości.

Historia

W projekcie e-commerce filtrowanie zbierano przez dynamiczny SQL bez parametrów, podstawiając wartości bezpośrednio. Wstrzyknięcia umożliwiły użytkownikom uzyskanie listy wszystkich klientów, ponieważ filtr był podatny na ataki — hasła trzeba było pilnie zmieniać.

Historia

Raporty BI budowano przez szablon parametryzowany bez uwzględnienia tego, że warunki OR eliminują skany indeksowane. Przy milionowych zestawach raporty „zamierały”, dopóki nie przepisano filtrowania na optymalną wersję z obligatoryjnymi warunkami i osobną logiką dla różnych kombinacji parametrów.

Jak poprawnie i efektywnie zrealizować warunkową logikę filtrowania danych w SQL, gdy parametry wyszukiwania są określane dynamicznie (na przykład, filtr internetowy z dowolnym zestawem warunków)?

Odpowiedź.

Pytanie z haczykiem.