🥇Metode za optimizacijo poizvedb LINQ v C#.NET

Predstavitev

В ta članek upoštevane so bile nekatere metode optimizacije poizvedbe LINQ.
Tukaj predstavljamo tudi nekaj več pristopov k optimizaciji kode, povezanih z poizvedbe LINQ.

Znano je, da LINQ(Language-Integrated Query) je preprost in priročen jezik za poizvedovanje po viru podatkov.

А LINQ v SQL je tehnologija za dostop do podatkov v DBMS. To je zmogljivo orodje za delo s podatki, kjer se poizvedbe oblikujejo prek deklarativnega jezika, ki se nato pretvori v SQL poizvedbe platformo in pošlje strežniku baze podatkov v izvedbo. V našem primeru mislimo na DBMS MS SQL Server.

Vendar pa poizvedbe LINQ niso pretvorjene v optimalno zapisane SQL poizvedbe, ki bi ga lahko napisal izkušen DBA z vsemi niansami optimizacije SQL poizvedbe:

optimalne povezave (PRIDRUŽITE) in filtriranje rezultatov (KJE)
številne nianse pri uporabi povezav in skupinskih pogojev
številne različice pogojev zamenjave IN o OBSTOJIи NI NOTRI, <> vklopljeno OBSTOJI
vmesno predpomnjenje rezultatov preko začasnih tabel, CTE, spremenljivk tabele
uporaba stavka (MOŽNOST) z navodili in namigi za tabele Z (...)
uporabo indeksiranih pogledov kot enega od načinov, da se znebite odvečnih odčitkov podatkov med izbiranjem

Glavna ozka grla pri delovanju nastalega SQL poizvedbe pri sestavljanju poizvedbe LINQ so:

združitev celotnega mehanizma za izbiro podatkov v enem zahtevku
podvajanje identičnih blokov kode, kar na koncu vodi do večkratnih nepotrebnih branj podatkov
skupine večkomponentnih pogojev (logični "in" in "ali") - IN и OR, ki se združuje v zapletene pogoje, vodi do dejstva, da optimizator, ki ima ustrezne negručaste indekse za potrebna polja, končno začne pregledovati glede na gručasti indeks (SKEN INDEKSA) po skupinah pogojev
zaradi globokega gnezdenja podpoizvedb je razčlenjevanje zelo problematično Stavki SQL in analiza načrta poizvedbe s strani razvijalcev in DBA

Metode optimizacije

Zdaj pa preidimo neposredno na metode optimizacije.

1) Dodatno indeksiranje

Najbolje je razmisliti o filtrih na glavnih izbirnih tabelah, saj je zelo pogosto celotna poizvedba zgrajena okoli ene ali dveh glavnih tabel (aplikacije-ljudje-operacije) in s standardnim naborom pogojev (Je zaprto, Preklicano, Omogočeno, Status). Pomembno je ustvariti ustrezne indekse za identificirane vzorce.

Ta rešitev je smiselna, če izbira teh polj znatno omeji vrnjeni niz za poizvedbo.

Na primer, imamo 500000 prijav. Vendar je aktivnih le 2000 aplikacij. Takrat nas bo pravilno izbrani indeks rešil pred SKEN INDEKSA na veliki tabeli in vam bo omogočil hitro izbiro podatkov prek negručastega indeksa.

Prav tako je pomanjkanje indeksov mogoče prepoznati prek pozivov za razčlenjevanje načrtov poizvedb ali zbiranje statističnih podatkov o sistemskih pogledih MS SQL Server:

Vsi podatki pogleda vsebujejo informacije o manjkajočih indeksih, z izjemo prostorskih indeksov.

Vendar so indeksi in predpomnjenje pogosto metode za boj proti posledicam slabo napisanega poizvedbe LINQ и SQL poizvedbe.

Kot kaže ostra življenjska praksa, je za podjetje pogosto pomembno, da poslovne poteze uvede do določenih rokov. In zato se težke zahteve pogosto prenesejo v ozadje s predpomnjenjem.

Delno je to upravičeno, saj uporabnik ne potrebuje vedno najnovejših podatkov, odzivnost uporabniškega vmesnika pa je sprejemljiva.

Ta pristop omogoča reševanje poslovnih potreb, vendar na koncu zmanjša zmogljivost informacijskega sistema s preprostim odlašanjem rešitev težav.

Prav tako je vredno zapomniti, da v procesu iskanja potrebnih indeksov za dodajanje predlogov MS SQL optimizacija je lahko napačna, tudi pod naslednjimi pogoji:

če že obstajajo indeksi s podobnim nizom polj
če polj v tabeli ni mogoče indeksirati zaradi omejitev indeksiranja (podrobneje opisano tukaj).

2) Združevanje atributov v en nov atribut

Včasih lahko nekatera polja iz ene tabele, ki služijo kot osnova za skupino pogojev, nadomestimo z uvedbo enega novega polja.

