🥇Metoder til optimering af LINQ-forespørgsler i C#.NET

Indledning

В denne artikel nogle optimeringsmetoder blev overvejet LINQ forespørgsler.
Her præsenterer vi nogle flere tilgange til kodeoptimering relateret til LINQ forespørgsler.

Kendte det LINQ(Language-Integrated Query) er et simpelt og bekvemt sprog til at forespørge på en datakilde.

А LINQ til SQL er en teknologi til at få adgang til data i et DBMS. Dette er et kraftfuldt værktøj til at arbejde med data, hvor forespørgsler konstrueres gennem et deklarativt sprog, som derefter konverteres til SQL-forespørgsler platform og sendt til databaseserveren til udførelse. I vores tilfælde mener vi med DBMS MS SQL Server.

Imidlertid LINQ forespørgsler er ikke konverteret til optimalt skrevne SQL-forespørgsler, som en erfaren DBA kunne skrive med alle nuancer af optimering SQL-forespørgsler:

optimale forbindelser (JOIN) og filtrering af resultaterne (HVOR)
mange nuancer i at bruge forbindelser og gruppeforhold
mange variationer i erstatningsforhold IN på EXISTSи IKKE I, <> på EXISTS
mellemliggende caching af resultater via midlertidige tabeller, CTE, tabelvariabler
brug af sætning (MULIGHED) med instruktioner og tabeltip MED (...)
ved at bruge indekserede visninger som et af midlerne til at slippe af med overflødige dataaflæsninger under valg

De vigtigste flaskehalse i den resulterende ydeevne SQL-forespørgsler ved kompilering LINQ forespørgsler De er:

konsolidering af hele dataudvælgelsesmekanismen i én anmodning
duplikere identiske kodeblokke, hvilket i sidste ende fører til flere unødvendige datalæsninger
grupper af multi-komponent betingelser (logiske "og" og "eller") - AND и OR, ved at kombinere til komplekse forhold, fører det til, at optimeringsværktøjet, der har passende ikke-klyngede indekser til de nødvendige felter, i sidste ende begynder at scanne mod det klyngede indeks (INDEKSSCANNING) efter grupper af forhold
dyb indlejring af underforespørgsler gør parsing meget problematisk SQL-sætninger og analyse af forespørgselsplanen fra udviklere og DBA

Optimeringsmetoder

Lad os nu gå direkte til optimeringsmetoder.

1) Yderligere indeksering

Det er bedst at overveje filtre på hovedudvælgelsestabellerne, da hele forespørgslen meget ofte er bygget op omkring en eller to hovedtabeller (applikationer-folk-operationer) og med et standardsæt af betingelser (IsClosed, Canceled, Enabled, Status). Det er vigtigt at skabe passende indekser for de identificerede prøver.

Denne løsning giver mening, når valg af disse felter begrænser det returnerede sæt til forespørgslen markant.

For eksempel har vi 500000 ansøgninger. Der er dog kun 2000 aktive applikationer. Så vil et korrekt valgt indeks redde os fra INDEKSSCANNING på en stor tabel og giver dig mulighed for hurtigt at vælge data gennem et ikke-klynget indeks.

Manglen på indekser kan også identificeres gennem prompter om at parse forespørgselsplaner eller indsamle systemvisningsstatistikker MS SQL Server:

Alle visningsdata indeholder oplysninger om manglende indekser, med undtagelse af rumlige indekser.

Indeks og caching er dog ofte metoder til at bekæmpe konsekvenserne af dårligt skrevet LINQ forespørgsler и SQL-forespørgsler.

Som livets hårde praksis viser, er det ofte vigtigt for en virksomhed at implementere forretningsfunktioner inden for bestemte deadlines. Og derfor bliver tunge anmodninger ofte overført til baggrunden med caching.

Dette er delvist berettiget, da brugeren ikke altid har brug for de nyeste data, og der er et acceptabelt niveau af reaktionsevne i brugergrænsefladen.

Denne tilgang tillader løsning af forretningsbehov, men reducerer i sidste ende informationssystemets ydeevne ved blot at forsinke løsninger på problemer.

Det er også værd at huske, at i processen med at søge efter de nødvendige indekser for at tilføje, forslag MSSQL optimering kan være forkert, herunder under følgende forhold:

hvis der allerede er indekser med et lignende sæt felter
hvis felterne i tabellen ikke kan indekseres på grund af indekseringsbegrænsninger (beskrevet mere detaljeret her).

2) Sammenlægning af attributter til én ny attribut

Nogle gange kan nogle felter fra en tabel, som tjener som grundlag for en gruppe af betingelser, erstattes ved at indføre et nyt felt.

