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

Introduktion

I denna artikel Några metoder för optimering övervägdes LINQ-frågor.
Här kommer vi att presentera ytterligare några metoder för att optimera koden relaterade till LINQ-frågor.

Det är känt att LINQ(Language-Integrated Query) — är ett enkelt och praktiskt frågespråk för datakällor.

A LINQ till SQL är en databasåtkomstteknik. Det är ett kraftfullt verktyg för databehandling, där deklarativt språk används för att konstruera frågor som sedan omvandlas till SQL-frågor plattform och skickas till databasservern för utförande. I vårt fall kommer vi att förstå MS SQL Server.

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

optimala föreningar (JOIN) och filtrering av resultat (DÄR)
många nyanser i användningen av föreningar och gruppvillkor
många variationer i ersättning av villkor IN till EXISTSoch NOT IN, till EXISTS
intermittent cache av resultat genom temporära tabeller, CTE, tabellvariabler
användning av konstruktionen (OPTION) med anvisningar och tabellhintar WITH (…)
användning av indexerade vyer som ett sätt att bli av med överflödig dataavläsning vid val

De främsta flaskhalsarna för prestanda som uppstår SQL-frågor vid kompilering LINQ-frågor är:

konsolidering av hela mekanismen för dataselektion i en fråga
duplicering av identiska kodblock, vilket så småningom leder till flera onödiga dataläsningar
grupper av sammansatta villkor (logiska 'och' och 'eller') — OCH och ELLER, som förenas i komplexa villkor, leder till att optimeraren, med lämpliga icke-klusterade index på nödvändiga fält, trots allt börjar göra en skanning av det klusterade indexet (INDEX SCAN) baserat på villkorsgrupper
djup inbäddning av underfrågor gör tolkningen av SQL-instruktioner och analysen av frågeplanen från utvecklarnas och DBA

Optimeringsmetoder

Nu går vi direkt över till optimeringsmetoder.

1) Ytterligare indexering

Det är bäst att betrakta filter på de huvudtabeller som väljningar görs från, eftersom en fråga ofta byggs kring en eller två huvudtabeller (ansökningar-människor-operationer) med en standarduppsättning villkor (IsClosed, Canceled, Enabled, Status). Det är viktigt att skapa motsvarande index för de identifierade väljningarna.

Denna lösning är meningsfull när urvalet av dessa fält väsentligt begränsar mängden resultat från en fråga.

Till exempel har vi 500000 ansökningar. Men det finns endast 2000 aktiva ansökningar. Då kommer ett väl valt index att befria oss från INDEX SCAN genom den stora tabellen och möjliggöra snabb hämtning av data genom ett icke-klustrat index.

Bristen på index kan också identifieras genom tips i analyser av frågeplaner eller genom att samla statistik från systemvyer MS SQL Server:

Alla data från vyerna innehåller information om saknade index, med undantag för rumsliga index.

Dock är index och cache ofta metoder för att hantera konsekvenserna av dåligt skrivna LINQ-frågor och SQL-frågor.

Som hård praktik visar är det ofta viktigt för företag att implementera affärsfunktioner inom vissa tidsramar. Därför konverteras tunga frågor ofta till bakgrundsläge med caching.

Delvis är detta motiverat, eftersom användaren inte alltid behöver de senaste data och den acceptabla nivån för användargränssnittets respons bibehålls.

Denna metod möjliggör hantering av affärsfrågor, men sänker i slutändan prestandan för informationssystemet, eftersom problem bara skjuts upp.

Det är också värt att komma ihåg att det i processen att hitta nödvändiga förslag för att lägga till nya index kan framställas MS SQL felaktiga optimeringsförslag, särskilt 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 (detta beskrivs mer i detalj här).

2) Sammanfoga attribut till ett nytt attribut

Ibland kan vissa fält från en tabell, där ett gruppvillkor förekommer, ersättas med ett nytt enskilt fält.

Detta är särskilt relevant för tillståndsfält som vanligtvis är antingen bitar eller heltal.

Exempel:

IsClosed = 0 OCH Canceled = 0 OCH Enabled = 0 ersätts med Status = 1.

Här anges en heltalsattribut som Status, vilket säkerställs genom att fylla i dessa statuser i tabellen. Sedan görs indexering av denna nya attribut.

Detta är en grundläggande lösning på prestandaproblemet, eftersom vi hämtar data utan onödiga beräkningar.

3) Materialisering av vyer

Tyvärr kan LINQ-frågor inte direkt använda temporära tabeller, CTE och tabellvariabler.

Men det finns ett annat sätt att optimera i detta fall — indexerade vyer.

