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

Introduktion

I denne artikel nogle metoder til optimering blev overvejet LINQ-forespørgsler.
Her vil vi også præsentere nogle tilgange til optimering af koden relateret til LINQ-forespørgsler.

Det er kendt, at LINQ(Language-Integrated Query) — er et simpelt og praktisk forespørgselssprog til datakilder.

A LINQ til SQL er en teknologi til datatilgang i databasesystemer. Det er et kraftfuldt værktøj til dataarbejde, hvor forespørgsler konstrueres gennem et deklarativt sprog, som derefter vil blive transformeret til SQL-forespørgsler platformen og sendes til databaseserveren for udførelse. I vores tilfælde vil vi forstå MS SQL Server.

Men, LINQ-forespørgsler ikke blive omformuleret til optimalt skrevne SQL-forespørgsler, som en erfaren DBA ville kunne skrive med alle nuancer af optimering SQL-forespørgsler:

optimale forbindelser (JOIN) og filtrering af resultater (HVOR)
mange nuancer i brugen af forbindelser og gruppebetingelser
mange variationer i erstatning af betingelser IN og få en fungerende kommando. EXISTSog NOT IN, til EXISTS
midlertidig caching af resultater via midlertidige tabeller, CTE, tabelvariabler
brugen af sættet (OPTION) med anvisninger og tabel hints WITH (…)
brug af indekserede visninger som et af midlerne til at undgå overflødige dataoplysninger ved valg

De vigtigste flaskehalse i ydeevnen, der opstår SQL-forespørgsler ved kompilering LINQ-forespørgsler er:

konsolidering af hele mekanismen til dataudtag i én forespørgsel
duplikering af identiske kodeblokke, hvilket i sidste ende fører til gentagne unødvendige dataoplysninger
grupper af komplekse betingelser (logiske "og" og "eller") — OG og OR, som forbindes i komplekse betingelser, får optimizeren, der har passende ikke-klusterindekser på de nødvendige felter, til sidst alligevel til at udføre en scanning på det klusterede indeks (INDEX SCAN) efter grupper af betingelser
en dyb indlejring af underforespørgsler gør det meget problematisk at analysere SQL-instruktioner og analysen af forespørgselsplanen fra udviklernes side DBA

Optimeringsmetoder

Lad os nu gå direkte til optimeringsmetoderne.

1) Yderligere indeksering

Det er bedst at overveje filtre på de primære tabeller for valg, idet hele forespørgslen ofte er bygget op omkring en eller to primære tabeller (ansøgninger-mennesker-operationer) og med et standard sæt betingelser (IsClosed, Canceled, Enabled, Status). Det er vigtigt at oprette de relevante indekser for de identificerede vælger.

Denne løsning giver mening, når valget af disse felter væsentligt begrænser det samlede antal returnerede resultater.

For eksempel har vi 500.000 ansøgninger. Dog er der kun 2.000 aktive ansøgninger. Et korrekt designet indeks vil fjerne behovet for INDEX SCAN at søge i den store tabel og muliggøre hurtig datavalidering gennem et ikke-klusteret indeks.

Manglen på indekser kan også identificeres gennem forespørgselsplananalyse eller indsamling af statistik fra systemvisninger MS SQL Server:

Alle data i visningerne indeholder oplysninger om de manglende indekser, bortset fra rumlige indekser.

Dog er indekser og caching ofte metoder til at tackle konsekvenserne af dårligt skrevne LINQ-forespørgsler og SQL-forespørgsler.

Som hård livserfaring viser, er implementering af forretningsfunktioner til bestemte deadlines ofte vigtig for virksomheder. Derfor bliver tunge forespørgsler ofte overført til baggrund med caching.

Delvist er dette berettiget, da brugeren ikke altid har brug for de seneste data, og der opnås et acceptabelt responset niveau i brugergrænsefladen.

Denne tilgang gør det muligt at imødekomme forretningsforespørgsler, men reducerer til syvende og sidst effektiviteten af informationssystemet ved blot at udsætte løsning af problemer.

Det er også vigtigt at huske, at forslagene MS SQL til optimering kan være forkerte, især under følgende betingelser:

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

2) Sammenlægning af attributter til en ny attribut

Nogle gange kan visse felter fra én tabel, som danner grupper af betingelser, erstattes ved at indføre ét nyt felt.

