🥇Metoder for å optimalisere LINQ-spørringer i C#.NET

Innledning

В denne artikkelen noen optimaliseringsmetoder ble vurdert LINQ-spørsmål.
Her presenterer vi også noen flere tilnærminger til kodeoptimalisering knyttet til LINQ-spørsmål.

Det er kjent at LINQ(Language-Integrated Query) er et enkelt og praktisk språk for å spørre etter en datakilde.

А LINQ til SQL er en teknologi for å få tilgang til data i et DBMS. Dette er et kraftig verktøy for å arbeide med data, der spørringer konstrueres gjennom et deklarativt språk, som deretter konverteres til SQL-spørringer plattform og sendt til databaseserveren for utførelse. I vårt tilfelle mener vi med DBMS MS SQL Server.

Imidlertid LINQ-spørsmål er ikke konvertert til optimalt skrevne SQL-spørringer, som en erfaren DBA kunne skrive med alle nyanser av optimalisering SQL-spørringer:

optimale tilkoblinger (BLI) og filtrering av resultatene (HVOR)
mange nyanser i bruk av sammenhenger og gruppeforhold
mange variasjoner i erstatningsforhold IN på Eksistererи IKKE I, <> på Eksisterer
mellomliggende caching av resultater via midlertidige tabeller, CTE, tabellvariabler
bruk av setning (ALTERNATIV) med instruksjoner og tabelltips MED (...)
å bruke indekserte visninger som et av virkemidlene for å bli kvitt overflødige dataavlesninger under valg

De viktigste ytelsesflaskehalsene til resultatet SQL-spørringer ved kompilering LINQ-spørsmål De er:

konsolidering av hele datavalgmekanismen i én forespørsel
duplisere identiske blokker med kode, noe som til slutt fører til flere unødvendige datalesninger
grupper av multi-komponent betingelser (logiske "og" og "eller") - OG и OR, ved å kombinere til komplekse forhold, fører til det faktum at optimalisereren, som har passende ikke-klyngede indekser for de nødvendige feltene, til slutt begynner å skanne mot den klyngede indeksen (INDEKSSKANN) etter grupper av forhold
dyp nesting av underspørringer gjør parsing svært problematisk SQL-setninger og analyse av spørringsplanen fra utviklere og DBA

Optimaliseringsmetoder

La oss nå gå direkte til optimaliseringsmetoder.

1) Ytterligere indeksering

Det er best å vurdere filtre på hovedutvalgstabellene, siden svært ofte hele spørringen er bygget rundt en eller to hovedtabeller (applikasjoner-mennesker-operasjoner) og med et standard sett med betingelser (IsClosed, Cancelled, Enabled, Status). Det er viktig å lage passende indekser for de identifiserte prøvene.

Denne løsningen er fornuftig når du velger disse feltene, og begrenser det returnerte settet til spørringen betydelig.

For eksempel har vi 500000 2000 søknader. Imidlertid er det bare XNUMX aktive applikasjoner. Da vil en riktig valgt indeks redde oss fra INDEKSSKANN på et stort bord og lar deg raskt velge data gjennom en ikke-klynget indeks.

Mangelen på indekser kan også identifiseres gjennom spørsmål om å analysere spørringsplaner eller samle inn systemvisningsstatistikk MS SQL Server:

Alle visningsdata inneholder informasjon om manglende indekser, med unntak av romlige indekser.

Imidlertid er indekser og caching ofte metoder for å bekjempe konsekvensene av dårlig skrevet LINQ-spørsmål и SQL-spørringer.

Som livets harde praksis viser, er det ofte viktig for en bedrift å implementere forretningsfunksjoner innen visse tidsfrister. Og derfor blir tunge forespørsler ofte overført til bakgrunnen med caching.

Dette er delvis berettiget, siden brukeren ikke alltid trenger de nyeste dataene, og det er et akseptabelt nivå av respons på brukergrensesnittet.

Denne tilnærmingen gjør det mulig å løse forretningsbehov, men reduserer til slutt ytelsen til informasjonssystemet ved ganske enkelt å forsinke løsninger på problemer.

Det er også verdt å huske at i prosessen med å søke etter de nødvendige indeksene for å legge til, forslag MS SQL optimering kan være feil, inkludert under følgende forhold:

hvis det allerede finnes indekser med et lignende sett med felt
hvis feltene i tabellen ikke kan indekseres på grunn av indekseringsbegrensninger (beskrevet mer detaljert her).

2) Slå sammen attributter til ett nytt attributt

Noen ganger kan noen felt fra én tabell, som fungerer som grunnlag for en gruppe betingelser, erstattes ved å introdusere ett nytt felt.

