Metodat e optimizimit të LINQ

Hyrje

Në këto artikuj u shqyrtuan disa metoda optimizimi kërkesat LINQ.
Këtu do të paraqesim edhe disa qasje për optimizimin e kodit, të lidhura me kërkesat LINQ.

Është e njohur se LINQ(Language-Integrated Query) — është një gjuhë e thjeshtë dhe e përshtatshme për kërkesat ndaj burimeve të të dhënave.

A LINQ to SQL është teknologjia e qasjes ndaj të dhënave në DBMS. Kjo është një mjet i fuqishëm për punën me të dhënat, ku përmes një gjuhe deklarative ndërtohen kërkesa, të cilat më pas do të transformohen në kërkesa SQL platformën dhe do të dërgohen në serverin e bazës së të dhënave për ekzekutim. Në rastin tonë, nën DBMS do të kuptojmë MS SQL Server.

Megjithatë, kërkesat LINQ nuk konvertohen në kërkesa të shkruara optimalisht kërkesa SQL, që mund të shkruante një DBA me përvojë me të gjitha nuancat e optimizimit kërkesat SQL:

lidhet optimale (JOIN) dhe filtrimin e rezultateve (KU)
shumë nuanca në përdorimin e lidhjeve dhe kushteve grupore
shumë variacione në zëvendësimin e kushtit IN në EKZISTONdhe NOT IN, në EKZISTON
keshim të përkohshëm të rezultateve përmes tabelave temporale, CTE, variablat tabelarë
përdorimi i propozimit (OPTION) me udhëzime dhe hintet tabelare ME (…)
përdorimi i pamjeve të indeksuara, si një nga mjetet për t'u çliruar nga leximet e panevojshme të të dhënave gjatë selektimeve

Pikat kryesore të ngushta në performancën e rezultateve kërkesat SQL në kompilim kërkesat LINQ janë:

konsolidimi i gjithë mekanizmit të përzgjedhjes së të dhënave në një kërkesë
ripërsëritja e blloqeve identike të kodit, e cila në fund çon në lexime të panevojshme të të dhënave
grupet e kushteve komplekse (logjikët "dhe" dhe "ose") — DHE dhe OSE, duke u lidhur në kushte të ndërlikuara, çon në atë që optimizuesi, duke patur indekse joklasifikuese të përshtatshme për fushat e nevojshme, përfundimisht fillon të bëjë skanimin e indekseve klasifikuese (INDEX SCAN) për grupet e kushteve
thellësia e nënshtresave të nënkërkesave e bën shumë problematike analizimin instruksioneve SQL dhe analizimi i planit të kërkesave nga ana e zhvilluesve dhe DBA

Metodat e optimizimit

Tani do të kalojmë direkt në metodat e optimizimit.

1) Indeksim shtesë

Më mirë është të shqyrtohen filtrat në tabelat kryesore të seleksionit, pasi shpesh e gjithë kërkesa ndërtohet rreth një ose dy tabelave kryesore (aplikime-njerëz-operacione) dhe me një grup të zakonshëm kushtesh (IsClosed, Canceled, Enabled, Status). Është e rëndësishme që për selitë e identifikura të krijohen indekse përkatëse.

Ky kjo zgjidhje ka kuptim kur zgjedhja në këto fusha kufizon ndjeshëm numrin e rezultateve të kthyera nga pyetja.

Për shembull, kemi 500,000 aplikime. Megjithatë, aplikimet aktive janë vetëm 2,000 regjistrime. Atëherë, një indeks i mirëzgjedhur do të na ndihmojë të eliminojmë INDEX SCAN shfletimin e një tabele të madhe dhe të zgjedhim shpejt të dhënat përmes një indeksi jo-klaster.

Gjithashtu, mungesa e indekseve mund të zbulohet përmes sugjerimeve të shpjegimit të planeve të kërkesave ose grumbullimit të statistikave të paraqitjeve sistemike MS SQL Server:

Të dhënat e të gjitha paraqitjeve përmbajnë informacion në lidhje me indekset e munguar, përveç indekseve hapësinore.

