🥇Metodat për optimizimin e pyetjeve LINQ në C#.NET

Paraqitje

В Ky artikull u morën parasysh disa metoda optimizimi Pyetjet e LINQ.
Këtu ne gjithashtu paraqesim disa qasje të tjera për optimizimin e kodit në lidhje me Pyetjet e LINQ.

Dihet se LINQ(Gjuha-Integrated Query) është një gjuhë e thjeshtë dhe e përshtatshme për të kërkuar një burim të dhënash.

А LINQ në SQL është një teknologji për aksesimin e të dhënave në një DBMS. Ky është një mjet i fuqishëm për të punuar me të dhënat, ku pyetjet ndërtohen përmes një gjuhe deklarative, e cila më pas do të konvertohet në Pyetjet SQL platformë dhe dërgohet në serverin e bazës së të dhënave për ekzekutim. Në rastin tonë, me DBMS nënkuptojmë Shërbyesi MS SQL.

Megjithatë, Pyetjet e LINQ nuk shndërrohen në të shkruara në mënyrë optimale Pyetjet SQL, të cilin një DBA me përvojë mund ta shkruante me të gjitha nuancat e optimizimit Pyetjet SQL:

lidhjet optimale (BASHKOHU) dhe filtrimi i rezultateve (KU)
shumë nuanca në përdorimin e lidhjeve dhe kushteve të grupit
shumë variacione në kushtet e zëvendësimit IN mbi Ekzistonи JO NË, <> në Ekziston
memoria e ndërmjetme e rezultateve nëpërmjet tabelave të përkohshme, CTE, variablave të tabelës
përdorimi i fjalisë (OPTION) me udhëzime dhe këshilla tabele ME (...)
duke përdorur pamjet e indeksuar si një nga mjetet për të hequr qafe leximet e tepërta të të dhënave gjatë përzgjedhjeve

Blloqet kryesore të performancës së rezultateve Pyetjet SQL gjatë përpilimit Pyetjet e LINQ Ato janë:

konsolidimi i të gjithë mekanizmit të përzgjedhjes së të dhënave në një kërkesë
dublikimi i blloqeve identike të kodit, i cili përfundimisht çon në lexime të shumta të të dhënave të panevojshme
grupe kushtesh me shumë komponentë (logjike "dhe" dhe "ose") - DHE и OR, duke u kombinuar në kushte komplekse, çon në faktin se optimizuesi, duke pasur indekse të përshtatshme jo të grumbulluara për fushat e nevojshme, në fund fillon të skanojë kundër indeksit të grupuar (SKANI I INDEKSIT) sipas grupeve të kushteve
foleja e thellë e nënpyetjeve e bën analizimin shumë problematik Deklaratat SQL dhe analiza e planit të pyetjeve nga ana e zhvilluesve dhe DBA

Metodat e optimizimit

Tani le të kalojmë drejtpërdrejt në metodat e optimizimit.

1) Indeksimi shtesë

Është më mirë të merren parasysh filtrat në tabelat kryesore të përzgjedhjes, pasi shumë shpesh i gjithë pyetja ndërtohet rreth një ose dy tabelave kryesore (aplikacione-njerëz-operacione) dhe me një grup standard kushtesh (IsMbyllur, Anuluar, Aktivizuar, Status). Është e rëndësishme të krijohen indekse të përshtatshme për mostrat e identifikuara.

Kjo zgjidhje ka kuptim kur zgjedhja e këtyre fushave kufizon ndjeshëm grupin e kthyer në pyetje.

Për shembull, ne kemi 500000 aplikime. Megjithatë, ka vetëm 2000 aplikacione aktive. Pastaj një indeks i zgjedhur saktë do të na shpëtojë nga SKANI I INDEKSIT në një tabelë të madhe dhe do t'ju lejojë të zgjidhni shpejt të dhënat përmes një indeksi jo të grupuar.

Gjithashtu, mungesa e indekseve mund të identifikohet përmes kërkesave për analizimin e planeve të pyetjeve ose mbledhjes së statistikave të pamjes së sistemit Shërbyesi MS SQL:

Të gjitha të dhënat e pamjes përmbajnë informacione për indekset që mungojnë, me përjashtim të indekseve hapësinore.

