Tabela z wieloma poleceniami a funkcja wyceniana w tabeli

Kilka przykładów do pokazania, po prostu:

Tabela Inline 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

Wartościowana tabela z wieloma wyciągami

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 istnieje korzyść z używania jednego typu (instrukcji w wierszu lub wielu) nad drugim? Czy istnieją pewne scenariusze, w których jeden jest lepszy od drugiego, czy różnice są czysto składniowe? Zdaję sobie sprawę, że dwa przykładowe zapytania robią różne rzeczy, ale czy istnieje powód, dla którego miałbym je tak napisać?

Czytanie o nich i zalety / różnice tak naprawdę nie zostały wyjaśnione.

Badając komentarz Matta, zrewidowałem swoje oryginalne stwierdzenie. Ma rację, będzie istniała różnica w wydajności między wbudowaną funkcją wycenioną w tabeli (ITVF) a wielowątkową funkcją wycenioną w tabeli (MSTVF), nawet jeśli oba wykonają po prostu instrukcję SELECT. SQL Server będzie traktował ITVF w pewien sposóbVIEWw tym, że obliczy plan wykonania przy użyciu najnowszych statystyk dotyczących danych tabel. MSTVF jest równoznaczne z upchnięciem całej zawartości instrukcji SELECT do zmiennej tabeli, a następnie dołączeniem do niej. W związku z tym kompilator nie może używać żadnych statystyk tabel w tabelach w MSTVF. Tak więc, wszystkie rzeczy są równe (którymi rzadko są), ITVF będzie działał lepiej niż MSTVF. W moich testach różnica wydajności w czasie ukończenia była znikoma, jednak z punktu widzenia statystyki była zauważalna.

W twoim przypadku dwie funkcje nie są funkcjonalnie równoważne. Funkcja MSTV wykonuje dodatkowe zapytanie przy każdym wywołaniu i, co najważniejsze, filtruje identyfikator klienta. W dużym zapytaniu optymalizator nie byłby w stanie skorzystać z innych rodzajów sprzężeń, ponieważ musiałby wywoływać funkcję dla każdego przekazanego klienta. Jeśli jednak ponownie napisałeś swoją funkcję MSTV w następujący 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, nie sparametryzowany ITVS lub a VIEW.

ITVF powinny być preferowane nad MSTVF, jeśli jest to wykonalne, ponieważ typy danych, nullability i sortowanie z kolumn w tabeli, podczas gdy deklarujesz te właściwości w funkcji wartościowanej w tabeli z wieloma instrukcjami i, co ważne, uzyskasz lepsze plany wykonania z ITVF. Z mojego doświadczenia nie spotkałem wielu okoliczności, w których ITVF była lepszą opcją niż WIDOK, ale przebieg może się różnić.

Dzięki Matt.

Dodanie

Ponieważ widziałem to niedawno, oto doskonała analiza przeprowadzona przez Wayne'a Sheffielda, porównująca różnicę wydajności między funkcjami tabeli wartości inline a funkcjami wielu instrukcji.

Jego oryginalny post na blogu.

Skopiuj na SQL Server Central

Wewnętrznie SQL Server traktuje wbudowaną funkcję o wartości tabeli podobnie jak widok i traktuje funkcję o wielu tabelach o wartości podobnej do procedury przechowywanej.

Gdy wbudowana funkcja wartościowana w tabeli jest używana jako część zapytania zewnętrznego, procesor zapytań rozszerza definicję UDF i generuje plan wykonania, który uzyskuje dostęp do podstawowych obiektów, przy użyciu indeksów tych obiektów.

W przypadku funkcji cenionej w tabeli z wieloma instrukcjami, plan wykonania jest tworzony dla samej funkcji i zapisywany w pamięci podręcznej planu wykonania (po pierwszym uruchomieniu funkcji). Jeśli funkcje o wartościach w tabeli z wieloma instrukcjami są używane jako część większych zapytań, wówczas optymalizator nie wie, co funkcja zwraca, a zatem przyjmuje pewne standardowe założenia - w efekcie zakłada, że ​​funkcja zwróci pojedynczy wiersz i że zwróci funkcja będzie dostępna za pomocą skanowania tabeli w stosunku do tabeli z pojedynczym wierszem.

Funkcje wycenione w tabeli z wieloma instrukcjami mogą słabo działać, gdy zwracają dużą liczbę wierszy i są łączone w zapytaniach zewnętrznych. Problemy z wydajnością wynikają przede wszystkim z faktu, że optymalizator opracuje plan przy założeniu zwrócenia jednego wiersza, co niekoniecznie będzie najodpowiedniejszym planem.

