🥇 Aðferðir til að fínstilla LINQ fyrirspurnir í C#.NET

Inngangur

В Þessi grein nokkrar hagræðingaraðferðir voru skoðaðar LINQ fyrirspurnir.
Hér kynnum við nokkrar fleiri aðferðir við hagræðingu kóða sem tengjast LINQ fyrirspurnir.

Það er vitað að LINQ(Language-Integrated Query) er einfalt og þægilegt tungumál til að spyrjast fyrir um gagnagjafa.

А LINQ til SQL er tækni til að fá aðgang að gögnum í DBMS. Þetta er öflugt tól til að vinna með gögn, þar sem fyrirspurnir eru smíðaðar í gegnum yfirlýsingumál, sem síðan verður breytt í SQL fyrirspurnir vettvang og send á gagnagrunnsþjóninn til framkvæmdar. Í okkar tilviki er átt við með DBMS MS SQL netþjónn.

Hins vegar, LINQ fyrirspurnir er ekki breytt í best skrifaðar SQL fyrirspurnir, sem reyndur DBA gæti skrifað með öllum blæbrigðum hagræðingar SQL fyrirspurnir:

bestu tengingar (JOIN) og sía niðurstöðurnar (HVAR)
mörg blæbrigði í notkun tengsla og hópskilyrða
mörg afbrigði í að skipta um aðstæður IN á VERÐURи EKKI VIÐ, <> á VERÐUR
millivistun niðurstaðna í gegnum tímabundnar töflur, CTE, töflubreytur
notkun setningar (Valkostur) með leiðbeiningum og töfluráðum mEÐ (...)
að nota verðtryggðar skoðanir sem eina af leiðunum til að losna við óþarfa gagnalestur við val

Helstu frammistöðu flöskuhálsar af því SQL fyrirspurnir við samantekt LINQ fyrirspurnir eru:

sameining alls gagnavalskerfisins í einni beiðni
afrit af eins blokkum af kóða, sem að lokum leiðir til margra óþarfa gagnalestra
hópar fjölþátta skilyrða (rökrétt „og“ og „eða“) - OG и OR, sameining í flóknar aðstæður, leiðir til þess að fínstillingarmaðurinn, sem hefur viðeigandi vísitölur sem ekki eru í þyrpingum fyrir nauðsynlega reiti, byrjar að lokum að skanna á móti þyrpingavísinum (INDEX SCAN) eftir skilyrðum
djúp hreiður undirfyrirspurna gerir þáttun mjög erfið SQL staðhæfingar og greining á fyrirspurnaráætlun af hálfu hönnuða og DBA

Hagræðingaraðferðir

Nú skulum við fara beint í hagræðingaraðferðir.

1) Viðbótarverðtrygging

Best er að huga að síum á aðalvalstöflunum, þar sem mjög oft er öll fyrirspurnin byggð í kringum eina eða tvær aðaltöflur (applications-people-operations) og með stöðluðum skilyrðum (IsClosed, Cancelled, Enabled, Status). Mikilvægt er að búa til viðeigandi vísitölur fyrir auðkennd sýni.

Þessi lausn er skynsamleg þegar þessi reiti eru valin takmarkar verulega skilasettið við fyrirspurnina.

Til dæmis erum við með 500000 umsóknir. Hins vegar eru aðeins 2000 virkar umsóknir. Þá mun rétt valin vísitala bjarga okkur frá INDEX SCAN á stóru borði og gerir þér kleift að velja gögn fljótt í gegnum vísitölu sem ekki er þyrping.

Einnig er hægt að bera kennsl á skort á vísitölum með því að hvetja til að flokka fyrirspurnaáætlanir eða safna tölfræði kerfisskoðunar MS SQL netþjónn:

Öll yfirlitsgögn innihalda upplýsingar um vísitölur sem vantar, að undanskildum staðbundnum vísitölum.

Hins vegar eru vísitölur og skyndiminni oft aðferðir til að berjast gegn afleiðingum illa skrifaðra LINQ fyrirspurnir и SQL fyrirspurnir.

