Optimaliseringsmetoder for LINQ-spørsmål i C#.NET

Innledning

I denne artikkelen noen metoder for optimalisering ble vurdert LINQ-spørsmål.
Her vil vi også presentere noen tilnærminger for å optimalisere koden relatert til LINQ-spørsmålene.

Det er kjent at LINQ(Language-Integrated Query) er et enkelt og praktisk spørrespråk for datakilder.

A LINQ to SQL er en datatilgangsteknologi i RDBMS. Dette er et kraftig verktøy for databehandling, der forespørslene bygges gjennom et deklarativt språk, som deretter blir transformert til SQL-spørsmål plattform og sendt til databasen for behandling. I vårt tilfelle vil vi forstå RDBMS som MS SQL Server.

Imidlertid, LINQ-spørsmål blir ikke konvertert til optimalt skrevne SQL-spørsmål, som en erfaren DBA ville skrevet med alle optimaliseringsnyansene for SQL-spørsmål:

1. optimale koblinger (JOIN) og filtrering av resultater (Den maksimale/minimale hurtig oppfriskningen etter sletting eller blandede DML har ikke samme oppførsel som innsetting-tilfelle. Den sletter og beregner på nytt maks/min verdiene for de berørte gruppene. Du må være oppmerksom på dens ytelseseffekt.)
2. mange nyanser i bruken av koblinger og gruppere betingelser
3. mange variasjoner i å erstatte betingelser IN En funksjon har blitt EXISTSog NOT IN, til EXISTS
4. midlertidig caching av resultater gjennom midlertidige tabeller, CTE, tabellvariabler
5. bruk av setning (OPTION) med instruksjoner og tabellhint WITH (…)
6. bruk av indekserte visninger som et av hjelpemidlene for å bli kvitt overflødige datalesninger ved spørringer

De viktigste flaskhalser i ytelsen for de resulterende SQL-spørsmål ved kompileringsprosessen LINQ-spørsmål er:

1. konsolidering av hele datavalgmekanismen i en spørring
2. duplisering av identiske kodeblokker, noe som til slutt fører til unødvendige datalesninger
3. grupper av sammensatte betingelser (logiske «og» og «eller») — OG og Eller, som kombineres i komplekse betingelser, fører til at optimalisatoren, med passende ikke-klyngede indekser for de nødvendige feltene, til slutt begynner å skanne gjennom klyngeindeksen (INDEX SCAN) etter grupper av betingelser
4. dyp nesting av underforespørsel gjør analysen av SQL-setningene og analysen av forespørselplaner fra utviklernes og DBA

Optimaliseringsmetoder

La oss nå gå direkte videre til optimaliseringsmetodene.

1) Ytterligere indeksering

Det er best å se på filtre på de viktigste utvalgstabellene, ettersom hele spørringen ofte bygges rundt en eller to hovedtabeller (applikasjoner-personer-operasjoner) og med et standard sett av betingelser (IsClosed, Canceled, Enabled, Status). Det er viktig å opprette passende indekser for de valgte utvalgene.

Denne løsningen gir mening når utvalget etter disse feltene betydelig begrenser det returnerte mangfoldet av forespørselen.

For eksempel har vi 500000 søknader. Men det er kun 2000 aktive søknader. Da vil en riktig valgt indeks spare oss for INDEX SCAN motorveien og tillate oss å raskt hente data gjennom en ikke-klaseindeks.

Mangelen på indekser kan også identifiseres gjennom analysering av spørringsplaner eller innsamling av statistikk fra systemvisninger MS SQL Server:

1. sys.dm_db_missing_index_groups
2. sys.dm_db_missing_index_group_stats
3. sys.dm_db_missing_index_details

Alle dataene i visningene inneholder informasjon om manglende indekser, unntatt for romlige indekser.

Men indekser og caching er ofte metoder for å takle konsekvensene av dårlig skrivne LINQ-spørsmål og SQL-spørsmål.

Som hard praksis viser, er det ofte viktig for virksomheten å implementere forretningsfunksjoner innen bestemte frister. Og derfor blir ofte tunge forespørseler satt til bakgrunnsmodus med caching.

Delvis er dette berettiget, ettersom brukeren ikke alltid trenger de nyeste dataene, og det oppstår et akseptabelt nivå av respons fra brukergrensesnittet.

Denne tilnærmingen lar oss møte forretningsforespørselene, men reduserer til slutt funksjonaliteten til informasjonssystemet ved bare å utsette løsningene på problemene.

Det er også viktig å huske at i prosessen med å finne nødvendige nye indekser å legge til, kan forslagene MS SQL for optimalisering være uriktige, spesielt under følgende forhold:

1. hvis det allerede finnes indekser med et lignende sett av felt
2. hvis felt i tabellen ikke kan indekseres på grunn av indeksbegrensninger (mer detaljert om dette er beskrevet her).

