🥇Metode pentru optimizarea interogărilor LINQ în C#.NET

Introducere

В acest articol au fost luate în considerare unele metode de optimizare interogări LINQ.
Aici vă prezentăm și câteva abordări legate de optimizarea codului interogări LINQ.

Este cunoscut faptul că LINQ(Language-Integrated Query) este un limbaj simplu și convenabil pentru interogarea unei surse de date.

А LINQ to SQL este o tehnologie de accesare a datelor într-un SGBD. Acesta este un instrument puternic pentru lucrul cu date, în care interogările sunt construite printr-un limbaj declarativ, care va fi apoi convertit în interogări SQL platformă și trimis la serverul bazei de date pentru execuție. În cazul nostru, prin DBMS ne referim MS SQL Server.

Cu toate acestea, interogări LINQ nu sunt convertite în cele scrise optim interogări SQL, pe care un DBA experimentat l-ar putea scrie cu toate nuanțele optimizării interogări SQL:

conexiuni optime (JOIN) și filtrarea rezultatelor (UNDE)
multe nuanțe în utilizarea conexiunilor și a condițiilor de grup
multe variații în condițiile de înlocuire IN pe EXISTĂи NU ÎN, <> activat EXISTĂ
stocarea intermediară a rezultatelor prin tabele temporare, CTE, variabile de tabel
folosirea propoziției (OPȚIUNE) cu instrucțiuni și indicii de tabel CU (...)
utilizarea vizualizărilor indexate ca unul dintre mijloacele de a scăpa de citirile redundante ale datelor în timpul selecțiilor

Principalele blocaje de performanță ale rezultatului interogări SQL la compilare interogări LINQ Acestea sunt:

consolidarea întregului mecanism de selecție a datelor într-o singură cerere
duplicarea blocurilor identice de cod, ceea ce duce în cele din urmă la citiri multiple de date inutile
grupuri de condiții cu mai multe componente („și” logic și „sau”) - AND и OR, combinându-se în condiții complexe, duce la faptul că optimizatorul, având indecși adecvați non-cluster pentru câmpurile necesare, începe în cele din urmă să scaneze împotriva indexului grupat (SCANARE INDEX) pe grupe de condiții
imbricarea profundă a subinterogărilor face ca analizarea să fie foarte problematică Instrucțiuni SQL și analiza planului de interogare din partea dezvoltatorilor și DBA

Metode de optimizare

Acum să trecem direct la metodele de optimizare.

1) Indexare suplimentară

Cel mai bine este să luați în considerare filtrele pe tabelele de selecție principale, deoarece foarte adesea întreaga interogare este construită în jurul unuia sau două tabele principale (aplicații-oameni-operații) și cu un set standard de condiții (IsClosed, Canceled, Enabled, Status). Este important să se creeze indici corespunzători pentru eșantioanele identificate.

Această soluție are sens atunci când selectarea acestor câmpuri limitează semnificativ setul returnat la interogare.

De exemplu, avem 500000 de aplicații. Cu toate acestea, există doar 2000 de aplicații active. Apoi, un index selectat corect ne va salva de SCANARE INDEX pe un tabel mare și vă va permite să selectați rapid datele printr-un index non-cluster.

De asemenea, lipsa indexurilor poate fi identificată prin solicitări pentru analizarea planurilor de interogare sau colectarea statisticilor de vizualizare a sistemului MS SQL Server:

Toate datele de vizualizare conțin informații despre indecșii lipsă, cu excepția indecșilor spațiali.

Cu toate acestea, indexurile și memorarea în cache sunt adesea metode de combatere a consecințelor scrisului prost interogări LINQ и interogări SQL.

După cum arată practica dură a vieții, este adesea important pentru o afacere să implementeze caracteristicile de afaceri până la anumite termene limită. Și, prin urmare, solicitările grele sunt adesea transferate în fundal cu cache.

Acest lucru este parțial justificat, deoarece utilizatorul nu are întotdeauna nevoie de cele mai recente date și există un nivel acceptabil de receptivitate al interfeței cu utilizatorul.

Această abordare permite rezolvarea nevoilor afacerii, dar în cele din urmă reduce performanța sistemului informațional prin simpla întârziere a soluțiilor la probleme.

De asemenea, merită să ne amintim că în procesul de căutare a indecșilor necesari pentru a adăuga, sugestii MS SQL optimizarea poate fi incorectă, inclusiv în următoarele condiții:

dacă există deja indecși cu un set similar de câmpuri
dacă câmpurile din tabel nu pot fi indexate din cauza restricțiilor de indexare (descrise mai detaliat aici).

2) Îmbinarea atributelor într-un singur atribut nou