Eins og hörð lífsreynsla sýnir er það oft mikilvægt fyrir fyrirtæki að innleiða viðskiptaeiginleika innan ákveðinna tímamarka. Og þess vegna eru þungar beiðnir oft fluttar í bakgrunninn með skyndiminni.

Þetta er að hluta til réttlætanlegt, þar sem notandinn þarf ekki alltaf nýjustu gögnin og það er ásættanleg svörun notendaviðmótsins.

Þessi nálgun gerir kleift að leysa þarfir fyrirtækja, en dregur að lokum úr afköstum upplýsingakerfisins með því einfaldlega að seinka lausnum á vandamálum.

Það er líka þess virði að muna að í því ferli að leita að nauðsynlegum vísitölum til að bæta við, tillögur MS SQL hagræðing gæti verið röng, þar á meðal við eftirfarandi aðstæður:

ef það eru þegar til vísitölur með svipað sett af reitum
ef ekki er hægt að verðtryggja reitina í töflunni vegna verðtryggingartakmarkana (lýst nánar hér).

2) Sameina eiginleika í eina nýja eiginleika

Stundum er hægt að skipta út sumum reitum úr einni töflu, sem þjóna sem grundvöllur fyrir hóp skilyrða, með því að kynna eitt nýtt reit.

Þetta á sérstaklega við um stöðureiti, sem venjulega eru annað hvort bita eða heiltala að gerð.

Dæmi:

IsClosed = 0 OG Hætt við = 0 OG Virkt = 0 er skipt út fyrir Staða = 1.

Þetta er þar sem heiltala Staða eigindin er kynnt til að tryggja að þessar stöður séu fylltar út í töflunni. Næst er þessi nýja eiginleiki verðtryggður.

Þetta er grundvallarlausn á frammistöðuvandanum, vegna þess að við fáum aðgang að gögnum án óþarfa útreikninga.

3) Raunhæfing útsýnisins

Því miður í LINQ fyrirspurnir Ekki er hægt að nota tímabundnar töflur, CTE og töflubreytur beint.

Hins vegar er önnur leið til að hagræða fyrir þetta tilvik - verðtryggð skoðanir.

Ástandshópur (úr dæminu hér að ofan) IsClosed = 0 OG Hætt við = 0 OG Virkt = 0 (eða sett af öðrum svipuðum skilyrðum) verður góður kostur til að nota þau í verðtryggðu yfirliti og geymir litla sneið af gögnum úr stóru mengi.

En það eru nokkrar takmarkanir þegar skoðanir verða að veruleika:

notkun undirfyrirspurna, ákvæða VERÐUR ætti að skipta út með því að nota JOIN
þú getur ekki notað setningar UNION, SAMBAND ALLT, Undantekning, VIÐSKIPTI
Þú getur ekki notað töfluvísbendingar og ákvæði Valkostur
enginn möguleiki á að vinna með hringrásir
Það er ómögulegt að birta gögn í einni sýn úr mismunandi töflum

Það er mikilvægt að muna að raunverulegur ávinningur af því að nota verðtryggða skoðun er aðeins hægt að ná með því að raunverulega verðtryggja það.

En þegar hringt er í útsýni er ekki hægt að nota þessar vísitölur og til að nota þær beint verður þú að tilgreina MEÐ(NOEXPAND).

Síðan í LINQ fyrirspurnir Það er ómögulegt að skilgreina töfluvísbendingar, svo þú verður að búa til aðra framsetningu - "umbúðir" af eftirfarandi formi:

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

4) Notkun töfluaðgerða

Oft í LINQ fyrirspurnir Stórir blokkir af undirfyrirspurnum eða blokkir sem nota skoðanir með flókna uppbyggingu mynda lokafyrirspurn með mjög flókinni og óhagkvæmri framkvæmdaruppbyggingu.

Helstu kostir þess að nota töfluaðgerðir í LINQ fyrirspurnir:

Hæfni, eins og í tilfelli skoðana, til að nota og tilgreina sem hlut, en þú getur sent sett af inntaksbreytum:
FRÁ FUNCTION(@param1, @param2 ...)
Fyrir vikið er hægt að ná fram sveigjanlegri gagnasýnatöku
Þegar um er að ræða notkun töflufalls eru engar eins sterkar takmarkanir og þegar um er að ræða verðtryggðar skoðanir sem lýst er hér að ofan:
1. Ábendingar um töflu:
  gegnum LINQ Þú getur ekki tilgreint hvaða vísitölur eigi að nota og ákvarða gagnaeinangrunarstigið þegar spurt er.
  En aðgerðin hefur þessa möguleika.
  Með aðgerðinni er hægt að ná fram nokkuð stöðugri framkvæmd fyrirspurnaáætlun, þar sem reglur um að vinna með vísitölur og gagnaeinangrunarstig eru skilgreindar
2. Með því að nota aðgerðina er hægt, í samanburði við verðtryggðar skoðanir, að fá:
  - flókin gagnasýnafræði (jafnvel með því að nota lykkjur)
  - að sækja gögn úr mörgum mismunandi töflum
  - notkun UNION и VERÐUR
Tilboð Valkostur mjög gagnlegt þegar við þurfum að veita samhliða eftirlit VALKOSTI(MAXDOP N), röð framkvæmdaráætlunar fyrirspurnar. Til dæmis:
- þú getur tilgreint þvingaða endurgerð á fyrirspurnaráætluninni VALKOSTUR (SAMLAÐA aftur)
- þú getur tilgreint hvort þvinga eigi fyrirspurnaráætlunina til að nota sameiningarröðina sem tilgreind er í fyrirspurninni VALKOSTUR (AÐVÖLD)
Nánari upplýsingar um Valkostur lýst hér.
Með því að nota þrengstu og nauðsynlegustu gagnasneiðina:
Það er engin þörf á að geyma stór gagnasöfn í skyndiminni (eins og raunin er með verðtryggð útsýni), sem þú þarft samt að sía gögnin eftir færibreytum.
Til dæmis er tafla þar sem sía HVAR þrír reitir eru notaðir (a, b, c).

Venjulega hafa allar beiðnir stöðugt ástand a = 0 og b = 0.

Hins vegar beiðni um völlinn c breytilegri.

Láttu skilyrðið a = 0 og b = 0 Það hjálpar okkur í raun að takmarka nauðsynlegar niðurstöður settar við þúsundir skráa, en skilyrðið á с þrengir úrvalið niður í hundrað plötur.

Hér gæti borðaðgerðin verið betri kostur.

Einnig er töfluaðgerð fyrirsjáanlegri og stöðugri í framkvæmdartíma.

dæmi

Við skulum skoða dæmi um útfærslu með því að nota spurningagagnagrunninn sem dæmi.

Það er beiðni VELJA, sem sameinar nokkrar töflur og notar eina sýn (OperativeQuestions), þar sem tenging er athugað með tölvupósti (í gegnum VERÐUR) í „Virkar fyrirspurnir“ ([OperativeQuestions]):

Beiðni 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])
));

Útsýnið hefur frekar flókna uppbyggingu: það hefur undirfyrirspurnartengingar og notar flokkun DISTINCT, sem almennt er nokkuð auðlindafrekur rekstur.

Úrtak úr OperativeQuestions er um tíu þúsund færslur.

Helsta vandamálið við þessa fyrirspurn er að fyrir færslur úr ytri fyrirspurninni er innri undirfyrirspurn keyrð á [OperativeQuestions] skjánum, sem ætti fyrir [Email] = @p__linq__0 að takmarka framleiðsluvalið (í gegnum VERÐUR) allt að hundruðum gagna.

Og það kann að virðast sem undirfyrirspurnin ætti að reikna færslurnar einu sinni með [Email] = @p__linq__0, og þá ættu þessar nokkur hundruð færslur að vera tengdar með auðkenni með spurningum, og fyrirspurnin verður hröð.