Dette gælder især for statusfelter, som normalt er enten bit- eller heltalstype.

Eksempel:

IsClosed = 0 OG Annulleret = 0 OG Aktiveret = 0 erstattet af Status = 1.

Det er her, heltal Status-attributten introduceres for at sikre, at disse statusser er udfyldt i tabellen. Dernæst indekseres denne nye attribut.

Dette er en grundlæggende løsning på ydeevneproblemet, fordi vi tilgår data uden unødvendige beregninger.

3) Materialisering af udsigten

Desværre i LINQ forespørgsler Midlertidige tabeller, CTE'er og tabelvariabler kan ikke bruges direkte.

Der er dog en anden måde at optimere til dette tilfælde - indekserede visninger.

Tilstandsgruppe (fra eksemplet ovenfor) IsClosed = 0 OG Annulleret = 0 OG Aktiveret = 0 (eller et sæt andre lignende betingelser) bliver en god mulighed for at bruge dem i en indekseret visning, der cachelagrer et lille udsnit af data fra et stort sæt.

Men der er en række begrænsninger, når en visning materialiseres:

brug af underforespørgsler, klausuler EXISTS skal udskiftes ved at bruge JOIN
du kan ikke bruge sætninger UNION, UNION ALLE, UNDTAGELSE, KRYDSE
Du kan ikke bruge tabeltip og klausuler MULIGHED
ingen mulighed for at arbejde med cykler
Det er umuligt at vise data i én visning fra forskellige tabeller

Det er vigtigt at huske, at den reelle fordel ved at bruge en indekseret visning kun kan opnås ved faktisk at indeksere den.

Men når du kalder en visning, må disse indekser ikke bruges, og for at bruge dem eksplicit, skal du angive MED(NOEXPAND).

Siden i LINQ forespørgsler Det er umuligt at definere tabeltip, så du skal oprette en anden repræsentation - en "indpakning" af følgende form:

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

4) Brug af tabelfunktioner

Ofte i LINQ forespørgsler Store blokke af underforespørgsler eller blokke, der bruger visninger med en kompleks struktur, danner en endelig forespørgsel med en meget kompleks og suboptimal udførelsesstruktur.

Vigtigste fordele ved at bruge tabelfunktioner i LINQ forespørgsler:

Muligheden, som i tilfældet med visninger, til at blive brugt og specificeret som et objekt, men du kan sende et sæt inputparametre:
FRA FUNKTION(@param1, @param2 ...)
Som et resultat kan der opnås fleksibel datasampling
I tilfælde af brug af en tabelfunktion er der ikke så stærke begrænsninger som i tilfælde af indekserede visninger beskrevet ovenfor:
1. Tabel tip:
  gennem LINQ Du kan ikke angive, hvilke indekser der skal bruges og bestemme dataisolationsniveauet, når du forespørger.
  Men funktionen har disse muligheder.
  Med funktionen kan du opnå en ret konstant eksekveringsforespørgselsplan, hvor regler for arbejde med indekser og dataisoleringsniveauer er defineret
2. Brug af funktionen gør det muligt i sammenligning med indekserede visninger at opnå:
  - kompleks datasamplinglogik (selv ved brug af loops)
  - hente data fra mange forskellige tabeller
  - anvendelse af UNION и EXISTS
tilbud MULIGHED meget nyttigt, når vi skal sørge for samtidighedskontrol MULIGHED (MAXDOP N), rækkefølgen af forespørgselsudførelsesplanen. For eksempel:
- du kan angive en tvungen genskabelse af forespørgselsplanen MULIGHED (GENKOMPILER)
- du kan angive, om forespørgselsplanen skal tvinges til at bruge den join-rækkefølge, der er angivet i forespørgslen MULIGHED (TVANGSORDRE)
Flere detaljer vedr MULIGHED beskrevet her.
Brug af det smalleste og mest nødvendige dataudsnit:
Der er ingen grund til at gemme store datasæt i caches (som det er tilfældet med indekserede visninger), hvorfra du stadig skal filtrere dataene efter parameter.
For eksempel er der en tabel, hvis filter HVOR tre felter bruges (a, b, c).

Konventionelt har alle anmodninger en konstant tilstand a = 0 og b = 0.

Dog anmodningen om feltet c mere varierende.

Lad tilstanden a = 0 og b = 0 Det hjælper os virkelig med at begrænse det nødvendige resulterende sæt til tusindvis af poster, men betingelsen på с indsnævrer udvalget til hundrede poster.

Her kan bordfunktionen være en bedre mulighed.

