ProHoster > Blog > Administracija > Metode optimizacije LINQ upita u C#.NET

Metode optimizacije LINQ upita u C#.NET

Uvod

U ovom članku razmatrane su neke metode optimizacije LINQ upita.
Ovdje ćemo također navesti neke pristupe optimizaciji koda vezane uz LINQ upite.

Poznato je da LINQ(Language-Integrated Query) — to je jednostavan i praktičan jezik za upite na izvor podataka.

A LINQ to SQL je tehnologija pristupa podacima u DBMS-u. To je moćan alat za rad s podacima, gdje se deklarativnim jezikom konstruiraju upiti, koji se zatim pretvaraju u SQL upite platformu i šalju na server baze podataka na izvršavanje. U našem slučaju, pod DBMS-om ćemo podrazumijevati MS SQL Server.

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

  1. optimalna spajanja (JOIN) i filtriranje rezultata (GDJE)
  2. mnogo nijansi u korištenju spajanja i grupnih uvjeta
  3. mnogo varijacija u zamjeni uvjeta IN na EXISTSi NOT IN, , na EXISTS
  4. interim keširanje rezultata putem privremenih tablica, CTE, tabličnih varijabli
  5. korištenje izjave (OPTION) s uputama i tabličnim hintovima WITH (…)
  6. korištenje indeksiranih prikaza, kao jedan od načina da se oslobodimo suvišnog čitanja podataka prilikom selekcija

Glavne uska grla performansi nastaju SQL upita prilikom kompajliranja LINQ upita su:

  1. konsolidacija cijelog mehanizma izbora podataka u jednom upitu
  2. dupliranje identičnih blokova koda, što na kraju dovodi do višestrukih suvišnih čitanja podataka
  3. grupe višesloženih uvjeta (logički 'i' i 'ili') — AND i ILI, spajajući se u složene uvjete, dovodi do toga da optimizator, imajući odgovarajuće neklasterizirane indekse, na potrebnim poljima, na kraju ipak počinje raditi skeniranje po klaster indeksu (INDEX SCAN) po grupama uvjeta
  4. duboka ugniježdenost podupita otežava analizu SQL instrukcija i analizu plana upita sa strane programera i DBA

Metode optimizacije

Sada prelazimo direktno na metode optimizacije.

1) Dodatno indeksiranje

Najbolje je razmatrati filtere na glavnim tablicama selekcije, jer se vrlo često cijeli upit gradi oko jedne do dvije glavne tablice (zahtjevi- ljudi- operacije) i sa standardnim setom uvjeta (IsClosed, Canceled, Enabled, Status). Važno je da se za identificirane selekcije stvore odgovarajući indeksi.

Ovo rješenje ima smisla kada izbor prema ovim poljima značajno ograničava vraćeni skup upitima.

Na primjer, imamo 500000 prijava. Međutim, aktivnih prijava ima samo 2000 zapisa. Tada će dobro odabrani indeks ukloniti INDEX SCAN pretragu velike tablice i omogućiti brz odabir podataka putem neklasteriziranog indeksa.

Nedostatak indeksa također se može otkriti putem savjeta za analizu planova upita ili prikupljanja statistike sistemskih prikaza MS SQL Server:

  1. sys.dm_db_missing_index_groups
  2. sys.dm_db_missing_index_group_stats
  3. sys.dm_db_missing_index_details

Svi podaci prikaza sadrže informacije o nedostajućim indeksima, osim prostornog indeksa.

Međutim, indeksi i međuspremnici često su metode za borbu protiv posljedica loše napisane LINQ upita i SQL upita.

Kako pokazuje surova poslovna praksa, često je važna implementacija poslovnih značajki do određenih rokova. I stoga se često teški upiti prebacuju u pozadinu s međuspremnikom.

Djelomično je to opravdano, budući da korisnicima ne trebaju uvijek najnoviji podaci i prihvatljiva je razina odgovora korisničkog sučelja.

Ovaj pristup omogućuje rješavanje poslovnih zahtjeva, ali na kraju smanjuje funkcionalnost informacijskog sustava, jednostavno odgađajući rješavanje problema.

Također treba imati na umu da u procesu pronalaženja potrebnih za dodavanje novih indeksa, prijedlozi Graf*, dokument za optimizaciju mogu biti netočni, osobito u sljedećim uvjetima:

  1. ako već postoje indeksi s sličnim skupom polja
  2. ako polja u tablici ne mogu biti indeksirana zbog ograničenja indeksiranja (više detalja o tome je opisano ovdje).

2) Kombinacija atributa u jedan novi atribut

Ponekad se neka polja iz jedne tablice, prema kojima se postavljaju uvjeti grupe, mogu zamijeniti uvođenjem jednog novog polja.

