🥇Metoder för att optimera LINQ-frågor i C#.NET

Inledning

В den här artikeln några optimeringsmetoder övervägdes LINQ-frågor.
Här presenterar vi också några fler tillvägagångssätt för kodoptimering relaterade till LINQ-frågor.

Det är känt att LINQ(Language-Integrated Query) är ett enkelt och bekvämt språk för att söka efter en datakälla.

А LINQ till SQL är en teknik för att komma åt data i ett DBMS. Detta är ett kraftfullt verktyg för att arbeta med data, där frågor konstrueras genom ett deklarativt språk, som sedan konverteras till SQL-frågor plattform och skickas till databasservern för exekvering. I vårt fall menar vi med DBMS MS SQL Server.

Emellertid LINQ-frågor inte omvandlas till optimalt skrivna SQL-frågor, som en erfaren DBA kunde skriva med alla nyanser av optimering SQL-frågor:

optimala anslutningar (JOIN) och filtrera resultaten (VAR)
många nyanser i att använda kopplingar och gruppförhållanden
många variationer i ersättande förhållanden IN på EXISTSи INTE I, <> på EXISTS
mellanliggande cachning av resultat via temporära tabeller, CTE, tabellvariabler
användning av mening (ALTERNATIV) med instruktioner och tabelltips MED STORT (...)
använda indexerade vyer som ett av sätten att bli av med redundanta dataavläsningar under urval

De viktigaste flaskhalsarna i resultatet SQL-frågor vid sammanställning LINQ-frågor De är:

konsolidering av hela dataurvalsmekanismen i en begäran
duplicera identiska kodblock, vilket i slutändan leder till flera onödiga dataläsningar
grupper av flerkomponentsvillkor (logiska "och" och "eller") - AND и OR, att kombinera till komplexa förhållanden, leder till det faktum att optimeraren, som har lämpliga icke-klustrade index för de nödvändiga fälten, slutligen börjar skanna mot det klustrade indexet (INDEXSKANNING) efter grupper av förhållanden
djup kapsling av underfrågor gör analysen mycket problematisk SQL-satser och analys av frågeplanen från utvecklare och DBA

Optimeringsmetoder

Låt oss nu gå direkt till optimeringsmetoder.

1) Ytterligare indexering

Det är bäst att överväga filter på huvudvalstabellerna, eftersom hela frågan ofta är uppbyggd kring en eller två huvudtabeller (applikationer-människor-operationer) och med en standarduppsättning villkor (IsClosed, Canceled, Enabled, Status). Det är viktigt att skapa lämpliga index för de identifierade proverna.

Den här lösningen är vettig när du väljer dessa fält begränsar den returnerade uppsättningen avsevärt till frågan.

Vi har till exempel 500000 2000 ansökningar. Det finns dock bara XNUMX aktiva applikationer. Då kommer ett korrekt valt index att rädda oss från INDEXSKANNING på ett stort bord och gör att du snabbt kan välja data genom ett icke-klustrat index.

Bristen på index kan också identifieras genom uppmaningar om att analysera frågeplaner eller samla in systemvystatistik MS SQL Server:

All vydata innehåller information om saknade index, med undantag för rumsliga index.

Men index och cachning är ofta metoder för att bekämpa konsekvenserna av dåligt skrivet LINQ-frågor и SQL-frågor.

Som livets hårda praxis visar är det ofta viktigt för ett företag att implementera affärsfunktioner inom vissa deadlines. Och därför överförs ofta tunga förfrågningar till bakgrunden med cachning.

Detta är delvis motiverat, eftersom användaren inte alltid behöver den senaste informationen och användargränssnittet har en acceptabel nivå av lyhördhet.

Detta tillvägagångssätt gör det möjligt att lösa affärsbehov, men i slutändan minskar informationssystemets prestanda genom att helt enkelt försena lösningar på problem.

Det är också värt att komma ihåg att i processen att söka efter de nödvändiga indexen att lägga till, förslag MSSQL optimering kan vara felaktig, inklusive under följande förhållanden:

om det redan finns index med en liknande uppsättning fält
om fälten i tabellen inte kan indexeras på grund av indexeringsbegränsningar (beskrivs mer i detalj här).

2) Slå ihop attribut till ett nytt attribut

Ibland kan vissa fält från en tabell, som fungerar som grund för en grupp av villkor, ersättas med att införa ett nytt fält.

