Jak skonfigurować widok indeksowany podczas WYBORU TOP 1 z ORDER BY z różnych tabel

Usiłuję skonfigurować widok indeksowany w następującym scenariuszu, aby następujące zapytanie zostało wykonane bez dwóch skanów indeksu klastrowego. Ilekroć tworzę widok indeksu dla tego zapytania, a następnie go używam, wydaje się, że ignoruje on każdy indeks, który na niego umieszczam:

    -- +++ THE QUERY THAT I WANT TO IMPROVE PERFORMANCE-WISE +++

    SELECT TOP 1 *
    FROM    dbo.TB_test1 t1
            INNER JOIN dbo.TB_test2 t2 ON t1.PK_ID1 = t2.FK_ID1
    ORDER BY t1.somethingelse1
           ,t2.somethingelse2;


    GO

Konfiguracja tabeli jest następująca:

• dwa stoły
• są połączone wewnętrznym złączeniem przez powyższe zapytanie
• i uporządkowane według kolumny z pierwszej, a następnie kolumny z drugiej tabeli według powyższego zapytania; wybrano tylko TOP 1

• (w skrypcie poniżej znajdują się również wiersze do generowania danych testowych, na wypadek, gdyby to pomogło odtworzyć problem)

  -- +++ TABLE SETUP +++
  
  CREATE TABLE [dbo].[TB_test1]
      (
       [PK_ID1] [INT] IDENTITY(1, 1)  NOT NULL
      ,[something1] VARCHAR(40) NOT NULL
      ,[somethingelse1] BIGINT NOT NULL
          CONSTRAINT [PK_TB_test1] PRIMARY KEY CLUSTERED ( [PK_ID1] ASC )
      );
  
  GO
  
  create TABLE [dbo].[TB_test2]
      (
       [PK_ID2] [INT] IDENTITY(1, 1)  NOT NULL
      ,[FK_ID1] [INT] NOT NULL
      ,[something2] VARCHAR(40) NOT NULL
      ,[somethingelse2] BIGINT NOT NULL
          CONSTRAINT [PK_TB_test2] PRIMARY KEY CLUSTERED ( [PK_ID2] ASC )
      );
  
  GO
  
  ALTER TABLE [dbo].[TB_test2]  WITH CHECK ADD  CONSTRAINT [FK_TB_Test1] FOREIGN KEY([FK_ID1])
  REFERENCES [dbo].[TB_test1] ([PK_ID1])
  GO
  
  ALTER TABLE [dbo].[TB_test2] CHECK CONSTRAINT [FK_TB_Test1]
  
  GO
  
  
  -- +++ TABLE DATA GENERATION +++
  
  -- this might not be the quickest way, but it's only to set up test data
  
  INSERT INTO dbo.TB_test1
          ( something1, somethingelse1 )
  VALUES  ( CONVERT(VARCHAR(40), NEWID())  -- something1 - varchar(40)
            ,ISNULL(ABS(CHECKSUM(NewId())) % 92233720368547758078, 1)   -- somethingelse1 - bigint
            )
  
  GO 100000
  
  RAISERROR( 'Finished setting up dbo.TB_test1', 0, 1) WITH NOWAIT    
  
  GO    
  
  INSERT INTO dbo.TB_test2
          ( FK_ID1, something2, somethingelse2 )
  VALUES  ( ISNULL(ABS(CHECKSUM(NewId())) % ((SELECT MAX(PK_ID1) FROM dbo.TB_test1) - 1), 0) + 1 -- FK_ID1 - int
            ,CONVERT(VARCHAR(40), NEWID())  -- something2 - varchar(40)
            ,ISNULL(ABS(CHECKSUM(NewId())) % 92233720368547758078, 1)   -- somethingelse2 - bigint
            )
  
  GO 100000
  
  RAISERROR( 'Finished setting up dbo.TB_test2', 0, 1) WITH NOWAIT          
  
  GO
  

Widok indeksowany należy prawdopodobnie zdefiniować w następujący sposób, a wynikowe zapytanie TOP 1 znajduje się poniżej. Ale jakich indeksów potrzebuję, aby to zapytanie działało lepiej niż bez widoku indeksowanego?

    CREATE VIEW VI_test
    WITH SCHEMABINDING
    AS
        SELECT  t1.PK_ID1
               ,t1.something1
               ,t1.somethingelse1
               ,t2.PK_ID2
               ,t2.FK_ID1
               ,t2.something2
               ,t2.somethingelse2
        FROM    dbo.TB_test1 t1
                INNER JOIN dbo.TB_test2 t2 ON t1.PK_ID1 = t2.FK_ID1


    GO


    SELECT TOP 1 * FROM dbo.VI_test ORDER BY somethingelse1,somethingelse2


    GO

Wydaje się, że ignoruje każdy indeks, który na niego umieszczam

O ile nie korzystasz z SQL Server Enterprise Edition (lub równoważnie, wersja próbna i programista), musisz użyć WITH (NOEXPAND)odwołania do widoku, aby go użyć. W rzeczywistości, nawet jeśli korzystasz z wersji Enterprise, istnieją dobre powody, aby skorzystać z tej wskazówki .

Bez podpowiedzi optymalizator zapytań (w wersji Enterprise Edition) może dokonać wyboru opartego na kosztach między użyciem zmaterializowanego widoku lub dostępem do tabel podstawowych. Jeśli widok jest tak duży jak tabele podstawowe, obliczenia te mogą faworyzować tabele podstawowe.

Inną ciekawostką jest to, że bez NOEXPANDpodpowiedzi odwołania do widoku są zawsze rozszerzane do zapytania podstawowego przed rozpoczęciem optymalizacji. W miarę postępu optymalizacji optymalizator może, ale nie musi, dopasować rozszerzonej definicji z powrotem do zmaterializowanego widoku, w zależności od wcześniejszych działań optymalizacyjnych. Prawie na pewno nie jest tak w przypadku prostego zapytania, ale wymieniam to dla kompletności.

Tak więc użycie NOEXPANDpodpowiedzi do tabeli jest twoją główną opcją, ale możesz również pomyśleć o zmaterializowaniu kluczy tabeli podstawowej i kolumn potrzebnych do zamówienia w widoku. Utwórz unikalny indeks klastrowy w połączonych kolumnach kluczowych, a następnie oddzielny indeks klastrowany w kolumnach porządkowych.

Zmniejszy to rozmiar zmaterializowanego widoku i ograniczy liczbę automatycznych aktualizacji, które należy wykonać, aby widok był zsynchronizowany z tabelami podstawowymi. Zapytanie można następnie napisać, aby pobrać 1 górny klucz w wymaganej kolejności z widoku (najlepiej z NOEXPAND), a następnie połączyć się z powrotem do tabel podstawowych, aby pobrać pozostałe kolumny za pomocą kluczy z widoku.

Inną odmianą jest skupienie widoku na kolumnach porządkowych i kluczach tabeli, a następnie napisanie zapytania w celu ręcznego pobrania kolumn niewidocznych z tabeli podstawowej za pomocą kluczy. Najlepsza opcja zależy od szerszego kontekstu. Dobrym sposobem na podjęcie decyzji jest przetestowanie go pod kątem rzeczywistych danych i obciążenia.

Podstawowe rozwiązanie

CREATE VIEW VI_test
WITH SCHEMABINDING
AS
    SELECT
        t1.PK_ID1,
        t1.something1,
        t1.somethingelse1,
        t2.PK_ID2,
        t2.FK_ID1,
        t2.something2,
        t2.somethingelse2
    FROM dbo.TB_test1 t1
    INNER JOIN dbo.TB_test2 t2 
        ON t1.PK_ID1 = t2.FK_ID1;
GO
-- Brute force unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq 
ON dbo.VI_test 
    (somethingelse1, somethingelse2, PK_ID1, PK_ID2);
GO
SELECT TOP (1) * 
FROM dbo.VI_test WITH (NOEXPAND)
ORDER BY somethingelse1,somethingelse2;

Plan wykonania:

Korzystanie z indeksu nieklastrowanego

-- Minimal unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq 
ON dbo.VI_test 
    (PK_ID1, PK_ID2)
WITH (DROP_EXISTING = ON);
GO
-- Nonclustered index for ordering
CREATE NONCLUSTERED INDEX ix 
ON dbo.VI_test (somethingelse1, somethingelse2);

Plan wykonania:

Ten plan zawiera wyszukiwanie, ale służy tylko do pobrania pojedynczego wiersza.

Minimalny widok indeksowany

ALTER VIEW VI_test
WITH SCHEMABINDING
AS
    SELECT
        t1.PK_ID1,
        t2.PK_ID2,
        t1.somethingelse1,
        t2.somethingelse2
    FROM dbo.TB_test1 t1
    INNER JOIN dbo.TB_test2 t2 
        ON t1.PK_ID1 = t2.FK_ID1;
GO
-- Unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq 
ON dbo.VI_test 
    (somethingelse1, somethingelse2, PK_ID1, PK_ID2);

Pytanie:

SELECT TOP (1)
    V.PK_ID1,
    TT1.something1,
    V.somethingelse1,
    V.PK_ID2,
    TT2.FK_ID1,
    TT2.something2,
    V.somethingelse2
FROM dbo.VI_test AS V WITH (NOEXPAND)
JOIN dbo.TB_test1 AS TT1 ON TT1.PK_ID1 = V.PK_ID1
JOIN dbo.TB_test2 AS TT2 ON TT2.PK_ID2 = V.PK_ID2
ORDER BY somethingelse1,somethingelse2;

Plan wykonania:

To pokazuje, że klucze tabeli są pobierane (pobieranie pojedynczego wiersza z indeksu klastrowanego widoku w kolejności), a następnie dwa wyszukiwania pojedynczych wierszy w tabelach podstawowych w celu pobrania pozostałych kolumn.