🥇Metody optymalizacji zapytań LINQ w C#.NET

Wprowadzenie

В ten artykuł rozważono pewne metody optymalizacji Zapytania LINQ.
Tutaj przedstawiamy również kilka innych podejść do optymalizacji kodu związanych z Zapytania LINQ.

Wiadomym jest, że LINQ(Language-Integrated Query) to prosty i wygodny język do wykonywania zapytań do źródła danych.

А LINQ do SQL to technologia dostępu do danych w systemie DBMS. Jest to potężne narzędzie do pracy z danymi, w którym zapytania są konstruowane za pomocą języka deklaratywnego, na który następnie są konwertowane Zapytania SQL platformę i wysyłane do serwera bazy danych w celu wykonania. W naszym przypadku przez DBMS mamy na myśli MS SQL Server.

Jednak Zapytania LINQ nie są konwertowane na optymalnie zapisane Zapytania SQL, który doświadczony administrator danych mógłby napisać ze wszystkimi niuansami optymalizacji zapytania SQL:

optymalne połączenia (DOŁĄCZ) i filtrowanie wyników (WHERE)
wiele niuansów w korzystaniu z połączeń i warunków grupowych
wiele odmian warunków wymiany IN na ISTNIEJEи NIE W, <>wł ISTNIEJE
pośrednie buforowanie wyników poprzez tabele tymczasowe, CTE, zmienne tabeli
użycie zdania (OPCJA) z instrukcjami i wskazówkami do tabel Z (...)
używanie widoków indeksowanych jako jednego ze sposobów pozbycia się zbędnych odczytów danych podczas selekcji

Główne wąskie gardła wydajności wynikające zapytania SQL podczas kompilacji Zapytania LINQ są:

konsolidacja całego mechanizmu selekcji danych w jednym żądaniu
powielanie identycznych bloków kodu, co ostatecznie prowadzi do wielokrotnych, niepotrzebnych odczytów danych
grupy warunków wieloskładnikowych (logiczne „i” i „lub”) - ROLNICZE и OR, łącząc się w złożone warunki, prowadzi do tego, że optymalizator, mając odpowiednie indeksy nieklastrowe dla niezbędnych pól, ostatecznie zaczyna skanować pod kątem indeksu klastrowego (SKAN INDEKSU) według grup warunków
głębokie zagnieżdżanie podzapytań sprawia, że analiza jest bardzo problematyczna Instrukcje SQL i analiza planu zapytań ze strony programistów i DBA

Metody optymalizacji

Przejdźmy teraz bezpośrednio do metod optymalizacji.

1) Dodatkowe indeksowanie

Najlepiej jest rozważyć filtry na głównych tabelach wyboru, ponieważ bardzo często całe zapytanie jest zbudowane wokół jednej lub dwóch głównych tabel (aplikacje-ludzie-operacje) i ze standardowym zestawem warunków (IsClosed, Canceled, Enabled, Status). Ważne jest, aby dla identyfikowanych próbek stworzyć odpowiednie indeksy.

Rozwiązanie to ma sens w sytuacji, gdy wybranie tych pól znacznie ogranicza zwracany zestaw do zapytania.

Dla przykładu mamy 500000 2000 wniosków. Jednak aktywnych aplikacji jest tylko XNUMX. Wtedy odpowiednio dobrany indeks nas uratuje SKAN INDEKSU na dużej tabeli i umożliwi szybką selekcję danych poprzez indeks nieklastrowany.

Ponadto brak indeksów można zidentyfikować poprzez monity o przeanalizowanie planów zapytań lub zebranie statystyk widoku systemu MS SQL Server:

Wszystkie dane widoku zawierają informację o brakujących indeksach, za wyjątkiem indeksów przestrzennych.

Jednak indeksy i buforowanie są często metodami zwalczania konsekwencji źle napisanych Zapytania LINQ и zapytania SQL.

Jak pokazuje surowa praktyka życiowa, dla firmy często ważne jest wdrożenie funkcji biznesowych w określonych terminach. Dlatego też duże żądania są często przenoszone w tło za pomocą buforowania.