To je posebno relevantno za polja-stanja, koja su obično ili bitna ili cjelobrojna.

Primjer:

IsClosed = 0 I Canceled = 0 I Enabled = 0 zamjenjuje se s Status = 1.

Ovdje se uvodi cjelobrojni atribut Status, osiguran ispunjavanjem tih statusa u tablici. Zatim se indeksira ovaj novi atribut.

To je temeljno rješenje problema izvedbe, jer se podacima obraćamo bez suvišnih izračuna.

3) Materializacija prikaza

Nažalost, u LINQ upitima nije moguće izravno koristiti privremene tablice, CTE i tablične varijable.

Međutim, postoji još jedan način optimizacije u ovom slučaju — indeksirani prikazi.

Grupa uvjeta (iz gornjeg primjera) IsClosed = 0 I Canceled = 0 I Enabled = 0 (ili skup drugih sličnih uvjeta) postaje dobra opcija za korištenje u indeksiranom prikazu, keširajući mali isječak podataka iz velikog skupa.

Ali postoje određena ograničenja pri materijalizaciji prikaza:

  1. korištenje podupita, prijedloga EXISTS moraju se zamijeniti korištenjem JOIN
  2. nije moguće koristiti prijedloge UNION, UNION ALL, IZUZEĆE, PREKLAPANJE
  3. nije moguće koristiti tablične savjete i prijedloge OPTION
  4. nema mogućnosti rada s petljama
  5. nije moguće prikazivati podatke u jednom prikazu iz različitih tablica

Važno je zapamtiti da se stvarna korist od korištenja indeksiranog prikaza može ostvariti samo kada je indeksiran.

Ali pri pozivu prikaza ti indeksi možda neće biti korišteni, a za njihovu eksplicitnu upotrebu potrebno je naznačiti WITH (NOEXPAND).

Budući da u LINQ upitima nije moguće definirati tablične savjete, tako da se mora napraviti još jedan prikaz — 'omotač' sljedećeg tipa:

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

4) Korištenje tabličnih funkcija

Često u LINQ upitima veliki blokovi podupita ili blokovi koji koriste prikaze sa složenom strukturom čine konačni upit s vrlo složenom i neoptimalnom strukturom izvršavanja.

Osnovne prednosti korištenja tabličnih funkcija u LINQ upitima:

  1. Mogućnost, kao i u slučaju prikaza, koristiti i naznačiti kao objekt, ali se može prenijeti skup ulaznih parametara:
    FROM FUNCTION(@param1, @param2 …)
    na kraju se može postići fleksibilan izbor podataka
  2. U slučaju korištenja tablične funkcije nema tako stroga ograničenja kao u slučaju s indeksiranim prikazima, opisanim iznad:
    1. Tablični savjeti:
      preko LINQ nije moguće naznačiti koje indekse treba koristiti i definirati razinu izolacije podataka prilikom upita.
      Ali u funkcijama ove mogućnosti postoje.
      S funkcijom se može postići dovoljno dosljedan plan izvršavanja upita, gdje su definirana pravila rada s indeksima i razine izolacije podataka
    2. Korištenje funkcije omogućuje, u usporedbi s indeksiranim prikazima, dobivanje:
      • kompleksnu logiku za preuzimanje podataka (ni do korištenja petlji)
      • preuzimanje podataka iz više različitih tablica
      • korisnik UNION i EXISTS

  3. Ponuda OPTION vrlo korisno kada trebamo osigurati upravljanje paralelizmom OPTION(MAXDOP N), redoslijedom plana izvršavanja upita. Na primjer:
    • može se odrediti prisilno ponovno stvaranje plana upita OPTION (RECOMPILE)
    • može se odrediti potreba za prisilnom upotrebom reda spajanja navedenog u upitu OPTION (FORCE ORDER)

    Detaljnije o OPTION je opisano ovdje.

  4. Korištenje najužeg i potrebnog presjeka podataka:
    Nema potrebe za zadržavanjem velikih skupova podataka u predmemorijama (kao u slučaju indeksiranih prikaza), iz kojih se još mora filtrirati podaci prema parametru.
    Na primjer, postoji tablica koja ima tri polja za filtriranje GDJE (a, b, c) Kondicionalno za sve upite postoji stalni uvjet.

    a = 0 i b = 0 Međutim, upit na polju.

    je više varijantni. c Neka uvjet

    doista pomaže ograničiti potrebni dobiveni skup na tisuće zapisa, ali uvjet po Međutim, upit na polju sužava izbor do stotinu zapisa. s Ovdje bi tablična funkcija mogla biti isplativija opcija.

    Također, tablična funkcija je predvidljivija i stabilnija u pogledu vremena izvršavanja.

    Razmotrimo primjer implementacije na primjeru baze podataka Questions.

