Serwer Sql nie używa indeksu przy prostej bijectcji

To kolejna zagadka optymalizatora zapytań.

Może po prostu przeszacowuję optymalizatory zapytań, a może coś mi brakuje - więc zamieszczam to.

Mam prosty stół

CREATE TABLE [dbo].[MyEntities](
  [Id] [uniqueidentifier] NOT NULL,
  [Number] [int] NOT NULL,
  CONSTRAINT [PK_dbo.MyEntities] PRIMARY KEY CLUSTERED ([Id])
)

CREATE NONCLUSTERED INDEX [IX_Number] ON [dbo].[MyEntities] ([Number])

z indeksem i kilkoma tysiącami wierszy, Numberrównomiernie rozmieszczonymi w wartościach 0, 1 i 2.

Teraz to zapytanie:

SELECT * FROM
    (SELECT
        [Extent1].[Number] AS [Number],
        CASE
        WHEN (0 = [Extent1].[Number]) THEN 'one'
        WHEN (1 = [Extent1].[Number]) THEN 'two'
        WHEN (2 = [Extent1].[Number]) THEN 'three'
        ELSE '?'
        END AS [Name]
        FROM [dbo].[MyEntities] AS [Extent1]
        ) P
WHERE P.Number = 0;

indeks szuka IX_Numberzgodnie z oczekiwaniami.

Jeśli klauzula gdzie jest

WHERE P.Name = 'one';

staje się jednak skanem.

Klauzula przypadku jest oczywiście bijectionem, więc teoretycznie optymalizacja powinna umożliwić odjęcie pierwszego planu zapytania od drugiego zapytania.

Nie jest to również czysto akademickie: zapytanie jest inspirowane tłumaczeniem wartości wyliczeniowych na ich przyjazne nazwy.

Chciałbym usłyszeć od kogoś, kto wie, czego można się spodziewać po optymalizatorach zapytań (a konkretnie w Sql Server): czy po prostu oczekuję zbyt wiele?

Pytam, tak jak kiedyś miałem przypadki, w których niewielka zmiana zapytania sprawiłaby, że optymalizacja nagle wyszła na jaw.

Używam Sql Server 2016 Developer Edition.

Czy po prostu oczekuję zbyt wiele?

Tak. Przynajmniej w aktualnych wersjach produktu.

SQL Server nie rozdzieli CASEinstrukcji i nie dokona inżynierii wstecznej, aby odkryć, że jeśli wynik obliczonej kolumny musi być, 'one'to [Extent1].[Number]musi być 0.

Musisz upewnić się, że piszesz predykaty, aby można je było sprzedawać. Co prawie zawsze wymaga formy. basetable_column_name comparison_operator expression.

Nawet niewielkie odchylenia niszczą podatność na zakup.

WHERE P.Number + 0 = 0;

nie użyłby również wyszukiwania indeksu, nawet jeśli uproszczenie jest jeszcze prostsze niż CASEwyrażenie.

Jeśli chcesz wyszukać nazwę ciągu i uzyskać szukanie na numer, potrzebujesz tabeli odwzorowania z nazwami i liczbami i dołącz do niej w zapytaniu, wówczas plan może mieć wyszukiwanie w tabeli odwzorowania, a następnie wyszukiwanie skorelowane na [dbo].[MyEntities]z numerem powrócił z pierwszym szukać.

Nie wyświetlaj swojego wyliczenia jako stwierdzenia przypadku. Projektuj go jako tabelę pochodną tak:

SELECT * FROM
   (SELECT
      [Extent1].[Number] AS [Number],
      enum.Name
   FROM
      [dbo].[MyEntities] AS [Extent1]
      LEFT JOIN (VALUES
         (0, 'one'),
         (1, 'two'),
         (2, 'three')
      ) enum (Number, Name)
         ON Extent1.Number = enum.Number
   ) P
WHERE
   P.Name = 'one';

Podejrzewam, że uzyskasz lepsze wyniki. (Nie zmieniłem nazwy na ?brakującą, ponieważ prawdopodobnie zakłóciłoby to ewentualny wzrost wydajności. Możesz jednak przenieść WHEREklauzulę do zewnętrznego zapytania, aby umieścić predykat na enumstole, lub możesz zwrócić dwie kolumny z zapytanie wewnętrzne, jedno dla predykatu i jedno do wyświetlenia, gdzie jest predykatNULL gdy nie ma pasującej wartości wyliczeniowej).

Zgaduję jednak, że z tego powodu [Extent1]używasz ORM, takiego jak Entity Framework lub Linq-To-SQL. Nie potrafię wytłumaczyć, jak wykonać taką projekcję natywnie, ale możesz użyć innej techniki.

