🥇 LINQ užklausų optimizavimo C#.NET

įvedimas

В Šis straipsnis buvo apsvarstyti kai kurie optimizavimo būdai LINQ užklausos.
Čia taip pat pateikiame dar keletą kodo optimizavimo metodų, susijusių su LINQ užklausos.

Yra žinoma, kad LINQ(Language-Integrated Query) yra paprasta ir patogi kalba, skirta duomenų šaltinio užklausoms pateikti.

А LINQ į SQL yra DBVS duomenų prieigos technologija. Tai galingas įrankis darbui su duomenimis, kai užklausos sudaromos naudojant deklaratyviąją kalbą, kuri vėliau bus konvertuojama į SQL užklausos platforma ir išsiųstas į duomenų bazės serverį vykdyti. Mūsų atveju DBVS turime omenyje MS SQL serveris.

Tačiau LINQ užklausos nėra konvertuojami į optimaliai parašytas SQL užklausos, kurią patyręs DBA galėtų parašyti su visais optimizavimo niuansais SQL užklausos:

optimalios jungtys (PRISIJUNK) ir rezultatų filtravimą (KUR)
daug niuansų naudojant ryšius ir grupės sąlygas
daug pakeitimo sąlygų variantų IN apie ESAи NE Į, <> įjungta ESA
tarpinis rezultatų kaupimas talpykloje per laikinas lenteles, CTE, lentelės kintamuosius
sakinio vartojimas (PASIRINKIMAS) su instrukcijomis ir lentelės patarimais SU (...)
naudojant indeksuotus rodinius kaip vieną iš būdų atsikratyti perteklinių duomenų nuskaitymo pasirinkimų metu

Pagrindiniai veiklos trūkumai dėl to SQL užklausos sudarant LINQ užklausos yra:

viso duomenų atrankos mechanizmo konsolidavimas vienoje užklausoje
identiškų kodo blokų dubliavimas, dėl kurio galiausiai nuskaitomi keli nereikalingi duomenys
kelių komponentų sąlygų grupės (loginiai „ir“ ir „arba“) IR и OR, derinant sudėtingas sąlygas, lemia tai, kad optimizatorius, turėdamas tinkamus nesugrupuotus indeksus reikiamiems laukams, galiausiai pradeda nuskaityti sugrupuotą indeksą (INDEKSO NUSKAITYMAS) pagal sąlygų grupes
Dėl gilaus antrinių užklausų įdėjimo analizuoti labai sunku SQL teiginiai ir užklausos plano analizė iš kūrėjų pusės ir Administratorius

Optimizavimo metodai

Dabar pereikime tiesiai prie optimizavimo metodų.

1) Papildomas indeksavimas

Geriausia apsvarstyti filtrus pagrindinėse pasirinkimo lentelėse, nes labai dažnai visa užklausa sudaroma aplink vieną ar dvi pagrindines lenteles (programos-žmonės-operacijos) ir su standartiniu sąlygų rinkiniu (IsClosed, Canceled, Enabled, Status). Svarbu sukurti atitinkamus identifikuotų mėginių indeksus.

Šis sprendimas yra prasmingas, kai pasirinkus šiuos laukus labai apribojamas grąžinamas užklausos rinkinys.

Pavyzdžiui, turime 500000 2000 programų. Tačiau yra tik XNUMX aktyvių programų. Tada teisingai parinktas indeksas mus išgelbės nuo INDEKSO NUSKAITYMAS didelėje lentelėje ir leis greitai pasirinkti duomenis per nesugrupuotą indeksą.

Be to, indeksų trūkumą galima nustatyti pagal raginimus analizuoti užklausų planus arba rinkti sistemos rodinio statistiką. MS SQL serveris:

Visuose rodinio duomenyse yra informacijos apie trūkstamus indeksus, išskyrus erdvinius indeksus.