Megjithatë, indekset dhe memoria janë shpesh metoda për të luftuar pasojat e shkrimit të dobët Pyetjet e LINQ и Pyetjet SQL.

Siç tregon praktika e ashpër e jetës, shpesh është e rëndësishme që një biznes të zbatojë veçoritë e biznesit brenda afateve të caktuara. Dhe për këtë arsye, kërkesat e rënda shpesh transferohen në sfond me caching.

Kjo është pjesërisht e justifikuar, pasi përdoruesi nuk ka gjithmonë nevojë për të dhënat më të fundit dhe ekziston një nivel i pranueshëm i reagimit të ndërfaqes së përdoruesit.

Kjo qasje lejon zgjidhjen e nevojave të biznesit, por në fund të fundit zvogëlon performancën e sistemit të informacionit thjesht duke vonuar zgjidhjet e problemeve.

Vlen gjithashtu të kujtohet se në procesin e kërkimit të indekseve të nevojshme për të shtuar, sugjerime MS SQL optimizimi mund të jetë i pasaktë, duke përfshirë kushtet e mëposhtme:

nëse tashmë ka indekse me një grup të ngjashëm fushash
nëse fushat në tabelë nuk mund të indeksohen për shkak të kufizimeve të indeksimit (të përshkruara më në detaje këtu).

2) Bashkimi i atributeve në një atribut të ri

Ndonjëherë disa fusha nga një tabelë, të cilat shërbejnë si bazë për një grup kushtesh, mund të zëvendësohen duke futur një fushë të re.

Kjo është veçanërisht e vërtetë për fushat e statusit, të cilat zakonisht janë ose bit ose numër i plotë në lloj.

Shembull:

IsClosed = 0 DHE Anuluar = 0 DHE Aktivizuar = 0 zëvendësohet nga Statusi = 1.

Këtu futet atributi i statusit të numrit të plotë për të siguruar që këto statuse të jenë të mbushura në tabelë. Më pas, ky atribut i ri indeksohet.

Kjo është një zgjidhje themelore për problemin e performancës, sepse ne i qasemi të dhënave pa llogaritje të panevojshme.

3) Materializimi i pamjes

Fatkeqësisht, në Pyetjet e LINQ Tabelat e përkohshme, CTE-të dhe variablat e tabelave nuk mund të përdoren drejtpërdrejt.

Sidoqoftë, ekziston një mënyrë tjetër për të optimizuar për këtë rast - pamjet e indeksuara.

Grupi i kushteve (nga shembulli i mësipërm) IsClosed = 0 DHE Anuluar = 0 DHE Aktivizuar = 0 (ose një grup kushtesh të tjera të ngjashme) bëhet një opsion i mirë për t'i përdorur ato në një pamje të indeksuar, duke ruajtur një pjesë të vogël të të dhënave nga një grup i madh.

Por ka një sërë kufizimesh kur materializohet një pamje:

përdorimi i nënpyetjeve, klauzolave Ekziston duhet të zëvendësohet duke përdorur BASHKOHU
nuk mund të përdorësh fjali BASHKIMI, BASHKIMI GJITHA, PËRJASHTIM, ND .RPRERJE
Ju nuk mund të përdorni sugjerime dhe klauzola të tabelës OPTION
nuk ka mundësi për të punuar me cikle
Është e pamundur të shfaqen të dhënat në një pamje nga tabela të ndryshme

Është e rëndësishme të mbani mend se përfitimi i vërtetë i përdorimit të një pamje të indeksuar mund të arrihet vetëm duke e indeksuar atë.

Por kur thirrni një pamje, këto indekse mund të mos përdoren dhe për t'i përdorur ato në mënyrë eksplicite, duhet të specifikoni ME (NOEXPAND).

Që në Pyetjet e LINQ Është e pamundur të përcaktohen sugjerimet e tabelës, kështu që ju duhet të krijoni një paraqitje tjetër - një "mbështjellës" të formës së mëposhtme:

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

4) Përdorimi i funksioneve të tabelës

Shpesh në Pyetjet e LINQ Blloqe të mëdha të nënpyetjeve ose blloqe që përdorin pamje me një strukturë komplekse formojnë një pyetje përfundimtare me një strukturë ekzekutimi shumë komplekse dhe jooptimale.

