Popraw wydajność zapytania za pomocą IN ()

Mam następujące zapytanie SQL:

SELECT
  Event.ID,
  Event.IATA,
  Device.Name,
  EventType.Description,
  Event.Data1,
  Event.Data2
  Event.PLCTimeStamp,
  Event.EventTypeID
FROM
  Event
INNER JOIN EventType ON EventType.ID = Event.EventTypeID
INNER JOIN Device ON Device.ID = Event.DeviceID
WHERE
  Event.EventTypeID IN (3, 30, 40, 41, 42, 46, 49, 50)
  AND Event.PLCTimeStamp BETWEEN '2011-01-28' AND '2011-01-29'
  AND Event.IATA LIKE '%0005836217%'
ORDER BY Event.ID;

Mam również indeks Eventtabeli dla kolumny TimeStamp. Rozumiem, że ten indeks nie jest używany z powodu IN()instrukcji. Więc moje pytanie: czy istnieje sposób na utworzenie indeksu dla tej konkretnej IN()instrukcji w celu przyspieszenia tego zapytania?

Próbowałem również dodać Event.EventTypeID IN (2, 5, 7, 8, 9, 14)jako filtr do indeksu TimeStamp, ale patrząc na plan wykonania, wydaje się, że nie używa tego indeksu. Wszelkie sugestie lub wgląd w to będą bardzo mile widziane.

Poniżej znajduje się plan graficzny:

A tutaj jest link do pliku .sqlplan .

Podane tabele o następującej formie ogólnej:

CREATE TABLE Device 
(
    ID integer PRIMARY KEY
);

CREATE TABLE EventType
(
    ID integer PRIMARY KEY, 
    Name nvarchar(50) NOT NULL
);

CREATE TABLE [Event]
(
    ID integer PRIMARY KEY, 
    [TimeStamp] datetime NOT NULL, 
    EventTypeID integer NOT NULL REFERENCES EventType, 
    DeviceID integer NOT NULL REFERENCES Device
);

Przydatny jest następujący indeks:

CREATE INDEX f1 
ON [Event] ([TimeStamp], EventTypeID) 
INCLUDE (DeviceID)
WHERE EventTypeID IN (2, 5, 7, 8, 9, 14);

W przypadku zapytania:

SELECT
  [Event].ID,
  [Event].[TimeStamp],
  EventType.Name,
  Device.ID
FROM
  [Event]
INNER JOIN EventType ON EventType.ID = [Event].EventTypeID
INNER JOIN Device ON Device.ID = [Event].DeviceID
WHERE
  [Event].[TimeStamp] BETWEEN '2011-01-28' AND '2011-01-29'
  AND Event.EventTypeID IN (2, 5, 7, 8, 9, 14);

Filtr spełnia ANDwymagania klauzuli, pierwszy klucz indeksu umożliwia wyszukiwanie [TimeStamp]dla filtrowanego EventTypeIDsi włączenie DeviceIDkolumny powoduje pokrycie indeksu (ponieważ DeviceIDjest to wymagane do przyłączenia do Devicetabeli).

Drugi klucz indeksu - EventTypeIDnie jest ściśle wymagany (może to być również INCLUDEdkolumna); Mam włączone go w kluczu do powodów podanych tutaj . Ogólnie radzę ludziom, aby przynajmniej INCLUDEkolumny z filtrowanej WHEREklauzuli indeksu .

W oparciu o zaktualizowany plan zapytania i wykonania w pytaniu zgadzam się, że bardziej ogólny indeks sugerowany przez SSMS jest prawdopodobnie lepszym wyborem tutaj, chyba że lista filtrowanych EventTypeIDs jest statyczna, jak Aaron wspomina również w swojej odpowiedzi:

CREATE TABLE Device 
(
    ID integer PRIMARY KEY,
    Name nvarchar(50) NOT NULL UNIQUE
);

CREATE TABLE EventType
(
    ID integer PRIMARY KEY, 
    Name nvarchar(20) NOT NULL UNIQUE,
    [Description] nvarchar(100) NOT NULL
);

CREATE TABLE [Event]
(
    ID integer PRIMARY KEY, 
    PLCTimeStamp datetime NOT NULL,
    EventTypeID integer NOT NULL REFERENCES EventType, 
    DeviceID integer NOT NULL REFERENCES Device,
    IATA varchar(50) NOT NULL,
    Data1 integer NULL,
    Data2 integer NULL,
);

Sugerowany indeks (jeśli jest to właściwe, zadeklaruj go jako unikalny)

CREATE UNIQUE INDEX uq1
ON [Event]
    (EventTypeID, PLCTimeStamp)
INCLUDE 
    (DeviceID, IATA, Data1, Data2, ID);

Informacje o liczności z planu wykonania (nieudokumentowana składnia, nie używaj w systemach produkcyjnych):