2) Sammenføring av attributter til et nytt attributt

Noen ganger kan enkelte felt fra én tabell, som er gruppert, erstattes med innføringen av et nytt felt.

Dette er spesielt aktuelt for tilstandsfelt, som vanligvis er enten binære eller heltall.

Eksempel:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 erstattes med Status = 1.

Her skrivast ein heilag heiltal-attributt om Status, som vert sikra ved å fylle ut desse statusane i tabellen. Deretter vert det gjennomført indeksering av dette nye attributtet.

Dette er ei grunnleggjande løysing på ytelsesproblemet, sidan vi henter data utan unødvendige berekningar.

3) Materialisering av visninga

Dessverre kan i LINQ-spørsmål ein ikkje bruke midlertidige tabeller, CTE eller tabellvariablar direkte.

Men det finst ei anna måte å optimalisere i dette tilfellet — indekserte visningar.

Gruppene av vilkår (frå dømet ovanfor) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (eller eit sett med andre liknande vilkår) blir eit godt alternativ for å bruke dei i den indekserte visninga, og lagre eit lite utsnitt av data frå eit stort mangfald.

Men det er ein rekkje avgrensingar ved materialisering av visninga:

1. bruken av underspørsmål, forslag EXISTS må verte erstatta med bruk JOIN
2. ein kan ikkje bruke forslag Den definerende spørringen må ha, UNION ALL, UNNTAK, INTERSECT
3. ein kan ikkje bruke tabellhint og forslag OPTION
4. det er ikkje mogleg å jobbe med løkker
5. det er ikkje mogleg å hente data i éin visning frå ulike tabeller

Det er viktig å hugse at den verkelege nytta med å bruke indekserte visningar kan oppnås faktisk berre ved indeksering av dei.

Men ved å kalle visninga kan desse indeksane kanskje ikkje bli brukt, og for å bruke dei eksplisitt må ein angi WITH (NOEXPAND).

Sidan ein kan ikkje definere tabellhint, må ein lage ei ny visning — «innpakning» av følgjande slag: i LINQ-spørsmål CREATE VIEW NAMN_presentation AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Bruk av tabellfunksjonar

Ofte i

store blokkane med underspørsmål eller blokker som bruker visningar med komplisert struktur, dannar ein endeleg forespørsel med ei svært kompleks og ikkje optimal utførelsesstruktur. i LINQ-spørsmål Dei viktigaste fordelane med å bruke tabellfunksjonar i

Moglegheita, akkurat som med visningar, å bruke og indikere som objekt, men ein kan overføre eit sett med inndata-parametrar: i LINQ-spørsmål:

1. FRÅ FUNKSJON(@param1, @param2 …)
   til slutt kan ein oppnå ei fleksibel dataval
   I tilfelle av å bruke tabellfunksjonen er det ikkje slike strenge avgrensingar som i tilfellet med indekserte visningar, som nevnt ovanfor:
2. Tabellhint:
   1. Tabellhint:
      gjennom LINQ Det er ikke mulig å spesifisere hvilke indekser som skal brukes og bestemme isolasjonsnivået for data under forespørselen.
      Men i funksjonen er disse mulighetene tilgjengelige.
      Med funksjonen kan man oppnå en ganske konstant forespørselplan, hvor reglene for arbeidet med indekser og nivåene for datainndeling er definert.
   2. Bruken av funksjonen gir, sammenlignet med indekserte visninger, mulighet for:
      • kompleks logikk for datauthenting (til og med med bruk av løkker)
      • uthenting av data fra mange forskjellige tabeller
      • bruk av Den definerende spørringen må ha og EXISTS

3. Forslag OPTION er veldig nyttig når vi må sørge for styring av parallellisme. OPTION(MAXDOP N), rekkefølgen på forespørselplanen. For eksempel:
   • man kan spesifisere tvungen rekreasjon av forespørselplanen. OPTION (RECOMPILE)
   • man kan spesifisere behovet for å sikre at forespørselplanen bruker rekkefølgen for sammenkoblingen som er angitt i forespørselen. OPTION (FORCE ORDER)

   Mer detaljert om OPTION beskrevet her.

4. Bruken av det smaleste og mest nødvendige dataskjermbildet:
   Det er ingen behov for å holde store datasett i hurtigbufferen (som i tilfelle med indekserte visninger), hvor man fortsatt må filtrere dataene etter parameter.
   For eksempel, det er en tabell, som for filteret Den maksimale/minimale hurtig oppfriskningen etter sletting eller blandede DML har ikke samme oppførsel som innsetting-tilfelle. Den sletter og beregner på nytt maks/min verdiene for de berørte gruppene. Du må være oppmerksom på dens ytelseseffekt. bruken av tre felt (a, b, c).

   Betingelsen for alle forespørslene er en konstant betingelse a = 0 og b = 0..

   Imidlertid er forespørselen til feltet med mer variert.

   La oss si at betingelsen a = 0 og b = 0. virkelig hjelper oss med å begrense det nødvendige oppnådde settet til tusen poster, men betingelsen for med innskrenker utvalget til hundre poster.

   Her kan en tabellfunksjon vise seg å være et bedre alternativ.

   Også er tabellfunksjonen mer forutsigbar og konstant i utførelsestid.