Primjeri

Postoji upit

, koji spaja nekoliko tablica i koristi jedno prikazivanje (OperativeQuestions) u kojem se provjerava pripadnost putem emaila (kroz SELECT) "Aktivnim upitima"([OperativeQuestions]): EXISTSUpit 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]) ));

Prikazivanje ima prilično kompleksnu strukturu: uključuje spajanja podupita i korištenje sortiranja

, što je u općem slučaju prilično zahtjevna operacija. DISTINCT, koja je u svom osnovnom obliku prilično resursno zahtevna operacija.

Odabir iz OperativeQuestions s približno deset tisuća zapisa.

Glavni problem ovog upita je u tome što se za zapise iz vanjskog upita izvršava unutarnji podupit na prikazu [OperativeQuestions], koji bi trebao za [Email] = @p__linq__0 ograničiti prikaz sadržaja (putem EXISTS) na stotine zapisa.

Može se činiti da bi podupit trebao jednom izračunati zapise za [Email] = @p__linq__0, a zatim bi se tih nekoliko stotina zapisa trebali spojiti po id-u s Questions, a upit bi bio brz.

U stvarnosti se događa sekvencijalno spajanje svih tablica: i provjera podudarnosti Id Questions s Id iz OperativeQuestions, i filtriranje prema Email.

U suštini, upit radi sa svih desetaka tisuća zapisa OperativeQuestions, dok su potrebni samo relevantni podaci prema Email.

Tekst prikaza OperativeQuestions:

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

Izvorna mapa prikaza u DbContext (EF Core 2)

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

Izvorni 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 ovom konkretном slučaju razmatra se rješenje ovog problema bez infrastrukturnih promjena, bez stvaranja posebne tablice s gotovim rezultatima („Aktivni upiti“), za koju bi bio potreban mehanizam za punjenje podacima i održavanje ažurnosti.

Iako je ovo dobar način, postoji i druga opcija za optimizaciju ovog zadatka.

Glavni cilj je keširati zapise po [Email] = @p__linq__0 iz prikaza OperativeQuestions.

Uvodimo tabličnu funkciju [dbo].[OperativeQuestionsUserMail] u bazu podataka.

Šaljući kao ulazni parametar Email, vraćamo tablicu vrijednosti:

Upit 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

Ovdje se vraća tablica vrijednosti s unaprijed definiranom strukturom podataka.

Da bi upiti prema OperativeQuestionsUserMail bili optimalni i imali optimalne planove upita, potrebna je stroga struktura, a ne RETURNS TABLE AS RETURN…

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

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

Mapiranje prikaza i funkcije u 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})");
}

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

Vrijeme izvršavanja smanjeno je s 200-800 ms na 2-20 ms, tj. nekoliko desetaka puta brže.

Ako uzmemo u obzir prosjek, umjesto 350 ms dobivamo 8 ms.

Među očitim prednostima također dobijamo:

  1. ukupno smanjenje opterećenja pri čitanju,
  2. značajno smanjenje vjerojatnosti blokada
  3. smanjenje prosječnog vremena blokade na prihvatljive vrijednosti

Izvještaj

Optimizacija i fino podešavanje upita prema bazi podataka Graf*, dokument preko LINQ je zadatak koji se može riješiti.

U ovom radu vrlo su važni pažnja i dosljednost.

Na početku procesa:

  1. potrebno je provjeriti podatke s kojima radi upit (vrijednosti, odabrani tipovi podataka)
  2. provesti ispravno indeksiranje tih podataka
  3. provjeriti točnost uvjeta spajanja između tablica

Na sljedećoj iteraciji optimizacije otkrivaju se:

  1. osnova upita i određuje se glavni filter upita
  2. ponavljajući slični blokovi upita i analizira se presjek uvjeta
  3. u SSMS-u ili drugom GUI-u za SQL Server optimizira se sam SQL upit (izdvajanje međuspremnika podataka, izgradnja rezultantnog upita koristeći ovaj spremnik (može ih biti više))
  4. u posljednjoj fazi, uzimajući za osnovu rezultantni SQL upit, restrukturira se struktura LINQ upita

Kao rezultat, dobiveni LINQ upit treba postati strukturno identičan utvrđenom optimalnom SQL upitu iz točke 3.

Zahvale

Veliko hvala kolegama jobgemws i alex_ozr iz tvrtke Fortis za pomoć u pripremi ovog materijala.

Izvor: habr.com

Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere 🔥 Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere | ProHoster