Dette gjelder spesielt for statusfelt, som vanligvis er enten bit eller heltall.

Eksempel:

IsClosed = 0 OG Avbrutt = 0 OG aktivert = 0 er erstattet av Status = 1.

Det er her integer Status-attributtet introduseres for å sikre at disse statusene er fylt ut i tabellen. Deretter blir dette nye attributtet indeksert.

Dette er en grunnleggende løsning på ytelsesproblemet, fordi vi får tilgang til data uten unødvendige beregninger.

3) Materialisering av utsikten

Dessverre, i LINQ-spørsmål Midlertidige tabeller, CTE-er og tabellvariabler kan ikke brukes direkte.

Det er imidlertid en annen måte å optimalisere for dette tilfellet - indekserte visninger.

Tilstandsgruppe (fra eksempelet ovenfor) IsClosed = 0 OG Avbrutt = 0 OG aktivert = 0 (eller et sett med andre lignende forhold) blir et godt alternativ for å bruke dem i en indeksert visning, og bufre en liten del av data fra et stort sett.

Men det er en rekke begrensninger når du materialiserer en visning:

bruk av underspørringer, klausuler Eksisterer bør erstattes ved bruk BLI
du kan ikke bruke setninger UNION, UNION ALLE, UNNTAK, KRYSSE
Du kan ikke bruke tabellhint og klausuler ALTERNATIV
ingen mulighet til å jobbe med sykluser
Det er umulig å vise data i én visning fra forskjellige tabeller

Det er viktig å huske at den virkelige fordelen med å bruke en indeksert visning bare kan oppnås ved å faktisk indeksere den.

Men når du kaller en visning, kan disse indeksene ikke brukes, og for å bruke dem eksplisitt, må du spesifisere MED(NOEXPAND).

Siden i LINQ-spørsmål Det er umulig å definere tabellhint, så du må lage en annen representasjon - en "wrapper" av følgende form:

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

4) Bruke tabellfunksjoner

Ofte i LINQ-spørsmål Store blokker med underspørringer eller blokker som bruker visninger med en kompleks struktur, danner en endelig spørring med en svært kompleks og suboptimal utførelsesstruktur.

Viktige fordeler ved å bruke tabellfunksjoner i LINQ-spørsmål:

Muligheten, som i tilfellet med visninger, til å bli brukt og spesifisert som et objekt, men du kan sende et sett med inndataparametere:
FRA FUNKSJON(@param1, @param2 ...)
Som et resultat kan fleksibel datasampling oppnås
Når det gjelder bruk av en tabellfunksjon, er det ingen så sterke begrensninger som i tilfellet med indekserte visninger beskrevet ovenfor:
1. Tabelltips:
  gjennom LINQ Du kan ikke spesifisere hvilke indekser som skal brukes og bestemme dataisolasjonsnivået når du spør.
  Men funksjonen har disse egenskapene.
  Med funksjonen kan du oppnå en ganske konstant utførelsesspørringsplan, der regler for arbeid med indekser og dataisolasjonsnivåer er definert
2. Ved å bruke funksjonen kan du, sammenlignet med indekserte visninger, oppnå:
  - kompleks datasamplingslogikk (selv ved bruk av loops)
  - hente data fra mange forskjellige tabeller
  - bruk av UNION и Eksisterer
Tilbud ALTERNATIV veldig nyttig når vi trenger å gi samtidighetskontroll OPTION(MAXDOP N), rekkefølgen på planen for utførelse av spørringen. For eksempel:
- du kan spesifisere en tvungen gjenoppretting av spørringsplanen ALTERNATIV (REKOMPILER)
- du kan angi om du vil tvinge spørringsplanen til å bruke sammenføyningsrekkefølgen som er angitt i spørringen OPSJON (TVANGSORDRE)
Flere detaljer om ALTERNATIV beskrevet her.
Bruke den smaleste og mest nødvendige datadelen:
Det er ikke nødvendig å lagre store datasett i cacher (som tilfellet er med indekserte visninger), hvorfra du fortsatt må filtrere dataene etter parameter.
For eksempel er det en tabell hvis filter HVOR tre felt brukes (a, b, c).

Konvensjonelt har alle forespørsler en konstant tilstand a = 0 og b = 0.

Men forespørselen om feltet c mer variabel.

La tilstanden a = 0 og b = 0 Det hjelper oss virkelig å begrense det nødvendige resulterende settet til tusenvis av poster, men betingelsen på с begrenser utvalget til hundre poster.

Her kan bordfunksjonen være et bedre alternativ.

