Funkcja wartości tabeli wielostanowiskowej vs funkcja wartości tabeli Inline
Kilka przykładów do pokazania, wystarczy przykryć:
Inline Table Valued
CREATE FUNCTION MyNS.GetUnshippedOrders()
RETURNS TABLE
AS
RETURN SELECT a.SaleId, a.CustomerID, b.Qty
FROM Sales.Sales a INNER JOIN Sales.SaleDetail b
ON a.SaleId = b.SaleId
INNER JOIN Production.Product c ON b.ProductID = c.ProductID
WHERE a.ShipDate IS NULL
GO
Tabela Wielostanowiskowa
CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT)
RETURNS @CustomerOrder TABLE
(SaleOrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderQty INT NOT NULL)
AS
BEGIN
DECLARE @MaxDate DATETIME
SELECT @MaxDate = MAX(OrderDate)
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
INSERT @CustomerOrder
SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b
ON a.SalesOrderID = b.SalesOrderID
INNER JOIN Production.Product c ON b.ProductID = c.ProductID
WHERE a.OrderDate = @MaxDate
AND a.CustomerID = @CustomerID
RETURN
END
GO
Czy jest zaleta używania jednego typu (in-line lub multi) nad drugim? Czy istnieją pewne scenariusze, kiedy jeden jest lepszy od drugiego, czy różnice są czysto składniowe? Zdaję sobie sprawę, że te dwa przykładowe zapytania robią różne rzeczy, ale czy jest jakiś powód, dla którego miałbym je tak pisać?
Czytanie o nich a zalety/różnice nie zostały tak naprawdę wyjaśnione.
6 answers
Badając komentarz Matta, poprawiłem moje pierwotne stwierdzenie. Jest on poprawny, będzie różnica w wydajności między funkcją inline table valued (ITVF) i funkcją Multi-statement table valued (mstvf), nawet jeśli obie po prostu wykonają instrukcję SELECT. SQL Server będzie traktował ITVF nieco jak VIEW, ponieważ obliczy plan wykonania przy użyciu najnowszych statystyk dotyczących danych tabel. MSTVF jest równoznaczne z nadzieniem całej zawartości Twojego Wybierz polecenie do zmiennej tabeli, a następnie połącz się z nią. W związku z tym kompilator nie może używać żadnych statystyk tabel na tabelach w mstvf. Tak więc, wszystkie rzeczy są równe (które rzadko są), ITVF będzie działać lepiej niż MSTVF. W moich testach różnica w wydajności w czasie realizacji była znikoma, jednak z punktu widzenia statystyk była zauważalna.
W Twoim przypadku obie funkcje nie są funkcjonalnie równoważne. Funkcja MSTV za każdym razem wykonuje dodatkowe zapytanie nazywa się i, co najważniejsze, filtruje na ID klienta. W dużym zapytaniu optymalizator nie byłby w stanie skorzystać z innych typów złączeń, ponieważ musiałby wywołać funkcję dla każdego przekazanego customerId. Jednak, jeśli ponownie napisałeś swoją funkcję MSTV w ten sposób:
CREATE FUNCTION MyNS.GetLastShipped()
RETURNS @CustomerOrder TABLE
(
SaleOrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderQty INT NOT NULL
)
AS
BEGIN
INSERT @CustomerOrder
SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
FROM Sales.SalesOrderHeader a
INNER JOIN Sales.SalesOrderHeader b
ON a.SalesOrderID = b.SalesOrderID
INNER JOIN Production.Product c
ON b.ProductID = c.ProductID
WHERE a.OrderDate = (
Select Max(SH1.OrderDate)
FROM Sales.SalesOrderHeader As SH1
WHERE SH1.CustomerID = A.CustomerId
)
RETURN
END
GO
W zapytaniu optymalizator byłby w stanie wywołać tę funkcję raz i zbudować lepszy plan wykonania, ale nadal nie byłby lepszy niż równoważny, nieparametryzowany ITVS lub VIEW.
ITVFs powinien być preferowany niż MSTVFs, gdy jest to możliwe, ponieważ typy danych, nullability i zestawianie z kolumn w tabeli, podczas gdy deklarujesz te właściwości w funkcji wartości tabeli wielostanowiskowej i, co ważne, otrzymasz lepsze plany wykonania z ITVF. Z mojego doświadczenia wynika, że nie znalazłem wielu okoliczności, w których ITVF był lepszym rozwiązaniem niż widok, ale przebieg może się różnić.
Dzięki Mattowi.Dodatek
Odkąd to zobaczyłem niedawno, oto doskonała analiza wykonana przez Wayne ' a Sheffielda porównująca różnicę wydajności między funkcjami Wartościowanymi w tabeli Inline i funkcjami Wielostanowiskowymi.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-05-15 14:42:27
Wewnętrznie, SQL Server traktuje funkcję wartą tabeli inline podobnie jak widok i traktuje funkcję wartą tabeli z wieloma instrukcjami podobną do procedury składowanej.
Gdy wbudowana funkcja o wartości tabelarycznej jest używana jako część zewnętrznego zapytania, procesor zapytań rozszerza definicję UDF i generuje plan wykonania, który uzyskuje dostęp do bazowych obiektów, używając indeksów na tych obiektach.
Dla wielostanowiskowej funkcji wartościowej tabeli, wykonanie plan jest tworzony dla samej funkcji i przechowywany w pamięci podręcznej planu wykonania (po pierwszym uruchomieniu funkcji). Jeśli w większych zapytaniach używane są funkcje o wartości tabeli wielozadaniowej, optymalizator nie wie, co zwraca funkcja, a zatem przyjmuje pewne standardowe założenia - w efekcie zakłada, że funkcja zwróci pojedynczy wiersz, a do zwracanych funkcji będzie można uzyskać dostęp za pomocą skanowania tabeli w stosunku do tabeli z pojedynczym wierszem. wiosłować.
Gdzie funkcje wielostanowiskowe table valued mogą wykonywać się źle, to wtedy, gdy zwracają dużą liczbę wierszy i są z nimi połączone w zewnętrznych zapytaniach. Problemy z wydajnością wynikają przede wszystkim z faktu, że optymalizator stworzy plan, zakładając, że zostanie zwrócony jeden wiersz, który niekoniecznie będzie najbardziej odpowiednim planem.
Jako ogólna zasada stwierdziliśmy, że tam, gdzie to możliwe, funkcje wartościowane w tabeli inline powinny być używane zamiast wielostanowiskowe (gdy UDF będzie używany jako część zewnętrznego zapytania) ze względu na potencjalne problemy z wydajnością.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-04-16 19:20:28
Jest jeszcze jedna różnica. Wbudowana funkcja o wartości tabeli może być wstawiana, aktualizowana i usuwana - podobnie jak widok. Obowiązują podobne ograniczenia - nie można aktualizować funkcji za pomocą agregatów, nie można aktualizować kolumn obliczonych i tak dalej.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-07-25 09:23:51
Twoje przykłady, myślę, że bardzo dobrze odpowiadają na pytanie. Pierwsza funkcja może być wykonana jako pojedynczy select i jest dobrym powodem do używania stylu inline. Druga może być wykonana jako pojedyncza Instrukcja (używając pod-zapytania, aby uzyskać maksymalną datę), ale niektórzy programiści mogą być łatwiejsi do odczytania lub bardziej naturalni, aby zrobić to w wielu instrukcjach, jak to zrobiłeś. Niektóre funkcje po prostu nie mogą być wykonane w jednej instrukcji, a więc wymagają wersji z wieloma instrukcjami.
Proponuję użyć najprostsze (inline), gdy tylko jest to możliwe, i używanie wielu poleceń, gdy jest to konieczne (oczywiście) lub gdy osobiste preferencje/czytelność sprawia, że wirth dodatkowe typowanie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-03-31 15:47:57
Spójrz na porównując funkcje inline i Multi-Statement Table-Valued znajdziesz dobre opisy i Benchmarki wydajności
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-02-17 09:45:01
Jeśli masz zamiar wykonać zapytanie, możesz dołączyć do funkcji Inline table Valued, takiej jak:
SELECT
a.*,b.*
FROM AAAA a
INNER JOIN MyNS.GetUnshippedOrders() b ON a.z=b.z
Jeśli spróbujesz użyć tabeli instrukcji Multi o wartości w podobnym zapytaniu, będziesz miał problemy z wydajnością:
SELECT
x.a,x.b,x.c,(SELECT OrderQty FROM MyNS.GetLastShipped(x.CustomerID)) AS Qty
FROM xxxx x
Ponieważ wykonasz funkcję 1 raz dla każdego zwracanego wiersza, gdy zestaw wyników będzie duży, będzie działał wolniej i wolniej.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-03-31 17:38:23