Jest to częściowo uzasadnione, ponieważ użytkownik nie zawsze potrzebuje najświeższych danych, a poziom responsywności interfejsu użytkownika jest akceptowalny.

Takie podejście pozwala na rozwiązywanie potrzeb biznesowych, ale ostatecznie zmniejsza wydajność systemu informatycznego, po prostu opóźniając rozwiązanie problemów.

Warto również pamiętać, że w procesie poszukiwania niezbędnych indeksów do dodania, sugestii MS SQL optymalizacja może być nieprawidłowa, m.in. w następujących sytuacjach:

jeśli istnieją już indeksy z podobnym zestawem pól
jeżeli pola w tabeli nie mogą być indeksowane ze względu na ograniczenia indeksowania (opisane szerzej tutaj).

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

Czasami niektóre pola z jednej tabeli, na których opiera się grupa warunków, można zastąpić wprowadzając jedno nowe pole.

Jest to szczególnie prawdziwe w przypadku pól stanu, które zwykle są typu bitowego lub całkowitego.

Przykład:

IsClosed = 0 ORAZ Anulowane = 0 ORAZ Włączone = 0 jest zastąpiony przez Stan = 1.

W tym miejscu wprowadza się atrybut statusu typu integer, aby zapewnić wypełnienie tych statusów w tabeli. Następnie ten nowy atrybut jest indeksowany.

Jest to podstawowe rozwiązanie problemu wydajnościowego, ponieważ uzyskujemy dostęp do danych bez zbędnych obliczeń.

3) Materializacja poglądu

Niestety w Zapytania LINQ Tablic tymczasowych, CTE i zmiennych tabeli nie można używać bezpośrednio.

Istnieje jednak inny sposób optymalizacji w tym przypadku – widoki indeksowane.

Grupa warunków (z powyższego przykładu) IsClosed = 0 ORAZ Anulowane = 0 ORAZ Włączone = 0 (lub zestaw innych podobnych warunków) staje się dobrą opcją, aby użyć ich w widoku indeksowanym, buforując mały wycinek danych z dużego zestawu.

Istnieje jednak wiele ograniczeń związanych z materializacją widoku:

użycie podzapytań, klauzul ISTNIEJE należy zastąpić użyciem DOŁĄCZ
nie możesz używać zdań UNION, UNIA WSZYSTKIE, WYJĄTEK, KRZYŻOWAĆ
Nie można używać wskazówek i klauzul dotyczących tabel OPCJA
brak możliwości pracy z cyklami
Niemożliwe jest wyświetlenie danych w jednym widoku z różnych tabel

Należy pamiętać, że rzeczywiste korzyści z używania widoku indeksowanego można osiągnąć jedynie poprzez faktyczne jego indeksowanie.

Jednak przy wywoływaniu widoku indeksy te nie mogą być używane i aby móc z nich korzystać jawnie, należy je określić Z(NOEXPAND).

Ponieważ w Zapytania LINQ Nie ma możliwości zdefiniowania podpowiedzi do tabeli, dlatego należy stworzyć inną reprezentację - „opakowanie” w postaci:

CREATE VIEW ИМЯ_представления AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Korzystanie z funkcji tabelowych

Często w Zapytania LINQ Duże bloki podzapytań lub bloki wykorzystujące widoki o złożonej strukturze tworzą końcowe zapytanie o bardzo złożonej i nieoptymalnej strukturze wykonania.

Kluczowe zalety korzystania z funkcji tabelowych w Zapytania LINQ:

Możliwość, podobnie jak w przypadku widoków, wykorzystania i określenia jako obiektu, przy czym można przekazać zestaw parametrów wejściowych:
Z FUNKCJI(@param1, @param2 ...)
W rezultacie można osiągnąć elastyczne próbkowanie danych
W przypadku wykorzystania funkcji tabelarycznej nie ma tak mocnych ograniczeń, jak w przypadku opisanych powyżej widoków indeksowanych:
1. Wskazówki do tabeli:
  przez LINQ Nie można określić, które indeksy mają być używane ani określić poziomu izolacji danych podczas wykonywania zapytań.
  Ale funkcja ma takie możliwości.
  Dzięki tej funkcji można osiągnąć w miarę stały plan zapytań wykonawczych, w którym zdefiniowane są zasady pracy z indeksami i poziomy izolacji danych