Detta gäller särskilt för statusfält, som vanligtvis är antingen bit- eller heltalstyp.

Exempel:

IsClosed = 0 OCH Avbruten = 0 OCH Aktiverad = 0 ersätts med Status = 1.

Det är här som integer Status-attributet introduceras för att säkerställa att dessa statusar fylls i i tabellen. Därefter indexeras detta nya attribut.

Detta är en grundläggande lösning på prestandaproblemet, eftersom vi får tillgång till data utan onödiga beräkningar.

3) Materialisering av utsikten

Tyvärr, i LINQ-frågor Temporära tabeller, CTE:er och tabellvariabler kan inte användas direkt.

Det finns dock ett annat sätt att optimera för detta fall - indexerade vyer.

Tillståndsgrupp (från exemplet ovan) IsClosed = 0 OCH Avbruten = 0 OCH Aktiverad = 0 (eller en uppsättning andra liknande villkor) blir ett bra alternativ för att använda dem i en indexerad vy och cachelagrar en liten del av data från en stor uppsättning.

Men det finns ett antal begränsningar när en vy förverkligas:

användning av delfrågor, klausuler EXISTS bör ersättas med användning JOIN
du kan inte använda meningar UNION, UNION ALLA, UNDANTAG, KORSAS
Du kan inte använda tabelltips och klausuler ALTERNATIV
ingen möjlighet att arbeta med cykler
Det är omöjligt att visa data i en vy från olika tabeller

Det är viktigt att komma ihåg att den verkliga fördelen med att använda en indexerad vy bara kan uppnås genom att faktiskt indexera den.

Men när du anropar en vy får dessa index inte användas, och för att använda dem explicit måste du ange MED(NOEXPAND).

Sedan i LINQ-frågor Det är omöjligt att definiera tabelltips, så du måste skapa en annan representation - ett "omslag" av följande form:

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

4) Använda tabellfunktioner

Ofta i LINQ-frågor Stora block av underfrågor eller block som använder vyer med en komplex struktur bildar en slutlig fråga med en mycket komplex och suboptimal exekveringsstruktur.

Viktiga fördelar med att använda tabellfunktioner i LINQ-frågor:

Möjligheten, som i fallet med vyer, att användas och specificeras som ett objekt, men du kan skicka en uppsättning indataparametrar:
FRÅN FUNKTION(@param1, @param2 ...)
Som ett resultat kan flexibel datasampling uppnås
Vid användning av en tabellfunktion finns det inga så starka begränsningar som i fallet med indexerade vyer som beskrivs ovan:
1. Tabelltips:
  genom LINQ Du kan inte ange vilka index som ska användas och bestämma dataisoleringsnivån när du frågar.
  Men funktionen har dessa möjligheter.
  Med funktionen kan du uppnå en ganska konstant exekveringsfrågeplan, där regler för att arbeta med index och dataisoleringsnivåer definieras
2. Genom att använda funktionen kan man, i jämförelse med indexerade vyer, få:
  - komplex datasamplingslogik (även med loopar)
  - hämta data från många olika tabeller
  - användningen av UNION и EXISTS
erbjudande ALTERNATIV mycket användbart när vi behöver tillhandahålla samtidighetskontroll ALTERNATIV(MAXDOP N), ordningen för exekveringsplanen för frågan. Till exempel:
- du kan ange ett påtvingat återskapande av frågeplanen ALTERNATIV (OMKOMPILERA)
- du kan ange om du vill tvinga frågeplanen att använda den sammanfogningsordning som anges i frågan ALTERNATIV (TVÅNGSORDNING)
Mer information om ALTERNATIV beskrivs här.
Använd den smalaste och mest nödvändiga datadelen:
Det finns inget behov av att lagra stora datamängder i cacher (som är fallet med indexerade vyer), från vilka du fortfarande behöver filtrera data efter parameter.
Till exempel finns det en tabell vars filter VAR tre fält används (a, b, c).

Konventionellt har alla förfrågningar ett konstant tillstånd a = 0 och b = 0.

Dock begäran om fältet c mer varierande.

Låt villkoret a = 0 och b = 0 Det hjälper oss verkligen att begränsa den erforderliga uppsättningen till tusentals poster, men villkoret på с begränsar urvalet till hundra poster.

Här kan tabellfunktionen vara ett bättre alternativ.

