Funkcje okna powodują okropny plan wykonania, gdy są wywoływane z widoku z zewnętrzną parametryzowaną klauzulą ​​„gdzie”


10

Miałem ten problem dawno temu, znalazłem obejście, które mi pasowało i zapomniałem o tym.

Ale teraz jest takie pytanie na SO, więc jestem skłonny poruszyć ten problem.

Istnieje widok, który łączy kilka tabel w bardzo prosty sposób (zamówienia + wiersze zamówień).

Zapytanie bez whereklauzuli powoduje zwrócenie kilku milionów wierszy.
Jednak nikt nigdy tak go nie nazywa. Zwykle jest to zapytanie

select * from that_nasty_view where order_number = 123456;

Zwraca około 10 rekordów z 5m.

Ważna rzecz: widok zawiera funkcję okna rank(), która jest dokładnie podzielona na pola, za pomocą których widok jest zawsze odpytywany:

rank() over (partition by order_number order by detail_line_number)

Teraz, jeśli ten widok jest zapytany o dosłowne parametry w ciągu zapytania, dokładnie tak jak pokazano powyżej, natychmiast zwraca wiersze. Plan wykonania jest w porządku:

  • Szukaj indeksu w obu tabelach przy użyciu indeksów order_number(zwraca 10 wierszy).
  • Obliczanie okien nad zwróconym drobnym wynikiem.
  • Wybieranie

Jednak gdy widok jest wywoływany w sparametryzowany sposób, robi się nieprzyjemnie:

  • Index scanwe wszystkich tabelach ignorując indeksy. Zwraca 5m wierszy.
  • Ogromne dołączenie.
  • Obliczanie okien dla wszystkich partitions (około 500k okien).
  • Filter wziąć 10 rzędów z 5 m.
  • Wybierz

Dzieje się tak we wszystkich przypadkach, gdy zaangażowane są parametry. Może to być SSMS:

declare @order_number int = 123456;
select * from that_nasty_view where order_number = @order_number;

Może to być klient ODBC, taki jak Excel:

select * from that_nasty_view where order_number = ?

Lub może to być dowolny inny klient, który używa parametrów, a nie konkatenacji SQL.

Jeśli funkcja okna zostanie usunięta z widoku, działa idealnie szybko, niezależnie od tego, czy jest pytana o parametry, czy nie.

Moim obejściem było usunięcie funkcji obrażającej i ponowne zastosowanie jej na późniejszym etapie.

Ale co daje? Czy to naprawdę błąd w działaniu funkcji okna przez SQL Server 2008?


numer_ zamówienia jest kluczem podstawowym? Typy danych dopasowania kolumn i parametrów?
gbn

order_numbernie jest kluczem podstawowym. Jest int not nullw nim indeks nieklastrowany w obu tabelach.
GSerg

5
SQL Server 2005 miał problemy z przepychaniem predykatów w tym obszarze. Myślałem, że teraz zostały naprawione. BTW twój przykład TSQL używa zmiennej, a nie parametru. Czy dodanie OPTION (RECOMPILE)pomocy?
Martin Smith

1
@GSerg - Więc na złym planie z filtrem jako ostatnim ma około 5 milionów wierszy do filtra i około 10 wierszy pasujących do rzeczywistych? Jeśli tak, to może problem z przepychaniem predykatów wciąż nie został całkowicie rozwiązany.
Martin Smith

Odpowiedzi:


5

Wydaje się, że jest to długotrwały problem, który utrzymuje się na nowo w takiej czy innej formie i nadal występuje w SQL Server 2012.

Niektóre posty na ten temat są

Wszystkie bieżące wersje programu SQL Server do 2012 roku włącznie nie są w stanie wypchnąć filtra w grupie partycjonowania poza projekt sekwencji dla sparametryzowanego predykatu, chyba że option(recompile)jest używany (jeśli 2008+).

Alternatywą dla recompilepodpowiedzi byłoby przepisanie zapytania w celu użycia sparametryzowanego wbudowanego TVF, zgodnie z sugestią @ a1ex07)


Właśnie miałem tę sprawę również w SQL Server 2014
Guillaume86

3

Spróbowałbym zastąpić widok udf o wartości tabeli. W ten sposób najpierw filtruje rekordy, a następnie stosuje funkcję okna. Ta funkcja może akceptować parametr tabeli, dzięki czemu można przekazać order_numberdo niego wiele parametrów


Jeszcze jedno obejście, tak. Nie mogłem tego jednak zrobić, ponieważ nie wszyscy klienci byli w stanie korzystać z funkcji wycenianej w tabeli.
GSerg

Dlaczego? Nie jestem w 100% pewien, ale myślę, że wystarczy trochę zmienić zapytanie na coś w styluSELECT * FROM my_funct(12345)
a1ex07

Jednym z wymagań było to, że zapytanie jest zużywane przez użytkowników końcowych korzystających z Excela (czyli przez MS Query), a MS Query nie pozwoli ci tego zrobić, przynajmniej w wersjach do 2003 roku.
GSerg

it will filter records first, and then apply window functionjest nieprawidłowe. Nie ma deterministycznego nakazu egzekucji
Remus Rusanu,
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.