Indeks kolumny Persisted Computed wymaga wyszukiwania klucza, aby uzyskać kolumny w wyliczonym wyrażeniu

Mam utrwaloną kolumnę obliczeniową na stole, która jest po prostu złożona z połączonych kolumn, np

CREATE TABLE dbo.T 
(   
    ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY,
    A VARCHAR(20) NOT NULL,
    B VARCHAR(20) NOT NULL,
    C VARCHAR(20) NOT NULL,
    D DATE NULL,
    E VARCHAR(20) NULL,
    Comp AS A + '-' + B + '-' + C PERSISTED NOT NULL 
);

W tym Compnie jest unikalny, a D jest prawidłowy od daty każdej kombinacji A, B, C, dlatego używam następującego zapytania, aby uzyskać datę końcową dla każdej A, B, C(w zasadzie następnej daty początkowej dla tej samej wartości Comp):

SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1
WHERE   t1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY t1.Comp;

Następnie dodałem indeks do kolumny obliczanej, aby pomóc w tym zapytaniu (i innych):

CREATE NONCLUSTERED INDEX IX_T_Comp_D ON dbo.T (Comp, D) WHERE D IS NOT NULL;

Plan zapytania mnie jednak zaskoczył. Pomyślałbym, że skoro mam klauzulę where stwierdzającą to D IS NOT NULLi sortuję według Comp, i nie odnoszę się do żadnej kolumny poza indeksem, to że indeks w kolumnie obliczeniowej może być użyty do skanowania t1 i t2, ale widziałem indeks klastrowany skandować.

Zmusiłem więc użycie tego indeksu, aby sprawdzić, czy przyniósł lepszy plan:

SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1 WITH (INDEX (IX_T_Comp_D))
WHERE   t1.D IS NOT NULL
ORDER BY t1.Comp;

Co dało ten plan

To pokazuje, że używane jest wyszukiwanie klucza, którego szczegółami są:

Teraz, zgodnie z dokumentacją SQL-Server:

Można utworzyć indeks w kolumnie obliczanej, która jest zdefiniowana za pomocą deterministycznego, ale nieprecyzyjnego wyrażenia, jeśli kolumna jest oznaczona jako PERSISTED w instrukcji CREATE TABLE lub ALTER TABLE. Oznacza to, że Aparat baz danych przechowuje obliczone wartości w tabeli i aktualizuje je, gdy są aktualizowane inne kolumny, od których zależy obliczona kolumna. Aparat baz danych korzysta z tych utrwalonych wartości podczas tworzenia indeksu w kolumnie i do odwołania do indeksu w zapytaniu. Ta opcja umożliwia utworzenie indeksu w kolumnie obliczeniowej, gdy aparat bazy danych nie może dokładnie udowodnić, czy funkcja zwracająca wyrażenia w kolumnie obliczeniowej, w szczególności funkcja CLR utworzona w .NET Framework, jest zarówno deterministyczna, jak i precyzyjna.

Jeśli więc, jak mówią doktorzy: „ Aparat baz danych przechowuje obliczone wartości w tabeli” , a wartość jest również przechowywana w moim indeksie, dlaczego wymagane jest wyszukiwanie klucza, aby uzyskać A, B i C, gdy nie ma w nich odwołania zapytanie w ogóle? Zakładam, że są one używane do obliczania Comp, ale dlaczego? Ponadto, dlaczego zapytanie może korzystać z indeksu na t2, ale nie na t1?

Zapytania i DDL w SQL Fiddle

Uwaga: otagowałem SQL Server 2008, ponieważ jest to wersja, na której jest mój główny problem, ale otrzymuję to samo zachowanie w 2012 roku.

Dlaczego wymagane jest wyszukiwanie klucza, aby uzyskać A, B i C, skoro w ogóle nie są przywoływane w zapytaniu? Zakładam, że są one używane do obliczania Comp, ale dlaczego?

Kolumny A, B, and C są przywoływane w planie zapytań - są używane przez wyszukiwanie T2.

Ponadto, dlaczego zapytanie może wykorzystywać indeks na t2, ale nie na t1?

Optymalizator zdecydował, że skanowanie indeksu klastrowego jest tańsze niż skanowanie przefiltrowanego indeksu nieklastrowego, a następnie wykonanie wyszukiwania w celu pobrania wartości dla kolumn A, B i C.

Wyjaśnienie

Prawdziwe pytanie brzmi: dlaczego optymalizator czuł potrzebę odzyskania A, B i C w ogóle dla wyszukiwania indeksu. Spodziewalibyśmy się, że odczytuje Compkolumnę przy użyciu skanowania indeksu nieklastrowego, a następnie wykonuje wyszukiwanie w tym samym indeksie (alias T2), aby zlokalizować rekord Top 1.

