🥇Metody optymalizacji zapytań LINQ w C#.NET

Wprowadzenie

W w tym artykule omówiono kilka metod optymalizacji zapytania LINQ.
Dodatkowo przedstawimy kilka podejść do optymalizacji kodu związanych z zapytaniami LINQ.

Znane jest to, że LINQ(Language-Integrated Query) — to prosty i wygodny język zapytań do źródeł danych.

A LINQ to SQL jest technologią dostępu do danych w bazach danych. To potężne narzędzie do pracy z danymi, w którym za pomocą deklaratywnego języka konstruowane są zapytania, które następnie będą przekształcane do zapytania SQL platformy i wysyłane na serwer bazy danych do wykonania. W naszym przypadku przez bazę danych rozumiemy MS SQL Server.

Jednakże, zapytania LINQ nie są przekształcane w optymalnie napisane zapytania SQL, które mógłby napisać doświadczony DBA ze wszystkimi aspektami optymalizacji zapytania SQL:

optymalne połączenia (JOIN) i filtrowanie wyników (WHERE)
wiele niuansów w używaniu połączeń i warunków grupowych
wiele wariacji w zastępowaniu warunków IN na EXISTSi NOT IN, na EXISTS
przechowywanie wyników w pamięci podręcznej poprzez tabele tymczasowe, CTE, zmienne tabelaryczne
użycie klauzuli (OPTION) z wskazówkami oraz wskazówkami tabeli WITH (…)
użycie indeksowanych widoków jako jednej z metod, aby pozbyć się zbędnych odczytów danych podczas pobierania

Główne wąskie gardła wydajności, które występują zapytania SQL podczas kompilacji zapytania LINQ to:

konsolidacja całego mechanizmu selekcji danych w jednym zapytaniu
duplikacja identycznych bloków kodu, co prowadzi do wielokrotnych zbędnych odczytów danych
grupy złożonych warunków (logicznych „i” oraz „lub”) — AND i OR, łącząc się w złożone warunki, prowadzi do sytuacji, w której optymalizator, mając odpowiednie indeksy nieklasteryzowane, w końcu zaczyna przeprowadzać skanowanie po indeksie klasteryzowanym (INDEX SCAN) w grupach warunków
głębokie zagnieżdżenie podzapytania znacznie utrudnia analizę instrukcji SQL oraz analizę planu zapytania ze strony programistów i DBA

Metody optymalizacji

Teraz przejdźmy bezpośrednio do metod optymalizacji.

1) Dodatkowe indeksowanie

Najlepiej rozważać filtry na głównych tabelach zapytań, ponieważ bardzo często całe zapytanie koncentruje się wokół jednej lub dwóch głównych tabel (wnioski-ludzie-operacje) oraz standardowego zestawu warunków (IsClosed, Canceled, Enabled, Status). Ważne jest, aby dla wskazanych zbiorów stworzyć odpowiednie indeksy.

To rozwiązanie ma sens, gdy wybór na tych polach znacznie ogranicza zwracany zbiór zapytania.

Na przykład, mamy 500000 wniosków. Jednak aktywnych wniosków jest tylko 2000 rekordów. Wtedy odpowiednio dobrany indeks pozwoli nam na INDEX SCAN szybkie wybieranie danych przez nieuporządkowany indeks, unikając dużej tabeli.

Brak indeksów można również zidentyfikować poprzez wskazówki analizy planów zapytań lub zbierania statystyk systemowych przedstawień. MS SQL Server:

Wszystkie dane przedstawienia zawierają informacje o brakujących indeksach, z wyjątkiem indeksów przestrzennych.

Jednak indeksy i buforowanie często są metodami przeciwdziałania konsekwencjom źle napisanych zapytania LINQ i zapytania SQL.

Jak pokazuje surowa praktyka życia w biznesie, często ważna jest realizacja funkcjonalności biznesowych w określonych terminach. Dlatego często ciężkie zapytania są przesuwane w tryb tła z buforowaniem.

Częściowo jest to uzasadnione, ponieważ użytkownik nie zawsze potrzebuje najnowszych danych, a poziom reakcji interfejsu użytkownika jest akceptowalny.

To podejście pozwala na rozwiązanie potrzeb biznesowych, ale w efekcie obniża wydajność systemu informacyjnego, po prostu opóźniając rozwiązania problemów.

Warto również pamiętać, że podczas poszukiwania potrzebnych do dodania nowych indeksów, propozycje MS SQL optymalizacji mogą być niepoprawne również w następujących warunkach:

jeśli już istnieją indeksy z podobnym zestawem pól
jeśli pola w tabeli nie mogą być indeksowane z powodu ograniczeń indeksowania (więcej szczegółów opisano tutaj).

2) Łączenie atrybutów w jeden nowy atrybut

Czasami niektóre pola z jednej tabeli, dla których są grupy warunków, można zastąpić wprowadzeniem jednego nowego pola.

Szczególnie jest to aktualne dla pól-stanów, które zwykle są typu bitowego lub całkowitego.

Przykład:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 zastępuje Status = 1.

Wprowadza tutaj atrybut liczbowy Status, zapewniony przez wypełnienie tych statusów w tabeli. Następnie następuje indeksowanie tego nowego atrybutu.

To fundamentalne rozwiązanie problemu wydajności, ponieważ uzyskujemy dane bez zbędnych obliczeń.

3) Materializacja widoku

Niestety, w zapytaniach LINQ nie można bezpośrednio używać tymczasowych tabel, CTE i zmiennych tabelarycznych.

Jednak jest jeszcze inny sposób optymalizacji w tym przypadku — to widoki indeksowane.

Grupa warunków (z powyższego przykładu) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (lub zestaw innych podobnych warunków) staje się dobrym wariantem do ich użycia w widoku indeksowanym, buforując mały wycinek danych z dużej ilości.

Jednak istnieje szereg ograniczeń przy materializacji widoku:

użycie podzapytań, propozycje EXISTS muszą być zastąpione użyciem JOIN
nie można używać propozycji UNION, UNION ALL, WYJĄTEK, INTERSECT
nie można używać wskazówek tabelarycznych i propozycji OPTION
brak możliwości pracy z pętlami
niemożliwe jest wyświetlenie danych w jednym widoku z różnych tabel

Ważne jest, aby pamiętać, że rzeczywista korzyść z użycia widoku indeksowanego może być uzyskana de facto tylko przy jego indeksowaniu.

Jednak przy wywołaniu widoku te indeksy mogą nie być używane, a dla ich jawnego wykorzystania należy wskazać WITH (NOEXPAND).

Ponieważ w zapytaniach LINQ nie można definiować wskazówek tabelarycznych, więc trzeba stworzyć jeszcze jeden widok — "opakowanie" w następującej formie:

CREATE VIEW NAZWA_widoku AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Użycie funkcji tabelarycznych

Często w zapytaniach LINQ duże bloki podzapytań lub bloki wykorzystujące widoki o skomplikowanej strukturze tworzą końcowe zapytanie o bardzo skomplikowanej i nieoptymalnej strukturze wykonania.

Główne zalety użycia funkcji tabelarycznych w zapytaniach LINQ:

Możliwość, tak jak w przypadku widoków, używać i określać jako obiekt, ale można przekazać zestaw parametrów wejściowych:
FROM FUNCTION(@param1, @param2 …)
w rezultacie można osiągnąć elastyczne pobieranie danych.
W przypadku użycia funkcji tabelarycznej nie ma takich silnych ograniczeń, jak w przypadku widoków indeksowanych opisanych powyżej:
1. Wskazówki tabelaryczne:
  przez LINQ Nie można określić, jakie indeksy należy używać i ustalać poziom izolacji danych podczas zapytania.
  Jednak w funkcji te możliwości istnieją.
  Dzięki funkcji można uzyskać dość stały plan wykonania zapytania, w którym określone są zasady działania z indeksami i poziomy izolacji danych.
2. Użycie funkcji pozwala, w porównaniu do indeksowanych widoków, uzyskać:
  - złożoną logikę pobierania danych (włącznie z użyciem pętli)
  - pobieranie danych z wielu różnych tabel.
  - użycie UNION i EXISTS