Përfitimet kryesore të përdorimit të funksioneve të tabelës në Pyetjet e LINQ:

Mundësia, si në rastin e pamjeve, për t'u përdorur dhe specifikuar si një objekt, por ju mund të kaloni një grup parametrash hyrës:
FROM FUNCTION (@param1, @param2 ...)
Si rezultat, mund të arrihet kampionimi fleksibël i të dhënave
Në rastin e përdorimit të një funksioni tabele, nuk ka kufizime kaq të forta si në rastin e pamjeve të indeksuara të përshkruara më sipër:
1. Këshilla për tabelën:
  përmes LINQ Nuk mund të specifikoni se cilët indekse duhet të përdoren dhe të përcaktoni nivelin e izolimit të të dhënave kur kërkoni.
  Por funksioni i ka këto aftësi.
  Me funksionin, mund të arrini një plan pyetjesh ekzekutimi mjaft konstante, ku përcaktohen rregullat për punën me indekset dhe nivelet e izolimit të të dhënave
2. Përdorimi i funksionit lejon, në krahasim me pamjet e indeksuara, të merrni:
  - logjika komplekse e kampionimit të të dhënave (madje edhe duke përdorur sythe)
  - duke marrë të dhëna nga shumë tabela të ndryshme
  - Përdorimi i BASHKIMI и Ekziston
ofroj OPTION shumë i dobishëm kur duhet të sigurojmë kontroll të konkurencës OPTION (MAXDOP N), rendi i planit të ekzekutimit të pyetjes. Për shembull:
- mund të specifikoni një rikrijim të detyruar të planit të pyetjes OPTION (RICOMPILE)
- ju mund të specifikoni nëse do ta detyroni planin e pyetjes të përdorë rendin e bashkimit të specifikuar në pyetje OPTION (URDHJE DETYRIME)
Më shumë detaje rreth OPTION përshkruar këtu.
Përdorimi i pjesës më të ngushtë dhe më të kërkuar të të dhënave:
Nuk ka nevojë të ruani grupe të mëdha të dhënash në cache (siç është rasti me pamjet e indeksuara), nga të cilat ju ende duhet të filtroni të dhënat sipas parametrave.
Për shembull, ekziston një tabelë, filtri i së cilës KU janë përdorur tre fusha (a, b, c).

Në mënyrë konvencionale, të gjitha kërkesat kanë një gjendje konstante a = 0 dhe b = 0.

Megjithatë, kërkesa për terren c më të ndryshueshme.

Lëreni kushtin a = 0 dhe b = 0 Me të vërtetë na ndihmon të kufizojmë grupin e kërkuar rezultues në mijëra rekorde, por kushti vazhdon с e ngushton përzgjedhjen në njëqind rekorde.

Këtu funksioni i tabelës mund të jetë një opsion më i mirë.

Gjithashtu, një funksion i tabelës është më i parashikueshëm dhe më konsistent në kohën e ekzekutimit.

shembuj

Le të shohim një shembull të zbatimit duke përdorur bazën e të dhënave Questions si shembull.

Ka një kërkesë SELECT, i cili kombinon disa tabela dhe përdor një pamje (OperativeQuestions), në të cilën përkatësia kontrollohet me email (nëpërmjet Ekziston) te “Pyetjet Operative”:

Kërkesa 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])
));

Pamja ka një strukturë mjaft komplekse: ka bashkime nën-pyetëse dhe përdor renditjen dallueshme, i cili në përgjithësi është një operacion mjaft intensiv me burime.

Një mostër nga OperativeQuestions është rreth dhjetë mijë regjistrime.

Problemi kryesor me këtë pyetje është se për regjistrimet nga pyetja e jashtme, një nënpyetje e brendshme ekzekutohet në pamjen [OperativeQuestions], e cila duhet për [Email] = @p__linq__0 të kufizojmë përzgjedhjen e daljes (nëpërmjet Ekziston) deri në qindra regjistrime.

Dhe mund të duket se nënpyetja duhet të llogarisë rekordet një herë me [Email] = @p__linq__0, dhe më pas këto dy qindra rekorde duhet të lidhen me Id me Pyetjet, dhe pyetja do të jetë e shpejtë.

