Czy istnieje sposób na sprawdzenie wartości zerowej zmiennej w klauzuli WHERE tylko raz?

Mam zapytanie na dużej tabeli, która wygląda następująco:

declare @myIdParam int = 1

select * 
from myTable
where (@myIdParam is null or myTable.Id = @myIdParam)

Istnieje kilka podobnych warunków warunkowych w klauzuli where, a także wiele sprzężeń, ale jest to streszczenie.

W efekcie, jeśli @myIdParam ma wartość null, nie chcemy ograniczać wyników za pomocą tego parametru.

Nie jestem profesjonalistą DB, ale z moich testów wynika, że ta kontrola NULL jest wykonywana dla każdego rekordu i nie jest w żaden sposób zoptymalizowana.

Jeśli usunę sprawdzanie wartości NULL i założę, że parametr nie ma wartości NULL, zapytanie jest natychmiast zwracane. W przeciwnym razie zajmuje to do dziesięciu sekund.

Czy istnieje sposób na zoptymalizowanie tego, aby sprawdzanie było wykonywane tylko raz w czasie wykonywania?

sql-server null

— Mistagog
źródło

Spójrz na tę odpowiedź: stackoverflow.com/questions/3415582/... tl; dr useOPTION(RECOMPILE)

— vercelli

@vercelli to załatwia sprawę. Biorąc pod uwagę, że to pytanie dotyczy naprawdę parametrów opcjonalnych, powiedziałbym, że jest to duplikat tego, który połączyłeś.

— Mystagogue

Prawdopodobnie, ale jest to post sprzed 6 lat. Może z SqlServer 2014 lub 2016 istnieje nowe podejście. (Przetestowałem go w 2014 roku bez ponownej kompilacji i wziąłem na zawsze)

— vercelli

Ponieważ twoje zapytanie ma wiele opcjonalnych parametrów, dynamiczny SQL zapewni najlepszą wydajność. Zobacz sommarskog.se/dyn-search.html do dokładnego artykułu na ten temat.

— Dan Guzman

@ DanGuzman przy użyciu Z RECOMPILEM, jak opisano w pytaniu połączonym vercelli, skrócił czas zapytania z niecałej minuty do praktycznie natychmiastowej dzięki wysoce selektywnym kryteriom. Uważam to za najlepszą opcję równoważenia wydajności i czytelności.

— Mystagogue,

Odpowiedzi:

Jednym ze sposobów jest użycie dynamicznego SQL przy użyciu zerowego sprawdzenia, aby opcjonalnie dodać tę część klauzuli where.

declare @myIdParam int = 1
declare @vc_dynamicsql varchar(max)

set @vc_dynamicsql = 'select * from myTable where 1=1'

if @myIdParam is not null
    set @vc_dynamicsql = @vc_dynamicsql + ' and  myTable.Id = @myIdParam'

EXECUTE sp_executesql @vc_dynamicsql

— Mistagog
źródło

Naprawdę wolałbym tego nie robić, ale jest to rozwiązanie. Mam nadzieję, że ktoś przyjdzie ze znacznie lepszym.

— Mystagogue

To najlepszy sposób na obsługę tej klasy zapytań. Odpowiedź stackoverflow, do której odwołuje się @vercelli, zawiera świetne odniesienia do tego, jak to zrobić.

— Max Vernon

Jest to najlepsza metoda, ale zauważyłem, że brakuje parametru @params dla parametru sp_ExecuteSQLi @vc_dynamicsqlparametr musi być a NVARCHAR.

— James Anderson

Za każdym razem, gdy umieszczasz funkcję wokół kolumny `ISNULL (@var, table.col) ', na przykład, usuwasz zdolność SQL do używania indeksu. Jest to naprawdę najlepsza opcja, jeśli chcesz zachować ją w jednym zapytaniu.

@var IS NULL or @var = table.col

W przeciwnym razie masz dwie opcje. Pierwszym z nich jest dynamiczny SQL, a odpowiedź @ Mystagogue jest do tego wystarczająca, w przeciwnym razie możesz wprowadzić dwa zapytania takie jak to:

IF @var is NULL
     SELECT * FROM table
ELSE
     SELECT * FROM table WHERE @var = col

Zarówno w tym formacie, jak i dynamicznym SQL, faktycznie otrzymujesz inny plan zapytań dla każdego zapytania (co potencjalnie da lepszą wydajność).

— Kenneth Fisher
źródło

Sql w pytaniu nie używa ISNULL ani żadnej innej funkcji.

— Mystagogue

@MystagogueI Odniosłem się do usuniętej odpowiedzi.

— Kenneth Fisher

Cóż, możesz:

declare @myIdParam int = 1;

select *
from myTable
where nullif(@myIdParam, myTable.Id) is null;

Należy jednak pamiętać, że ta nullif()funkcja jest zasadniczo zawijana case. To nie jest srebrna kula, która magicznie eliminuje, ORa tym samym przyspiesza zapytanie.

— Roger Wolf
źródło

używanie funkcji w klauzuli where ma negatywny wpływ na wydajność, ponieważ uniemożliwia korzystanie z indeksów (a przynajmniej tak słyszałem)

— Mystagogue

@ Mistagogue, tak - zazwyczaj warunki wyszukiwania nie są dostosowane do SARGable. Niestety, to jedyny sposób, w jaki wiem, jak odpowiedzieć na twoje pytanie bez uciekania się do dynamicznego SQL lub wielu UNIONs. Kiedy miałem dokładnie to zadanie, wybrałem dynamiczny SQL.

— Roger Wolf,