Eksempler

La oss se på et eksempel på implementering med databasen Questions.

Det er en forespørsel SELECT, som setter sammen flere tabeller og bruker en visning (OperativeQuestions), der det sjekkes gjennom email om tilhørighet (gjennom EXISTS) til "Aktive forespørselene"([OperativeQuestions]):

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 komplisert struktur: den har underforespørsel-tilkoblinger og bruk av sortering DISTINCT, som generelt er en ganske ressurskrevende operasjon.

Utvalg fra OperativeQuestions inkluderer omtrent ti tusen oppføringer.

Hovedproblemet med denne forespørselen er at for postene fra den ytre forespørselen utføres en intern underforespørsel på visningen [OperativeQuestions], som for [Email] = @p__linq__0 skal begrense utvalget (gjennom EXISTS) til hundrevis av oppføringer.

Og det kan virke som om underforespørselen bare skal beregne postene én gang for [Email] = @p__linq__0, og deretter skal disse par hundre postene kobles sammen etter Id med Questions, og forespørselen vil være rask.

I virkeligheten skjer det imidlertid en sekvensiell sammenkobling av alle tabellene: både kontroll av samsvar mellom Id i Questions og Id fra OperativeQuestions, samt filtrering etter Email.

I bunn og grunn jobber forespørselen med alle titusener av oppføringer i OperativeQuestions, mens vi bare trenger de relevante dataene etter Email.

Teksten i visningen OperativeQuestions:

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

Den opprinnelige mappingen av visningen i DbContext (EF Core 2)

offentlig klasse QuestionsDbContext : DbContext
{
    //...
    offentlig DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    beskyttet override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}

Ursprunglig LINQ-fråga

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 detta specifika fall övervägs lösningen av detta problem utan infrastrukturella förändringar, utan att införa en separat tabell med färdiga resultat ("Aktiva frågor"), för vilken en mekanism skulle vara nödvändig för att fylla den med data och hålla den uppdaterad.

Även om det här är en bra lösning finns det ett annat alternativ för att optimera denna uppgift.

Huvudsyftet är att cachelagra poster med [Email] = @p__linq__0 från vyen OperativeQuestions.

Vi introducerar tabellfunktionen [dbo].[OperativeQuestionsUserMail] i databasen.

Genom att skicka in som parameter Email får vi tillbaka en värdetabell:

Fråga 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

Här returneras en värdetabell med en fördefinierad databasstruktur.

För att förfrågningar till OperativeQuestionsUserMail ska vara optimala och ha optimala frågeplaner krävs en strikt struktur, och inte RETURNS TABLE AS RETURN…

I det här fallet omvandlas den sökta Fråga 1 till Fråga 4:

Fråga 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]);

Mappning av vy och 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ørselen

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

Kjørets tid har blitt redusert fra 200-800 ms til 2-20 ms, og så videre, det vil si flere tiår raskere.

Ser man mer i snitt, fikk vi i stedet for 350 ms, 8 ms.

Noen åpenbare fordeler er også:

1. generell reduksjon i lese belastning,
2. betydelig reduksjon i sannsynligheten for låsing
3. reduksjon av gjennomsnittlig låsetid til akseptable verdier

Utdata

Optimalisering og finjustering av forespørslene til databasen MS SQL gjennom LINQ er en oppgave som kan løses.

I dette arbeidet er oppmerksomhet og konsistens veldig viktig.

I starten av prosessen:

1. må dataene som forespørselen arbeider med sjekkes (verdier, valgte datatyper)
2. gjennomføre riktig indeksering av disse dataene
3. sjekke korrektheten av join-betingelsene mellom tabellene

I neste iterasjon av optimaliseringen identifiseres:

1. grunnlaget for forespørselen og den viktigste filtreringen blir bestemt
2. gjentakende like blokker av forespørselen og kryssanalysere betingelsene
3. i SSMS eller annen GUI for SQL Server optimaliseres selve SQL-forespørselen (opprettelse av mellomlagringslagring, bygd opp resultatforespørselen ved hjelp av dette lageret (kan være flere))
4. I det siste steget, basert på resultatet SQL-forespørselen, omstrukturert struktur av LINQ-forespørselen

Som et resultat skal den resulterende LINQ-forespørselen bli strukturmessig identisk med den optimale SQL-forespørselen fra punkt 3.

Takk

Stor takk til kollegene jobgemws og alex_ozr fra selskapet Fortis for hjelpen med forberedelsen av dette materialet.

Kilde: habr.com