Zauważam, że gdy dochodzi do rozlania zdarzeń tempdb (powodujących powolne zapytania), to często szacunki wierszy są dalekie od konkretnego łączenia. Widziałem zdarzenia rozlewania występujące w połączeniach scalania i mieszania, które często zwiększają czas działania 3x do 10x. To pytanie dotyczy sposobu poprawy oszacowań wierszy przy założeniu, że zmniejszy to ryzyko wystąpienia wycieków.
Rzeczywista liczba rzędów 40 tys.
W przypadku tego zapytania plan pokazuje złe oszacowanie wiersza (11,3 wiersza):
select Value
from Oav.ValueArray
where ObjectId = (select convert(bigint, Value) NodeId
from Oav.ValueArray
where PropertyId = 3331
and ObjectId = 3540233
and Sequence = 2)
and PropertyId = 2840
option (recompile);
W przypadku tego zapytania plan pokazuje dobre oszacowanie wiersza (56 tys. Wierszy):
declare @a bigint = (select convert(bigint, Value) NodeId
from Oav.ValueArray
where PropertyId = 3331
and ObjectId = 3540233
and Sequence = 2);
select Value
from Oav.ValueArray
where ObjectId = @a
and PropertyId = 2840
option (recompile);
Czy można dodać statystyki lub wskazówki, aby poprawić szacunki wierszy dla pierwszego przypadku? Próbowałem dodać statystyki z określonymi wartościami filtrów (właściwość = 2840), ale albo nie udało się uzyskać poprawnej kombinacji, albo być może jest ona ignorowana, ponieważ ObjectId jest nieznany w czasie kompilacji i może wybierać średnią dla wszystkich ObjectIds.
Czy jest jakiś tryb, w którym najpierw wykonałby zapytanie z sondy, a następnie użył go do ustalenia szacunków wiersza, czy też musi latać na ślepo?
Ta konkretna właściwość ma wiele wartości (40k) na kilku obiektach i zero na zdecydowanej większości. Byłbym zadowolony z podpowiedzi, w której można określić maksymalną oczekiwaną liczbę wierszy dla danego sprzężenia. Jest to ogólnie problem nawiedzający, ponieważ niektóre parametry mogą być określane dynamicznie jako część złączenia lub byłyby lepiej umieszczone w widoku (brak obsługi zmiennych).
Czy są jakieś parametry, które można dostosować, aby zminimalizować ryzyko wycieków do tempdb (np. Minimalna pamięć na zapytanie)? Solidny plan nie miał wpływu na oszacowanie.
Edytuj 2013.11.06 : Odpowiedź na komentarze i dodatkowe informacje:
Oto obrazy planu zapytania. Ostrzeżenia dotyczą predykatu liczności / poszukiwania za pomocą funkcji convert ():
Według komentarza @Aarona Bertranda próbowałem zastąpić metodę convert () jako test:
create table Oav.SeekObject (
LookupId bigint not null primary key,
ObjectId bigint not null
);
insert into Oav.SeekObject (
LookupId, ObjectId
) VALUES (
1, 3540233
)
select Value
from Oav.ValueArray
where ObjectId = (select ObjectId
from Oav.SeekObject
where LookupId = 1)
and PropertyId = 2840
option (recompile);
Jako dziwny, ale udany punkt zainteresowania, pozwolił również na zwarcie wyszukiwania:
select Value
from Oav.ValueArray
where ObjectId = (select ObjectId
from Oav.ValueArray
where PropertyId = 2840
and ObjectId = 3540233
and Sequence = 2)
and PropertyId = 2840
option (recompile);
Oba z nich zawierają poprawne wyszukiwanie klucza, ale tylko te pierwsze zawierają „wynik” ObjectId. Wydaje mi się, że to wskazuje, że drugim jest zwarcie?
Czy ktoś może zweryfikować, czy kiedykolwiek wykonywane są sondy jednorzędowe, aby pomóc w oszacowaniu rzędu? Wydaje się niewłaściwe ograniczanie optymalizacji do szacunków histogramu tylko wtedy, gdy jednorzędowe wyszukiwanie PK może znacznie poprawić dokładność wyszukiwania w histogramie (szczególnie jeśli istnieje potencjał rozlania lub historia). Gdy w prawdziwym zapytaniu występuje 10 takich podłączy, idealnie byłyby one realizowane równolegle.
Na marginesie, ponieważ sql_variant przechowuje swój typ podstawowy (SQL_VARIANT_PROPERTY = BaseType) w samym polu, oczekiwałbym, że funkcja convert () będzie prawie bezkosztowa, pod warunkiem, że jest „bezpośrednio” konwertowalna (np. Nie ciąg znaków na dziesiętne, ale raczej int int, a może int to bigint). Ponieważ nie jest to znane w czasie kompilacji, ale może być znane użytkownikowi, być może funkcja „AssumeType (typ, ...)” dla zmiennych_sql pozwoliłaby na ich bardziej przejrzyste traktowanie.
declare @a bigint = tego, co zrobiłeś, wydaje mi się naturalnym rozwiązaniem, dlaczego jest to niedopuszczalne?
CONVERT()w kolumnach, a następnie do ich połączenia. To z pewnością nie jest wydajne w większości przypadków. W tym konkretnym przypadku należy przekonwertować tylko jedną wartość, więc prawdopodobnie nie jest to problem, ale jakie indeksy masz na stole? Projekty EAV zwykle działają dobrze, tylko przy odpowiednim indeksowaniu (co oznacza wiele indeksów w zwykle wąskich tabelach).