En tabellfunktion är också mer förutsägbar och konsekvent i exekveringstid.

Примеры

Låt oss titta på en exempelimplementering med hjälp av Questions-databasen som exempel.

Det finns en begäran VÄLJA, som kombinerar flera tabeller och använder en vy (OperativeQuestions), där anknytningen kontrolleras via e-post (via EXISTS) till "Operativa frågor":

Begäran 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])
));

Vyn har en ganska komplex struktur: den har subquery-kopplingar och använder sortering TYDLIG, vilket i allmänhet är en ganska resurskrävande verksamhet.

Ett prov från OperativeQuestions är cirka tio tusen poster.

Huvudproblemet med denna fråga är att för posterna från den yttre frågan exekveras en intern underfråga i vyn [OperativeQuestions], vilket bör för [Email] = @p__linq__0 tillåta oss att begränsa valet av utdata (via EXISTS) upp till hundratals poster.

Och det kan tyckas som att underfrågan ska beräkna posterna en gång med [E-post] = @p__linq__0, och sedan bör dessa par hundra poster kopplas ihop med Id med frågor, och frågan kommer att vara snabb.

Faktum är att det finns en sekventiell anslutning av alla tabeller: kontrollera överensstämmelsen mellan Id-frågor och Id från OperativeQuestions, och filtrering med e-post.

Faktum är att begäran fungerar med alla tiotusentals OperativeQuestions-poster, men endast data av intresse behövs via e-post.

Operativa frågor vytext:

Begäran 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));

Initial vymappning 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");
    }
}

Initial 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 det här specifika fallet överväger vi en lösning på detta problem utan infrastrukturella förändringar, utan att införa en separat tabell med färdiga resultat ("Aktiva frågor"), vilket skulle kräva en mekanism för att fylla den med data och hålla den uppdaterad .

Även om detta är en bra lösning, finns det ett annat alternativ för att optimera detta problem.

Huvudsyftet är att cachelagra poster med [E-post] = @p__linq__0 från vyn OperativeQuestions.

Introducera tabellfunktionen [dbo].[OperativeQuestionsUserMail] i databasen.

Genom att skicka e-post som en indataparameter får vi tillbaka en värdetabell:

Begäran 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

Detta returnerar en värdetabell med en fördefinierad datastruktur.

För att frågor till OperativeQuestionsUserMail ska vara optimala och ha optimala frågeplaner krävs en strikt struktur, och inte RETUR TABELL SOM RETUR.

I det här fallet konverteras den nödvändiga fråga 1 till fråga 4:

Begäran 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]);

Kartläggning av vyer och 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})");
}

Sista LINQ-frågan

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

Ordningen på utförandetiden har sjunkit från 200-800 ms, till 2-20 ms, etc., dvs tiotals gånger snabbare.

Om vi tar det mer genomsnittligt, så fick vi istället för 350 ms 8 ms.

Från de uppenbara fördelarna får vi också:

allmän minskning av läsbelastningen,
betydande minskning av sannolikheten för blockering
minska den genomsnittliga blockeringstiden till acceptabla värden

Utgång

Optimering och finjustering av databasanrop MSSQL genom LINQ är ett problem som kan lösas.

Uppmärksamhet och konsekvens är mycket viktigt i detta arbete.

I början av processen:

det är nödvändigt att kontrollera de uppgifter som förfrågan fungerar med (värden, valda datatyper)
utföra korrekt indexering av dessa data
kontrollera att sammanfogningsvillkoren mellan tabellerna är korrekta

Nästa optimeringsiteration avslöjar:

grunden för begäran och definierar huvudförfrågningsfiltret
upprepa liknande frågeblock och analysera skärningspunkten mellan villkor
i SSMS eller annat GUI för SQL Server optimerar sig själv SQL-fråga (tilldela en mellanliggande datalagring, bygga den resulterande frågan med denna lagring (det kan finnas flera))
i det sista skedet, med utgångspunkt i resultatet SQL-fråga, strukturen håller på att byggas om LINQ-fråga

Det resulterande LINQ-fråga bör bli identisk i struktur med det identifierade optimala SQL-fråga från punkt 3.

Kvitteringar

Stort tack till kollegor jobgemws и alex_ozr från företaget Fortis för hjälp med att förbereda detta material.

Källa: will.com

Metoder för att optimera LINQ-frågor i C#.NET