🥇 Metodes vir die optimalisering van LINQ-navrae in C#.NET

Inleiding

В Hierdie artikel sommige optimeringsmetodes is oorweeg LINQ navrae.
Hier bied ons ook 'n paar meer benaderings tot kode-optimalisering wat verband hou met LINQ navrae.

Dit is bekend dat LINQ(Taalgeïntegreerde navraag) is 'n eenvoudige en gerieflike taal om 'n databron navraag te doen.

А LINQ na SQL is 'n tegnologie vir toegang tot data in 'n DBBS. Dit is 'n kragtige hulpmiddel om met data te werk, waar navrae saamgestel word deur 'n verklarende taal, wat dan omgeskakel sal word in SQL-navrae platform en na die databasisbediener gestuur vir uitvoering. In ons geval bedoel ons met DBMS MS SQL Server.

Egter LINQ navrae word nie omgeskakel in optimaal geskrewes nie SQL-navrae, wat 'n ervare DBA kon skryf met al die nuanses van optimalisering SQL-navrae:

optimale verbindings (SLUIT) en filter die resultate (WAAR)
baie nuanses in die gebruik van verbindings en groepstoestande
baie variasies in die vervanging van toestande IN op BESTAANи NIE IN NIE, <> aan BESTAAN
intermediêre kas van resultate via tydelike tabelle, CTE, tabelveranderlikes
gebruik van sin (OPSIE) met instruksies en tabelwenke MET (...)
die gebruik van geïndekseerde aansigte as een van die maniere om ontslae te raak van oortollige datalesings tydens keuses

Die belangrikste prestasie knelpunte van die gevolglike SQL-navrae by samestelling LINQ navrae Hulle is:

konsolidasie van die hele dataseleksiemeganisme in een versoek
identiese blokke kode dupliseer, wat uiteindelik lei tot veelvuldige onnodige data-lees
groepe multi-komponent toestande (logiese "en" en "of") - EN и OR, wat in komplekse toestande gekombineer word, lei daartoe dat die optimeerder, met geskikte nie-gegroepeerde indekse vir die nodige velde, uiteindelik begin skandeer teen die gegroepeerde indeks (INDEKS SKANDERING) volgens groepe toestande
diep nes van subnavrae maak ontleding baie problematies SQL-stellings en ontleding van die navraagplan aan die kant van ontwikkelaars en DBA

Optimaliseringsmetodes

Kom ons gaan nou direk na optimaliseringsmetodes.

1) Bykomende indeksering

Dit is die beste om filters op die hoofseleksietabelle te oorweeg, aangesien die hele navraag baie dikwels rondom een of twee hooftabelle gebou word (toepassings-mense-bedrywighede) en met 'n standaard stel voorwaardes (Is Gesluit, Gekanselleer, Geaktiveer, Status). Dit is belangrik om toepaslike indekse vir die geïdentifiseerde monsters te skep.

Hierdie oplossing maak sin wanneer die keuse van hierdie velde die teruggekeerde stel aansienlik beperk tot die navraag.

Ons het byvoorbeeld 500000 2000 aansoeke. Daar is egter net XNUMX aktiewe toepassings. Dan sal 'n korrek geselekteerde indeks ons red van INDEKS SKANDERING op 'n groot tabel en sal jou toelaat om vinnig data te kies deur 'n nie-gegroepeerde indeks.

Die gebrek aan indekse kan ook geïdentifiseer word deur aansporings vir die ontleding van navraagplanne of die versameling van stelselaansigstatistieke MS SQL Server:

Alle aansigdata bevat inligting oor ontbrekende indekse, met die uitsondering van ruimtelike indekse.

Indekse en caching is egter dikwels metodes om die gevolge van swak geskryf te bekamp LINQ navrae и SQL-navrae.

Soos die harde praktyk van die lewe toon, is dit dikwels belangrik vir 'n besigheid om besigheidskenmerke teen sekere spertye te implementeer. En daarom word swaar versoeke dikwels met caching na die agtergrond oorgedra.

Dit is gedeeltelik geregverdig, aangesien die gebruiker nie altyd die nuutste data benodig nie en daar 'n aanvaarbare vlak van reaksie van die gebruikerskoppelvlak is.

Hierdie benadering laat toe om besigheidsbehoeftes op te los, maar verminder uiteindelik die werkverrigting van die inligtingstelsel deur bloot oplossings vir probleme te vertraag.

Dit is ook die moeite werd om te onthou dat in die proses van soek na die nodige indekse om by te voeg, voorstelle MS SQL optimalisering kan verkeerd wees, insluitend onder die volgende toestande:

as daar reeds indekse met 'n soortgelyke stel velde is
as die velde in die tabel nie geïndekseer kan word nie as gevolg van indekseringsbeperkings (in meer besonderhede beskryf hier).

2) Voeg eienskappe saam in een nuwe kenmerk

Soms kan sommige velde uit een tabel, wat as basis vir 'n groep toestande dien, vervang word deur een nuwe veld in te voer.

