Klauzula SARGable WHERE dla dwóch kolumn dat

Mam interesujące pytanie dotyczące SARGability. W tym przypadku chodzi o użycie predykatu na podstawie różnicy między dwiema kolumnami dat. Oto konfiguracja:

USE [tempdb]
SET NOCOUNT ON  

IF OBJECT_ID('tempdb..#sargme') IS NOT NULL
BEGIN
DROP TABLE #sargme
END

SELECT TOP 1000
IDENTITY (BIGINT, 1,1) AS ID,
CAST(DATEADD(DAY, [m].[severity] * -1, GETDATE()) AS DATE) AS [DateCol1],
CAST(DATEADD(DAY, [m].[severity], GETDATE()) AS DATE) AS [DateCol2]
INTO #sargme
FROM sys.[messages] AS [m]

ALTER TABLE [#sargme] ADD CONSTRAINT [pk_whatever] PRIMARY KEY CLUSTERED ([ID])
CREATE NONCLUSTERED INDEX [ix_dates] ON [#sargme] ([DateCol1], [DateCol2])

To, co często widuję, to coś takiego:

/*definitely not sargable*/
SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [#sargme] AS [s]
WHERE
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) >= 48;

... co zdecydowanie nie jest SARGable. Powoduje to skanowanie indeksu, odczytuje wszystkie 1000 wierszy, co nie jest dobre. Szacowane rzędy śmierdzą. Nigdy nie wprowadziłbyś tego do produkcji.

Byłoby miło, gdybyśmy mogli zmaterializować CTE, ponieważ pomogłoby nam to uczynić to, cóż, bardziej SARGable, technicznie rzecz biorąc. Ale nie, otrzymujemy ten sam plan wykonania, co do góry.

/*would be nice if it were sargable*/
WITH    [x] AS ( SELECT
                * ,
                DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) AS [ddif]
               FROM
                [#sargme] AS [s])
     SELECT
        *
     FROM
        [x]
     WHERE
        [x].[ddif] >= 48;

I oczywiście, ponieważ nie używamy stałych, ten kod nic nie zmienia, a nawet nie jest w połowie SARGable. Nie ma zabawy. Ten sam plan wykonania.

/*not even half sargable*/
SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [#sargme] AS [s]
WHERE
    [s].[DateCol2] >= DATEADD(DAY, 48, [s].[DateCol1])

Jeśli masz szczęście i przestrzegasz wszystkich opcji ANSI SET w ciągach połączeń, możesz dodać kolumnę obliczoną i wyszukać ją ...

ALTER TABLE [#sargme] ADD [ddiff] AS 
DATEDIFF(DAY, DateCol1, DateCol2) PERSISTED

CREATE NONCLUSTERED INDEX [ix_dates2] ON [#sargme] ([ddiff], [DateCol1], [DateCol2])

SELECT [s].[ID] ,
       [s].[DateCol1] ,
       [s].[DateCol2]
FROM [#sargme] AS [s]
WHERE [ddiff] >= 48

Otrzymasz wyszukiwanie indeksu z trzema zapytaniami. Dziwny człowiek obecnie dodaje 48 dni do DateCol1. Zapytanie ze DATEDIFFw WHEREklauzuli, w miejsce CTE, a ostateczna kwerendy z predykat na kolumnie komputerowej wszystkim daje dużo ładniejszy planu ze znacznie szacunków ładniejszych, i to wszystko.

Co prowadzi mnie do pytania: czy w jednym zapytaniu istnieje SARGable sposób na przeprowadzenie tego wyszukiwania?

Bez tabel tymczasowych, bez zmiennych tabel, bez zmiany struktury tabeli i bez widoków.

Nie przeszkadzają mi samodzielne połączenia, CTE, podzapytania lub wielokrotne przekazywanie danych. Może współpracować z dowolną wersją SQL Server.

Unikanie kolumny obliczeniowej jest sztucznym ograniczeniem, ponieważ bardziej interesuje mnie rozwiązanie do zapytań niż cokolwiek innego.

Po prostu dodając to szybko, aby istniał jako odpowiedź (choć wiem, że nie jest to odpowiedź, której chcesz).

Indeksowana kolumna obliczana jest zazwyczaj dobrym rozwiązaniem dla tego typu problemu.

To:

• czyni predykat wyrażeniem indeksowalnym
• umożliwia tworzenie automatycznych statystyk w celu lepszego oszacowania liczności
• nie musi zajmować miejsca w tabeli podstawowej

Aby wyjaśnić ten ostatni punkt, obliczona kolumna nie musi być utrwalona w tym przypadku:

-- Note: not PERSISTED, metadata change only
ALTER TABLE #sargme
ADD DayDiff AS DATEDIFF(DAY, DateCol1, DateCol2);

-- Index the expression
CREATE NONCLUSTERED INDEX index_name
ON #sargme (DayDiff)
INCLUDE (DateCol1, DateCol2);

Teraz zapytanie:

SELECT
    S.ID,
    S.DateCol1,
    S.DateCol2,
    DATEDIFF(DAY, S.DateCol1, S.DateCol2)
FROM
    #sargme AS S
WHERE
    DATEDIFF(DAY, S.DateCol1, S.DateCol2) >= 48;

... daje następujący trywialny plan:

Jak powiedział Martin Smith, jeśli masz połączenia przy użyciu złych opcji zestawu, możesz utworzyć zwykłą kolumnę i zachować obliczoną wartość za pomocą wyzwalaczy.

Wszystko to naprawdę ma znaczenie (z wyjątkiem wyzwania kodu), jeśli istnieje prawdziwy problem do rozwiązania, jak mówi Aaron w swojej odpowiedzi .

Przyjemnie jest o tym myśleć, ale nie znam żadnego sposobu na osiągnięcie tego, co chcesz, biorąc pod uwagę ograniczenia w pytaniu. Wydaje się, że każde optymalne rozwiązanie wymagałoby nowej struktury danych pewnego rodzaju; najbliższe jest przybliżenie „indeksu funkcji” podane przez indeks na nietrwałej kolumnie obliczeniowej, jak powyżej.

Zaryzykując wyśmiewanie się przez niektóre z największych nazwisk w społeczności SQL Server, wyciągnę szyję i powiem: nie.

Aby twoje zapytanie było SARGable, musisz w zasadzie zbudować zapytanie, które może wskazać wiersz początkowy w zakresie kolejnych wierszy w indeksie. Dzięki indeksowi ix_dateswiersze nie są uporządkowane według różnicy dat między DateCol1i DateCol2, więc wiersze docelowe mogą zostać rozłożone w dowolnym miejscu w indeksie.

Łączenie siebie, wielokrotne przejścia itp. Mają wspólną cechę, że obejmują one co najmniej jedno skanowanie indeksu, chociaż łączenie (pętla zagnieżdżona) może również korzystać z wyszukiwania indeksowego. Ale nie widzę, jak można wyeliminować Skan.

Jeśli chodzi o uzyskiwanie dokładniejszych oszacowań wierszy, nie ma statystyk dotyczących różnicy dat.

Poniższa, dość brzydka rekurencyjna konstrukcja CTE technicznie eliminuje skanowanie całej tabeli, chociaż wprowadza Zagnieżdżone Łączenie Pętli i (potencjalnie bardzo dużą) liczbę Poszukiwań Indeksów.

DECLARE @from date, @count int;
SELECT TOP 1 @from=DateCol1 FROM #sargme ORDER BY DateCol1;
SELECT TOP 1 @count=DATEDIFF(day, @from, DateCol1) FROM #sargme WHERE DateCol1<=DATEADD(day, -48, {d '9999-12-31'}) ORDER BY DateCol1 DESC;

WITH cte AS (
    SELECT 0 AS i UNION ALL
    SELECT i+1 FROM cte WHERE i<@count)

SELECT b.*
FROM cte AS a
INNER JOIN #sargme AS b ON
    b.DateCol1=DATEADD(day, a.i, @from) AND
    b.DateCol2>=DATEADD(day, 48+a.i, @from)
OPTION (MAXRECURSION 0);

Tworzy szpulę indeksu zawierającą każdą DateCol1w tabeli, a następnie wykonuje wyszukiwanie indeksu (skanowanie zakresu) dla każdej z nich DateCol1i DateCol2co najmniej 48 dni do przodu.

Więcej operacji we / wy, nieco dłuższy czas wykonania, oszacowanie wiersza jest wciąż daleko, i zerowa szansa na równoległość z powodu rekurencji: Zgaduję, że to zapytanie może być przydatne, jeśli masz bardzo dużą liczbę wartości w obrębie stosunkowo kilku odrębnych, kolejnych DateCol1(utrzymanie liczby wyszukiwań na niskim poziomie).

Próbowałem wielu zwariowanych odmian, ale nie znalazłem żadnej wersji lepszej niż jedna z twoich. Głównym problemem jest to, że twój indeks wygląda tak pod względem sortowania razem date1 i date2. Pierwsza kolumna będzie na ładnej półce, a przerwa między nimi będzie bardzo poszarpana. Chcesz, aby wyglądało to bardziej na lejek niż tak, jak naprawdę:

Date1    Date2
-----    -------
*             *
*             *
*              *
 *       * 
 *        *
 *         *
  *      *
  *           *

Naprawdę nie ma sposobu, aby wymyślić to dla pewnej delty (lub zakresu delt) między dwoma punktami. Mam na myśli pojedyncze wyszukiwanie wykonane raz + skanowanie zasięgu, a nie wyszukiwanie wykonywane dla każdego wiersza. W pewnym momencie będzie to wymagało skanowania i / lub sortowania, a tych rzeczy oczywiście należy unikać. Szkoda, że ​​nie można używać wyrażeń takich jak DATEADD/ DATEDIFFw filtrowanych indeksach ani wykonywać jakichkolwiek możliwych modyfikacji schematu, które pozwoliłyby na sortowanie na podstawie różnicy dat (np. Obliczanie delty w czasie wstawiania / aktualizacji). Jak się wydaje, jest to jeden z tych przypadków, w których skanowanie jest w rzeczywistości optymalną metodą pobierania.

Powiedziałeś, że to zapytanie nie było zabawne, ale jeśli przyjrzysz się bliżej, jest to zdecydowanie najlepsze (i byłoby jeszcze lepiej, gdybyś pominął obliczeniowe dane skalarne):

SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [#sargme] AS [s]
WHERE
    [s].[DateCol2] >= DATEADD(DAY, 48, [s].[DateCol1])

Powodem jest to, że unikanie DATEDIFFpotencjalnie goli część procesora w porównaniu do obliczeń tylko w przypadku kolumny wiodącej w indeksie, a także unika pewnych nieprzyjemnych niejawnych konwersji do datetimeoffset(7)(nie pytaj mnie, dlaczego one istnieją, ale tak jest). Oto DATEDIFFwersja:

<Predicate>
<ScalarOperator ScalarString = "datediff (dzień, CONVERT_IMPLICIT (datetimeoffset (7), [splunge]. [Dbo]. [Sargme]. [DateCol1] as [s]. [DateCol1], 0), CONVERT_IMPLICIT (datetimeoffset ( 7), [splunge]. [Dbo]. [Sargme]. [DateCol2] as [s]. [DateCol2], 0))> = (48) ">

A oto ten bez DATEDIFF:

<Predicate>
<ScalarOperator ScalarString = "[splunge]. [Dbo]. [Sargme]. [DateCol2] as [s]. [DateCol2]> = dateadd (dzień, (48), [splunge]. [Dbo]. [ sargme]. [DateCol1] as [s]. [DateCol1]) ">

Znalazłem też nieco lepsze wyniki pod względem czasu trwania, kiedy zmieniłem indeks tylko na uwzględniający DateCol2 (a gdy oba indeksy były obecne, SQL Server zawsze wybrał ten z jednym kluczem, a drugi z kolumną na wiele kluczy). W przypadku tego zapytania, ponieważ i tak musimy przeskanować wszystkie wiersze, aby znaleźć zakres, nie ma korzyści, aby druga kolumna daty była częścią klucza i była sortowana w jakikolwiek sposób. I chociaż wiem, że nie możemy tutaj szukać, jest coś z natury dobrego w tym, że nie przeszkadza to w uzyskaniu go przez wymuszenie obliczeń względem wiodącej kolumny kluczowej i wykonywanie ich tylko dla kolumn pomocniczych lub uwzględnionych.

Gdybym to był ja i zrezygnowałbym ze znalezienia odpowiedniego rozwiązania, wiem, które wybrałbym - ten, który sprawia, że ​​SQL Server wykonuje najmniej pracy (nawet jeśli delta prawie nie istnieje). Albo jeszcze lepiej, złagodziłbym swoje ograniczenia dotyczące zmiany schematu i tym podobne.

A ile to wszystko ma znaczenie? Nie wiem Zrobiłem tabelę 10 milionów wierszy, a wszystkie powyższe warianty zapytań wciąż zostały ukończone w ciągu sekundy. I to jest na maszynie wirtualnej na laptopie (przyznane, z dyskiem SSD).

Wszystkie sposoby, w jakie wymyśliłem, aby klauzula WHERE była możliwa do sarg, są złożone i czuję, że praca nad indeksem dąży raczej jako cel końcowy niż środek. Więc nie, nie sądzę, że jest to (pragmatycznie) możliwe.

Nie byłem pewien, czy „brak zmiany struktury tabeli” oznacza brak dodatkowych indeksów. Oto rozwiązanie, które całkowicie eliminuje skanowanie indeksów, ale powoduje DUŻO oddzielnych poszukiwań indeksu, tj. Po jednej dla każdej możliwej daty DateCol1 w zakresie wartości dat Min / Max w tabeli. (W przeciwieństwie do Daniela, która powoduje jedno wyszukiwanie dla każdej odrębnej daty, która faktycznie pojawia się w tabeli). Teoretycznie jest kandydatem do równoległości, ale unika rekurencji. Ale szczerze mówiąc, trudno jest dostrzec dystrybucję danych, w której jest to szybsze niż tylko skanowanie i wykonywanie DATEDIFF. (Może naprawdę wysoki DOP?) I ... kod jest brzydki. Myślę, że ten wysiłek liczy się jako „ćwiczenie mentalne”.

--Add this index to avoid the scan when determining the @MaxDate value
--CREATE NONCLUSTERED INDEX [ix_dates2] ON [#sargme] ([DateCol2]);
DECLARE @MinDate DATE, @MaxDate DATE;
SELECT @MinDate=DateCol1 FROM (SELECT TOP 1 DateCol1 FROM #sargme ORDER BY DateCol1 ASC) ss;
SELECT @MaxDate=DateCol2 FROM (SELECT TOP 1 DateCol2 FROM #sargme ORDER BY DateCol2 DESC) ss;

--Used 44 just to get a few more rows to test my logic
DECLARE @DateDiffSearchValue INT = 44, 
    @MinMaxDifference INT = DATEDIFF(DAY, @MinDate, @MaxDate);

--basic data profile in the table
SELECT [MinDate] = @MinDate, 
        [MaxDate] = @MaxDate, 
        [MinMaxDifference] = @MinMaxDifference, 
        [LastDate1SearchValue] = DATEADD(DAY, 0-@DateDiffSearchValue, @MaxDate);

;WITH rn_base AS (
SELECT [col1] = 0
        UNION ALL SELECT 0
        UNION ALL SELECT 0
        UNION ALL SELECT 0
),
rn_1 AS (
    SELECT t0.col1 FROM rn_base t0
        CROSS JOIN rn_base t1
        CROSS JOIN rn_base t2
        CROSS JOIN rn_base t3
),
rn_2 AS (
    SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM rn_1 t0
        CROSS JOIN rn_1 t1
),
candidate_searches AS (
    SELECT 
        [Date1_EqualitySearch] = DATEADD(DAY, t.rn-1, @MinDate),
        [Date2_RangeSearch] = DATEADD(DAY, t.rn-1+@DateDiffSearchValue, @MinDate)
    FROM rn_2 t
    WHERE DATEADD(DAY, t.rn-1, @MinDate) <= DATEADD(DAY, 0-@DateDiffSearchValue, @MaxDate)
    /* Of course, ignore row-number values that would result in a
       Date1_EqualitySearch value that is < @DateDiffSearchValue days before @MaxDate */
)
--select * from candidate_searches

SELECT c.*, xapp.*, dd_rows = DATEDIFF(DAY, xapp.DateCol1, xapp.DateCol2)
FROM candidate_searches c
    cross apply (
        SELECT t.*
        FROM #sargme t
        WHERE t.DateCol1 = c.date1_equalitysearch
        AND t.DateCol2 >= c.date2_rangesearch
    ) xapp
ORDER BY xapp.ID asc --xapp.DateCol1, xapp.DateCol2 

_{Społeczność Wiki odpowiedź pierwotnie dodana przez autora pytania jako edycja pytania}

Po tym, jak pozwolę temu usiąść na chwilę, a niektórzy naprawdę inteligentni ludzie wezmą się do środka, moja początkowa myśl na ten temat wydaje się poprawna: nie ma rozsądnego i SARGable sposobu na napisanie tego zapytania bez dodania kolumny, obliczonej lub utrzymywanej za pomocą innego mechanizmu, a mianowicie wyzwalacze.

Próbowałem kilku innych rzeczy i mam inne spostrzeżenia, które mogą, ale nie muszą być interesujące dla każdego, kto czyta.

Po pierwsze, uruchom ponownie instalatora przy użyciu zwykłego stołu zamiast tabeli tymczasowej

• Chociaż znam ich reputację, chciałem wypróbować statystyki wielokolumnowe. Były bezużyteczne.
• Chciałem zobaczyć, które statystyki zostały użyte

Oto nowa konfiguracja:

USE [tempdb]
SET NOCOUNT ON  

DBCC FREEPROCCACHE

IF OBJECT_ID('tempdb..sargme') IS NOT NULL
BEGIN
DROP TABLE sargme
END

SELECT TOP 1000
IDENTITY (BIGINT, 1,1) AS ID,
CAST(DATEADD(DAY, [m].[severity] * -1, GETDATE()) AS DATE) AS [DateCol1],
CAST(DATEADD(DAY, [m].[severity], GETDATE()) AS DATE) AS [DateCol2]
INTO sargme
FROM sys.[messages] AS [m]

ALTER TABLE [sargme] ADD CONSTRAINT [pk_whatever] PRIMARY KEY CLUSTERED ([ID])
CREATE NONCLUSTERED INDEX [ix_dates] ON [sargme] ([DateCol1], [DateCol2])

CREATE STATISTICS [s_sargme] ON [sargme] ([DateCol1], [DateCol2])

Następnie, uruchamiając pierwsze zapytanie, używa indeksu ix_dates i skanuje, tak jak poprzednio. Żadnych zmian tutaj. To wydaje się zbędne, ale trzymaj się mnie.

SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [sargme] AS [s]
WHERE
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) >= 48

Ponownie uruchom zapytanie CTE, wciąż to samo ...

WITH    [x] AS ( SELECT
                * ,
                DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) AS [ddif]
               FROM
                [sargme] AS [s])
     SELECT
        *
     FROM
        [x]
     WHERE
        [x].[ddif] >= 48;

W porządku! Ponownie uruchom zapytanie, którego nie można wyrównać nawet do połowy:

SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [sargme] AS [s]
WHERE
    [s].[DateCol2] >= DATEADD(DAY, 48, [s].[DateCol1])

Teraz dodaj kolumnę obliczeniową i ponownie uruchom wszystkie trzy, wraz z zapytaniem trafiającym do kolumny obliczeniowej:

ALTER TABLE [sargme] ADD [ddiff] AS 
DATEDIFF(DAY, DateCol1, DateCol2) PERSISTED

CREATE NONCLUSTERED INDEX [ix_dates2] ON [sargme] ([ddiff], [DateCol1], [DateCol2])

SELECT [s].[ID] ,
       [s].[DateCol1] ,
       [s].[DateCol2]
FROM [sargme] AS [s]
WHERE [ddiff] >= 48

Jeśli utknąłeś tu ze mną, dzięki. To jest interesująca część obserwacyjna postu.

Uruchamianie zapytania z nieudokumentowaną flagą śledzenia przez Fabiano Amorima, aby zobaczyć, które statystyki każde użyte zapytanie jest całkiem fajne. Widząc, że żaden plan nie dotknął obiektu statystycznego, dopóki kolumna obliczeniowa nie została utworzona i zindeksowana, wydawała się dziwna.

Do licha, nawet zapytanie, które trafiło TYLKO do kolumny obliczeniowej, nie dotknęło obiektu statystyki, dopóki nie uruchomiłem go kilka razy i uzyskałem prostą parametryzację. Więc chociaż wszyscy początkowo zeskanowali indeks ix_dates, użyli zakodowanych oszacowań liczności (30% tabeli) zamiast dowolnego dostępnego obiektu statystycznego.

Kolejną kwestią, która podniosła brew, jest to, że kiedy dodałem tylko indeks nieklastrowany, wszystkie plany zapytań skanowały HEAP, zamiast używać indeksu nieklastrowanego w obu kolumnach daty.

Dziękujemy wszystkim, którzy odpowiedzieli. Jesteście wszyscy wspaniali.