Zasadniczo ustaliliśmy, że tam, gdzie to możliwe, funkcje o wartościach w tabeli powinny być używane zamiast funkcji wieloskładnikowych (gdy UDF będzie używany jako część zapytania zewnętrznego) z powodu tych potencjalnych problemów z wydajnością.

Jest jeszcze jedna różnica. Wbudowana funkcja wartościowana w tabeli może być wstawiana, aktualizowana i usuwana z - tak jak widok. Obowiązują podobne ograniczenia - nie można zaktualizować funkcji za pomocą agregatów, nie można zaktualizować kolumn obliczeniowych itp.

Myślę, że twoje przykłady bardzo dobrze odpowiadają na pytanie. Pierwszą funkcję można wykonać jako pojedyncze zaznaczenie i jest to dobry powód do używania stylu wbudowanego. Druga może być prawdopodobnie wykonana jako pojedyncza instrukcja (przy użyciu kwerendy podrzędnej, aby uzyskać maksymalną datę), ale niektórzy koderzy mogą łatwiej czytać lub bardziej naturalnie robić to w wielu instrukcjach, tak jak to zrobiliście. Niektóre funkcje po prostu nie mogą być wykonane w jednej instrukcji, dlatego wymagają wersji z wieloma instrukcjami.

Sugeruję używanie najprostszego (wbudowanego), gdy tylko jest to możliwe, i stosowanie wielu instrukcji, gdy jest to konieczne (oczywiście) lub gdy osobiste preferencje / czytelność sprawiają, że jest to dodatkowe pisanie na klawiaturze.

spójrz na Porównanie funkcji wycenianych w tabeli i wielu instrukcji z tabelami , gdzie można znaleźć dobre opisy i testy wydajności

Nie testowałem tego, ale funkcja wielu instrukcji buforuje zestaw wyników. Mogą zdarzyć się przypadki, gdy optymalizator zbyt wiele dzieje się, aby wstawić funkcję. Załóżmy na przykład, że masz funkcję, która zwraca wynik z różnych baz danych w zależności od tego, co podajesz jako „numer firmy”. Zwykle można utworzyć widok ze związkiem, a następnie filtrować według numeru firmy, ale okazało się, że czasami serwer SQL odciąga cały związek i nie jest wystarczająco inteligentny, aby wywołać jedną opcję select. Funkcja tabeli może mieć logikę pozwalającą wybrać źródło.

Innym przypadkiem użycia funkcji wieloliniowej byłoby obejście serwera SQL przed wypychaniem klauzuli where.

Na przykład mam tabelę z nazwami tabel, a niektóre nazwy tabel są sformatowane jak C05_2019 i C12_2018, a wszystkie tabele sformatowane w ten sposób mają ten sam schemat. Chciałem połączyć wszystkie te dane w jedną tabelę i przeanalizować 05 i 12 w kolumnie CompNo i 2018,2019 w kolumnie roku. Istnieją jednak inne tabele, takie jak ACA_StupidTable, których nie mogę wyodrębnić CompNo i CompYr i gdybym spróbował, wystąpiłby błąd konwersji. Tak więc moje zapytanie składało się z dwóch części: wewnętrznego zapytania, które zwróciło tylko tabele sformatowane jak „C_______”, a następnie zewnętrzne zapytanie wykonało konwersję podciągu i int. tj. Cast (podciąg (2, 2) jako int) jako CompNo. Wszystko wygląda dobrze, z wyjątkiem tego, że serwer SQL postanowił umieścić moją funkcję Cast przed filtrowaniem wyników, więc pojawia się błąd konwersji szyfrowania myśli. Funkcja tabeli z wieloma instrukcjami może temu zapobiec,

Może w bardzo skondensowany sposób. ITVF (inline TVF): więcej, jeśli jesteś osobą DB, to rodzaj sparametryzowanego widoku, weź pojedynczy WYBÓR st

MTVF (Multi-statement TVF): Deweloper, tworzy i ładuje zmienną tabelową.

jeśli masz zamiar wykonać zapytanie, możesz dołączyć do swojej funkcji Inline Table Valued, takiej jak:

SELECT
    a.*,b.*
    FROM AAAA a
        INNER JOIN MyNS.GetUnshippedOrders() b ON a.z=b.z

spowoduje to niewielkie obciążenie i będzie działało dobrze.

jeśli spróbujesz użyć tabeli Multi Statement Valued w podobnym zapytaniu, wystąpią problemy z wydajnością:

SELECT
    x.a,x.b,x.c,(SELECT OrderQty FROM MyNS.GetLastShipped(x.CustomerID)) AS Qty
    FROM xxxx   x

ponieważ funkcja będzie wykonywana 1 raz dla każdego zwracanego wiersza, ponieważ zestaw wyników staje się większy, będzie działał coraz wolniej.