Dit is veral waar vir statusvelde, wat gewoonlik óf 'n bietjie of 'n heelgetal in tipe is.

Voorbeeld:

IsGesluit = 0 EN Gekanselleer = 0 EN Geaktiveer = 0 word vervang deur Status = 1.

Dit is waar die heelgetal Status-kenmerk ingestel word om te verseker dat hierdie statusse in die tabel gevul word. Vervolgens word hierdie nuwe kenmerk geïndekseer.

Dit is 'n fundamentele oplossing vir die prestasieprobleem, want Ons kry toegang tot data sonder onnodige berekeninge.

3) Materialisering van die uitsig

Ongelukkig in LINQ navrae Tydelike tabelle, CTE's en tabelveranderlikes kan nie direk gebruik word nie.

Daar is egter 'n ander manier om vir hierdie geval te optimaliseer - geïndekseerde aansigte.

Toestandgroep (van die voorbeeld hierbo) IsGesluit = 0 EN Gekanselleer = 0 EN Geaktiveer = 0 (of 'n stel ander soortgelyke toestande) word 'n goeie opsie om dit in 'n geïndekseerde aansig te gebruik, wat 'n klein stukkie data van 'n groot stel in die kas kas.

Maar daar is 'n aantal beperkings wanneer 'n siening gerealiseer word:

gebruik van subnavrae, klousules BESTAAN vervang moet word deur gebruik te maak SLUIT
jy kan nie sinne gebruik nie UNION, UNIE ALMAL, UITSONDERING, sny
Jy kan nie tabelwenke en klousules gebruik nie OPSIE
geen moontlikheid om met siklusse te werk nie
Dit is onmoontlik om data in een aansig uit verskillende tabelle te vertoon

Dit is belangrik om te onthou dat die werklike voordeel van die gebruik van 'n geïndekseerde aansig slegs bereik kan word deur dit werklik te indekseer.

Maar wanneer 'n aansig geroep word, mag hierdie indekse nie gebruik word nie, en om dit eksplisiet te gebruik, moet jy spesifiseer MET (NOEXPAND).

Sedert in LINQ navrae Dit is onmoontlik om tabelwenke te definieer, so jy moet 'n ander voorstelling skep - 'n "omhulsel" van die volgende vorm:

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

4) Gebruik tabelfunksies

Dikwels in LINQ navrae Groot blokke subnavrae of blokke wat aansigte met 'n komplekse struktuur gebruik, vorm 'n finale navraag met 'n baie komplekse en suboptimale uitvoeringstruktuur.

Sleutelvoordele van die gebruik van tabelfunksies in LINQ navrae:

Die vermoë, soos in die geval van aansigte, om as 'n voorwerp gebruik en gespesifiseer te word, maar jy kan 'n stel invoerparameters deurgee:
VANAF FUNKSIE(@param1, @param2 ...)
As gevolg hiervan kan buigsame datasteekproefneming verkry word
In die geval van die gebruik van 'n tabelfunksie, is daar nie sulke sterk beperkings soos in die geval van geïndekseerde aansigte wat hierbo beskryf word nie:
1. Tabelwenke:
  deur LINQ Jy kan nie spesifiseer watter indekse gebruik moet word en die data-isolasievlak bepaal wanneer navraag gedoen word nie.
  Maar die funksie het hierdie vermoëns.
  Met die funksie kan u 'n redelik konstante uitvoeringsnavraagplan bereik, waar reëls vir die werk met indekse en data-isolasievlakke gedefinieer word
2. Deur die funksie te gebruik, kan, in vergelyking met geïndekseerde aansigte, verkry word:
  - komplekse datasteekproeflogika (selfs met behulp van lusse)
  - haal data van baie verskillende tabelle af
  - die gebruik van UNION и BESTAAN
aanbod OPSIE baie nuttig wanneer ons gelyktydig beheer moet verskaf OPSIE (MAXDOP N), die volgorde van die navraaguitvoerplan. Byvoorbeeld:
- jy kan 'n gedwonge herskepping van die navraagplan spesifiseer OPSIE (HERSTEL)
- jy kan spesifiseer of die navraagplan moet dwing om die aansluitorde te gebruik wat in die navraag gespesifiseer is OPSIE (Dwangbevel)
Meer besonderhede oor OPSIE beskryf hier.
Gebruik die smalste en mees vereiste dataskyf:
Dit is nie nodig om groot datastelle in kas te stoor nie (soos die geval is met geïndekseerde aansigte), waaruit jy steeds die data volgens parameter moet filtreer.
Byvoorbeeld, daar is 'n tabel waarvan die filter WAAR drie velde word gebruik (a, b, c).

Konvensioneel het alle versoeke 'n konstante toestand a = 0 en b = 0.

Maar die versoek vir die veld c meer veranderlik.

Laat die toestand a = 0 en b = 0 Dit help ons regtig om die vereiste resulterende stel tot duisende rekords te beperk, maar die toestand op с verminder die keuse tot honderd rekords.