Megjithatë, indiset dhe ndihma e memorie shpesh janë metoda për të luftuar pasojat e keqshkrimeve kërkesat LINQ dhe kërkesat SQL.

Siç tregon praktika e vështirë e jetës për biznesin, shpesh është e rëndësishme të realizosh karakteristikat e biznesit në afate të caktuara. Prandaj shpesh kërkesat e mëdha kalojnë në një background me ndihmën e ndihmës së memorie.

Pjesërisht kjo është e justifikuar, pasi përdoruesi nuk ka nevojë gjithmonë për të dhëna të reja dhe arrihet një nivel i pranueshëm i reagimit të ndërfaqes së përdoruesit.

Ky qasje lejon që të zgjidhen kërkesat e biznesit, por në fund dëmton funksionalitetin e sistemit informatik, duke vonuar thjesht zgjidhjet e problemeve.

Gjithashtu, duhet të mbani mend se gjatë procesit të kërkimit të të dhënave të nevojshme për të shtuar indekse të reja, propozimet Grafik*, dokumentar për optimizimin mund të jenë të papërshtatshme nën rrethana të caktuara:

nëse tashmë ekzistojnë indekse me një grup të ngjashëm fushash
nëse fushat në tabelë nuk mund të indeksohen për shkak të kufizimeve të indekseve (me hollësi më të madhe për këtë është përshkruar këtu).

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

Ndonjëherë disa fusha nga një tabelë, sipas të cilave bëhet grupi i kushteve, mund të zëvendësohen duke futur një fushë të re.

Kjo është veçanërisht e rëndësishme për fushat e statusit, të cilat zakonisht janë ose bllokues ose të numrueshme.

Shembulli:

IsClosed = 0 DHE Canceled = 0 DHE Enabled = 0 zëvendësohet me Status = 1.

Këtu futet një atribut numëror Status, që sigurohet nga plotësimi i këtyre statusve në tabelë. Më pas, realizohet indeksimi i këtij atributi të ri.

Ky është një zgjidhje themelore për problematikën e performancës, sepse ne kërkojmë të dhëna pa llogaritje të panevojshme.

3) Materializimi i paraqitjes

Fatkeqësisht, në Kërkesat LINQ nuk është e mundur të përdoren drejtpërdrejt tabelat e përkohshme, CTE dhe variablat tabelarë.

Megjithatë, ka një mënyrë tjetër për optimizim në këtë rast - vistas e indekseve.

Grupi i kushteve (nga shembulli më sipër) IsClosed = 0 DHE Canceled = 0 DHE Enabled = 0 (ose një set kushtesh të ngjashme) bëhet një opsion i mirë për t'i përdorur ato në një pamje të indekseve, duke ruajtur një pjesë të vogël të të dhënave nga një shumë të madhe.

Por ka një sërë kufizimesh në materializimin e pamjes:

përdorimi i nënkërkesave, propozimi EKZISTON duhet të zëvendësohet me përdorimin e JOIN
nuk mund të përdoren propozime UNION, UNION ALL, PËRFUNDIMI, Të dhënat e tabelave
nuk mund të përdoren hinte tabelarë dhe propozime OPTION
nuk ka mundësi për të punuar me ciklet
nuk është e mundur të nxirren të dhënat në një pamje nga tabela të ndryshme

Është e rëndësishme të kujtohet se përfitimi real nga përdorimi i pamjes së indekseve mund të arrihet në fakt vetëm kur kjo është e indeksuar.

Por kur thirret pamja, këto indekse mund të mos përdoren, dhe për t'i përdorur ato qartë është e nevojshme të tregoni ME (NOEXPAND).

Pasi në Kërkesat LINQ nuk është e mundur të përcaktohen hinta tabelarë, kështu që duhet të bëhet një pamje tjetër - "mbështjellës" i këtij lloji:

KRIJONI PAMJEN EMRI_PAMJES AS SELECT * FROM MAT_VIEW ME (NOEXPAND);

4) Përdorimi i funksioneve tabelarë

Shpesh në Kërkesat LINQ bloku të mëdha të nënkërkesave ose blloqet që përdorin pamje me strukturë të komplikuar, formojnë një kërkesë përfundimtare me një strukturë shumë të komplikuar dhe jo optimale të ekzekutimit.

Përfitimet kryesore nga përdorimi i funksioneve tabelarë në Kërkesat LINQ:

Mundësia, ashtu si në rastin e pamjeve, përdor të deklarohet si objekt, por mund të kaloni një set parametrash hyrës:
FROM FUNKSION(@param1, @param2 ...)
në fund mund të arrihet një seleksion i fleksibël të të dhënave
Në rastin e përdorimit të funksionit tabelar, nuk ka kufizime të forta si në rastin e pamjeve të indekseve, të përshkruara më lart:
1. Hinta tabelarë:
  nëpërmjet LINQ nuk mund të specifikoni se cilat indekse duhet të përdoren dhe të përcaktoni nivelin e izolimit të të dhënave gjatë kërkesës.
  Por në funksion këto mundësi ekzistojnë.
  Me funksionin mund të arrihet një plan ekzekutimi të kërkesës mjaft konstant, ku janë të përcaktuara rregullat për punën me indet dhe nivelet e izolimit të të dhënave
2. Përdorimi i funksionit lejon, krahasuar me pamjet e indekseve, të merret:
  - logjikë të ndërlikuar për seleksionin e të dhënave (duke përfshirë përdorimin e cikleve)
  - selektoni të dhëna nga shumë tabela të ndryshme
  - përdorimin UNION dhe EKZISTON
Oferta OPTION shumë e dobishme kur na nevojitet të sigurojmë menaxhimin e paralelizmit OPCION(MAXDOP N), në rregullin e planit të ekzekutimit të kërkesës. Për shembull:
- mund të specifikohet ri-krijimi i detyrueshëm i planit të kërkesës OPCION (RIKOMPILE)
- mund të specifikohet nevoja për të siguruar përdorimin e detyrueshëm të rendit të bashkimit të planit të kërkesës, siç është e treguar në kërkesë OPCION (FORCE ORDER)
Më shumë detaje rreth OPTION përshkruhet këtu.
Përdorimi i prerjes më të ngushtë dhe të nevojshme të të dhënave:
Nuk ka nevojë të mbani grupe të mëdha të dhënash në cache (si në rastin e pamjeve të indeksuara), nga të cilat akoma duhet të filtrohen të dhënat bazuar në parametrin.
Për shembull, ka një tabelë, për të cilën për filtrin KU përdoren tri fushat (a, b, c).
Kondicionalisht për të gjitha kërkesat ka një kushtrim të vazhdueshëm a = 0 dhe b = 0.
Megjithatë, kërkesa për fushën c është më variabël.
Le të themi që kushti a = 0 dhe b = 0 na ndihmon vërtet të kufizojmë grupin e nevojshëm të marrë deri në mijëra regjistra, por kushti për me na ngushton seleksionin deri në një qind regjistra.
Këtu funksioni tabelar mund të rezultojë një opsion më të favorshëm.
Gjithashtu, funksioni tabelar është më i parashikueshëm dhe konstant në kohën e ekzekutimit.

Shembuj

Le të shqyrtojmë një shembull implementimi mbi bazën e të dhënave Questions.

Ka një kërkesë SELECT, që bashkon disa tabela dhe përdor një pamje (OperativeQuestions), në të cilën kontrollohet për email për përkatësinë (përmes EKZISTON) në «Kërkesat Aktive»([OperativeQuestions]):

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ë të mjaftueshme të ndërlikuar: ka bashkime nënpyetjesh dhe përdorim renditje DISTINCT, e cila në rregullin e përgjithshëm është një operacion mjaft kërkues.

Zgjedhja nga OperativeQuestions me rreth dhjetë mijë regjistrime.

Problemi kryesor i këtij kërkese është se për regjistrimet nga kërkesa e jashtme ekzekutohet një nënkërkesë mbi pamjen [OperativeQuestions], e cila duhet të kufizojë zgjedhjen e prodhimit për [Email] = @p__linq__0 (përmes EKZISTON) në qindra regjistrime.