Gruppen av villkor (från exemplet ovan) IsClosed = 0 OCH Canceled = 0 OCH Enabled = 0 (eller en uppsättning andra liknande villkor) blir ett bra alternativ att använda i en indexerad vy, vilket caching av en liten del av data från en stor mängd.

Men det finns ett antal begränsningar vid materialisering av vyer:

användning av underfrågor, satser EXISTS ska ersättas med användning av JOIN
kan inte använda satser UNION, UNION ALL, EXCEPTION, INTERSECT
kan inte använda tabellhintar och satser OPTION
det finns ingen möjlighet att arbeta med loopar
det är omöjligt att exportera data i en vy från olika tabeller

Det är viktigt att komma ihåg att den verkliga nyttan av att använda indexerade vyer faktiskt bara kan uppnås när de indexeras.

Men vid anrop av vyn kanske dessa index inte används, och för att uttryckligen använda dem måste man ange WITH (NOEXPAND).

Eftersom det i LINQ-frågor inte går att definiera tabellhintar, måste man skapa en annan vy — en "wrapper" av följande typ:

CREATE VIEW NAMN_PÅ_VY AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Användning av tabellfunktioner

Ofta i LINQ-frågor skapar stora block av underfrågor eller block som använder vyer med komplex struktur ett slutligt frågesvar med en mycket komplex och icke-optimal exekveringsstruktur.

Huvudfördelarna med att använda tabellfunktioner i LINQ-frågor:

Möjligheten, precis som med vyer, att ange och använda som objekt, men man kan också skicka en uppsättning inparametrar:
FROM FUNCTION(@param1, @param2 …)
i slutändan kan man uppnå flexibel dataval
Vid användning av tabellfunktioner finns det inga så strikta begränsningar som vid indexerade vyer, som beskrivits ovan:
1. Tabellhintar:
  via LINQ Det går inte att specificera vilka index som ska användas och bestämma dataskyddsnivån vid en förfrågan.
  Men i funktionen finns dessa möjligheter.
  Med funktionen kan man uppnå en tillräckligt stabil exekveringsplan där reglerna för arbete med index och dataskyddsnivåer är definierade.
2. Användningen av funktionen gör det möjligt, jämfört med indexerade vyer, att få:
  - komplex logik för dataval (inklusive användning av cykler)
  - dataval från flera olika tabeller.
  - användningen av UNION och EXISTS
Förslaget OPTION är mycket användbart när vi behöver säkerställa hantering av parallellism. OPTION(MAXDOP N), ordningen för exekveringsplanen. Till exempel:
- man kan ange att exekveringsplanen ska tvingas återskapas. OPTION (RECOMPILE)
- man kan ange behovet av att säkerställa tvingad användning av den angivna join-ordningen i förfrågan. OPTION (FORCE ORDER)
Mer detaljerat om OPTION beskrivet här.
Användningen av det smalaste och mest nödvändiga urvalet av data:
Det är inte nödvändigt att hålla stora datamängder i cache (som i fallet med indexerade vyer), från vilka man fortfarande behöver filtrera data baserat på parameter.
Till exempel finns det en tabell där filtreringen DÄR använder tre fält. (a, b, c).
Villkoret gäller för alla förfrågningar. a = 0 och b = 0..
Men förfrågan till fältet c är mer variabel.
Låt villkoret a = 0 och b = 0. verkligen hjälpa oss att begränsa den uppnådda uppsättningen till tusen poster, men villkoret angående med begränsar urvalet till hundra poster.
Här kan en tabellfunktion vara ett mer fördelaktigt alternativ.
Dessutom är tabellfunktionen mer förutsägbar och stabil när det gäller exekveringstid.

Exempel

Låt oss överväga ett exempel på implementering i databasen Questions.

Det finns en förfrågan VÄLJ, som förenar flera tabeller och använder en vy (OperativeQuestions), där man kontrollerar via e-post tillhörigheten (genom EXISTS) till "Aktiva förfrågningar" ([OperativeQuestions]):

Förfrågan № 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])
));

Representation has a fairly complex structure: it includes subquery joins and sorting usage. DISTINCT, which is generally a resource-intensive operation.

The selection from OperativeQuestions amounts to about ten thousand records.

The main issue with this query is that for records from the outer query, an inner subquery on the view [OperativeQuestions] is executed, which should limit the output selection for [Email] = @p__linq__0 (through EXISTS) to hundreds of records.

It may seem that the subquery should calculate the records for [Email] = @p__linq__0 once, and then these few hundred records should be joined by Id with Questions, making the query fast.