To še posebej velja za statusna polja, ki so navadno bitnega ali celega tipa.

Primer:

Je Zaprto = 0 IN Preklicano = 0 IN Omogočeno = 0 se nadomesti z Stanje = 1.

Tukaj je uveden celoštevilski atribut statusa, ki zagotavlja, da so ti statusi zapolnjeni v tabeli. Nato se ta novi atribut indeksira.

To je temeljna rešitev težave z zmogljivostjo, saj do podatkov dostopamo brez nepotrebnih izračunov.

3) Materializacija pogleda

Na žalost v poizvedbe LINQ Začasnih tabel, CTE-jev in spremenljivk tabele ni mogoče uporabiti neposredno.

Vendar pa obstaja še en način optimizacije za ta primer - indeksirani pogledi.

Skupina pogojev (iz zgornjega primera) Je Zaprto = 0 IN Preklicano = 0 IN Omogočeno = 0 (ali nabor drugih podobnih pogojev) postane dobra možnost, da jih uporabite v indeksiranem pogledu, pri čemer predpomnite majhen del podatkov iz velikega nabora.

Toda pri materializaciji pogleda obstajajo številne omejitve:

uporaba podpoizvedb, klavzul OBSTOJI je treba nadomestiti z uporabo PRIDRUŽITE
ne moreš uporabljati stavkov UNION, UNIJA VSE, IZJEMA, PRESEKANJE
Ne morete uporabljati namigov in klavzul tabel MOŽNOST
ni možnosti dela s cikli
Nemogoče je prikazati podatke v enem pogledu iz različnih tabel

Pomembno si je zapomniti, da lahko resnično korist uporabe indeksiranega pogleda dosežete le z njegovim dejanskim indeksiranjem.

Ko kličete pogled, teh indeksov morda ne boste uporabili in če jih želite izrecno uporabiti, morate podati Z (BREZ RAZŠIRJA).

Od leta poizvedbe LINQ Nemogoče je definirati namige tabele, zato morate ustvariti drugo predstavitev - "ovitek" naslednje oblike:

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

4) Uporaba funkcij tabele

Pogosto v poizvedbe LINQ Veliki bloki podpoizvedb ali bloki, ki uporabljajo poglede s kompleksno strukturo, tvorijo končno poizvedbo z zelo kompleksno in neoptimalno izvedbeno strukturo.

Ključne prednosti uporabe funkcij tabele v poizvedbe LINQ:

Zmožnost, kot v primeru pogledov, da se uporabi in določi kot objekt, vendar lahko posredujete nabor vhodnih parametrov:
IZ FUNKCIJE (@param1, @param2 ...)
Posledično je mogoče doseči prilagodljivo vzorčenje podatkov
V primeru uporabe funkcije tabele ni tako močnih omejitev kot v primeru zgoraj opisanih indeksiranih pogledov:
1. Namigi za tabele:
  prek LINQ Pri poizvedovanju ne morete podati, kateri indeksi naj se uporabljajo, in določiti ravni izolacije podatkov.
  Toda funkcija ima te zmožnosti.
  S funkcijo lahko dosežete dokaj konstanten izvedbeni načrt poizvedbe, kjer so definirana pravila za delo z indeksi in ravni izolacije podatkov
2. Uporaba funkcije omogoča, da v primerjavi z indeksiranimi pogledi pridobite:
  - kompleksna logika vzorčenja podatkov (tudi z uporabo zank)
  - pridobivanje podatkov iz številnih različnih tabel
  - Uporaba UNION и OBSTOJI
Ponudba MOŽNOST zelo uporabno, ko moramo zagotoviti nadzor sočasnosti MOŽNOST (MAXDOP N), vrstni red načrta izvajanja poizvedbe. Na primer:
- določite lahko prisilno ponovno ustvarjanje načrta poizvedbe MOŽNOST (PONOVNO PREVOJ)
- lahko določite, ali naj načrt poizvedbe prisili, da uporabi vrstni red združevanja, podanega v poizvedbi MOŽNOST (VSILNI NAROČILO)
Več podrobnosti o MOŽNOST opisano tukaj.
Uporaba najožje in najbolj zahtevane rezine podatkov:
Ni potrebe po shranjevanju velikih naborov podatkov v predpomnilnikih (kot je to v primeru indeksiranih pogledov), iz katerih morate še vedno filtrirati podatke po parametru.
Na primer, obstaja tabela, katere filter KJE uporabljena so tri polja (a, b, c).

Običajno imajo vse zahteve stalni pogoj a = 0 in b = 0.

Vendar pa je zahteva za polj c bolj spremenljiv.

Naj pogoj a = 0 in b = 0 Resnično nam pomaga omejiti zahtevani končni nabor na tisoče zapisov, vendar je pogoj vključen с zoži izbor na sto zapisov.