Tačiau indeksai ir talpyklos kaupimas dažnai yra būdai kovoti su prastai parašyto rašymo pasekmėmis LINQ užklausos и SQL užklausos.

Kaip rodo atšiauri gyvenimo praktika, verslui dažnai svarbu iki tam tikrų terminų įgyvendinti verslo ypatybes. Todėl sunkios užklausos dažnai perkeliamos į foną su talpyklomis.

Tai iš dalies pateisinama, nes vartotojui ne visada reikia naujausių duomenų ir vartotojo sąsajos reagavimo lygis yra priimtinas.

Šis metodas leidžia išspręsti verslo poreikius, tačiau galiausiai sumažina informacinės sistemos našumą, nes paprasčiausiai atitolina problemų sprendimą.

Taip pat verta atsiminti, kad ieškant reikalingų indeksų, kuriuos reikia pridėti, pateikiami pasiūlymai MS SQL optimizavimas gali būti neteisingas, įskaitant tokias sąlygas:

jei jau yra indeksų su panašiu laukų rinkiniu
jei lentelės laukų negalima indeksuoti dėl indeksavimo apribojimų (apibūdinta plačiau čia).

2) Atributų sujungimas į vieną naują atributą

Kartais kai kurie laukai iš vienos lentelės, kurie yra sąlygų grupės pagrindas, gali būti pakeisti įvedant vieną naują lauką.

Tai ypač pasakytina apie būsenos laukus, kurie paprastai yra bitų arba sveikųjų skaičių.

Pavyzdys:

IsClosed = 0 IR atšaukta = 0 IR įjungta = 0 yra pakeičiamas Būsena = 1.

Čia įvedamas sveikojo skaičiaus atributas Statusas, siekiant užtikrinti, kad šios būsenos būtų pateiktos lentelėje. Tada šis naujas atributas indeksuojamas.

Tai esminis našumo problemos sprendimas, nes mes pasiekiame duomenis be nereikalingų skaičiavimų.

3) Požiūrio materializavimas

Deja, į LINQ užklausos Laikinos lentelės, CTE ir lentelės kintamieji negali būti naudojami tiesiogiai.

Tačiau šiuo atveju yra ir kitas optimizavimo būdas – indeksuoti rodiniai.

Sąlygų grupė (iš anksčiau pateikto pavyzdžio) IsClosed = 0 IR atšaukta = 0 IR įjungta = 0 (arba kitų panašių sąlygų rinkinys) tampa gera galimybe jas naudoti indeksuotame rodinyje, talpykloje išsaugant nedidelę duomenų dalį iš didelio rinkinio.

Tačiau įgyvendinant vaizdą yra keletas apribojimų:

antrinių užklausų, sąlygų naudojimas ESA turėtų būti pakeistas naudojant PRISIJUNK
tu negali vartoti sakinių SĄJUNGA, SĄJUNGA VISI, IŠSKYRUS, SUSIEKITE
Negalite naudoti lentelės užuominų ir sąlygų PASIRINKIMAS
nėra galimybės dirbti su ciklais
Neįmanoma rodyti duomenų viename rodinyje iš skirtingų lentelių

Svarbu atsiminti, kad realią naudą naudojant indeksuotą rodinį galima pasiekti tik faktiškai jį indeksuojant.

Tačiau iškviečiant rodinį šie indeksai negali būti naudojami, o norėdami juos naudoti aiškiai, turite nurodyti SU (NOEXPAND).

Nuo tada, kai LINQ užklausos Neįmanoma apibrėžti lentelės užuominų, todėl turite sukurti kitą atvaizdą - tokios formos „įvyniojimą“:

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

4) Lentelių funkcijų naudojimas

Dažnai į LINQ užklausos Dideli antrinių užklausų blokai arba blokai, kuriuose naudojami sudėtingos struktūros rodiniai, sudaro galutinę užklausą su labai sudėtinga ir neoptimalia vykdymo struktūra.