In reality, there is a sequential join of all tables: checking the matching Id of Questions with Id from OperativeQuestions, and filtering by Email.

Essentially, the query operates on all tens of thousands of records from OperativeQuestions, while only the relevant data by Email is needed.

The text of the view OperativeQuestions:

Query No. 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));

The original mapping of the view in DbContext (EF Core 2)

offentlig klass QuestionsDbContext : DbContext
{
    
    offentlig DbQuery OperativeQuestions { få; set; }
    
    skyddad override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().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) => ny
                {
                    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 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"), för vilken det skulle behövas en mekanism för att fylla den med data och hålla den aktuell.

Även om detta är en bra lösning, finns det ett annat sätt att optimera denna uppgift.

Huvudsyftet är att cachelagra poster baserat på [Email] = @p__linq__0 från vyer OperativeQuestions.

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

Genom att skicka Email som inparameter, får vi tillbaka en värdestabell:

Fråga nr 3


SKAPA FUNKTION [dbo].[OperativeQuestionsUserMail]
(
    @Email  nvarchar(4000)
)
RETURNS
@tbl TABLE
(
    [Id]           uniqueidentifier,
    [Email]      nvarchar(4000)
)
AS
BEGIN
        INSERT INTO @tbl ([Id], [Email])
        VÄLJ Id, @Email
        FRÅN [OperativeQuestions]  SOM [x] DÄR [x].[Email] = @Email;
     
    RETURN;
SLUT

Här returneras en värdestabell med en förutbestämd datastruktur.

För att förfrågningar till OperativeQuestionsUserMail ska vara optimala, med optimala förfrågningsplaner, behövs en strikt struktur, inte RETURNS TABLE AS RETURN…

I detta fall omvandlas den önskade Fråga 1 till Fråga 4:

Fråga nr 4

(@p__linq__0 nvarchar(4000))VÄLJ
1 SOM [C1],
[Extent1].[Id] SOM [Id],
[Join2].[Object_Id] SOM [Object_Id],
[Join2].[ObjectType_Id] SOM [ObjectType_Id],
[Join2].[Name] SOM [Name],
[Join2].[ExternalId] SOM [ExternalId]
FRÅN (
    VÄLJ Id, Email FRÅN [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) SOM [Extent0]
INNER JOIN [dbo].[Questions] SOM [Extent1] PÅ([Extent0].Id=[Extent1].Id)
INNER JOIN (VÄLJ [Extent2].[Object_Id] SOM [Object_Id], [Extent2].[Question_Id] SOM [Question_Id], [Extent3].[ExternalId] SOM [ExternalId], [Extent3].[ObjectType_Id] SOM [ObjectType_Id], [Extent4].[Name] SOM [Name]
FRÅN [dbo].[ObjectQuestions] SOM [Extent2]
INNER JOIN [dbo].[Objects] SOM [Extent3] PÅ [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] SOM [Extent4] 
PÅ [Extent3].[ObjectType_Id] = [Extent4].[Id] ) SOM [Join2] 
PÅ [Extent1].[Id] = [Join2].[Question_Id]
DÄR ([Extent1].[AnswerId] ÄR NULL) OCH (0 = [Extent1].[Exp]);

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

Slutgiltig LINQ-fråga

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

Körningstiden har minskat från 200-800 ms till 2-20 ms, dvs. flera gånger snabbare.

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

Några uppenbara fördelar inkluderar:

ett allmänt minskande av läsbelastningen,
en betydande minskning av blockeringar
minskning av den genomsnittliga blockeringstiden till acceptabla nivåer

Utdata

Optimering och finjustering av databasfrågor MS SQL via LINQ är en uppgift som kan lösas.

I detta arbete är noggrannhet och konsekvens mycket viktiga.

I början av processen:

måste vi kontrollera de data som frågan arbetar med (värden, valda datatyper)
utföra korrekt indexering av dessa data
kontrollera korrektheten i anslutningsvillkoren mellan tabellerna

Vid nästa optimeringsiteration identifieras:

grunden för frågan och den huvudsakliga filtreringen av frågan fastställs
återkommande liknande delar av frågan och analys av överlappande villkor
i SSMS eller annan GUI för SQL Server själva optimeras SQL-fråga (extrahering av mellanlagring av data, bygga en resultatfråga med hjälp av denna lagring (kan vara flera))
i det sista steget, med den resulterande SQL-fråga, ombyggs strukturen av LINQ-frågan

I slutände ska den resulterande LINQ-frågan vara strukturellt identisk med den optimala SQL-frågan från punkt 3.