Dette er især relevant for statusfelter, som typisk er enten bit eller heltal.

Eksempel:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 erstatte Status = 1.

Her introduceres et heltalsattribut, Status, der sikres ved udfyldning af disse statuser i tabellen. Dette nye attribut indgås derefter i indekseringen.

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

3) Materialisering af visningen

Desværre, i LINQ-forespørgsler kan man ikke bruge midlertidige tabeller, CTE og tabelvariabler direkte.

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

Gruppen af betingelser (fra eksemplet ovenfor) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (eller et sæt andre lignende betingelser) bliver en god mulighed for at bruge dem i en indekseret visning, ved at cache et lille udsnit af data fra en stor mængde.

Men der er en række begrænsninger ved materialisering af visningen:

brug af underforespørgsler, forespørgsler EXISTS skal erstattes med brugen af JOIN
man kan ikke bruge forespørgsler UNION, UNION ALL, EXCEPTION, INTERSECT
man kan ikke bruge tabel hints og forespørgsler OPTION
der er ingen mulighed for at arbejde med løkker
det er umuligt at udtrække 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, når den faktisk er indekseret.

Men når man kalder visningen, kan disse indekser muligvis ikke blive brugt, og for at bruge dem eksplicit er det nødvendigt at angive WITH (NOEXPAND).

Since there are no dots in LINQ-forespørgsler man kan ikke definere tabel hints, så man må lave en anden visning - en "wrapper" af følgende art:

CREATE VIEW VIEW_NAME AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Brug af tabel funktioner

Ofte i LINQ-forespørgsler stor mængde af underforespørgsler eller blokke, der bruger visninger med kompleks struktur, danner en slutforespørgsel med meget kompleks og ikke optimal udførelsesstruktur.

De primære fordele ved at bruge tabel funktioner i LINQ-forespørgsler:

Muligheden, ligesom i tilfælde med visninger, at bruge og angive som objekt, men man kan overføre et sæt indgangsparametre:
FROM FUNCTION(@param1, @param2 …)
man kan opnå fleksibel datavalgt
I tilfælde af brug af tabel funktionen er der ikke så strenge begrænsninger som i tilfælde med indekserede visninger, beskrevet ovenfor:
1. Tabel hints:
  gennem LINQ man kan ikke angive hvilke indekser der skal bruges og definere niveauet for dataintegration ved forespørgslen.
  Men i funktionen er disse muligheder til stede.
  Med funktionen kan man opnå en relativt konstant udførelsesplan for forespørgsel, hvor reglerne for brug af indekser og niveauer for dataintegration er defineret.
2. Brugen af funktionen tillader, i forhold til indekserede visninger, at opnå:
  - kompleks logik til datavalidering (inklusive brug af løkker)
  - datavalidering fra mange forskellige tabeller
  - brug af UNION og EXISTS
Tilbud OPTION meget nyttigt, når vi skal sikre parallelismehåndtering OPTION(MAXDOP N), rækkefølgen af forespørgselsplanen. For eksempel:
- man kan angive tvangsgenoprettelse af forespørgselsplanen OPTION (RECOMPILE)
- man kan angive nødvendigheden af at sikre, at forespørgselsplanen tvinges til at bruge rækkefølgen af sammenslutningen angivet i forespørgslen OPTION (FORCE ORDER)
Mere detaljeret om OPTION beskrevet her.
Brug af den smaleste og mest nødvendige dataslice:
Der er ikke behov for at holde store datasæt i cache (som i tilfælde af indeksserede visninger), hvorfra man også skal filtrere dataene efter parameter.
For eksempel er der en tabel, der til filtre HVOR bruger tre felter (a, b, c).
Betegnelsesmæssigt for alle forespørgsler er der et konstant betingelse a = 0 og b = 0.
Men forespørgslen til feltet c mere variabel.
Lad os sige, at betingelsen a = 0 og b = 0 virkelig hjælper os med at begrænse det ønskede resultat til tusinde optegnelser, men betingelsen om med begrenser udvalget til hundrede optegnelser.
Her kan en tabelfunktion vise sig at være en mere fordelagtig mulighed.
Desuden er tabelfunktionen mere forudsigelig og konstant i udførelsestid.

Eksempler

Lad os overveje et eksempel på implementering i databasen Questions.

Der er en forespørgsel, SELECT, som kombinerer flere tabeller og bruger én visning (OperativeQuestions), hvor medlemskabet kontrolleres via email (gennem EXISTS) til 'Aktive forespørgsler' ([OperativeQuestions]):

Forespørgsel 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 indeholder sammenføjninger af underforespørgsler og brug af sortering DISTINCT, som generelt er en ret ressourcekrævende operation.

Udtrækning fra OperativeQuestions med omkring ti tusinde poster.

Hovedproblemet med denne forespørgsel er, at der for posterne fra den eksterne forespørgsel udføres en intern underforespørgsel på visningen [OperativeQuestions], som skal begrænse den returnerede udtrækning for [Email] = @p__linq__0 (gennem EXISTS) til hundreder af poster.

Og det kan synes, at underforespørgslen skal beregne posterne én gang for [Email] = @p__linq__0, og derefter skal disse par hundrede poster sammenkædes efter Id med Questions, hvorefter forespørgslen vil være hurtig.

I virkeligheden sker der en sekventiel sammenkædning af alle tabeller: både kontrollen af, at Id fra Questions stemmer overens med Id fra OperativeQuestions, og filtreringen efter Email.

Forespørgslen arbejder i bund og grund med alle titusinder af poster fra OperativeQuestions, men der kræves kun de relevante data for Email.

Texten fra visningen OperativeQuestions:

Forespørgsel 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));

Den oprindelige mapping af visningen 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");
    }
}

Den oprindelige 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 specifikke tilfælde overvejes løsningen på dette problem uden infrastrukturelle ændringer, uden at indføre en separat tabel med de færdige resultater ("Aktive anmodninger"), for hvilken det ville være nødvendigt med en mekanisme til at fylde den med data og opretholde den opdateret.

Selvom det er en god løsning, er der også en anden mulighed for optimering af denne opgave.

Hovedmålet er at cache posterne efter [Email] = @p__linq__0 fra visningen OperativeQuestions.

Vi indfører den tabelbaserede funktion [dbo].[OperativeQuestionsUserMail] i databasen.

Ved at sende Email som inputparameter, får vi en tabel med værdier tilbage:

Spørgsmål 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

Her returneres en tabel med værdier med en forudbestemt datastruktur.

For at forespørgslerne til OperativeQuestionsUserMail skal være optimale og have optimale forespørgselsplaner, er der brug for en streng struktur, ikke RETURNS TABLE AS RETURN…

I dette tilfælde omdannes den søgte Forespørgsel 1 til Forespørgsel 4:

Spørgsmål 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 af visning og funktion i 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})");
}

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

Tiden for udførsel er faldet fra 200-800 ms til 2-20 ms, osv., det vil sige mange gange hurtigere.

Hvis man ser mere gennemsnitligt, så har vi fået 8 ms i stedet for 350 ms.

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

den samlede reduktion af læselasten,
en væsentlig reduktion af blokkeringer,
reduktion af gennemsnitlig blokeringstid til acceptable niveauer.

Output

Optimering og finjustering af databaseopslag MS SQL gennem LINQ er en opgave, der kan løses.

I dette arbejde er opmærksomhed og rækkefølge meget vigtige.

I begyndelsen af processen:

er det nødvendigt at kontrollere de data, som forespørgselens arbejder med (værdier, valgte datatyper),
at udføre korrekt indeksering af disse data,
at kontrollere korrektheden af forbindelsesbetingelserne mellem tabellerne.

I næste iteration af optimeringen identificeres:

forespørgselens grundlag og den primære filtreringsbetingelse for forespørgslen,
gentagne lignende blokke af forespørgslen, og krydsning af betingelser analyseres.
i SSMS eller et andet GUI til SQL Server optimeres selve SQL-forespørgsel (udtræk af mellemlagringsdata, opbygning af resultatforespørgslen ved hjælp af dette lager (kan være flere))
i det sidste trin, baseret på den resulterende SQL-forespørgsel, omstruktureres strukturen af LINQ-forespørgslen.

Resultatet er en LINQ-forespørgsel som strukturelt skal blive identisk med den identificerede optimale SQL-forespørgsel fra punkt 3.

Taknemmelighed

Stort tak til kollegerne jobgemws og alex_ozr fra virksomheden Fortis for hjælpen til forberedelsen af dette materiale.

Kilde: habr.com