Pagrindiniai lentelės funkcijų naudojimo pranašumai LINQ užklausos:

Galimybė, kaip ir rodinių atveju, būti naudojama ir nurodyta kaip objektas, tačiau galite perduoti įvesties parametrų rinkinį:
FROM FUNCTION(@param1, @param2...)
Dėl to galima pasiekti lankstų duomenų atranką
Naudojant lentelės funkciją, nėra tokių griežtų apribojimų kaip aukščiau aprašytų indeksuotų rodinių atveju:
1. Lentelės patarimai:
  per LINQ Negalite nurodyti, kurie indeksai turi būti naudojami, ir nustatyti duomenų izoliacijos lygio, kai pateikiate užklausą.
  Tačiau ši funkcija turi šias galimybes.
  Naudodami funkciją galite pasiekti gana pastovų vykdymo užklausos planą, kuriame yra apibrėžtos darbo su indeksais taisyklės ir duomenų izoliavimo lygiai.
2. Naudojant šią funkciją, palyginti su indeksuotais rodiniais, galima gauti:
  - sudėtinga duomenų atrankos logika (net naudojant kilpas)
  - gauti duomenis iš daugelio skirtingų lentelių
  - использование SĄJUNGA и ESA
Pasiūlymas PASIRINKIMAS labai naudinga, kai reikia užtikrinti lygiagretumo valdymą OPTION (MAXDOP N), užklausos vykdymo plano tvarka. Pavyzdžiui:
- galite nurodyti priverstinį užklausos plano kūrimą iš naujo OPTION (PERCOMPILE)
- galite nurodyti, ar priversti užklausos planą naudoti užklausoje nurodytą sujungimo tvarką PARINKTIS (PRIVARTINIS UŽSAKYMAS)
Daugiau informacijos apie PASIRINKIMAS aprašyta čia.
Naudojant siauriausią ir reikalingiausią duomenų pjūvį:
Nereikia kaupti didelių duomenų rinkinių talpyklose (kaip yra indeksuotų rodinių atveju), iš kurių vis tiek reikia filtruoti duomenis pagal parametrus.
Pavyzdžiui, yra lentelė, kurios filtras KUR naudojami trys laukai (a, b, c).

Paprastai visi prašymai turi pastovią sąlygą a = 0 ir b = 0.

Tačiau prašymas dėl lauko c kintamesnis.

Tegul sąlyga a = 0 ir b = 0 Tai tikrai padeda mums apriboti reikiamą rezultato rinkinį iki tūkstančių įrašų, tačiau sąlyga с susiaurina pasirinkimą iki šimto įrašų.

Čia lentelės funkcija gali būti geresnis pasirinkimas.

Be to, lentelės funkcija yra labiau nuspėjama ir nuoseklesnė vykdymo metu.

pavyzdžiai

Pažvelkime į įgyvendinimo pavyzdį, kaip pavyzdį naudodami klausimų duomenų bazę.

Yra prašymas SELECT, kuris sujungia kelias lenteles ir naudoja vieną rodinį (OperativeQuestions), kuriame priklausomybė tikrinama el. paštu (per ESA) į „Operatyvūs klausimai“:

Prašymas 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])
));

Rodinys turi gana sudėtingą struktūrą: jame yra papildomos užklausos sujungimai ir naudojamas rūšiavimas DISTINCT, kuri apskritai yra gana daug išteklių reikalaujanti operacija.

„OperativeQuestions“ pavyzdys yra apie dešimt tūkstančių įrašų.

Pagrindinė šios užklausos problema yra ta, kad išorinės užklausos įrašams [OperativeQuestions] rodinyje vykdoma vidinė antrinė užklausa, kuri [Email] = @p__linq__0 turėtų leisti apriboti išvesties pasirinkimą (per ESA) iki šimtų įrašų.