Dhe mund të duket se nënkërkesa duhet të llogaritë një herë regjistrimet për [Email] = @p__linq__0, pastaj këto disa qindra regjistrime duhet të lidhen sipas Id me Questions, dhe kërkesa do të jetë e shpejtë.

Në të vërtetë po ndodh lidhja sekondare e të gjitha tabelave: dhe kontrollimi i përputhjes së Id Questions me Id nga OperativeQuestions, dhe filtrimi sipas Email.

Në thelb kërkesa funksionon me të gjitha dhjetëra mijëra regjistrime të OperativeQuestions, ndërkohë që ne na nevojiten vetëm të dhënat e rëndësishme mbi Email.

Teksti i pamjes OperativeQuestions:

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

Mappimi origjinal i 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");
    }
}

Kërkesa origjinale 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, po shqyrtojmë zgjidhjen e këtij problemi pa ndryshime infrastrukturore, pa futur një tabelë të veçantë me rezultatet e gatshme ("Kërkesat Aktive"), për të cilën do të ishte e nevojshme një mekanizëm për plotësimin e saj me të dhëna dhe mbajtjen e saj të përditësuar.

Megjithëse kjo është një zgjidhje e mirë, ekziston edhe një opsion tjetër për optimizimin e kësaj detyre.

Qëllimi kryesor është të ruhet në cache regjistrimi për [Email] = @p__linq__0 nga pamja OperativeQuestions.

Futem funksionin tabelar [dbo].[OperativeQuestionsUserMail] në bazën e të dhënave.

Duke dërguar si parametër hyrës Email, kënaqemi me 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

Këtu kthehet një tabelë vlerash me një strukturë të dhënash të paracaktuar.

Për të siguruar që kërkesat për OperativeQuestionsUserMail të jenë optimale, me plane optimale kërkimesh, nevojitet një strukturë strikte, e jo RETURNS TABLE AS RETURN…

Në këtë rast, Kërkesa 1 e desideruar kthehet në Kërkesën 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]);

Mapping i pamjes dhe funksionit në 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})");
}

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

Koha e ekzekutimit është ulur nga 200-800 ms në 2-20 ms, etj., që do të thotë shumë herë më shpejt.

Nëse marrim një mesatare, përveç 350 ms morëm 8 ms.

Nga përfitimet e dukshme marrim gjithashtu:

uli load-in e përgjithshëm në lexim,
reduktimin e ndjeshëm të mundësisë së bllokimeve
reduktimin e kohës mesatare të bllokimeve në nivele të pranueshme

Përfundimi

Optimizimi dhe përshtatja e thirrjeve në DB Grafik*, dokumentar nëpërmjet LINQ është një detyrë që mund të zgjidhet.

Në këtë punë, kujdesi dhe radhitja janë shumë të rëndësishme.

Në fillim të procesit:

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

Në iterimin tjetër të optimizimit, identifikohen:

baza e kërkesës dhe përcaktohet filtri kryesor i kërkesës
blloqet e ngjashme të përsëritura të kërkesës dhe analizohet ndërthurrja e kushteve
në SSMS ose GUI tjetër për SQL Server optimizimin e vetë Kërkesa SQL (dalja e një depoje përkohëse të të dhënave, ndërtimi i kërkesës rezultuese duke përdorur këtë depo (mund të jenë disa))
në etapën e fundit, duke marrë si bazë rezultatin Kërkesa SQL, struktura e kërkesës LINQ

Si rezultat, rezultati i marrë Kërkesa LINQ duhet të bëhet strukturisht identik me atë optimal SQL që përmendet në pikën 3. Faleminderit shumë kolegëve

Faleminderit

jobgemws alex_ozr dhe nga kompania Fortis për ndihmën në përgatitjen e këtij materiali. Hyrje Në këtë artikull u shqyrtuan disa metoda optimizimi të LINQ. Këtu do të paraqesim disa të tjera.

Burimi: habr.com