Në fakt, ekziston një lidhje sekuenciale e të gjitha tabelave: kontrollimi i korrespondencës së pyetjeve të identifikimit me ID nga OperativeQuestions dhe filtrimi me email.

Në fakt, kërkesa funksionon me të gjitha dhjetëra mijëra regjistrat OperativeQuestions, por nevojiten vetëm të dhënat me interes përmes Email-it.

OperativeQuestions shikoni tekstin:

Kërkesa 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));

Harta fillestare e pamjes në 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");
    }
}

Pyetja fillestare e 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();

Në këtë rast të veçantë, ne po shqyrtojmë zgjidhjen e këtij problemi pa ndryshime infrastrukturore, pa futur një tabelë të veçantë me rezultate të gatshme (“Active Queries”), e cila do të kërkonte një mekanizëm për plotësimin e tij me të dhëna dhe mbajtjen e tyre të përditësuar. .

Edhe pse kjo është një zgjidhje e mirë, ekziston një mundësi tjetër për të optimizuar këtë problem.

Qëllimi kryesor është të ruhen shënimet në memorien e fshehtë me [Email] = @p__linq__0 nga pamja OperativeQuestions.

Prezantoni funksionin e tabelës [dbo].[OperativeQuestionsUserMail] në bazën e të dhënave.

Duke dërguar Email si një parametër hyrës, ne marrim përsëri një tabelë vlerash:

Kërkesa 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

Kjo kthen një tabelë vlerash me një strukturë të dhënash të paracaktuar.

Në mënyrë që pyetjet për OperativeQuestionsUserMail të jenë optimale dhe të kenë plane optimale të pyetjeve, kërkohet një strukturë strikte dhe jo TABELA E KTHIMIT SI KTHIM...

Në këtë rast, pyetja e kërkuar 1 konvertohet në pyetjen 4:

Kërkesa 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]);

Pamjet dhe funksionet e hartës në 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})");
}

Pyetja përfundimtare e 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();

Rendi i kohës së ekzekutimit ka rënë nga 200-800 ms, në 2-20 ms, etj., pra dhjetëra herë më shpejt.

Nëse e marrim më mesatarisht, atëherë në vend të 350 ms kemi marrë 8 ms.

Nga avantazhet e dukshme marrim gjithashtu:

ulje e përgjithshme e ngarkesës së leximit,
ulje e ndjeshme e mundësisë së bllokimit
duke reduktuar kohën mesatare të bllokimit në vlera të pranueshme

Prodhim

Optimizimi dhe rregullimi i saktë i thirrjeve të bazës së të dhënave MS SQL përmes LINQ është një problem që mund të zgjidhet.

Vëmendja dhe qëndrueshmëria janë shumë të rëndësishme në këtë punë.

Në fillim të procesit:

është e nevojshme të kontrollohen të dhënat me të cilat funksionon kërkesa (vlerat, llojet e zgjedhura të të dhënave)
kryeni indeksimin e duhur të këtyre të dhënave
kontrolloni korrektësinë e kushteve të bashkimit midis tabelave

Përsëritja tjetër e optimizimit zbulon:

bazën e kërkesës dhe përcakton filtrin kryesor të kërkesës
duke përsëritur blloqe të ngjashme të pyetjeve dhe duke analizuar kryqëzimin e kushteve
në SSMS ose GUI tjetër për SQL Server optimizon veten Kërkesa SQL (caktimi i një ruajtjeje të ndërmjetme të të dhënave, ndërtimi i pyetjes që rezulton duke përdorur këtë ruajtje (mund të ketë disa))
në fazën e fundit, duke marrë për bazë atë që rezulton Kërkesa SQL, struktura është duke u rindërtuar Pyetje LINQ

Rezultati Pyetje LINQ duhet të bëhet identike në strukturë me optimalen e identifikuar Kërkesa SQL nga pika 3.

Mirënjohje

Shumë faleminderit për kolegët jobgemws и alex_ozr nga kompania Fortis për ndihmë në përgatitjen e këtij materiali.

Burimi: www.habr.com

Metodat për optimizimin e pyetjeve LINQ në C#.NET