2. Użycie funkcji pozwala w porównaniu z widokami indeksowanymi uzyskać:
  - złożona logika próbkowania danych (nawet przy użyciu pętli)
  - pobieranie danych z wielu różnych tabel
  - wykorzystanie UNION и ISTNIEJE
Propozycja OPCJA bardzo przydatne, gdy musimy zapewnić kontrolę współbieżności OPCJA (MAXDOP N), kolejność planu wykonania zapytania. Na przykład:
- możesz określić wymuszone ponowne utworzenie planu zapytań OPCJA (PONOWNA KOMPILACJA)
- możesz określić, czy wymusić w planie zapytania użycie kolejności łączenia określonej w zapytaniu OPCJA (WYMUSZAJ ROZKAZ)
Więcej szczegółów dot OPCJA opisane tutaj.
Używanie najwęższego i najbardziej wymaganego wycinka danych:
Nie ma potrzeby przechowywania dużych zbiorów danych w pamięciach podręcznych (jak ma to miejsce w przypadku widoków indeksowanych), z których nadal trzeba filtrować dane po parametrach.
Na przykład istnieje tabela, której filter WHERE używane są trzy pola (a, b, c).

Konwencjonalnie wszystkie żądania mają stały warunek a = 0 i b = 0.

Jednak prośba o pole c bardziej zmienne.

Niech warunek a = 0 i b = 0 To naprawdę pomaga nam ograniczyć wymagany zestaw wynikowy do tysięcy rekordów, ale warunek jest włączony с zawęża wybór do setek rekordów.

Tutaj funkcja tabeli może być lepszą opcją.

Ponadto funkcja tabelaryczna jest bardziej przewidywalna i spójna pod względem czasu wykonania.

Примеры

Przyjrzyjmy się przykładowej implementacji na przykładzie bazy danych Pytania.

Jest prośba SELECT, który łączy kilka tabel i wykorzystuje jeden widok (OperativeQuestions), w którym sprawdzanie przynależności odbywa się drogą mailową (poprzez ISTNIEJE) do „Pytań operacyjnych”:

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ść złożoną strukturę: zawiera łączenia podzapytań i wykorzystuje sortowanie DISTINCT, co ogólnie jest operacją wymagającą dużych zasobów.

Próbka z OperativeQuestions obejmuje około dziesięciu tysięcy rekordów.

Głównym problemem tego zapytania jest to, że dla rekordów z zapytania zewnętrznego wykonywane jest wewnętrzne podzapytanie w widoku [OperativeQuestions], co powinno dla [Email] = @p__linq__0 pozwolić nam ograniczyć wybór wyników (poprzez ISTNIEJE) do setek rekordów.

I mogłoby się wydawać, że podzapytanie powinno przeliczyć rekordy raz przez [Email] = @p__linq__0, a potem te kilkaset rekordów należy połączyć Id z Pytaniami i zapytanie będzie szybkie.

W rzeczywistości istnieje sekwencyjne połączenie wszystkich tabel: sprawdzanie zgodności pytań identyfikacyjnych z identyfikatorem z OperativeQuestions i filtrowanie według poczty elektronicznej.

W rzeczywistości żądanie działa ze wszystkimi dziesiątkami tysięcy rekordów OperativeQuestions, ale potrzebne są tylko interesujące dane za pośrednictwem poczty elektronicznej.

Tekst widoku OperativeQuestions:

Zapytanie nr 2

 
CREATE VIEW [dbo].[OperativeQuestions]
AS
SELECT DISTINCT Q.Id, USR.email AS Email
FROM            [dbo].Questions AS Q INNER JOIN
                         [dbo].ProcessUserAccesses AS BPU ON BPU.ProcessId = CQ.Process_Id 