Tu je morda boljša možnost funkcija tabele.

Poleg tega je funkcija tabele bolj predvidljiva in konsistentna v času izvajanja.

Primeri

Oglejmo si primer izvedbe z uporabo zbirke podatkov Questions kot primera.

Obstaja prošnja IZBIRA, ki združuje več tabel in uporablja en pogled (OperativeQuestions), v katerem se pripadnost preverja po elektronski pošti (prek OBSTOJI) na “Operativna vprašanja”:

Zahteva št. 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 precej zapleteno strukturo: ima združevanja podpoizvedb in uporablja razvrščanje DISTINCT, kar je na splošno operacija, ki zahteva precej virov.

Vzorec iz OperativeQuestions je približno deset tisoč zapisov.

Glavna težava s to poizvedbo je, da se za zapise iz zunanje poizvedbe izvede notranja podpoizvedba v pogledu [OperativeQuestions], ki bi nam morala za [Email] = @p__linq__0 omogočiti omejitev izbire izhoda (prek OBSTOJI) do več sto zapisov.

In morda se zdi, da bi morala podpoizvedba enkrat izračunati zapise z [Email] = @p__linq__0, nato pa bi bilo treba teh nekaj sto zapisov povezati z Id z vprašanji in poizvedba bo hitra.

Pravzaprav obstaja zaporedna povezava vseh tabel: preverjanje ujemanja Id vprašanj z Id iz OperativeQuestions in filtriranje po e-pošti.

Dejansko zahteva deluje z vsemi desettisoči zapisov OperativeQuestions, vendar so potrebni samo podatki, ki vas zanimajo, prek e-pošte.

Ogled besedila OperativeQuestions:

Zahteva št. 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));

Preslikava začetnega pogleda v 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");
    }
}

Začetna poizvedba 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();

V konkretnem primeru razmišljamo o rešitvi tega problema brez infrastrukturnih sprememb, brez uvedbe ločene tabele z že pripravljenimi rezultati (“Aktivne poizvedbe”), ki bi zahtevala mehanizem za njeno polnjenje s podatki in njihovo posodabljanje. .

Čeprav je to dobra rešitev, obstaja še ena možnost za optimizacijo te težave.

Glavni namen je predpomniti vnose [Email] = @p__linq__0 iz pogleda OperativeQuestions.

V bazo podatkov uvedite funkcijo tabele [dbo].[OperativeQuestionsUserMail].

Če kot vhodni parameter pošljemo e-pošto, dobimo nazaj tabelo vrednosti:

Zahteva št. 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

To vrne tabelo vrednosti z vnaprej določeno strukturo podatkov.

Da bi bile poizvedbe do OperativeQuestionsUserMail optimalne in imele optimalne načrte poizvedb, je potrebna stroga struktura in ne TABELA VRAČIL KOT VRAČILA...

V tem primeru se zahtevana poizvedba 1 pretvori v poizvedbo 4:

Zahteva št. 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]);

Preslikava pogledov in funkcij v 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})");
}

Končna poizvedba 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();

Vrstni red izvajalnega časa je padel z 200-800 ms na 2-20 ms itd., torej več desetkrat hitreje.

Če vzamemo bolj povprečno, potem namesto 350 ms dobimo 8 ms.

Od očitnih prednosti dobimo tudi:

splošno zmanjšanje bralne obremenitve,
znatno zmanjšanje verjetnosti blokade
zmanjšanje povprečnega časa blokiranja na sprejemljive vrednosti

Izhod

Optimizacija in fina nastavitev klicev baze podatkov MS SQL prek LINQ je problem, ki ga je mogoče rešiti.

Pri tem delu sta zelo pomembna pozornost in doslednost.

Na začetku postopka:

potrebno je preveriti podatke, s katerimi deluje zahteva (vrednosti, izbrani tipi podatkov)
izvesti ustrezno indeksiranje teh podatkov
preverite pravilnost pogojev spajanja med tabelami

Naslednja ponovitev optimizacije razkrije:

podlagi zahtevka in definira glavni filter zahtevka
ponavljanje podobnih blokov poizvedb in analiziranje presečišča pogojev
v SSMS ali drugem GUI za SQL Server optimizira samega sebe SQL poizvedba (dodelitev vmesnega pomnilnika podatkov, izdelava nastale poizvedbe z uporabo tega pomnilnika (lahko jih je več))
na zadnji stopnji, pri čemer je za osnovo nastalo SQL poizvedba, struktura se obnavlja poizvedba LINQ

Posledično poizvedba LINQ bi morala po strukturi postati enaka ugotovljeni optimalni SQL poizvedba iz 3. točke.

Zahvala

Najlepša hvala kolegom jobgemws и alex_ozr iz podjetja Fortis za pomoč pri pripravi tega gradiva.

Vir: www.habr.com

Metode za optimizacijo poizvedb LINQ v C#.NET