Mam problem ze zrozumieniem, dlaczego SQL Server decyduje się na wywołanie funkcji zdefiniowanej przez użytkownika dla każdej wartości w tabeli, mimo że należy pobrać tylko jeden wiersz. Rzeczywisty SQL jest o wiele bardziej złożony, ale udało mi się zredukować problem do tego stopnia:
select
S.GROUPCODE,
H.ORDERCATEGORY
from
ORDERLINE L
join ORDERHDR H on H.ORDERID = L.ORDERID
join PRODUCT P on P.PRODUCT = L.PRODUCT
cross apply dbo.GetGroupCode (P.FACTORY) S
where
L.ORDERNUMBER = 'XXX/YYY-123456' and
L.RMPHASE = '0' and
L.ORDERLINE = '01'
W przypadku tego zapytania SQL Server decyduje się wywołać funkcję GetGroupCode dla każdej wartości istniejącej w tabeli PRODUCT, nawet jeśli szacunkowa i rzeczywista liczba wierszy zwróconych z ORDERLINE wynosi 1 (jest to klucz podstawowy):
Ten sam plan w Eksploratorze planów, pokazujący liczbę wierszy:
Stoły:
ORDERLINE: 1.5M rows, primary key: ORDERNUMBER + ORDERLINE + RMPHASE (clustered)
ORDERHDR: 900k rows, primary key: ORDERID (clustered)
PRODUCT: 6655 rows, primary key: PRODUCT (clustered)
Indeks używany do skanowania to:
create unique nonclustered index PRODUCT_FACTORY on PRODUCT (PRODUCT, FACTORY)Ta funkcja jest w rzeczywistości nieco bardziej złożona, ale to samo dzieje się z fikcyjną funkcją składającą się z wielu instrukcji:
create function GetGroupCode (@FACTORY varchar(4))
returns @t table(
TYPE varchar(8),
GROUPCODE varchar(30)
)
as begin
insert into @t (TYPE, GROUPCODE) values ('XX', 'YY')
return
end
Byłem w stanie „naprawić” wydajność, zmuszając serwer SQL do pobrania 1 najlepszego produktu, chociaż 1 to maksimum, jakie kiedykolwiek można znaleźć:
select
S.GROUPCODE,
H.ORDERCAT
from
ORDERLINE L
join ORDERHDR H
on H.ORDERID = M.ORDERID
cross apply (select top 1 P.FACTORY from PRODUCT P where P.PRODUCT = L.PRODUCT) P
cross apply dbo.GetGroupCode (P.FACTORY) S
where
L.ORDERNUMBER = 'XXX/YYY-123456' and
L.RMPHASE = '0' and
L.ORDERLINE = '01'
Następnie kształt planu również się zmienia i jest czymś, czego się spodziewałem:
Wydawało mi się również, że indeks PRODUCT_FACTORY jest mniejszy niż indeks klastrowany PRODUCT_PK miałby wpływ, ale nawet przy zmuszaniu zapytania do użycia PRODUCT_PK, plan jest nadal taki sam jak oryginalny, z 6655 wywołaniami funkcji.
Jeśli całkowicie pomijam ORDERHDR, wówczas plan zaczyna się od zagnieżdżonej pętli między ORDERLINE a PRODUCT, a funkcja jest wywoływana tylko raz.
Chciałbym zrozumieć, co może być tego przyczyną, ponieważ wszystkie operacje są wykonywane przy użyciu kluczy podstawowych i jak to naprawić, jeśli dzieje się to w bardziej złożonym zapytaniu, którego nie można tak łatwo rozwiązać.
Edycja: Utwórz zestawienia tabel:
CREATE TABLE dbo.ORDERHDR(
ORDERID varchar(8) NOT NULL,
ORDERCATEGORY varchar(2) NULL,
CONSTRAINT ORDERHDR_PK PRIMARY KEY CLUSTERED (ORDERID)
)
CREATE TABLE dbo.ORDERLINE(
ORDERNUMBER varchar(16) NOT NULL,
RMPHASE char(1) NOT NULL,
ORDERLINE char(2) NOT NULL,
ORDERID varchar(8) NOT NULL,
PRODUCT varchar(8) NOT NULL,
CONSTRAINT ORDERLINE_PK PRIMARY KEY CLUSTERED (ORDERNUMBER,ORDERLINE,RMPHASE)
)
CREATE TABLE dbo.PRODUCT(
PRODUCT varchar(8) NOT NULL,
FACTORY varchar(4) NULL,
CONSTRAINT PRODUCT_PK PRIMARY KEY CLUSTERED (PRODUCT)
)