OUTER APPLY
                     (SELECT   1 AS HasNoObjects
                      WHERE   NOT EXISTS
                                    (SELECT   1
                                     FROM     [dbo].ObjectUserAccesses AS BOU
                                     WHERE   BOU.ProcessUserAccessId = BPU.[Id] AND BOU.[To] IS NULL)
) AS BO INNER JOIN
                         [dbo].Users AS USR ON USR.Id = BPU.UserId
WHERE        CQ.[Exp] = 0 AND CQ.AnswerId IS NULL AND BPU.[To] IS NULL 
AND (BO.HasNoObjects = 1 OR
              EXISTS (SELECT   1
                           FROM   [dbo].ObjectUserAccesses AS BOU INNER JOIN
                                      [dbo].ObjectQuestions AS QBO 
                                                  ON QBO.[Object_Id] =BOU.ObjectId
                               WHERE  BOU.ProcessUserAccessId = BPU.Id 
                               AND BOU.[To] IS NULL AND QBO.Question_Id = CQ.Id));

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

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().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 tym konkretnym przypadku rozważamy rozwiązanie tego problemu bez zmian infrastrukturalnych, bez wprowadzania osobnej tabeli z gotowymi wynikami („Aktywne zapytania”), co wymagałoby mechanizmu wypełniania jej danymi i aktualizowania .

Chociaż jest to dobre rozwiązanie, istnieje inna możliwość optymalizacji tego problemu.

Głównym celem jest buforowanie wpisów przez [Email] = @p__linq__0 z widoku OperativeQuestions.

Wprowadź funkcję tabelową [dbo].[OperativeQuestionsUserMail] do bazy danych.

Wysyłając Email jako parametr wejściowy, otrzymujemy 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

Zwraca to tabelę wartości z predefiniowaną strukturą danych.

Aby zapytania do OperativeQuestionsUserMail były optymalne i miały optymalne plany zapytań, wymagana jest ścisła struktura, a nie TABELA ZWROTÓW JAKO ZWROT...

W tym przypadku wymagane Zapytanie 1 jest konwertowane na 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 widoków i funkcji w DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> 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 zamówienia spadł z 200-800 ms do 2-20 ms itd., czyli kilkadziesiąt razy szybciej.

Jeśli weźmiemy to bardziej przeciętnie, to zamiast 350 ms otrzymamy 8 ms.

Z oczywistych zalet otrzymujemy również:

ogólne zmniejszenie obciążenia czytelniczego,
znaczne zmniejszenie prawdopodobieństwa zablokowania
skrócenie średniego czasu blokowania do akceptowalnych wartości

Wniosek

Optymalizacja i dostrajanie wywołań baz danych MS SQL przez LINQ jest problemem, który można rozwiązać.

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

Na początku procesu:

konieczne jest sprawdzenie danych, z którymi współpracuje żądanie (wartości, wybrane typy danych)
przeprowadzić odpowiednią indeksację tych danych
sprawdź poprawność warunków łączenia tabel

Następna iteracja optymalizacji ujawnia:

podstawie żądania i definiuje główny filtr żądań
powtarzanie podobnych bloków zapytań i analizowanie przecięcia warunków
w SSMS lub innym GUI dla SQL Server sam się optymalizuje Zapytanie SQL (przydzielanie pośredniego magazynu danych, budowanie wynikowego zapytania przy użyciu tego magazynu (może być ich kilka))
na ostatnim etapie, biorąc za podstawę wynik Zapytanie SQL, konstrukcja jest w trakcie odbudowy Zapytanie LINQ

Wynikowy Zapytanie LINQ powinien uzyskać identyczną strukturę ze zidentyfikowanym optymalnym Zapytanie SQL z punktu 3.

Podziękowanie

Wielkie dzięki dla kolegów Jobgemws и alex_ozr od firmy Fortis za pomoc w przygotowaniu tego materiału.

Źródło: www.habr.com

Metody optymalizacji zapytań LINQ w C#.NET