Table Valued Function Killing My Query Performance

Dzieje się tak dlatego, że wartość tabeli z wieloma instrukcjami UDF nie może być przetwarzana w linii z resztą stanu SQL, w którym jest używana, i dlatego nie może być częścią planu Cache instrukcji.. Oznacza to, że musi być skompilowany oddzielnie od reszty SQL, w którym jest używany, w kółko, dla każdego wiersza w wynikowym zestawie wygenerowane przez zapytanie.

An Inline Tabela wartości UDF, otoh, jest przetwarzany i kompilowany wraz z sql, w którym jest używany, i staje się więc część planu cache i tylko jest przetwarzany i kompilowany raz, bez względu na to, ile wierszy wygenerujesz.

Naprawdę niemożliwe, aby odpowiedzieć definitywnie bez więcej informacji. Jednak, ponieważ lubię brać szalone pchnięcia w ciemności . . .

Zmienne tabeli nie mogą być optymalizowane przez silnik--silnik zawsze "zakłada", że zmienna tabeli ma tylko jeden wiersz, gdy generuje plan wykonania. To jest jeden z powodów, dla których możesz widzieć dziwne wyniki.

W przypadku użycia UDF o wartości tabeli z wieloma instrukcjami, UDF jest uruchamiany do końca, zanim jego wyniki będą mogły zostać użyte przez wywołującego. Dzięki wbudowanemu w tabelę UDF serwer SQL zasadniczo rozszerza UDF do wywołującego zapytanie, podobnie jak rozszerzenie makra . Ma to m.in. następujące implikacje:

• klauzula WHERE wywołującego zapytanie może być interpolowana bezpośrednio do wartościowego w wierszu tabeli UDF, ale nie do wielostanowiskowego UDF. Tak więc, jeśli twój wartościowy UDF generuje wiele wierszy, które byłyby filtrowane przez klauzulę WHERE wywołującego zapytanie, optymalizator zapytań może zastosować klauzulę WHERE bezpośrednio do wartościowego w tabeli wiersza UDF, ale nie do wielozadaniowego UDF.
• inline table-valued UDF zachowuje się tak, jak sparametryzowany VIEW, gdyby SQL Server miał taką koncepcję, podczas gdy multi-statement table-valued UDF zachowywałby się tak, jakbyś wypełnił, a następnie użył zmiennej table w zapytaniu.

Jeśli twój UDF zwraca wiele wierszy i jest wsparty na stole, wyobrażam sobie, że to może być miejsce, skąd pochodzi skanowanie tabeli. Albo dodaj więcej parametrów do swojego UDF, aby umożliwić wywołującemu ograniczenie jego rozmiaru wyniku, albo spróbuj przeformułować go jako wartościowy w tabeli UDF z pomocą znajomych, takich jak UNION et al. Za wszelką cenę unikałbym wielostanowiskowych, wycenianych w tabeli UDFs, chyba że rozmiar wyniku jest znany tylko z kilku wierszy i , trudno jest uzyskać wymagane wyniki za pomocą logiki opartej na zestawach.

Na SQL Server 2014 udało nam się rozwiązać nasz problem, wstawiając dane funkcji wartości tabeli do tabeli temp, a następnie wykonując join na niej. Zamiast łączenia bezpośrednio z funkcją wartości tabeli.

-- SLOW QUERY (2 min):

DECLARE @id INT = 1;

SELECT * 
FROM [data].[someTable] T
INNER JOIN [data].[tableValueFunction](@id) TVF ON TVF.id = T.id;

-- FAST QUERY (4 sec):

DECLARE @id INT = 1;

SELECT * 
INTO #tableValueFunction
FROM [data].[tableValueFunction](@id) TVF

SELECT * 
FROM [data].[someTable] T
INNER JOIN #tableValueFunction TVF ON TVF.id = T.id;