Desuden er en tabelfunktion mere forudsigelig og konsekvent i udførelsestid.

Примеры

Lad os se på et eksempel på implementering ved hjælp af Questions-databasen som et eksempel.

Der er en anmodning SELECT, som kombinerer flere tabeller og bruger én visning (OperativeQuestions), hvor tilknytningen kontrolleres via e-mail (via EXISTS) til "Operative spørgsmål":

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

Visningen har en ret kompleks struktur: den har underforespørgselssammenføjninger og bruger sortering DISTINCT, hvilket generelt er en ret ressourcekrævende operation.

En prøve fra OperativeQuestions er omkring ti tusinde poster.

Hovedproblemet med denne forespørgsel er, at for posterne fra den ydre forespørgsel udføres en intern underforespørgsel på [OperativeQuestions]-visningen, som for [E-mail] = @p__linq__0 skulle tillade os at begrænse outputvalget (via EXISTS) op til hundredvis af poster.

Og det kan se ud til, at underforespørgslen skal beregne posterne én gang af [Email] = @p__linq__0, og så skal disse par hundrede poster forbindes med Id med spørgsmål, og forespørgslen vil være hurtig.

Faktisk er der en sekventiel forbindelse af alle tabeller: kontrol af overensstemmelsen mellem Id-spørgsmål og Id fra OperativeQuestions og filtrering efter e-mail.

Faktisk fungerer anmodningen med alle titusindvis af OperativeQuestions-poster, men kun de relevante data er nødvendige via e-mail.

Operative Questions visningstekst:

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

Indledende visningskortlægning i 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");
    }
}

Indledende LINQ-forespørgsel

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

I dette særlige tilfælde overvejer vi en løsning på dette problem uden infrastrukturelle ændringer, uden at indføre en separat tabel med færdige resultater ("Aktive forespørgsler"), som ville kræve en mekanisme til at fylde den med data og holde den opdateret .

Selvom dette er en god løsning, er der en anden mulighed for at optimere dette problem.

Hovedformålet er at cache poster med [E-mail] = @p__linq__0 fra OperativeQuestions-visningen.

Introducer tabelfunktionen [dbo].[OperativeQuestionsUserMail] i databasen.

Ved at sende e-mail som inputparameter får vi en tabel med værdier tilbage:

Anmodning 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

Dette returnerer en tabel med værdier med en foruddefineret datastruktur.

For at forespørgsler til OperativeQuestionsUserMail skal være optimale og have optimale forespørgselsplaner, kræves der en stram struktur, og ikke RETURTABEL SOM RETURNERING...

I dette tilfælde konverteres den påkrævede forespørgsel 1 til forespørgsel 4:

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

Kortlægning af visninger og funktioner i 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})");
}

Endelig LINQ-forespørgsel

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ækkefølgen af eksekveringstiden er faldet fra 200-800 ms til 2-20 ms osv., dvs. titusindvis af gange hurtigere.

Hvis vi tager det mere gennemsnitligt, så fik vi i stedet for 350 ms 8 ms.

Fra de åbenlyse fordele får vi også:

generel reduktion i læsebelastning,
væsentlig reduktion af sandsynligheden for blokering
reducere den gennemsnitlige blokeringstid til acceptable værdier

Output

Optimering og finjustering af databasekald MSSQL gennem LINQ er et problem, der kan løses.

Opmærksomhed og konsekvens er meget vigtigt i dette arbejde.

I begyndelsen af processen:

det er nødvendigt at kontrollere de data, som anmodningen fungerer med (værdier, udvalgte datatyper)
udføre korrekt indeksering af disse data
kontrollere rigtigheden af sammenføjningsbetingelserne mellem tabeller

Den næste optimeringsiteration afslører:

grundlaget for anmodningen og definerer hovedanmodningsfilteret
gentagelse af lignende forespørgselsblokke og analyser af betingelsernes skæringspunkt
i SSMS eller anden GUI til SQL Server optimerer sig selv SQL-forespørgsel (tildeling af et mellemliggende datalager, opbygning af den resulterende forespørgsel ved hjælp af dette lager (der kan være flere))
på det sidste trin med udgangspunkt i det resulterende SQL-forespørgsel, er strukturen ved at blive genopbygget LINQ forespørgsel

Det resulterende LINQ forespørgsel skal blive identisk i struktur med det identificerede optimale SQL-forespørgsel fra punkt 3.

Tak

Mange tak til kollegerne jobgemws и alex_ozr fra virksomheden Fortis for hjælp til at udarbejde dette materiale.

Kilde: www.habr.com

Metoder til optimering af LINQ-forespørgsler i C#.NET