Słabo wydajne podzapytanie z porównywaniem dat

Podczas korzystania z podzapytania w celu znalezienia całkowitej liczby wszystkich poprzednich rekordów z pasującym polem wydajność jest okropna na stole z zaledwie 50 000 rekordów. Bez podzapytania zapytanie jest wykonywane w ciągu kilku milisekund. W przypadku podzapytania czas wykonania jest wyższy niż minuta.

W przypadku tego zapytania wynik musi:

• Uwzględnij tylko te rekordy w danym zakresie dat.
• Uwzględnij liczbę wszystkich wcześniejszych rekordów, bez bieżącego rekordu, niezależnie od zakresu dat.

Podstawowy schemat tabeli

Activity
======================
Id int Identifier
Address varchar(25)
ActionDate datetime2
Process varchar(50)
-- 7 other columns

Przykładowe dane

Id  Address     ActionDate (Time part excluded for simplicity)
===========================
99  000         2017-05-30
98  111         2017-05-30
97  000         2017-05-29
96  000         2017-05-28
95  111         2017-05-19
94  222         2017-05-30

oczekiwane rezultaty

Dla zakresu dat 2017-05-29do2017-05-30

Id  Address     ActionDate    PriorCount
=========================================
99  000         2017-05-30    2  (3 total, 2 prior to ActionDate)
98  111         2017-05-30    1  (2 total, 1 prior to ActionDate)
94  222         2017-05-30    0  (1 total, 0 prior to ActionDate)
97  000         2017-05-29    1  (3 total, 1 prior to ActionDate)

Rekordy 96 i 95 są wykluczone z wyniku, ale są uwzględnione w PriorCountpodzapytaniu

Bieżące zapytanie

select 
    *.a
    , ( select count(*) 
        from Activity
        where 
            Activity.Address = a.Address
            and Activity.ActionDate < a.ActionDate
    ) as PriorCount
from Activity a
where a.ActionDate between '2017-05-29' and '2017-05-30'
order by a.ActionDate desc

Aktualny indeks

CREATE NONCLUSTERED INDEX [IDX_my_nme] ON [dbo].[Activity]
(
    [ActionDate] ASC
)
INCLUDE ([Address]) WITH (
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = OFF, 
    DROP_EXISTING = OFF, 
    ONLINE = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON
)

Pytanie

• Jakie strategie można zastosować w celu poprawy wydajności tego zapytania?

Edycja 1
W odpowiedzi na pytanie, co mogę modyfikować w DB: Mogę modyfikować indeksy, a nie strukturę tabeli.

Edycja 2
Dodałem teraz podstawowy indeks do Addresskolumny, ale wydaje się, że niewiele się to poprawiło. Obecnie znajduję znacznie lepszą wydajność dzięki tworzeniu tabeli tymczasowej i wstawianiu wartości bez, PriorCounta następnie aktualizowaniu każdego wiersza o określoną liczbę.

Edycja 3
Znaleziona szpula Joe Obbish (zaakceptowana odpowiedź) była problemem. Po dodaniu nowego nonclustered index [xyz] on [Activity] (Address) include (ActionDate)czas zapytania skrócił się z minuty na minutę do mniej niż sekundy bez użycia tabeli tymczasowej (patrz edycja 2).

Dzięki posiadanej definicji indeksu IDX_my_nmeSQL Server będzie mógł wyszukiwać za pomocą ActionDatekolumny, ale nie z Addresskolumną. Indeks zawiera wszystkie kolumny potrzebne do pokrycia podzapytania, ale prawdopodobnie nie jest zbyt selektywny dla tego podzapytania. Załóżmy, że prawie wszystkie dane w tabeli mają ActionDatewartość wcześniejszą niż '2017-05-30'. Wyszukiwanie ActionDate < '2017-05-30'zwróci prawie wszystkie wiersze z indeksu, które są dalej filtrowane po pobraniu wiersza z indeksu. Jeśli zapytanie zwróci 200 wierszy, prawdopodobnie wykonasz prawie 200 pełnych skanów indeksu IDX_my_nme, co oznacza, że ​​przeczytasz około 50000 * 200 = 10 milionów wierszy z indeksu.