Ir gali atrodyti, kad antrinė užklausa turėtų vieną kartą apskaičiuoti įrašus pagal [Email] = @p__linq__0, o tada šiuos porą šimtų įrašų reikia sujungti Id su klausimais, ir užklausa bus greita.

Tiesą sakant, yra nuoseklus visų lentelių ryšys: patikrinama ID klausimų atitiktis su ID iš OperativeQuestions ir filtruojama pagal el.

Tiesą sakant, užklausa veikia su visais dešimtimis tūkstančių OperativeQuestions įrašų, tačiau el. paštu reikia tik dominančių duomenų.

OperativeQuestions rodinio tekstas:

Prašymas 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));

Pradinis rodinio atvaizdavimas 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");
    }
}

Pradinė LINQ užklausa

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

Šiuo konkrečiu atveju svarstome šios problemos sprendimą be infrastruktūros pakeitimų, neįvedant atskiros lentelės su jau paruoštais rezultatais („Aktyvios užklausos“), kuri reikalautų jos užpildymo duomenimis ir nuolatinio atnaujinimo mechanizmo. .

Nors tai yra geras sprendimas, yra ir kita galimybė optimizuoti šią problemą.

Pagrindinis tikslas yra įrašyti įrašus talpykloje [El. paštas] = @p__linq__0 iš OperativeQuestions rodinio.

Į duomenų bazę įtraukite lentelės funkciją [dbo].[OperativeQuestionsUserMail].

Išsiųsdami el. laišką kaip įvesties parametrą, gauname verčių lentelę:

Prašymas 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

Tai grąžina verčių lentelę su iš anksto nustatyta duomenų struktūra.

Kad užklausos OperativeQuestionsUserMail būtų optimalios ir turėtų optimalius užklausų planus, reikalinga griežta struktūra, o ne GRĄŽINIMO LENTELĖ KAIP GRĄŽINIMAS...

Tokiu atveju reikalinga 1 užklausa konvertuojama į 4 užklausą:

Prašymas 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]);

Rodinių ir funkcijų atvaizdavimas 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})");
}

Galutinė LINQ užklausa

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

Vykdymo laikas sumažėjo nuo 200-800 ms, iki 2-20 ms ir pan., ty dešimtis kartų greičiau.

Jei imsime vidutiniškai, tai vietoj 350 ms gavome 8 ms.

Iš akivaizdžių pranašumų taip pat gauname:

bendras skaitymo apkrovos sumažėjimas,
žymiai sumažina blokavimo tikimybę
sumažinti vidutinį blokavimo laiką iki priimtinų verčių

Produkcija

Duomenų bazės skambučių optimizavimas ir koregavimas MS SQL per LINQ yra problema, kurią galima išspręsti.

Šiame darbe labai svarbus atidumas ir nuoseklumas.

Proceso pradžioje:

būtina patikrinti duomenis, su kuriais veikia užklausa (reikšmes, pasirinktus duomenų tipus)
atlikti tinkamą šių duomenų indeksavimą
patikrinti sujungimo sąlygų tarp lentelių teisingumą

Kita optimizavimo iteracija atskleidžia:

užklausos pagrindu ir apibrėžia pagrindinį užklausos filtrą
kartojant panašius užklausų blokus ir analizuojant sąlygų sankirtą
SSMS ar kitoje GUI "SQL Server optimizuoja save SQL užklausa (tarpinės duomenų saugyklos paskyrimas, gautos užklausos sukūrimas naudojant šią saugyklą (gali būti kelios))
paskutiniame etape, remiantis gautu SQL užklausa, statinys atstatomas LINQ užklausa

Gautas LINQ užklausa savo struktūra turėtų tapti identiška nustatytam optimaliam SQL užklausa nuo 3 punkto.

Padėkos

Labai ačiū kolegoms jobgemws и alex_ozr iš įmonės Fortis už pagalbą rengiant šią medžiagą.

Šaltinis: www.habr.com

LINQ užklausų optimizavimo C#.NET metodai