Jak zoptymalizować zapytanie

Mam strukturę bazy danych podobną do tej,

CREATE TABLE [dbo].[Dispatch](
    [DispatchId] [int] NOT NULL,
    [ContractId] [int] NOT NULL,
    [DispatchDescription] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Dispatch] PRIMARY KEY CLUSTERED 
(
    [DispatchId] ASC,
    [ContractId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DispatchLink](
    [ContractLink1] [int] NOT NULL,
    [DispatchLink1] [int] NOT NULL,
    [ContractLink2] [int] NOT NULL,
    [DispatchLink2] [int] NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (1, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (2, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (3, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (4, 1, N'Test')
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 1, 1, 2)
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 1, 1, 3)
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 3, 1, 2)
GO

Celem tabeli DispatchLink jest połączenie ze sobą dwóch rekordów Dispatch. Nawiasem mówiąc, używam złożonego klucza głównego na mojej tabeli wysyłki z powodu starszej wersji, więc nie mogę tego zmienić bez większego bólu. Również tabela linków może być niewłaściwa? Ale znowu dziedzictwo.

Więc moje pytanie, jeśli uruchomię to zapytanie

select * from Dispatch d
inner join DispatchLink dl on d.DispatchId = dl.DispatchLink1 and d.ContractId = dl.ContractLink1
or d.DispatchId = dl.DispatchLink2 and d.ContractId = dl.ContractLink2

Nigdy nie mogę zmusić go do wyszukiwania indeksu w tabeli DispatchLink. Zawsze wykonuje pełne skanowanie indeksu. To jest w porządku z kilkoma rekordami, ale gdy masz 50000 w tej tabeli, skanuje 50000 rekordów w indeksie zgodnie z planem zapytań. Wynika to z faktu, że w klauzuli łączenia występują „ands” i „ors”, ale nie mogę się zastanowić, dlaczego SQL nie może zamiast tego wykonać kilku wyszukiwań indeksu, po jednej dla lewej strony „lub”, i jeden po prawej stronie „lub”.

Chciałbym wyjaśnienia tego, a nie sugestii, aby przyspieszyć zapytanie, chyba że można to zrobić bez dostosowania zapytania. Powodem jest to, że używam powyższego zapytania jako filtru łączenia replikacji scalającej, więc nie mogę po prostu dodać innego typu zapytania.

AKTUALIZACJA: Na przykład są to typy indeksów, które dodawałem,

CREATE NONCLUSTERED INDEX IDX1 ON DispatchLink (ContractLink1, DispatchLink1)
CREATE NONCLUSTERED INDEX IDX2 ON DispatchLink (ContractLink2, DispatchLink2)
CREATE NONCLUSTERED INDEX IDX3 ON DispatchLink (ContractLink1, DispatchLink1, ContractLink2, DispatchLink2)

Używa więc indeksów, ale skanuje indeks w całym indeksie, więc 50000 rekordów skanuje 50000 rekordów w indeksie.

Optymalizator może rozważyć wiele alternatywnych planów (w tym tych z wieloma ORszukaniami ), ale w przypadku rozłączeń ( predykatów) domyślnie nie bierze pod uwagę planów obejmujących przecięcia indeksu. Biorąc pod uwagę indeksy:

CREATE CLUSTERED INDEX cx 
ON dbo.DispatchLink (DispatchLink1, ContractLink1);

CREATE NONCLUSTERED INDEX nc1 
ON dbo.DispatchLink (DispatchLink2, ContractLink2);

Możemy wymusić wyszukiwanie indeksów (przy założeniu, że SQL Server 2008 lub nowszy):

SELECT * 
FROM dbo.Dispatch AS d
INNER JOIN dbo.DispatchLink AS dl WITH (FORCESEEK) ON 
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

Na podstawie przykładowych danych plan wyszukiwania kosztuje 0,0332551 jednostek w porównaniu z 0,0068057 dla planu skanowania:

Istnieje wiele możliwych przeróbek zapytań i wskazówek, które możemy wypróbować. Jednym z przykładów przepisywania w celu promowania opcji, której optymalizator nie rozważa dla oryginalnego planu, jest:

SELECT * 
FROM dbo.Dispatch AS d
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;

Ten plan wykonania nie szuka drugiego indeksu, jeśli znajdzie dopasowanie pierwszego:

Może to działać bardzo nieznacznie lepiej niż FORCESEEKplan domyślny .

Bez dodawania nowych indeksów możemy również wymusić wyszukiwanie w tabeli Dispatch:

SELECT * 
FROM dbo.DispatchLink AS dl
JOIN dbo.Dispatch AS d WITH (FORCESEEK) ON
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

Może to być lepsze lub gorsze niż pierwszy przykład, w zależności od rzeczy, takich jak liczba wierszy w każdej z tabel. APPLY + TOPPoprawa jest jeszcze możliwe:

SELECT * 
FROM dbo.DispatchLink AS dl
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;