UPDATE STATISTICS dbo.Event WITH ROWCOUNT = 4042700, PAGECOUNT = 400000;
UPDATE STATISTICS dbo.EventType WITH ROWCOUNT = 22, PAGECOUNT = 1;
UPDATE STATISTICS dbo.Device WITH ROWCOUNT = 2806, PAGECOUNT = 28;

Zaktualizowana kwerenda (powtarzanie INlisty dla EventTypetabeli pomaga optymalizatorowi w tym konkretnym przypadku):

SELECT
  Event.ID,
  Event.IATA,
  Device.Name,
  EventType.Description,
  Event.Data1,
  Event.Data2,
  Event.PLCTimeStamp,
  Event.EventTypeID
FROM
  Event
INNER JOIN EventType ON EventType.ID = Event.EventTypeID
INNER JOIN Device ON Device.ID = Event.DeviceID
WHERE
  Event.EventTypeID IN (3, 30, 40, 41, 42, 46, 49, 50)
  AND EventType.ID IN (3, 30, 40, 41, 42, 46, 49, 50)
  AND Event.PLCTimeStamp BETWEEN '2011-01-28' AND '2011-01-29'
  AND Event.IATA LIKE '%0005836217%'
ORDER BY Event.ID;

Szacowany plan wykonania:

Twój plan prawdopodobnie będzie inny, ponieważ używam zgadywanych statystyk. Ogólnie rzecz biorąc, należy podać optymalizatorowi jak najwięcej informacji i zapewnić skuteczną metodę dostępu (indeks) w [Event]tabeli z 4 milionami wierszy .

Większość kosztów to skanowanie indeksu klastrowego i jeśli ta tabela nie jest naprawdę szeroka lub nie potrzebujesz tak naprawdę wszystkich kolumn w danych wyjściowych, uważam, że SQL Server to optymalna ścieżka w bieżącym scenariuszu bez żadnych innych zmian . Używa skanu zakresu (oznaczonego jako wyszukiwanie CI), aby zawęzić zakres interesujących go wierszy, ale ze względu na dane wyjściowe nadal będzie wymagać wyszukiwania lub skanowania CI, nawet przy utworzonym indeksie filtrowanym jest ukierunkowany na ten zakres i nawet w takim przypadku skanowanie CI jest prawdopodobnie nadal najtańsze (lub przynajmniej SQL Server ocenia je jako takie).

Plan wykonania mówi, że ten indeks byłby przydatny:

CREATE NONCLUSTERED INDEX ix_EventTypeID_PLCTimeStamp_WithIncludes
  ON [dbo].[Event] ([EventTypeID],[PLCTimeStamp])
  INCLUDE ([ID],[DeviceID],[Data1],[Data2],[IATA]);

Chociaż w zależności od wypaczenia danych może być lepiej na odwrót, np .:

CREATE NONCLUSTERED INDEX ix_PLCTimeStamp_EventTypeID_WithIncludes
  ON [dbo].[Event] ([PLCTimeStamp],[EventTypeID])
  INCLUDE ([ID],[DeviceID],[Data1],[Data2],[IATA]);

Ale przetestowałbym oba, aby upewnić się, co jest lepsze, jeśli jedno z nich - różnica między jednym z tych indeksów a tym, co masz teraz, może być jedynie marginalna (zbyt wiele zmiennych, abyśmy mogli to wiedzieć) i musisz wziąć pod uwagę, że dodatkowy Indeks wymaga dodatkowej konserwacji, co może znacząco wpłynąć na operacje DML (wstawianie / aktualizowanie / usuwanie). Możesz również rozważyć włączenie kryteriów filtrowania do tego indeksu, zgodnie z sugestią @SQLKiwi , ale tylko wtedy, gdy jest to zestaw wartości EventTypeID, których często szukasz. Jeśli ten zestaw zmienia się w czasie, filtrowany indeks będzie przydatny tylko dla tego konkretnego zapytania.

Przy tak małej liczbie wierszy muszę się zastanawiać, jak słaba może być obecnie wydajność? To zapytanie zwraca 3 wiersze (ale nic nie wskazuje na to, ile wierszy zostało odrzuconych). Ile wierszy w tabeli?

Właśnie odkryłem, że SQL Server 2008 R2 faktycznie zasugerował indeks, kiedy uruchomiłem plan wykonania. Ten sugerowany indeks sprawia, że ​​zapytanie działa o około 90% szybciej.

Sugerowany przez nią indeks był następujący:

CREATE NONCLUSTERED INDEX [INDEX_spBagSearch] ON [dbo].[Event] 
(
    [EventTypeID] ASC,
    [PLCTimeStamp] ASC
)
INCLUDE ( [ID],
[DeviceID],
[Data1],
[Data2],
[IATA]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO