🥇Metode za optimizaciju LINQ upita u C#.NET

Uvod

В ovaj članak razmatrane su neke metode optimizacije LINQ upiti.
Ovdje također predstavljamo još neke pristupe optimizaciji koda koji se odnose na LINQ upiti.

To je poznato LINQ(Language-Integrated Query) je jednostavan i zgodan jezik za ispitivanje izvora podataka.

А LINQ na SQL je tehnologija za pristup podacima u DBMS-u. Ovo je moćan alat za rad sa podacima, gde se upiti konstruišu putem deklarativnog jezika, koji će se zatim konvertovati u SQL upiti platformi i poslat na server baze podataka na izvršenje. U našem slučaju, pod DBMS mislimo MS SQL Server.

Međutim, LINQ upiti se ne pretvaraju u optimalno napisane SQL upiti, koji bi iskusni DBA mogao napisati sa svim nijansama optimizacije SQL upiti:

optimalne veze (JOIN) i filtriranje rezultata (GDJE)
mnoge nijanse u korištenju veza i grupnih uvjeta
mnoge varijacije u uslovima zamene IN na POSTOJIи NOT IN, <> uključeno POSTOJI
srednje keširanje rezultata preko privremenih tabela, CTE, varijabli tablice
upotreba rečenice (OPCIJA) s uputama i tabličnim savjetima S (...)
korištenje indeksiranih pogleda kao jednog od načina da se riješite suvišnih očitavanja podataka tokom odabira

Glavna uska grla performansi rezultiraju SQL upiti prilikom kompajliranja LINQ upiti su:

objedinjavanje cjelokupnog mehanizma odabira podataka u jednom zahtjevu
dupliranje identičnih blokova koda, što u konačnici dovodi do višestrukog čitanja nepotrebnih podataka
grupe višekomponentnih uslova (logički "i" i "ili") - I и OR, kombinovanjem u složene uslove, dovodi do činjenice da optimizator, koji ima odgovarajuće neklasterizovane indekse za potrebna polja, na kraju počinje da skenira prema grupisanom indeksu (INDEX SCAN) po grupama uslova
duboko ugniježđenje potupita čini raščlanjivanje vrlo problematičnim SQL izrazi i analiza plana upita od strane programera i DBA

Metode optimizacije

Sada idemo direktno na metode optimizacije.

1) Dodatno indeksiranje

Najbolje je razmotriti filtere na glavnim tabelama odabira, jer se vrlo često cijeli upit gradi oko jedne ili dvije glavne tabele (aplikacije-ljudi-operacije) i sa standardnim skupom uslova (IsClosed, Canceled, Enabled, Status). Važno je kreirati odgovarajuće indekse za identifikovane uzorke.

Ovo rješenje ima smisla kada odabir ovih polja značajno ograničava vraćeni skup na upit.

Na primjer, imamo 500000 aplikacija. Međutim, postoji samo 2000 aktivnih aplikacija. Tada će nas ispravno odabran indeks spasiti INDEX SCAN na velikoj tablici i omogućit će vam da brzo odaberete podatke kroz negrupisani indeks.

Takođe, nedostatak indeksa se može identifikovati kroz upite za raščlanjivanje planova upita ili prikupljanje statistike pregleda sistema MS SQL Server:

Svi podaci pogleda sadrže informacije o indeksima koji nedostaju, s izuzetkom prostornih indeksa.

Međutim, indeksi i keširanje su često metode borbe protiv posljedica loše napisanog LINQ upiti и SQL upiti.

Kao što pokazuje surova životna praksa, za posao je često važno da u određenim rokovima implementira poslovne karakteristike. Stoga se teški zahtjevi često prenose u pozadinu uz keširanje.

Ovo je djelimično opravdano, jer korisniku nisu uvijek potrebni najnoviji podaci i postoji prihvatljiv nivo odziva korisničkog interfejsa.

Ovaj pristup omogućava rješavanje poslovnih potreba, ali u konačnici smanjuje performanse informacionog sistema jednostavnim odlaganjem rješenja problema.

Također je vrijedno zapamtiti da u procesu traženja potrebnih indeksa za dodavanje, prijedlozi MSSQL optimizacija može biti netačna, uključujući pod sljedećim uvjetima:

ako već postoje indeksi sa sličnim skupom polja
ako se polja u tabeli ne mogu indeksirati zbog ograničenja indeksiranja (detaljnije opisano ovdje).

2) Spajanje atributa u jedan novi atribut

Ponekad se neka polja iz jedne tabele, koja služe kao osnova za grupu uslova, mogu zameniti uvođenjem jednog novog polja.

Ovo posebno važi za statusna polja, koja su obično ili bitnog ili celobrojnog tipa.

Primjer:

Zatvoreno = 0 I Otkazano = 0 I Omogućeno = 0 zamjenjuje se sa Status = 1.

Ovdje se uvodi integer Status atribut kako bi se osiguralo da su ovi statusi popunjeni u tablici. Zatim se ovaj novi atribut indeksira.

Ovo je fundamentalno rješenje problema performansi, jer pristupamo podacima bez nepotrebnih proračuna.

3) Materijalizacija pogleda

Nažalost in LINQ upiti Privremene tablice, CTE-ovi i varijable tablice ne mogu se koristiti direktno.

Međutim, postoji još jedan način optimizacije za ovaj slučaj - indeksirani prikazi.

Grupa uvjeta (iz primjera iznad) Zatvoreno = 0 I Otkazano = 0 I Omogućeno = 0 (ili skup drugih sličnih uslova) postaje dobra opcija za njihovo korištenje u indeksiranom prikazu, keširajući mali dio podataka iz velikog skupa.

Ali postoji niz ograničenja kada se materijalizuje pogled:

korištenje potupita, klauzula POSTOJI treba zamijeniti korištenjem JOIN
ne možete koristiti rečenice UNION, UNION ALL, Izuzetak, INTERSECT
Ne možete koristiti nagoveštaje i klauzule tabele OPCIJA
nema mogućnosti rada sa ciklusima
Nemoguće je prikazati podatke u jednom prikazu iz različitih tabela

Važno je zapamtiti da se prava korist od korištenja indeksiranog pogleda može postići samo njegovim stvarnim indeksiranjem.

Ali kada pozivate pogled, ovi indeksi se možda neće koristiti, a da biste ih eksplicitno koristili, morate specificirati WITH(NOEXPAND).

Od godine LINQ upiti Nemoguće je definirati nagoveštaje tablice, tako da morate kreirati drugu reprezentaciju - "omotač" sljedećeg oblika:

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

4) Korištenje funkcija tablice

Često u LINQ upiti Veliki blokovi podupita ili blokovi koji koriste poglede sa složenom strukturom formiraju konačni upit sa vrlo složenom i suboptimalnom strukturom izvršenja.

Ključne prednosti korištenja tabličnih funkcija u LINQ upiti:

Mogućnost, kao u slučaju pogleda, da se koristi i specificira kao objekt, ali možete proći skup ulaznih parametara:
FROM FUNCTION(@param1, @param2 ...)
Kao rezultat, može se postići fleksibilno uzorkovanje podataka
U slučaju korištenja tablične funkcije, ne postoje tako jaka ograničenja kao u slučaju gore opisanih indeksiranih pogleda:
1. Tablični savjeti:
  kroz LINQ Ne možete specificirati koje indekse treba koristiti i odrediti razinu izolacije podataka prilikom postavljanja upita.
  Ali funkcija ima ove mogućnosti.
  Pomoću ove funkcije možete postići prilično konstantan plan izvršavanja upita, gdje su definirana pravila za rad s indeksima i nivoi izolacije podataka
2. Upotreba funkcije omogućava, u poređenju sa indeksiranim prikazima, da dobijete:
  - složena logika uzorkovanja podataka (čak i korištenjem petlji)
  - dohvaćanje podataka iz mnogo različitih tabela
  - korištenje UNION и POSTOJI
Ponuda OPCIJA vrlo korisno kada trebamo osigurati kontrolu konkurentnosti OPCIJA (MAXDOP N), redoslijed plana izvršenja upita. Na primjer:
- možete specificirati prisilno ponovno kreiranje plana upita OPCIJA (PREKOMPILACIJA)
- možete odrediti da li ćete prisiliti plan upita da koristi redoslijed spajanja naveden u upitu OPCIJA (NASILNO NARUDŽENJE)
Više detalja o OPCIJA opisano ovdje.
Koristeći najuži i najpotrebniji dio podataka:
Nema potrebe za pohranjivanjem velikih skupova podataka u keš memorije (kao što je slučaj s indeksiranim prikazima), iz kojih i dalje trebate filtrirati podatke po parametru.
Na primjer, postoji tabela čiji filter GDJE koriste se tri polja (a, b, c).

Konvencionalno, svi zahtjevi imaju stalan uslov a = 0 i b = 0.

Međutim, zahtjev za teren c varijabilnije.

Neka uslov a = 0 i b = 0 Zaista nam pomaže da ograničimo traženi rezultujući skup na hiljade zapisa, ali uslov je uključen с sužava izbor na stotinu zapisa.

Ovdje funkcija tablice može biti bolja opcija.

Također, tabela funkcija je predvidljivija i dosljednija u vremenu izvršenja.

primjeri

Pogledajmo primjer implementacije koristeći bazu podataka Questions kao primjer.

Postoji zahtjev SELECT, koji kombinuje nekoliko tabela i koristi jedan pogled (OperativeQuestions), u kojem se pripadnost provjerava putem e-pošte (putem POSTOJI) do “Operativna pitanja”:

Zahtjev br. 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])
));

Pogled ima prilično složenu strukturu: ima spojeve podupita i koristi sortiranje DISTINCT, što je generalno prilično intenzivna operacija.

Uzorak iz OperativeQuestions je oko deset hiljada zapisa.

Glavni problem sa ovim upitom je taj što se za zapise iz vanjskog upita izvršava interni potupit na prikazu [OperativeQuestions], što bi nam za [Email] = @p__linq__0 trebalo omogućiti da ograničimo izbor izlaza (preko POSTOJI) do stotine zapisa.

I može se činiti da potupit treba jednom izračunati zapise po [Email] = @p__linq__0, a zatim ovih par stotina zapisa treba povezati Id-om sa pitanjima i upit će biti brz.

U stvari, postoji sekvencijalna veza svih tabela: provjera korespondencije Id pitanja sa ID-om iz OperativeQuestions i filtriranje putem e-pošte.

Zapravo, zahtjev radi sa svim desetinama hiljada OperativeQuestions zapisa, ali su potrebni samo podaci od interesa putem e-pošte.

OperativeQuestions pogledajte tekst:

Zahtjev br. 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));

Početno mapiranje prikaza u 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");
    }
}

Početni LINQ upit

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

U konkretnom slučaju razmatramo rješenje ovog problema bez infrastrukturnih promjena, bez uvođenja posebne tabele sa gotovim rezultatima („Aktivni upiti“), što bi zahtijevalo mehanizam za popunjavanje podacima i njihovo ažuriranje. .

Iako je ovo dobro rješenje, postoji još jedna opcija za optimizaciju ovog problema.

Glavna svrha je keširanje unosa pomoću [Email] = @p__linq__0 iz prikaza OperativeQuestions.

Uvesti funkciju tabele [dbo].[OperativeQuestionsUserMail] u bazu podataka.

Slanjem e-pošte kao ulaznog parametra, dobijamo tabelu vrijednosti:

Zahtjev br. 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

Ovo vraća tablicu vrijednosti s unaprijed definiranom strukturom podataka.

Da bi upiti za OperativeQuestionsUserMail bili optimalni i imali optimalne planove upita, potrebna je stroga struktura, a ne TABLICA POVRATA KAO POVRATAK...

U ovom slučaju, traženi upit 1 se pretvara u upit 4:

Zahtjev br. 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]);

Pregledi i funkcije mapiranja u 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})");
}

Konačni LINQ upit

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

Redoslijed vremena izvršenja je pao sa 200-800 ms, na 2-20 ms itd., odnosno desetine puta brže.

Ako uzmemo prosječno, onda umjesto 350 ms dobili smo 8 ms.

Od očiglednih prednosti dobijamo i:

opšte smanjenje opterećenja čitanjem,
značajno smanjenje vjerovatnoće blokiranja
smanjenje prosječnog vremena blokiranja na prihvatljive vrijednosti

zaključak

Optimizacija i fino podešavanje poziva baze podataka MSSQL kroz LINQ je problem koji se može riješiti.

Pažnja i doslednost su veoma važni u ovom poslu.

Na početku procesa:

potrebno je provjeriti podatke sa kojima zahtjev funkcionira (vrijednosti, odabrani tipovi podataka)
izvršiti odgovarajuće indeksiranje ovih podataka
provjeriti ispravnost uslova spajanja između tabela

Sljedeća iteracija optimizacije otkriva:

osnova zahtjeva i definira glavni filter zahtjeva
ponavljanje sličnih blokova upita i analiziranje preseka uslova
u SSMS ili drugom GUI za SQL Server optimizira samu sebe SQL upit (dodjela posredne memorije podataka, izgradnja rezultirajućeg upita koristeći ovu pohranu (može ih biti nekoliko))
u posljednjoj fazi, uzimajući kao osnovu rezultat SQL upit, objekat se obnavlja LINQ upit

Rezultat LINQ upit treba da po strukturi postane identičan identifikovanom optimalnom SQL upit od tačke 3.

Zahvalnice

Veliko hvala kolegama jobgemws и alex_ozr iz kompanije Fortis za pomoć u pripremi ovog materijala.

izvor: www.habr.com

Metode za optimizaciju LINQ upita u C#.NET