Najbardziej efektywny sposób na wyszukiwanie zakresów dat

Jaki jest najskuteczniejszy sposób wyszukiwania zakresów dat przy takiej strukturze tabeli?

create table SomeDateTable
(
    id int identity(1, 1) not null,
    StartDate datetime not null,
    EndDate datetime not null
)
go

Powiedz, że chcesz mieć zasięg zarówno dla, jak StartDatei dla EndDate. Innymi słowy, jeśli StartDatewpada pomiędzy @StartDateBegini @StartDateEnd, i EndDatewpada pomiędzy @EndDateBegini @EndDateEnd, to zrób coś.

Wiem, że istnieje kilka sposobów, aby to zrobić, ale co jest najbardziej zalecane?

Jest to ogólnie trudny problem do rozwiązania, ale jest kilka rzeczy, które możemy zrobić, aby pomóc optymalizatorowi wybrać plan. Ten skrypt tworzy tabelę zawierającą 10 000 wierszy ze znanym pseudolosowym rozkładem wierszy, ilustrującym:

CREATE TABLE dbo.SomeDateTable
(
    Id          INTEGER IDENTITY(1, 1) PRIMARY KEY NOT NULL,
    StartDate   DATETIME NOT NULL,
    EndDate     DATETIME NOT NULL
);
GO
SET STATISTICS XML OFF
SET NOCOUNT ON;
DECLARE
    @i  INTEGER = 1,
    @s  FLOAT = RAND(20120104),
    @e  FLOAT = RAND();

WHILE @i <= 10000
BEGIN
    INSERT dbo.SomeDateTable
        (
        StartDate, 
        EndDate
        )
    VALUES
        (
        DATEADD(DAY, @s * 365, {d '2009-01-01'}),
        DATEADD(DAY, @s * 365 + @e * 14, {d '2009-01-01'})
        )

    SELECT
        @s = RAND(),
        @e = RAND(),
        @i += 1
END

Pierwsze pytanie dotyczy sposobu indeksowania tej tabeli. Jedną z opcji jest zapewnienie dwóch indeksów w DATETIMEkolumnach, aby optymalizator mógł przynajmniej wybrać, czy szukać w, StartDateczy w EndDate.

CREATE INDEX nc1 ON dbo.SomeDateTable (StartDate, EndDate)
CREATE INDEX nc2 ON dbo.SomeDateTable (EndDate, StartDate)

Naturalnie, nierówności na obu StartDatei EndDateoznacza to, że tylko jedna kolumna w każdym indeksie może obsługiwać szukać w przykładzie kwerendy, ale jest o najlepsze, co możemy zrobić. Możemy rozważyć utworzenie drugiej kolumny w każdym indeksie INCLUDEzamiast klucza, ale możemy mieć inne zapytania, które mogą wykonywać wyszukiwanie równości w kolumnie wiodącej i wyszukiwanie nierówności w kolumnie drugiej. W ten sposób możemy uzyskać lepsze statystyki. Tak czy siak...

DECLARE
    @StartDateBegin DATETIME = {d '2009-08-01'},
    @StartDateEnd DATETIME = {d '2009-10-15'},
    @EndDateBegin DATETIME = {d '2009-08-05'},
    @EndDateEnd DATETIME = {d '2009-10-22'}

SELECT
    COUNT_BIG(*)
FROM dbo.SomeDateTable AS sdt
WHERE
    sdt.StartDate BETWEEN @StartDateBegin AND @StartDateEnd
    AND sdt.EndDate BETWEEN @EndDateBegin AND @EndDateEnd

W tym zapytaniu wykorzystywane są zmienne, więc ogólnie optymalizator zgadnie selektywność i rozkład, w wyniku czego oszacowana kardynalność oszacuje na 81 wierszy . W rzeczywistości zapytanie generuje 2076 wierszy, co może być istotne w bardziej złożonym przykładzie.