Propozycja OPTION jest bardzo przydatna, gdy musimy zapewnić zarządzanie równoległością. OPTION(MAXDOP N), w porządku planu wykonania zapytania. Na przykład:
- można wymusić ponowne stworzenie planu zapytania. OPTION (RECOMPILE)
- można określić konieczność wymuszenia użycia przez plan zapytania porządku łączenia, wskazanego w zapytaniu. OPTION (FORCE ORDER)
Dokładniej o OPTION opisano tutaj.
Użycie najwęższego i wymaganego zestawu danych:
Nie ma potrzeby przechowywania dużych zbiorów danych w pamięciach podręcznych (jak w przypadku indeksowanych widoków), z których jeszcze trzeba by filtrować dane na podstawie parametrów.
Na przykład, mamy tabelę, w której dla filtra WHERE używane są trzy pola. (a, b, c).
Hipotetycznie, dla wszystkich zapytań istnieje stały warunek a = 0 i b = 0..
Jednak zapytanie dotyczące pola c jest bardziej zróżnicowane.
Niech warunek a = 0 i b = 0. naprawdę pomoże nam ograniczyć wymagany zbiór do tysięcy rekordów, ale warunek z ogranicza nasz wybór do setek rekordów.
Tutaj funkcja tabelaryczna może okazać się bardziej korzystną opcją.
Ponadto funkcja tabelaryczna jest bardziej przewidywalna i stała pod względem czasu wykonania.

Przykłady

Rozważmy przykład realizacji na przykładzie bazy danych Pytania.

Istnieje zapytanie SELECT, łączące kilka tabel i używające jednego widoku (OperacyjnePytania), w którym sprawdzana jest przynależność na podstawie emaila (przez EXISTS) do "Aktywnych zapytań" ([OperacyjnePytania]):

Zapytanie nr 1

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM [dbo].[Questions] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id],
[Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId],
[Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[OperativeQuestions] AS [Extent5]
WHERE (([Extent5].[Email] = @p__linq__0) OR (([Extent5].[Email] IS NULL) 
AND (@p__linq__0 IS NULL))) AND ([Extent5].[Id] = [Extent1].[Id])
));

Widok ma dość skomplikowaną strukturę: zawiera połączenia, podzapytania oraz wykorzystuje sortowanie UNIKALNE, co w ogólnym przypadku jest dość zasobochłonną operacją.

Zbiór danych z OperativeQuestions liczy około dziesięciu tysięcy rekordów.

Głównym problemem tego zapytania jest to, że dla rekordów z zewnętrznego zapytania wykonywane jest wewnętrzne podzapytanie na widoku [OperativeQuestions], które powinno dla [Email] = @p__linq__0 ograniczyć zwracaną próbkę (przez EXISTS) do setek rekordów.

Może się wydawać, że podzapytanie powinno najpierw obliczyć rekordy dla [Email] = @p__linq__0, a następnie te kilkaset rekordów powinno być łączone po Id z Questions, co sprawiłoby, że zapytanie byłoby szybkie.

W rzeczywistości następuje sekwencyjne łączenie wszystkich tabel: i sprawdzenie zgodności Id Questions z Id z OperativeQuestions, oraz filtrowanie po Email.

W zasadzie zapytanie działa na wszystkich dziesiątkach tysięcy rekordów OperativeQuestions, a potrzebne są tylko interesujące dane po Email.

Tekst widoku OperativeQuestions:

Zapytanie nr 2

 
UTWÓRZ WIDOK [dbo].[OperativeQuestions]
AS
WYBIERZ UNIKALNE Q.Id, USR.email AS Email
Z [dbo].Questions AS Q WEWNĘTRZNIE POŁĄCZ
                         [dbo].ProcessUserAccesses AS BPU ON BPU.ProcessId = CQ.Process_Id 
ZEWNETRZNE ZASTOSOWANIE
                     (WYBIERZ   1 AS HasNoObjects
                      GDZIE   NIE ISTNIEJE
                                    (WYBIERZ   1
                                     Z [dbo].ObjectUserAccesses AS BOU
                                     GDZIE   BOU.ProcessUserAccessId = BPU.[Id] I BOU.[Do] JEST NULL)
) AS BO WEWNĘTRZNIE POŁĄCZ
                         [dbo].Users AS USR ON USR.Id = BPU.UserId
GDZIE        CQ.[Exp] = 0 I CQ.AnswerId JEST NULL I BPU.[Do] JEST NULL 
I (BO.HasNoObjects = 1 LUB
              ISTNIEJE (WYBIERZ   1
                           Z   [dbo].ObjectUserAccesses AS BOU WEWNĘTRZNIE POŁĄCZ
                                      [dbo].ObjectQuestions AS QBO 
                                                  ON QBO.[Object_Id] =BOU.ObjectId
                               GDZIE  BOU.ProcessUserAccessId = BPU.Id 
                               I BOU.[Do] JEST NULL I QBO.Question_Id = CQ.Id));

Początkowe mapowanie widoku w DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().ToView("OperativeQuestions");
    }
}

Początkowe zapytanie LINQ

var businessObjectsData = await context
    .OperativeQuestions
    .Where(x => x.Email == Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

W tej konkretnej sytuacji rozważany jest sposób rozwiązania problemu bez zmian w infrastrukturze, bez wprowadzania osobnej tabeli z gotowymi wynikami („Aktywne zapytania”), dla której konieczny byłby mechanizm napełniania danymi i utrzymywania jej w aktualnym stanie.

Chociaż jest to dobre rozwiązanie, istnieje również inna opcja optymalizacji tego zadania.

Głównym celem jest zbuforowanie rekordów według [Email] = @p__linq__0 z widoku OperativeQuestions.

Wprowadzamy funkcję tabelaryczną [dbo].[OperativeQuestionsUserMail] do bazy danych.

Przesyłając jako parametr wejściowy Email, otrzymujemy z powrotem tabelę wartości:

Zapytanie nr 3


CREATE FUNCTION [dbo].[OperativeQuestionsUserMail]
(
    @Email  nvarchar(4000)
)
RETURNS
@tbl TABLE
(
    [Id]           uniqueidentifier,
    [Email]      nvarchar(4000)
)
AS
BEGIN
        INSERT INTO @tbl ([Id], [Email])
        SELECT Id, @Email
        FROM [OperativeQuestions]  AS [x] WHERE [x].[Email] = @Email;
     
    RETURN;
END

Tutaj zwracana jest tabela wartości o wcześniej określonej strukturze danych.

Aby zapytania do OperativeQuestionsUserMail były optymalne i miały optymalne plany zapytań, wymagana jest ścisła struktura, a nie RETURNS TABLE AS RETURN…

W tym przypadku poszukiwane Zapytanie 1 jest przekształcane w Zapytanie 4:

Zapytanie nr 4

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM (
    SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) AS [Extent0]
INNER JOIN [dbo].[Questions] AS [Extent1] ON([Extent0].Id=[Extent1].Id)
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id], [Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId], [Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]);

Mapowanie widoku i funkcji w DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().ToView("OperativeQuestions");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable GetByUserEmail(this DbQuery source, string Email)
        => source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}

Końcowe zapytanie LINQ

var businessObjectsData = await context
    .OperativeQuestions
    .GetByUserEmail(Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

Czas wykonania został skrócony z 200-800 ms do 2-20 ms, co oznacza dziesięciokrotną poprawę.

Średnio uzyskaliśmy 8 ms zamiast 350 ms.

Wśród oczywistych zalet otrzymujemy również:

ogólne zmniejszenie obciążenia podczas odczytu,
znaczące zmniejszenie ryzyka blokad
zmniejszenie średniego czasu blokady do akceptowalnych wartości

Wnioski

Optymalizacja i dostosowywanie zapytań do bazy danych MS SQL przez LINQ stanowią zadanie, które można wykonać.

W tej pracy bardzo ważna jest uwaga i konsekwencja.

Na początku procesu:

należy sprawdzić dane, z którymi pracuje zapytanie (wartości, wybrane typy danych)
prawidłowo zaindeksować te dane
sprawdzić poprawność warunków łączenia tabel

Na następnej iteracji optymalizacji wyodrębnia się:

podstawę zapytania i określa się główny filtr zapytania
powtarzające się podobne bloki zapytania i analizuje się przecinanie warunków
w SSMS lub innym GUI dla SQL Server optymalizuje sam zapytanie SQL (wydzielenie pośredniego magazynu danych, budowanie wynikowego zapytania z wykorzystaniem tego magazynu (może być ich kilka))
na ostatnim etapie, biorąc za podstawę wynikowe zapytanie SQL, przekształca się strukturę zapytania LINQ

Ostatecznie uzyskane zapytanie LINQ powinno stać się całkowicie zgodne z wyodrębnionym optymalnym zapytaniem SQL z punktu 3.

Podziękowania

Ogromne podziękowania dla kolegów jobgemws i alex_ozr z firmy Fortis za pomoc w przygotowaniu tego materiału.

Źródło: habr.com