Uneori, unele câmpuri dintr-un tabel, care servesc ca bază pentru un grup de condiții, pot fi înlocuite prin introducerea unui câmp nou.

Acest lucru este valabil mai ales pentru câmpurile de stare, care de obicei sunt de tip biți sau întregi.

Exemplu:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 este înlocuit cu Stare = 1.

Aici este introdus atributul de stare întreg pentru a se asigura că aceste stări sunt populate în tabel. Apoi, acest nou atribut este indexat.

Aceasta este o soluție fundamentală la problema performanței, deoarece Accesăm date fără calcule inutile.

3) Materializarea vederii

Din păcate, în interogări LINQ Tabelele temporare, CTE-urile și variabilele de tabel nu pot fi utilizate direct.

Cu toate acestea, există o altă modalitate de optimizare pentru acest caz - vizualizările indexate.

Grup de condiții (din exemplul de mai sus) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (sau un set de alte condiții similare) devine o opțiune bună pentru a le folosi într-o vizualizare indexată, memorând în cache o mică parte de date dintr-un set mare.

Dar există o serie de restricții la materializarea unei vederi:

utilizarea de subinterogări, clauze EXISTĂ trebuie înlocuit prin folosire JOIN
nu poți folosi propoziții UNIUNE, UNION TOATE, EXCEPȚIE, INTERSECT
Nu puteți folosi indicii și clauze de tabel OPȚIUNE
nicio posibilitate de a lucra cu cicluri
Este imposibil să afișați datele într-o singură vizualizare din tabele diferite

Este important să ne amintim că beneficiul real al utilizării unei vizualizări indexate poate fi obținut doar prin indexarea efectivă a acesteia.

Dar atunci când apelați o vizualizare, acești indecși nu pot fi utilizați și, pentru a le folosi în mod explicit, trebuie să specificați CU(NOEXPAND).

De când în interogări LINQ Este imposibil să definiți indicii de tabel, așa că trebuie să creați o altă reprezentare - un „înveliș” de următoarea formă:

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

4) Utilizarea funcțiilor de tabel

Adesea în interogări LINQ Blocurile mari de subinterogări sau blocurile care utilizează vederi cu o structură complexă formează o interogare finală cu o structură de execuție foarte complexă și suboptimă.

Avantajele cheie ale utilizării funcțiilor de tabel în interogări LINQ:

Capacitatea, ca și în cazul vizualizărilor, de a fi utilizat și specificat ca obiect, dar puteți trece un set de parametri de intrare:
FROM FUNCTION(@param1, @param2 ...)
Ca rezultat, poate fi realizată o eșantionare flexibilă a datelor
În cazul utilizării unei funcții de tabel, nu există restricții atât de puternice ca în cazul vizualizărilor indexate descrise mai sus:
1. Sugestii pentru tabel:
  prin LINQ Nu puteți specifica ce indecși trebuie utilizați și nu puteți determina nivelul de izolare a datelor atunci când interogați.
  Dar funcția are aceste capacități.
  Cu această funcție, puteți realiza un plan de interogare de execuție destul de constant, în care sunt definite reguli pentru lucrul cu indici și niveluri de izolare a datelor
2. Utilizarea funcției permite, în comparație cu vizualizările indexate, obținerea:
  - logica complexă de eșantionare a datelor (chiar folosind bucle)
  - preluarea datelor din multe tabele diferite
  - utilizarea UNIUNE и EXISTĂ
RџSЂRμRґR "RѕR¶RμRЅRёRμ OPȚIUNE foarte util atunci când trebuie să asigurăm controlul concurenței OPȚIUNE(MAXDOP N), ordinea planului de executare a interogării. De exemplu:
- puteți specifica o recreare forțată a planului de interogare OPȚIUNE (RECOMPILARE)
- puteți specifica dacă să forțați planul de interogare să utilizeze ordinea de alăturare specificată în interogare OPȚIUNE (COMANDA FORȚATĂ)
Mai multe detalii despre OPȚIUNE descris aici.
Folosind cea mai îngustă și mai solicitată secțiune de date:
Nu este nevoie să stocați seturi mari de date în cache (cum este cazul vizualizărilor indexate), din care încă trebuie să filtrați datele după parametru.
De exemplu, există un tabel al cărui filtru UNDE sunt utilizate trei câmpuri (a, b, c).

În mod convențional, toate cererile au o condiție constantă a = 0 și b = 0.

Cu toate acestea, cererea pentru teren c mai variabil.

Lasă starea a = 0 și b = 0 Ne ajută cu adevărat să limităm setul rezultat necesar la mii de înregistrări, dar condiția este valabilă с restrânge selecția la o sută de înregistrări.

Aici funcția tabel poate fi o opțiune mai bună.

De asemenea, o funcție de tabel este mai previzibilă și mai consecventă în timpul de execuție.

exemple

Să ne uităm la un exemplu de implementare folosind baza de date Întrebări ca exemplu.

Există o cerere SELECT, care combină mai multe tabele și utilizează o singură vizualizare (OperativeQuestions), în care afilierea este verificată prin e-mail (prin EXISTĂ) la „Întrebări operative”:

Cererea 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])
));

Vederea are o structură destul de complexă: are îmbinări de subinterogare și folosește sortarea DISTINCT, care, în general, este o operațiune destul de intensivă în resurse.

O mostră din OperativeQuestions este de aproximativ zece mii de înregistrări.

Problema principală cu această interogare este că pentru înregistrările din interogarea exterioară, se execută o subinterogare internă în vizualizarea [OperativeQuestions], care ar trebui să ne permită pentru [Email] = @p__linq__0 să limităm selecția de ieșire (prin EXISTĂ) până la sute de înregistrări.

Și poate părea că subinterogarea ar trebui să calculeze înregistrările o dată prin [Email] = @p__linq__0, iar apoi aceste două sute de înregistrări ar trebui să fie conectate prin Id cu întrebări, iar interogarea va fi rapidă.

De fapt, există o conexiune secvențială a tuturor tabelelor: verificarea corespondenței Întrebărilor Id cu Id-ul din OperativeQuestions și filtrarea prin e-mail.

De fapt, cererea funcționează cu toate zecile de mii de înregistrări OperativeQuestions, dar numai datele de interes sunt necesare prin e-mail.

Operative Questions vizualizați text:

Cererea 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));

Maparea inițială a vederii în 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");
    }
}

Interogare LINQ inițială

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();

În acest caz particular, avem în vedere o soluție la această problemă fără modificări de infrastructură, fără a introduce un tabel separat cu rezultate gata făcute („Interogări active”), care ar necesita un mecanism de completare cu date și menținere la zi. .

Deși aceasta este o soluție bună, există o altă opțiune pentru a optimiza această problemă.

Scopul principal este stocarea în cache a intrărilor prin [Email] = @p__linq__0 din vizualizarea OperativeQuestions.

Introduceți funcția tabel [dbo].[OperativeQuestionsUserMail] în baza de date.

Trimițând e-mail ca parametru de intrare, obținem înapoi un tabel cu valori:

Cererea 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

Aceasta returnează un tabel de valori cu o structură de date predefinită.

Pentru ca interogările către OperativeQuestionsUserMail să fie optime și să aibă planuri de interogare optime, este necesară o structură strictă și nu TABEL DE RETURNĂRI CA RETURNARE...

În acest caz, interogarea 1 necesară este convertită în interogarea 4:

Cererea 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]);

Maparea vizualizărilor și funcțiilor în 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})");
}

Interogare LINQ finală

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();

Ordinea timpului de execuție a scăzut de la 200-800 ms, la 2-20 ms etc., adică de zeci de ori mai rapid.

Dacă o luăm mai medie, atunci în loc de 350 ms avem 8 ms.

Din avantajele evidente obținem și:

reducerea generală a sarcinii de citire,
reducerea semnificativă a probabilității de blocare
reducerea timpului mediu de blocare la valori acceptabile

Producție

Optimizarea și reglarea fină a apelurilor la baza de date MS SQL prin LINQ este o problemă care poate fi rezolvată.

Atenția și consecvența sunt foarte importante în această muncă.

La începutul procesului:

este necesar să se verifice datele cu care funcționează cererea (valori, tipuri de date selectate)
efectuați o indexare adecvată a acestor date
verificați corectitudinea condițiilor de îmbinare între tabele

Următoarea iterație de optimizare dezvăluie:

baza cererii și definește filtrul de cerere principal
repetarea blocurilor de interogare similare și analizarea intersecției condițiilor
în SSMS sau altă GUI pentru SQL Server se optimizează singur interogare SQL (alocarea unei stocări intermediare de date, construirea interogării rezultate folosind această stocare (pot fi mai multe))
în ultima etapă luând ca bază rezultatul interogare SQL, structura este în curs de reconstrucție Interogare LINQ

Rezultați Interogare LINQ ar trebui să devină identic ca structură cu optimul identificat interogare SQL de la punctul 3.

Mulțumiri

Multumesc mult colegilor jobgemws и alex_ozr de la companie Fortis pentru asistență în pregătirea acestui material.

Sursa: www.habr.com

Metode pentru optimizarea interogărilor LINQ în C#.NET