Optymalizator zapytań rozszerza obliczone odwołania do kolumn przed rozpoczęciem optymalizacji, aby dać szansę oszacowania kosztów różnych planów zapytań. W przypadku niektórych zapytań rozszerzenie definicji kolumny obliczeniowej umożliwia optymalizatorowi znalezienie bardziej wydajnych planów.

Gdy optymalizator napotyka skorelowane podzapytanie, próbuje je „rozwinąć” do postaci, o której łatwiej będzie się przekonać. Jeśli nie można znaleźć bardziej skutecznego uproszczenia, należy przepisać skorelowane podzapytanie jako zastosowanie (sprzężenie skorelowane):

Zdarza się tak, że zastosowanie tego rozwijania powoduje, że logiczne drzewo zapytań staje się formą, która nie działa dobrze z normalizacją projektu (późniejszy etap, który ma na celu dopasowanie wyrażeń ogólnych do kolumn obliczeniowych, między innymi).

W twoim przypadku sposób, w jaki zapytanie jest pisane, współdziała z wewnętrznymi szczegółami optymalizatora, tak że definicja wyrażenia rozszerzonego nie jest dopasowywana z powrotem do kolumny obliczanej, a ty kończysz wyszukiwanie, które odwołuje się do kolumn A, B, and Czamiast kolumny obliczanej,Comp . To jest podstawowa przyczyna.

Obejście

Jednym ze sposobów obejścia tego efektu ubocznego jest napisanie zapytania jako zastosowania ręcznie:

SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
CROSS APPLY
(  
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Niestety, to zapytanie nie będzie korzystało z filtrowanego indeksu, tak jak chcielibyśmy. Test nierówności na kolumnie Dwewnątrz zastosowania odrzuca NULLs, więc pozornie nadmiarowy predykat WHERE T1.D IS NOT NULLjest optymalizowany.

Bez tego wyraźnego predykatu logika filtrowania dopasowującego indeks decyduje, że nie może użyć przefiltrowanego indeksu. Istnieje wiele sposobów obejścia tego drugiego efektu ubocznego, ale najłatwiej jest prawdopodobnie zmienić krzyżowe zastosowanie na zewnętrzne zastosowanie (odzwierciedlając logikę przepisania optymalizatora wykonanego wcześniej na skorelowanym podzapytaniu):

SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
OUTER APPLY
(  
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Teraz optymalizator nie musi korzystać z samego zastosowania przepisywania (więc obliczone dopasowanie kolumn działa zgodnie z oczekiwaniami), a predykat również nie jest optymalizowany, więc filtrowany indeks może być używany do obu operacji dostępu do danych, a wyszukiwanie używa Compkolumny po obu stronach:

Byłoby to na ogół lepsze niż dodawanie A, B i C jako INCLUDEdkolumn w przefiltrowanym indeksie, ponieważ rozwiązuje ono podstawową przyczynę problemu i nie wymaga niepotrzebnego poszerzania indeksu.

Utrwalone kolumny obliczane

Na marginesie, nie jest konieczne oznaczanie kolumny obliczanej jako PERSISTED, jeśli nie masz nic przeciwko powtarzaniu jej definicji z CHECKograniczeniem:

CREATE TABLE dbo.T 
(   
    ID integer IDENTITY(1, 1) NOT NULL,
    A varchar(20) NOT NULL,
    B varchar(20) NOT NULL,
    C varchar(20) NOT NULL,
    D date NULL,
    E varchar(20) NULL,
    Comp AS A + '-' + B + '-' + C,

    CONSTRAINT CK_T_Comp_NotNull
        CHECK (A + '-' + B + '-' + C IS NOT NULL),

    CONSTRAINT PK_T_ID 
        PRIMARY KEY (ID)
);

CREATE NONCLUSTERED INDEX IX_T_Comp_D
ON dbo.T (Comp, D) 
WHERE D IS NOT NULL;

Obliczona kolumna jest wymagana tylko PERSISTEDw tym przypadku, jeśli chcesz użyć NOT NULLograniczenia lub odwołać się Compbezpośrednio do kolumny (zamiast powtarzać jej definicję) w CHECKograniczeniu.

Chociaż może to być trochę współwystępowanie ze względu na sztuczny charakter danych testowych, ponieważ, jak wspomniałeś SQL 2012, próbowałem przepisać:

SELECT  ID,
        Comp,
        D,
        D2 = LEAD(D) OVER(PARTITION BY COMP ORDER BY D)
FROM    dbo.T 
WHERE   D IS NOT NULL
ORDER BY Comp;

Zaowocowało to dobrym, niedrogim planem z wykorzystaniem twojego indeksu i znacznie niższymi odczytami niż inne opcje (i takie same wyniki dla danych testowych).

Podejrzewam, że twoje prawdziwe dane są bardziej skomplikowane, więc mogą istnieć pewne scenariusze, w których to zapytanie zachowuje się semantycznie odmiennie od twojego, ale czasem pokazuje, że nowe funkcje mogą naprawdę zmienić.

Eksperymentowałem z kilkoma bardziej zróżnicowanymi danymi i znalazłem kilka scenariuszy do dopasowania, a niektóre nie:

--Example 1: results matched
TRUNCATE TABLE dbo.t

-- Generate some more interesting test data
;WITH cte AS
(
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT T (A, B, C, D)
SELECT  'A' + CAST( a.rn AS VARCHAR(5) ),
        'B' + CAST( a.rn AS VARCHAR(5) ),
        'C' + CAST( a.rn AS VARCHAR(5) ),
        DATEADD(DAY, a.rn + b.rn, '1 Jan 2013')
FROM cte a
    CROSS JOIN cte b
WHERE a.rn % 3 = 0
 AND b.rn % 5 = 0
ORDER BY 1, 2, 3
GO


-- Original query
SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY D
            )
INTO #tmp1
FROM    dbo.T t1 
WHERE   t1.D IS NOT NULL
ORDER BY t1.Comp;
GO

SELECT  ID,
        Comp,
        D,
        D2 = LEAD(D) OVER(PARTITION BY COMP ORDER BY D)
INTO #tmp2
FROM    dbo.T 
WHERE   D IS NOT NULL
ORDER BY Comp;
GO


-- Checks ...
SELECT * FROM #tmp1
EXCEPT
SELECT * FROM #tmp2

SELECT * FROM #tmp2
EXCEPT
SELECT * FROM #tmp1


Example 2: results did not match
TRUNCATE TABLE dbo.t

-- Generate some more interesting test data
;WITH cte AS
(
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT T (A, B, C, D)
SELECT  'A' + CAST( a.rn AS VARCHAR(5) ),
        'B' + CAST( a.rn AS VARCHAR(5) ),
        'C' + CAST( a.rn AS VARCHAR(5) ),
        DATEADD(DAY, a.rn, '1 Jan 2013')
FROM cte a

-- Add some more data
INSERT dbo.T (A, B, C, D)
SELECT A, B, C, D 
FROM dbo.T
WHERE DAY(D) In ( 3, 7, 9 )


INSERT dbo.T (A, B, C, D)
SELECT A, B, C, DATEADD( day, 1, D )
FROM dbo.T
WHERE DAY(D) In ( 12, 13, 17 )


SELECT * FROM #tmp1
EXCEPT
SELECT * FROM #tmp2

SELECT * FROM #tmp2
EXCEPT
SELECT * FROM #tmp1

SELECT * FROM #tmp2
INTERSECT
SELECT * FROM #tmp1


select * from #tmp1
where comp = 'A2-B2-C2'

select * from #tmp2
where comp = 'A2-B2-C2'

Kiedy próbowałem wykonać te same czynności, otrzymałem inne wyniki. Po pierwsze, mój plan wykonania dla tabeli bez indeksów wygląda następująco:

Jak możemy zobaczyć z Clustered Index Scan (t2), predykat służy do określenia potrzebnych wierszy do zwrócenia (z powodu warunku):

Po dodaniu indeksu, bez względu na to, czy został zdefiniowany przez operatora WITH, czy nie, plan wykonania wyglądał następująco:

Jak widzimy, skanowanie indeksów klastrowych jest zastępowane przez skanowanie indeksów. Jak widzieliśmy powyżej, SQL Server używa kolumn źródłowych kolumny obliczanej, aby wykonać dopasowanie zagnieżdżonego zapytania. Podczas skanowania indeksu klastrowego wszystkie te wartości można uzyskać w tym samym czasie (nie są wymagane żadne dodatkowe operacje). Po dodaniu indeksu filtrowanie niezbędnych wierszy z tabeli (w głównym wyborze) przebiega zgodnie z indeksem, ale wartości kolumn źródłowych dla kolumny obliczanej compnadal wymagają pobrania (ostatnia operacja Zagnieżdżona pętla) .

Z tego powodu używana jest operacja Wyszukiwanie klucza - w celu uzyskania danych z kolumn źródłowych obliczonej.

PS Wygląda jak błąd w SQL Server.