Dessuten er en tabellfunksjon mer forutsigbar og konsekvent i utførelsestid.

Примеры

La oss se på et eksempelimplementering ved å bruke Spørsmålsdatabasen som et eksempel.

Det er en forespørsel VELG, som kombinerer flere tabeller og bruker én visning (OperativeQuestions), der tilknytningen sjekkes på e-post (via Eksisterer) til "Operative spørsmål":

Forespørsel 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 ganske kompleks struktur: den har underspørringssammenføyninger og bruker sortering DISTINCT, som generelt sett er en ganske ressurskrevende operasjon.

Et utvalg fra OperativeQuestions er rundt ti tusen poster.

Hovedproblemet med denne spørringen er at for postene fra den ytre spørringen utføres en intern underspørring på [OperativeQuestions]-visningen, som for [Email] = @p__linq__0 skal tillate oss å begrense utdatavalget (via Eksisterer) opptil hundrevis av poster.

Og det kan virke som at underspørringen skal beregne postene en gang med [Email] = @p__linq__0, og så skal disse par hundre postene kobles sammen med Id med spørsmål, og spørringen vil være rask.

Faktisk er det en sekvensiell tilkobling av alle tabeller: sjekke korrespondansen mellom Id-spørsmål med Id fra OperativeQuestions, og filtrering etter e-post.

Faktisk fungerer forespørselen med alle titusenvis av OperativeQuestions-poster, men bare dataene av interesse er nødvendig via e-post.

Operative Questions-visningstekst:

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

Innledende visningskartlegging 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");
    }
}

Innledende LINQ-spørring

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 spesielle tilfellet vurderer vi en løsning på dette problemet uten infrastrukturelle endringer, uten å introdusere en egen tabell med ferdige resultater ("Aktive spørringer"), som vil kreve en mekanisme for å fylle den med data og holde den oppdatert .

Selv om dette er en god løsning, er det et annet alternativ for å optimalisere dette problemet.

Hovedformålet er å bufre oppføringer med [E-post] = @p__linq__0 fra OperativeQuestions-visningen.

Introduser tabellfunksjonen [dbo].[OperativeQuestionsUserMail] i databasen.

Ved å sende e-post som en inngangsparameter, får vi tilbake en tabell med verdier:

Forespørsel 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 verditabell med en forhåndsdefinert datastruktur.

For at spørringer til OperativeQuestionsUserMail skal være optimale og ha optimale spørringsplaner, kreves det en streng struktur, og ikke RETURTABEL SOM RETUR...

I dette tilfellet blir den nødvendige spørringen 1 konvertert til spørring 4:

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

Kartlegging av visninger og funksjoner 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})");
}

Siste LINQ-spørring

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

Rekkefølgen på utførelsestiden har sunket fra 200-800 ms, til 2-20 ms, osv., dvs. titalls ganger raskere.

Hvis vi tar det mer gjennomsnittlig, så fikk vi i stedet for 350 ms 8 ms.

Fra de åpenbare fordelene får vi også:

generell reduksjon i lesebelastning,
betydelig reduksjon i sannsynligheten for blokkering
redusere den gjennomsnittlige blokkeringstiden til akseptable verdier

Utgang

Optimalisering og finjustering av databaseanrop MS SQL gjennom LINQ er et problem som kan løses.

Oppmerksomhet og konsistens er svært viktig i dette arbeidet.

I begynnelsen av prosessen:

det er nødvendig å kontrollere dataene som forespørselen fungerer med (verdier, utvalgte datatyper)
utføre riktig indeksering av disse dataene
kontroller riktigheten av sammenføyningsbetingelsene mellom tabellene

Den neste optimaliseringsiterasjonen avslører:

grunnlaget for forespørselen og definerer hovedforespørselsfilteret
gjenta lignende spørringsblokker og analysere skjæringspunktet mellom forhold
i SSMS eller annen GUI for SQL Server optimerer seg selv SQL-spørring (tildele en mellomliggende datalagring, bygge den resulterende spørringen ved å bruke denne lagringen (det kan være flere))
på det siste stadiet, ta utgangspunkt i det resulterende SQL-spørring, bygges strukturen på nytt LINQ-spørring

Resultatet LINQ-spørring bør bli identisk i struktur med det identifiserte optimale SQL-spørring fra punkt 3.

Anerkjennelser

Tusen takk til kolleger jobbgemws и alex_ozr fra selskapet Fortis for hjelp til å utarbeide dette materialet.

Kilde: www.habr.com

Metoder for å optimalisere LINQ-spørringer i C#.NET