Jest prawdopodobne, że wyszukiwanie Addressbędzie znacznie bardziej selektywne dla twojego podzapytania, chociaż nie podałeś nam pełnych informacji statystycznych na temat zapytania, więc z mojej strony jest to założenie. Załóżmy jednak, że utworzyłeś indeks just, Addressa twoja tabela ma 10k unikalnych wartości dla Address. Dzięki nowemu indeksowi SQL Server będzie musiał wyszukiwać tylko 5 wierszy z indeksu dla każdego wykonania podzapytania, więc przeczytasz około 200 * 5 = 1000 wierszy z indeksu.

Testuję pod kątem SQL Server 2016, więc mogą występować niewielkie różnice w składni. Poniżej kilka przykładowych danych, w których podjąłem podobne założenia do powyższego dla dystrybucji danych:

CREATE TABLE #Activity (
    Id int NOT NULL,
    [Address] varchar(25) NULL,
    ActionDate datetime2 NULL,
    FILLER varchar(100),
    PRIMARY KEY (Id)
);

INSERT INTO #Activity WITH (TABLOCK)
SELECT TOP (50000) -- 50k total rows
x.RN
, x.RN % 10000 -- 10k unique addresses
, DATEADD(DAY, x.RN / 100, '20160201') -- 100 rows per day
, REPLICATE('Z', 100)
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) x;

CREATE NONCLUSTERED INDEX [IDX_my_nme] ON #Activity
([ActionDate] ASC) INCLUDE ([Address]);

Utworzyłem Twój indeks zgodnie z opisem w pytaniu. Testuję pod kątem tego zapytania, które zwraca te same dane, co dane w pytaniu:

select 
    a.*
    , ( select count(*) 
        from #Activity Activity
        where 
            Activity.[Address] = a.[Address]
            and Activity.ActionDate < a.ActionDate
    ) as PriorCount
from #Activity a
where a.ActionDate between '2017-05-29' and '2017-05-30'
order by a.ActionDate desc;

Dostaję szpulę indeksu. Na poziomie podstawowym oznacza to, że optymalizator zapytań buduje indeks tymczasowy w locie, ponieważ żaden z istniejących indeksów dla tabeli nie był odpowiedni.

Zapytanie wciąż kończy się dla mnie szybko. Być może nie otrzymujesz optymalizacji buforowania indeksów w systemie lub jest coś innego w definicji tabeli lub zapytaniu. Do celów edukacyjnych mogę użyć nieudokumentowanej funkcji OPTION (QUERYRULEOFF BuildSpool)do wyłączenia buforowania indeksu. Oto jak wygląda plan:

Nie daj się zwieść pojawieniu się zwykłego wyszukiwania indeksu. SQL Server odczytuje z indeksu prawie 10 milionów wierszy:

Jeśli mam zamiar uruchomić kwerendę więcej niż raz, prawdopodobnie optymalizator kwerendy nie ma sensu tworzyć indeksu przy każdym uruchomieniu. Mógłbym utworzyć z góry indeks, który byłby bardziej selektywny dla tego zapytania:

CREATE NONCLUSTERED INDEX [IDX_my_nme_2] ON #Activity
([Address] ASC) INCLUDE (ActionDate);

Plan jest podobny do wcześniejszego:

Jednak dzięki nowemu indeksowi SQL Server odczytuje tylko 1000 wierszy z indeksu. 800 wierszy jest zwracanych do zliczenia. Indeks można zdefiniować jako bardziej selektywny, ale może być wystarczająco dobry w zależności od dystrybucji danych.

Jeśli nie możesz zdefiniować żadnych dodatkowych indeksów w tabeli, rozważę użycie funkcji okna. Wydaje się, że działają:

SELECT t.*
FROM
(
    select 
        a.*
        , -1 + ROW_NUMBER() OVER (PARTITION BY [Address] ORDER BY ActionDate) PriorCount
    from #Activity a
) t
where t.ActionDate between '2017-05-29' and '2017-05-30'
order by t.ActionDate desc;

To zapytanie wykonuje pojedynczy skan danych, ale wykonuje kosztowne sortowanie i oblicza ROW_NUMBER()funkcję dla każdego wiersza w tabeli, więc wydaje się, że wykonano tutaj dodatkową pracę:

Jeśli jednak naprawdę podoba ci się ten wzorzec kodu, możesz zdefiniować indeks, aby zwiększyć jego wydajność:

CREATE NONCLUSTERED INDEX [IDX_my_nme] ON #Activity
([Address], [ActionDate]) INCLUDE (FILLER);

To przesuwa rodzaj pod koniec, który będzie znacznie tańszy:

Jeśli nic z tego nie pomoże, musisz dodać do pytania więcej informacji, najlepiej w tym rzeczywiste plany wykonania.