🥇Metode za optimiziranje LINQ upita u C#.NET

Uvod

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

Zna se da LINQ(Language-Integrated Query) jednostavan je i prikladan jezik za postavljanje upita izvoru podataka.

А LINQ na SQL je tehnologija za pristup podacima u DBMS-u. Ovo je moćan alat za rad s podacima, gdje se upiti konstruiraju kroz deklarativni jezik, koji će se zatim pretvoriti u SQL upiti platformi i šalje poslužitelju baze podataka na izvršenje. U našem slučaju, pod DBMS-om mislimo MS SQL poslužitelj.

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

optimalne veze (PRIDRUŽITE) i filtriranje rezultata (GDJE)
mnoge nijanse u korištenju veza i grupnih uvjeta
mnoge varijacije u uvjetima zamjene IN na POSTOJIи NE U, <> uključeno POSTOJI
srednje predmemoriranje rezultata putem privremenih tablica, CTE, tabličnih varijabli
upotreba rečenice (OPCIJA) s uputama i tabličnim savjetima S (...)
korištenje indeksiranih prikaza kao jednog od načina da se riješite suvišnih očitavanja podataka tijekom odabira

Glavna uska grla u izvedbi rezultirajućeg SQL upiti prilikom sastavljanja LINQ upiti To su:

konsolidacija cijelog mehanizma odabira podataka u jednom zahtjevu
dupliciranje identičnih blokova koda, što u konačnici dovodi do višestrukih nepotrebnih čitanja podataka
skupine višekomponentnih uvjeta (logičko "i" i "ili") - I и OR, kombinirajući u složene uvjete, dovodi do činjenice da optimizator, koji ima odgovarajuće ne-klasterirane indekse za potrebna polja, na kraju počinje skenirati prema klasteriranom indeksu (SKENIRANJE INDEKSA) po grupama uvjeta
duboko ugniježđivanje podupita čini raščlanjivanje vrlo problematičnim SQL izjave i analiza plana upita od strane programera i DBA

Metode optimizacije

Sada prijeđimo izravno na metode optimizacije.

1) Dodatno indeksiranje

Najbolje je razmotriti filtre na glavnim tablicama odabira, budući da je vrlo često cijeli upit izgrađen oko jedne ili dvije glavne tablice (aplikacije-ljudi-operacije) i sa standardnim skupom uvjeta (Zatvoreno, Otkazano, Omogućeno, Status). Važno je izraditi odgovarajuće indekse za identificirane uzorke.

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

Na primjer, imamo 500000 prijava. Međutim, postoji samo 2000 aktivnih prijava. Tada će nas ispravno odabrani indeks spasiti od SKENIRANJE INDEKSA na velikoj tablici i omogućit će vam brz odabir podataka putem neklasteriziranog indeksa.

Također, nedostatak indeksa može se identificirati putem upita za raščlanjivanje planova upita ili prikupljanje statistike prikaza sustava MS SQL poslužitelj:

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

Međutim, indeksi i predmemoriranje često su metode borbe protiv posljedica lošeg pisanja LINQ upiti и SQL upiti.

Kao što surova životna praksa pokazuje, za poduzeće je često važno implementirati poslovne karakteristike do određenih rokova. Stoga se teški zahtjevi često prenose u pozadinu s predmemoriranjem.

To je djelomično opravdano, budući da korisnik ne treba uvijek najnovije podatke, a postoji prihvatljiva razina odziva korisničkog sučelja.

Ovakav pristup omogućuje rješavanje poslovnih potreba, ali u konačnici smanjuje performanse informacijskog sustava jednostavnim odgađanjem rješenja problema.

Također je vrijedno zapamtiti da u procesu traženja potrebnih indeksa dodati, prijedloge MS SQL optimizacija može biti netočna, uključujući sljedeće uvjete:

ako već postoje indeksi sa sličnim skupom polja
ako se polja u tablici ne mogu indeksirati zbog ograničenja indeksiranja (detaljnije opisano здесь).

2) Spajanje atributa u jedan novi atribut

Ponekad se neka polja iz jedne tablice, koja služe kao osnova za skupinu uvjeta, mogu zamijeniti uvođenjem jednog novog polja.

To posebno vrijedi za statusna polja, koja su obično bitnog ili cijelog broja.

Primjer:

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

Ovdje se uvodi atribut statusa cijelog broja kako bi se osiguralo da su ti statusi popunjeni u tablici. Zatim se ovaj novi atribut indeksira.

Ovo je temeljno rješenje za problem performansi, jer podacima pristupamo bez nepotrebnih izračuna.

3) Materijalizacija pogleda

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

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

Grupa uvjeta (iz gornjeg primjera) Zatvoreno = 0 I Otkazano = 0 I Omogućeno = 0 (ili skup drugih sličnih uvjeta) postaje dobra opcija za njihovu upotrebu u indeksiranom prikazu, predmemoriranje malog dijela podataka iz velikog skupa.

Ali postoje brojna ograničenja kada se materijalizira pogled:

korištenje podupita, klauzula POSTOJI treba zamijeniti korištenjem PRIDRUŽITE
ne znaš koristiti rečenice UNIJA, SAVEZ SVE, IZNIMKA, PRESJEKATI
Ne možete koristiti tablične savjete i klauzule OPCIJA
nema mogućnosti rada s ciklusima
Nemoguće je prikazati podatke u jednom prikazu iz različitih tablica

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

Ali kada pozivate pogled, ti se indeksi ne smiju koristiti, a da biste ih eksplicitno koristili, morate navesti SA (BEZ PROŠIRIVANJA).

