Wolne wykonywanie procedury na parametrach datowych

Cześć, napotkałem ostatnio na ciekawy przypadek i chciałbym wiedzieć dlaczego tak się dzieje.
Mam procedure z 2 patrametrami datowymi: @dataOd i @dataDo (oba mają typ DATE). W klauzuli 'where' używam ich do zawężenia wyników:

WHERE dataModyfikacji BETWEEN @dataOd AND @dataDo 

dataModyfikacji ma typ datetime. Wykonuje tą procedurę za ostatnie 5 dni (czyli daty 2020-06-18 i 2020-06-22) i mieli mi ok 50 sekund, gdzie w wyniku otrzymuje tylko ok. 11 tys. rekordów.W ramach porównania robie sobie tą procedure ale już bez parametrów, w where używam GETDATE() - robię to aby sprawdzić czy w taki sposób zadziała szybciej:

WHERE dataModyfikacji >= GETDATE()-5

Okazuje się, że zapytanie wykonało mi się w kilka sekund. I teraz moje pytanie - czemu używając parametrów datowych z BETWEEN tak zamula zapytanie? I najważniejsze - co mogę zrobić aby przy tej parametryzacji na datach wykonywało mi się szybciej?
Próbowałem z WITH RECOMPILE ale nie przyspieszyło (chodzi o parameter sniffing). Jakby co mam pozakładane wszystkie indeksy (sprawdzałem na Execution Plan).

I pytanie na koniec - czy lepiej w where konwertowac datetime na date czy nie ma to znaczenia, tzn:

WHERE dataModyfikacji >= '2020-06-15' 

czy

WHERE CONVERT(DATE,dataModyfikacji) >= '2020-06-15' 

Zobacz index na kolumnie dataModyfikacji i jeżeli występuje to wystarczy zobaczyć czy w pierwszym zapytaniu z niego korzysta. Jeżeli prowónujesz date do napisu to baza musi coś skonwertować aby porównać wyniki. Jeżeli zrzutuje date na znaki z kolumny na której jest index to nie skorzysta z indexu.
Co do pytania odnośnie konweretowania, właśnie z powodu problemów z indexem zawsze warto konwertować parametr do typu jaki jest w kolumnie.
p.s.
Wyjątkiem jest index funkcyjny.

A co daje

WHERE dataModyfikacji >= GETDATE()-5 and dataModyfikacji <= GETDATE()-1

?

biela_ robiłem cos takiego ale na parametrach i dalej chodzi wolno.
oracledev zmieniłem typ parametru na datetime i poszło troche szybciej ale i tak sporo wolniej niz przy GETDATE(). Napisałes, że: jeżeli zrzutuje date na znaki z kolumny na której jest index to nie skorzysta z indexu - skąd mam wiedzieć że rzutuje na znaki a nie datę?

Moja kolumna dataModyfikacji ma typ datetime (np. 2020-06-23 1423:000). Jako parametr przekazuję (z poziomu Excela) datę np. 2020-06-23. W procedurze składowanej (jak podejrzę we właściwościach zapytania w Excelu) podstawia mi się parametr jako '2020-06-23' - także bez czasu. Nie wiem czy to ma znaczenie ale zastanawia mnie fakt, czy oby na pewno przekazywana wartość z poziomu Excela do procedury jest datą czy traktowana jest jako tekst (mimo, że z poziomu kodu VBA deklaruję ten parametr jako Date)

muskagap napisał(a):

Nie wiem czy to ma znaczenie ale zastanawia mnie fakt, czy oby na pewno przekazywana wartość z poziomu Excela do procedury jest datą czy traktowana jest jako tekst (mimo, że z poziomu kodu VBA deklaruję ten parametr jako Date)

A parametr w procedurze powinieneś mieć datetime, a co masz?

W procedurze mam datetime

musiałbyś pokazać plan zapytania dla getdate() i dla parametru

Ok, spraedziłem raz jeszcze Plan (mam w procedurze kilka zapytań) i de facto mam w jednym miejscu Index Scan. W wersji z GETDATE() koszt to 28%:

A w wersji z parametrami jest to 63%:

Tu Execution Plan podpowiada mi, że w jednym z zapytań procedury brakuje indexu (odnośnie screenów wyżej). Ja go zakładałem - po założeniu okazało się że znowu wyrzuca mi brak indexu na 3 zapytaniach procedury (niemal identycznego, różnica to dodanie extra kolumny). Założyłem więc i ten. Potem wyrzuca mi, że jeszcze brakuje Indexu, znowu bardzo podobnego do tego tyle, że tym razem ma to być kombinacja tego co jest w INCLUDE z tym co na indeksie głównym. Także w kółko każe mi zakłądać indeksy odnoszące się de facto do tych samych kolumn tylko, że raz sugeruje mniej, innym razem więcej a znowu innym razem ich n-tą kombinację. Robi się to goteskowe - będę miał 7 indeksów na jednej tabelce gdzie każdy będzie praktycznie taki sam.

muskagap napisał(a):

Robi się to goteskowe - będę miał 7 indeksów na jednej tabelce gdzie każdy będzie praktycznie taki sam.

Nie znam SQL Servera, ale na Oraclu było to normalne. Niektórzy mistrzowie Oracla mówili że nawet kolejność kolumn w indeksie ma znaczenie.

Trafił mi się HashTable z Warningiem, z tego co wiem Hash Table nie są specjalnie pożądane w Planie, co to dokładnie oznacza i jak można 'zamienić' go na Nested lub Merged?

SQL Server nie jest moim konikiem, ale:

1. patrzę na te plany wykonania -> czytasz 1 135 961 rekordów, zwracasz 834 411 używając indeksu.... Dobrze rozumiem, że w wejściowej masz jakieś 100 milionów rekordów? Inaczej używanie indeksu, który zwracałby ~73% danych byłoby niezłym WTF.

2. Wyizolowałeś konkretne zapytania i wiesz, że różnica wynika ze sposobu w jaki przekazujesz parametry, a nie z czegos innego? (np. inne zapytania w danej procedurze?)

1. Tak to wygląda aczkolwiek fakt, teraz widze że jest sporo zwracanych a jednak powinno byc mniej, sprawdze to
2. Tak, sprawdzałem inne i róznica zasadnicza jest przy uzyciu lub nie parametru

Próbowałeś jawnie konwertować parametr procedury do innego typu danych?

WHERE dataModyfikacji>= CONVERT(datetime,dataZParametruProcedury)

Powodzenia.

Jawnie konwertowałem tylko 'dataModyfikacji' w wherze ale wiadomo, mało to wydajne. Tak nie konwertowałem, spróbuję