W jednym z moich projektów odzwierciedliłem wartości wyliczenia kodu w rzeczywistych tabelach w bazie danych poprzez klasę kompilacji niestandardowej, która połączyła wartości wyliczenia z bazą danych. (Musisz przestrzegać zasady, że musisz jawnie wymieniać wartości wyliczeniowe, nigdy nie możesz ich usunąć bez przejrzenia swoich tabel i nigdy nie możesz ich nigdy zmienić, chociaż już teraz musisz przestrzegać przynajmniej niektórych z tych ustawień) .

Teraz użyłem Identifierwyliczenia klasy podstawowej, która ma wiele różnych konkretnych podklas, ale nie ma powodu, dla którego nie można tego zrobić zwykłym wyliczeniem waniliowym. Oto przykład użycia:

new EnumOrIdentifierProjector<CodeClassOrEnum, PrivateDbDtoObject>(
   _sqlConnector.Connection,
   "dbo.TableName",
   "PrimaryKeyId",
   "NameColumnName",
   dtoObject => dtoObject.PrimaryKeyId,
   dtoObject => dtoObject.NameField,
   EnumerableOfIdentifierOrTypeOfEnum
)
   .Populate();

Widać, że przekazałem wszystkie niezbędne informacje, aby zarówno zapisać, jak i odczytać wartości bazy danych. (Miałem sytuację, w której bieżące żądanie może nie zawierać wszystkich zachowanych wartości, więc musiałem zwrócić wszelkie dodatkowe dane z bazy danych oraz aktualnie załadowanego zestawu. Pozwoliłem też bazie danych przypisać identyfikatory, chociaż dla wyliczenia prawdopodobnie nie byłbyś chce to.)

Chodzi o to, że gdy masz tabelę, która jest odczytywana / zapisywana tylko raz przy starcie, która niezawodnie ma wszystkie wartości wyliczenia, po prostu dołączasz do niej jak do każdej innej tabeli, a wydajność powinna być dobra.

Mam nadzieję, że te pomysły wystarczą, aby dokonać ulepszeń.

Interpretuję to pytanie jako ogólnie zainteresowane optymalizatorami, ale ze szczególnym zainteresowaniem dla SQL Server. Przetestowałem twój scenariusz z db2 LUW V11.1:

]$ db2 "create table myentities ( id int not null, number int not null )"
]$ db2 "create index ix_number on myentities (number)"
]$ db2 "insert into myentities (id, number) with t(n) as ( values 0 union all select n+1 from t where n<10000) select n, mod(n,3) from t"

Optymalizator w DB2 przepisuje drugie zapytanie na pierwsze:

Original Statement:
------------------
SELECT 
  * 
FROM 
  (SELECT 
     number,

   CASE 
   WHEN (0 = Number) 
   THEN 'one' 
   WHEN (1 = Number) 
   THEN 'two' 
   WHEN (2 = Number) 
   THEN 'three' 
   ELSE '?' END AS Name 
   FROM 
     MyEntities
  ) P 
WHERE 
  P.name = 'one'


Optimized Statement:
-------------------
SELECT 
  Q1.NUMBER AS "NUMBER",

CASE 
WHEN (0 = Q1.NUMBER) 
THEN 'one' 
WHEN (1 = Q1.NUMBER) 
THEN 'two' 
WHEN (2 = Q1.NUMBER) 
THEN 'three' 
ELSE '?' END AS "NAME" 
FROM 
  LELLE.MYENTITIES AS Q1 
WHERE 
  (0 = Q1.NUMBER)

Plan wygląda następująco:

Access Plan:
-----------
        Total Cost:             33.5483
        Query Degree:           1


      Rows 
     RETURN
     (   1)
      Cost 
       I/O 
       |
      3334 
     IXSCAN
     (   2)
     33.1861 
     4.66713 
       |
      10001 
 INDEX: LELLE   
    IX_NUMBER
       Q1

Nie wiem wiele o innych optymalizatorach, ale mam wrażenie, że optymalizator DB2 jest uważany za całkiem dobry nawet wśród konkurentów.

W tym konkretnym zapytaniu głupota jest nawet mieć CASEinstrukcję. Filtrujesz do jednego konkretnego przypadku! Być może jest to tylko szczegół konkretnego przykładowego zapytania, które podałeś, ale jeśli nie, możesz napisać to zapytanie, aby uzyskać równoważne wyniki:

SELECT
    [Extent1].[Number] AS [Number],
    'one' AS [Name]
FROM [dbo].[MyEntities] AS [Extent1]
WHERE [Extent1].[Number] = 0;

To da ci dokładnie ten sam zestaw wyników, a ponieważ i tak już mocno kodujesz wartości w CASEinstrukcji, nie tracisz tutaj żadnej łatwości konserwacji.