Od godine u LINQ upiti Nemoguće je definirati tablične savjete, pa morate kreirati još jedan prikaz - "omot" 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 čine konačni upit s 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 proslijediti skup ulaznih parametara:
FROM FUNCTION(@param1, @param2 ...)
Kao rezultat, može se postići fleksibilno uzorkovanje podataka
U slučaju korištenja funkcije tablice, nema tako jakih ograničenja kao u slučaju gore opisanih indeksiranih prikaza:
1. Savjeti za tablicu:
  kroz LINQ Ne možete odrediti koji se indeksi trebaju koristiti i odrediti razinu izolacije podataka prilikom postavljanja upita.
  Ali funkcija ima ove mogućnosti.
  S funkcijom možete postići prilično konstantan plan upita izvršenja, gdje su definirana pravila za rad s indeksima i razinama izolacije podataka
2. Korištenje funkcije omogućuje, u usporedbi s indeksiranim prikazima, dobivanje:
  - složena logika uzorkovanja podataka (čak i korištenjem petlji)
  - dohvaćanje podataka iz mnogo različitih tablica
  - korištenje UNIJA и POSTOJI
ponuda OPCIJA vrlo korisno kada trebamo osigurati kontrolu istovremenosti OPCIJA (MAXDOP N), redoslijed plana izvršenja upita. Na primjer:
- možete navesti prisilno ponovno stvaranje plana upita OPCIJA (REKOMPAJLIRAJ)
- možete navesti hoćete li prisiliti plan upita da koristi redoslijed spajanja naveden u upitu OPCIJA (FORCE ORDER)
Više detalja o OPCIJA opisano здесь.
Korištenje najužeg i najpotrebnijeg podatkovnog odsječka:
Nema potrebe za pohranjivanjem velikih skupova podataka u predmemorije (kao što je slučaj s indeksiranim pregledima), iz kojih ipak morate filtrirati podatke prema parametru.
Na primjer, postoji tablica čiji filter GDJE koriste se tri polja (a, b, c).

Konvencionalno, svi zahtjevi imaju konstantan uvjet a = 0 i b = 0.

Međutim, zahtjev za polj c varijabilniji.

Neka uvjet a = 0 i b = 0 Stvarno nam pomaže da ograničimo potrebni rezultirajući skup na tisuće zapisa, ali uvjet je uključen с sužava izbor na stotinjak zapisa.

Ovdje bi funkcija tablice mogla biti bolja opcija.

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

Primjeri

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

Postoji zahtjev SELECT, koji kombinira nekoliko tablica i koristi jedan pogled (OperativeQuestions), u kojem se pripadnost provjerava e-poštom (putem POSTOJI) na “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 općenito prilično resursno intenzivna operacija.

Uzorak iz OperativeQuestions je oko deset tisuća zapisa.

Glavni problem s ovim upitom je taj što se za zapise iz vanjskog upita izvršava interni podupit u pogledu [OperativeQuestions], što bi nam za [Email] = @p__linq__0 trebalo omogućiti da ograničimo odabir izlaza (putem POSTOJI) do stotine zapisa.

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

Zapravo, postoji sekvencijalna veza svih tablica: provjera korespondencije ID pitanja s ID-om iz OperativeQuestions i filtriranje putem e-pošte.

Zapravo, zahtjev radi sa svim desecima tisuća zapisa OperativeQuestions, ali samo su podaci od interesa potrebni putem e-pošte.

OperativeQuestions prikaz teksta:

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 pogleda 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");
    }
}

Inicijalni 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 konkretnom slučaju razmatramo rješenje ovog problema bez infrastrukturnih promjena, bez uvođenja posebne tablice s gotovim rezultatima (“Active Queries”), što bi zahtijevalo mehanizam za njeno punjenje podacima i njihovo ažuriranje .

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

Glavna je svrha predmemorirati unose prema [Email] = @p__linq__0 iz prikaza OperativeQuestions.

U bazu podataka uvedite funkciju tablice [dbo].[OperativeQuestionsUserMail].

Slanjem e-pošte kao ulaznog parametra, vraćamo tablicu 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.

Kako bi upiti prema OperativeQuestionsUserMail bili optimalni i imali optimalne planove upita, potrebna je stroga struktura, a ne POVRATAK TABLICA KAO POVRAT...

U ovom slučaju, traženi upit 1 pretvara se 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]);

Prikazi 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 pao je sa 200-800 ms, na 2-20 ms, itd., tj. desetke puta brže.

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

Od očitih prednosti također dobivamo:

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

Izlaz

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

Pažljivost i dosljednost su vrlo važni u ovom poslu.

Na početku procesa:

potrebno je provjeriti podatke s kojima radi zahtjev (vrijednosti, odabrane vrste podataka)
provesti pravilno indeksiranje ovih podataka
provjeriti ispravnost uvjeta spajanja između tablica

Sljedeća iteracija optimizacije otkriva:

osnovu zahtjeva i definira glavni filtar zahtjeva
ponavljanje sličnih blokova upita i analiziranje presjeka uvjeta
u SSMS ili drugom GUI za SQL Server optimizira samu sebe SQL upit (dodjeljivanje srednje pohrane podataka, izrada rezultirajućeg upita pomoću ove pohrane (može ih biti nekoliko))
u posljednjoj fazi, uzimajući kao osnovu rezultirajuće SQL upit, struktura se obnavlja LINQ upit

Dobivena LINQ upit trebao postati identičan strukturi identificiranog optimalnog SQL upit iz točke 3.

Blagodarnosti

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

Izvor: www.habr.com

Metode za optimiziranje LINQ upita u C#.NET