W SQL Server 2008 SP1 CU5 lub nowszym (lub R2 RTM CU1) możemy skorzystać z Optymalizacji Osadzania Parametrów, aby uzyskać lepsze oszacowania, po prostu dodając OPTION (RECOMPILE)do SELECTpowyższego zapytania. Powoduje to kompilację tuż przed uruchomieniem partii, umożliwiając SQL Serverowi „zobaczenie” rzeczywistych wartości parametrów i ich optymalizację. Dzięki tej zmianie szacunek poprawia się do 468 wierszy (choć trzeba to sprawdzić w środowisku wykonawczym, aby to zobaczyć). Szacunek ten jest lepszy niż 81 wierszy, ale wciąż nie jest tak blisko. Rozszerzenia modelowania włączone przez flagę śledzenia 2301 mogą w niektórych przypadkach pomóc, ale nie w przypadku tego zapytania.

Problem polega na tym, że wiersze zakwalifikowane przez dwa wyszukiwania zakresu pokrywają się. Jednym z uproszczeń przyjętych w komponencie szacowania i liczności optymalizatora jest to, że predykaty są niezależne (więc jeśli oba mają selektywność 50%, zakłada się, że wynik zastosowania obu kwalifikuje 50% z 50% = 25% wierszy ). Tam, gdzie tego rodzaju korelacja stanowi problem, często możemy go obejść za pomocą statystyk wielokolumnowych i / lub filtrowanych. Z dwoma zakresami z nieznanymi punktami początkowymi i końcowymi staje się to niepraktyczne. W tym przypadku czasami musimy uciekać się do przepisania zapytania do formularza, który pozwala uzyskać lepsze oszacowanie:

SELECT COUNT(*) FROM
(
    SELECT
        sdt.Id
    FROM dbo.SomeDateTable AS sdt
    WHERE 
        sdt.StartDate BETWEEN @StartDateBegin AND @StartDateEnd
    INTERSECT
    SELECT
        sdt.Id
    FROM dbo.SomeDateTable AS sdt 
    WHERE
        sdt.EndDate BETWEEN @EndDateBegin AND @EndDateEnd
) AS intersected (id)
OPTION (RECOMPILE)

Ten formularz generuje oszacowanie czasu wykonania 2110 wierszy (w porównaniu do 2076 rzeczywistych). Chyba że masz TF 2301, w którym to przypadku bardziej zaawansowane techniki modelowania sprawdzają sztuczkę i dają dokładnie takie same oszacowanie jak poprzednio: 468 wierszy.

Pewnego dnia SQL Server może zyskać natywną obsługę interwałów. Jeśli zapewni to dobre wsparcie statystyczne, programiści mogą obawiać się strojenia takich planów zapytań nieco mniej.

Nie znam rozwiązania, które byłoby szybkie dla wszystkich dystrybucji danych, ale jeśli wszystkie zakresy są krótkie, zwykle możemy to przyspieszyć. Jeśli na przykład zakresy są krótsze niż jeden dzień, zamiast tego zapytania:

SELECT  TaskId ,    
        TaskDescription ,
        StartedAt ,    
        FinishedAt    
FROM    dbo.Tasks    
WHERE   '20101203' BETWEEN StartedAt AND FinishedAt

możemy dodać jeszcze jeden warunek:

SELECT  TaskId ,    
        TaskDescription ,
        StartedAt ,    
        FinishedAt    
FROM    dbo.Tasks    
WHERE   '20101203' BETWEEN StartedAt AND FinishedAt
    AND StartedAt >= '20101202'
    AND FinishedAt <= '20101204' ;

W rezultacie zamiast skanować całą tabelę, zapytanie będzie skanować zakres tylko dwóch dni, co jest szybsze. Jeśli zakresy mogą być dłuższe, możemy zapisać je jako sekwencje krótszych. Szczegóły tutaj: Dostrajanie zapytań SQL za pomocą ograniczeń