Reyndar er raðtenging allra taflna: að athuga samsvörun auðkennisspurninga við auðkennis frá OperativeQuestions og sía með tölvupósti.

Reyndar virkar beiðnin með öllum tugþúsundum OperativeQuestions-skráa, en aðeins er þörf á þeim gögnum sem vekur áhuga með tölvupósti.

Texti til að skoða rekstrarspurningar:

Beiðni 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));

Upphafleg kortlagning í 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");
    }
}

Upphafleg LINQ fyrirspurn

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

Í þessu tiltekna tilviki erum við að íhuga lausn á þessu vandamáli án innviðabreytinga, án þess að kynna sérstaka töflu með tilbúnum niðurstöðum („virkar fyrirspurnir“), sem myndi krefjast kerfis til að fylla hana af gögnum og halda þeim uppfærðum .

Þó að þetta sé góð lausn, þá er annar möguleiki til að hagræða þessu vandamáli.

Megintilgangurinn er að vista færslur með [Email] = @p__linq__0 frá OperativeQuestions skjánum.

Kynntu töfluaðgerðina [dbo].[OperativeQuestionsUserMail] í gagnagrunninn.

Með því að senda tölvupóst sem innsláttarbreytu fáum við til baka töflu yfir gildi:

Beiðni 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

Þetta skilar töflu yfir gilda með fyrirfram skilgreindri gagnauppbyggingu.

Til þess að fyrirspurnir til OperativeQuestionsUserMail séu ákjósanlegar og hafa ákjósanlegar fyrirspurnaáætlanir þarf stranga uppbyggingu og ekki SKILTAFLA SEM SKIL...

Í þessu tilviki er nauðsynlegri fyrirspurn 1 breytt í fyrirspurn 4:

Beiðni 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]);

Kortlagning skoðana og aðgerða í 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})");
}

Loka LINQ fyrirspurn

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

Röð framkvæmdartíma hefur lækkað úr 200-800 ms, í 2-20 ms, o.s.frv., þ.e.a.s. tugum sinnum hraðar.

Ef við tökum það meira að meðaltali, þá fengum við 350 ms í stað 8 ms.

Frá augljósum kostum fáum við líka:

almenn minnkun á lestrarálagi,
verulega minnkun á líkum á lokun
stytta meðallokunartímann niður í viðunandi gildi

Output

Hagræðing og fínstilling á gagnagrunnssímtölum MS SQL gegnum LINQ er vandamál sem hægt er að leysa.

Umhyggja og samkvæmni er mjög mikilvæg í þessari vinnu.

Í upphafi ferlisins:

það er nauðsynlegt að athuga gögnin sem beiðnin virkar með (gildi, valdar gagnategundir)
framkvæma viðeigandi flokkun þessara gagna
athuga hvort sameiningarskilyrði á milli taflna séu rétt

Næsta fínstillingarendurtekning sýnir:

grunnur beiðninnar og skilgreinir aðalbeiðnasíuna
endurtaka svipaðar fyrirspurnarblokkir og greina skurðpunkt skilyrða
í SSMS eða öðru GUI fyrir SQL Server hagræðir sér SQL fyrirspurn (úthluta milligagnageymslu, búa til fyrirspurnina sem myndast með því að nota þessa geymslu (það geta verið nokkrir))
á síðasta stigi, að teknu tilliti til niðurstöðunnar SQL fyrirspurn, er verið að endurbyggja mannvirkið LINQ fyrirspurn

Afleiðingin LINQ fyrirspurn ætti að verða eins að uppbyggingu og tilgreindu ákjósanlegu SQL fyrirspurn frá 3. tölul.

Viðurkenningar

Kærar þakkir til samstarfsmanna jobgemws и alex_ozr frá fyrirtækinu Fortis um aðstoð við gerð þessa efnis.

Heimild: www.habr.com

Aðferðir til að fínstilla LINQ fyrirspurnir í C#.NET