Hier kan die tabelfunksie 'n beter opsie wees.

'n Tabelfunksie is ook meer voorspelbaar en konsekwent in uitvoeringstyd.

voorbeelde

Kom ons kyk na 'n voorbeeldimplementering deur die Vrae-databasis as voorbeeld te gebruik.

Daar is 'n versoek KIES, wat verskeie tabelle kombineer en een aansig gebruik (OperativeQuestions), waarin affiliasie per e-pos nagegaan word (via BESTAAN) na "Bedryfsvrae":

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

Die aansig het 'n taamlik komplekse struktuur: dit het subnavraagverbindings en gebruik sortering DISTINCT, wat in die algemeen 'n redelik hulpbron-intensiewe operasie is.

'n Voorbeeld van OperativeQuestions is ongeveer tienduisend rekords.

Die hoofprobleem met hierdie navraag is dat vir die rekords van die buitenste navraag 'n interne subnavraag uitgevoer word op die [OperativeQuestions]-aansig, wat vir [E-pos] = @p__linq__0 ons moet toelaat om die uitvoerkeuse te beperk (via BESTAAN) tot honderde rekords.

En dit mag lyk asof die subnavraag die rekords een keer moet bereken deur [E-pos] = @p__linq__0, en dan moet hierdie paar honderd rekords deur Id met Vrae verbind word, en die navraag sal vinnig wees.

Trouens, daar is 'n opeenvolgende verbinding van alle tabelle: kontroleer die korrespondensie van Id-vrae met Id vanaf OperativeQuestions, en filter per e-pos.

Trouens, die versoek werk met al tienduisende OperativeQuestions-rekords, maar slegs die data van belang word via e-pos benodig.

Operative Questions-aansigteks:

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

Aanvanklike aansigkartering in 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");
    }
}

Aanvanklike LINQ-navraag

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

In hierdie spesifieke geval oorweeg ons 'n oplossing vir hierdie probleem sonder infrastruktuurveranderinge, sonder om 'n aparte tabel met klaargemaakte resultate ("Active Queries") bekend te stel, wat 'n meganisme sal vereis om dit met data te vul en op datum te hou .

Alhoewel dit 'n goeie oplossing is, is daar 'n ander opsie om hierdie probleem te optimaliseer.

Die hoofdoel is om inskrywings te kas deur [E-pos] = @p__linq__0 vanaf die OperativeQuestions-aansig.

Stel die tabelfunksie [dbo].[OperativeQuestionsUserMail] in die databasis in.

Deur e-pos as 'n invoerparameter te stuur, kry ons 'n tabel van waardes terug:

Versoek 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

Dit gee 'n tabel van waardes met 'n voorafbepaalde datastruktuur terug.

Om vir navrae na OperativeQuestionsUserMail optimaal te wees en optimale navraagplanne te hê, word 'n streng struktuur vereis, en nie TERUGKEERTABEL AS OPGAWE...

In hierdie geval word die vereiste navraag 1 omgeskakel na navraag 4:

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

Kartering van aansigte en funksies in 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})");
}

Finale LINQ-navraag

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

Die volgorde van uitvoeringstyd het gedaal van 200-800 ms, tot 2-20 ms, ens., d.w.s. tientalle kere vinniger.

As ons dit meer gemiddeld neem, dan kry ons in plaas van 350 ms 8 ms.

Uit die ooglopende voordele kry ons ook:

algemene vermindering in leeslading,
aansienlike vermindering in die waarskynlikheid van blokkering
die gemiddelde blokkeertyd tot aanvaarbare waardes te verminder

Output

Optimalisering en fyninstelling van databasisoproepe MS SQL deur LINQ is 'n probleem wat opgelos kan word.

Oplettendheid en konsekwentheid is baie belangrik in hierdie werk.

Aan die begin van die proses:

dit is nodig om die data te kontroleer waarmee die versoek werk (waardes, geselekteerde datatipes)
voer behoorlike indeksering van hierdie data uit
kontroleer die korrektheid van verbindingstoestande tussen tabelle

Die volgende optimeringsiterasie onthul:

basis van die versoek en definieer die hoofversoekfilter
herhaal soortgelyke navraagblokke en die ontleding van die kruising van toestande
in SSMS of ander GUI vir SQL Server optimaliseer homself SQL-navraag (toewysing van 'n intermediêre databerging, bou die gevolglike navraag deur hierdie berging te gebruik (daar kan verskeie wees))
op die laaste stadium, met die gevolglike as basis SQL-navraag, word die struktuur herbou LINQ-navraag

Die gevolglike LINQ-navraag moet identies in struktuur word aan die geïdentifiseerde optimale SQL-navraag vanaf punt 3.

Erkennings

Baie dankie aan kollegas jobgemws и alex_ozr van die maatskappy Fortis vir hulp met die voorbereiding van hierdie materiaal.

Bron: will.com

Metodes vir die optimalisering van LINQ-navrae in C#.NET