🥇Metoaden foar it optimalisearjen fan LINQ-fragen yn C#.NET

Ynlieding

В dit artikel guon optimisaasjemetoaden waarden beskôge LINQ fragen.
Hjir presintearje wy ek wat mear oanpakken foar koade-optimalisaasje yn ferbân mei LINQ fragen.

It is bekend dat LINQ(Language-Integrated Query) is in ienfâldige en handige taal foar it opfreegjen fan in gegevensboarne.

А LINQ nei SQL is in technology foar tagong ta gegevens yn in DBMS. Dit is in krêftich ark foar it wurkjen mei gegevens, wêrby't queries wurde konstruearre troch in deklarative taal, dy't dan wurde omsetten yn SQL-fragen platfoarm en stjoerd nei de databank tsjinner foar útfiering. Yn ús gefal bedoele wy mei DBMS MS SQL-tsjinner.

Mar, LINQ fragen wurde net omset yn optimaal skreaune SQL-fragen, dy't in betûfte DBA skriuwe koe mei alle nuânses fan optimalisaasje SQL-fragen:

optimale ferbiningen (JOIN) en filterje de resultaten (WÊR)
in protte nuânses yn it brûken fan ferbinings en groep betingsten
in protte fariaasjes yn ferfangende betingsten IN op BESTAANи NET YN, <> oan BESTAAN
tuskentiidse caching fan resultaten fia tydlike tabellen, CTE, tabel fariabelen
gebrûk fan sin (OPSJE) mei ynstruksjes en tabel hints MEI (...)
yndeksearre werjeften brûke as ien fan 'e middels om oerstallige gegevenslêzingen kwyt te reitsjen by seleksjes

De wichtichste prestaasjes knyppunten fan de resultearjende SQL-fragen by it gearstallen LINQ fragen binne:

konsolidaasje fan it hiele dataseleksjemeganisme yn ien fersyk
identike blokken fan koade duplisearje, wat úteinlik liedt ta meardere ûnnedige gegevenslêzen
groepen fan meardere komponinten betingsten (logyske "en" en "of") - EN и OR.INDEX SCAN) troch groepen fan betingsten
djippe nêsten fan subqueries makket parsing heul problematysk SQL útspraken en analyze fan de query plan oan 'e kant fan ûntwikkelers en DBA

Optimalisaasjemethoden

Litte wy no direkt nei optimisaasjemetoaden gean.

1) Oanfoljende yndeksearring

It is it bêste om filters te beskôgjen op 'e haadseleksjetabellen, om't heul faak de heule query is boud om ien of twa haadtabellen (applikaasjes-minsken-operaasjes) en mei in standert set fan betingsten (IsClosed, Cancelled, Enabled, Status). It is wichtich om passende yndeksen te meitsjen foar de identifisearre samples.

Dizze oplossing makket sin by it selektearjen fan dizze fjilden beheint de weromjûne set signifikant ta de query.

Wy hawwe bygelyks 500000 applikaasjes. D'r binne lykwols mar 2000 aktive applikaasjes. Dan sil in korrekt selektearre yndeks ús rêde fan INDEX SCAN op in grutte tafel en sil tastean jo fluch selektearje gegevens fia in net-klustere yndeks.

Ek kin it gebrek oan yndeksen wurde identifisearre troch prompts foar it parsearjen fan queryplannen of it sammeljen fan statistyk foar systeemwerjefte MS SQL-tsjinner:

Alle werjeftegegevens befetsje ynformaasje oer ûntbrekkende yndeksen, mei útsûndering fan romtlike yndeksen.

Yndeksen en caching binne lykwols faak metoaden om de gefolgen fan min skreaune te bestriden LINQ fragen и SQL-fragen.

As de hurde praktyk fan it libben lit sjen, is it faaks wichtich foar in bedriuw om saaklike funksjes te ymplementearjen troch bepaalde deadlines. En dêrom wurde swiere oanfragen faak oerbrocht nei de eftergrûn mei caching.

Dit is foar in part rjochtfeardige, om't de brûker net altyd de lêste gegevens nedich hat en d'r is in akseptabel nivo fan responsiviteit fan 'e brûkersynterface.

Dizze oanpak makket it mooglik om saaklike behoeften op te lossen, mar ferminderet úteinlik de prestaasjes fan it ynformaasjesysteem troch gewoan oplossingen foar problemen te fertrage.

It is ek de muoite wurdich om te ûnthâlden dat yn it proses fan it sykjen nei de nedige yndeksen te foegjen, suggestjes MS SQL optimisaasje kin ferkeard wêze, ynklusyf ûnder de folgjende betingsten:

as der al yndeksen binne mei in ferlykbere set fan fjilden
as de fjilden yn 'e tabel net kinne wurde yndeksearre fanwegen yndeksearjende beheiningen (yn mear detail beskreaun hjir).

2) Attributen gearfoegje yn ien nij attribút

Soms kinne guon fjilden út ien tabel, dy't tsjinje as basis foar in groep betingsten, wurde ferfongen troch it ynfieren fan ien nij fjild.

Dit is benammen wier foar statusfjilden, dy't meastentiids bit of hiel getal yn type binne.

Foarbyld:

IsClosed = 0 EN Annulearre = 0 EN ynskeakele = 0 wurdt ferfongen troch Status = 1.

Dit is wêr it integer Status attribút wurdt yntrodusearre om te soargjen dat dizze statusen wurde befolke yn de tabel. Dêrnei wurdt dit nije attribút yndeksearre.

Dit is in fûnemintele oplossing foar it prestaasjesprobleem, om't wy tagong krije ta gegevens sûnder ûnnedige berekkeningen.

3) Materialisaasje fan 'e werjefte

Spitigernôch yn LINQ fragen Tydlike tabellen, CTE's en tabelfariabelen kinne net direkt brûkt wurde.

D'r is lykwols in oare manier om te optimalisearjen foar dit gefal - yndekseare werjeften.

Betingstgroep (fanút it foarbyld hjirboppe) IsClosed = 0 EN Annulearre = 0 EN ynskeakele = 0 (as in set fan oare ferlykbere betingsten) wurdt in goede opsje om se te brûken yn in yndeksearre werjefte, it cache fan in lyts stikje gegevens fan in grutte set.

Mar d'r binne in oantal beheiningen by it materialisearjen fan in werjefte:

gebrûk fan subqueries, clauses BESTAAN moatte wurde ferfongen troch it brûken JOIN
do kinst gjin sinnen brûke UNION, UNION ALLE, ÚTSÛNDERING, KRYSJE
Jo kinne gjin tabel hints en klausules brûke OPSJE
gjin mooglikheid om te wurkjen mei cycles
It is ûnmooglik om gegevens te werjaan yn ien werjefte fan ferskate tabellen

It is wichtich om te betinken dat it echte foardiel fan it brûken fan in yndeksearre werjefte allinich kin wurde berikt troch it feitlik te yndeksearjen.

Mar by it roppen fan in werjefte meie dizze yndeksen net brûkt wurde, en om se eksplisyt te brûken, moatte jo spesifisearje WITH (NOEXPAND).

Sûnt yn LINQ fragen It is ûnmooglik om tabelhints te definiearjen, dus jo moatte in oare fertsjintwurdiging meitsje - in "wrapper" fan 'e folgjende foarm:

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

4) Mei help fan tabel funksjes

Faak yn LINQ fragen Grutte blokken fan subqueries of blokken mei werjeften mei in komplekse struktuer foarmje in definitive fraach mei in tige komplekse en suboptimale útfieringsstruktuer.

Wichtige foardielen fan it brûken fan tabelfunksjes yn LINQ fragen:

De mooglikheid, lykas yn it gefal fan werjeften, te brûken en oantsjutte as in objekt, mar jo kinne in set ynfierparameters trochjaan:
FROM FUNCTION(@param1, @param2 ...)
As gefolch kin fleksibele gegevenssampling berikt wurde
Yn it gefal fan it brûken fan in tabelfunksje binne d'r gjin sokke sterke beheiningen as yn it gefal fan yndeksearre werjeften hjirboppe beskreaun:
1. Tabel hints:
  через LINQ Jo kinne net oantsjutte hokker yndeksen moatte wurde brûkt en bepale de gegevens isolaasje nivo doe't query.
  Mar de funksje hat dizze mooglikheden.
  Mei de funksje kinne jo in frij konstant útfieringsfraachplan berikke, wêrby't regels foar wurkjen mei yndeksen en nivo's fan gegevensisolaasje wurde definieare
2. Mei it brûken fan de funksje kinne jo, yn ferliking mei yndeksearre werjeften, krije:
  - komplekse data sampling logika (sels mei loops)
  - it opheljen fan gegevens út in protte ferskillende tabellen
  - it brûken fan UNION и BESTAAN
Oanbod OPSJE heul nuttich as wy tagelyk kontrôle moatte leverje OPTION (MAXDOP N), de folchoarder fan it query-útfierplan. Bygelyks:
- jo kinne in twongen opnij oanmeitsje fan it queryplan OPSJE (RECOMPILE)
- jo kinne opjaan oft it queryplan twingt om de join-opdracht te brûken opjûn yn 'e query OPSJE (FORCE ORDER)
Mear details oer OPSJE beskriuwe hjir.
Gebrûk fan it smelste en meast fereaske dataslice:
D'r is gjin ferlet om grutte gegevenssets yn caches op te slaan (lykas it gefal is mei yndeksearre werjeften), wêrfan jo de gegevens noch moatte filterje op parameter.
Bygelyks, der is in tabel waans filter WÊR trije fjilden wurde brûkt (a, b, c).

Konvinsjoneel hawwe alle oanfragen in konstante betingst a = 0 en b = 0.

Lykwols, it fersyk foar it fjild c mear fariabele.

Lit de betingst a = 0 en b = 0 It helpt ús wirklik om de fereaske resultearjende set te beheinen ta tûzenen records, mar de betingst oan с smel de seleksje omleech nei hûndert records.

Hjir kin de tabelfunksje in bettere opsje wêze.

Ek is in tabelfunksje mear foarsisber en konsekwint yn útfieringstiid.

foarbylden

Lit ús sjen nei in foarbyld ymplemintaasje mei help fan de Questions databank as foarbyld.

Der is in fersyk ÚTKIEZE, dy't ferskate tabellen kombinearret en ien werjefte brûkt (OperativeQuestions), wêrby't affiliaasje kontrolearre wurdt per e-post (fia BESTAAN) nei "Operatyf fragen":

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

De werjefte hat in frij komplekse struktuer: it hat subquery joins en brûkt sortearring DISTINCT, wat yn 't algemien in frij boarne-yntinsive operaasje is.

In stekproef fan OperativeQuestions is sawat tsientûzen records.

It wichtichste probleem mei dizze query is dat foar de records fan 'e eksterne query in ynterne subquery wurdt útfierd yn' e werjefte [OperativeQuestions], dy't foar [Email] = @p__linq__0 ús tastean de útfierseleksje te beheinen (fia BESTAAN) oant hûnderten records.

En it kin lykje dat de subquery moat berekkenje de records ien kear troch [Email] = @p__linq__0, en dan dizze pear hûndert records moatte wurde ferbûn troch Id mei fragen, en de fraach sil wêze fluch.

Yn feite is d'r in sekwinsjele ferbining fan alle tabellen: kontrolearje de korrespondinsje fan Id Questions mei Id fan OperativeQuestions, en filterjen troch e-post.

Yn feite wurket it fersyk mei alle tsientûzenen OperativeQuestions-records, mar allinich de gegevens fan belang binne nedich fia e-post.

Operative Questions werjaan tekst:

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

Inisjele werjeftemapping yn 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");
    }
}

Inisjele LINQ query

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

Yn dit bysûndere gefal beskôgje wy in oplossing foar dit probleem sûnder ynfrastruktuerwizigingen, sûnder in aparte tabel yn te fieren mei klearmakke resultaten ("Active Queries"), dy't in meganisme soe fereaskje om it mei gegevens te foljen en by de tiid te hâlden .

Hoewol dit in goede oplossing is, is d'r in oare opsje om dit probleem te optimalisearjen.

It haaddoel is om yngongen te cache troch [E-post] = @p__linq__0 út de OperativeQuestions werjefte.

Yntrodusearje de tabelfunksje [dbo].[OperativeQuestionsUserMail] yn de databank.

Troch e-post as ynfierparameter te ferstjoeren, krije wy in tabel mei wearden werom:

Fersyk 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 jout in tabel mei wearden werom mei in foarôf definieare gegevensstruktuer.

Om queries nei OperativeQuestionsUserMail optimaal te wêzen en optimale queryplannen te hawwen, is in strikte struktuer fereaske, en net RETURNS TABLE AS RETURN...

Yn dit gefal wurdt de fereaske Query 1 omset yn Query 4:

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

Mapping werjeften en funksjes yn 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 query

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

De folchoarder fan útfieringstiid is sakke fan 200-800 ms, nei 2-20 ms, ensfh., d.w.s. tsientallen kear flugger.

As wy it mear gemiddeld nimme, dan krigen wy ynstee fan 350 ms 8 ms.

Fan 'e foar de hân lizzende foardielen krije wy ek:

algemiene fermindering fan lêsdruk,
signifikante fermindering fan 'e kâns op blokkearjen
it ferminderjen fan de gemiddelde blokkearjende tiid nei akseptabele wearden

konklúzje

Optimalisaasje en fine-tuning fan databankoproppen MS SQL через LINQ is in probleem dat kin wurde oplost.

Opmerksumens en konsistinsje binne tige wichtich yn dit wurk.

Oan it begjin fan it proses:

it is nedich om de gegevens te kontrolearjen wêrmei it fersyk wurket (wearden, selektearre gegevenstypen)
útfiere goede yndeksearring fan dizze gegevens
kontrolearje de krektens fan joining betingsten tusken tabellen

De folgjende optimisaasje-iteraasje lit sjen:

basis fan it fersyk en definiearret de wichtichste fersyk filter
ferlykbere queryblokken werhelje en de krusing fan betingsten analysearje
yn SSMS of oare GUI foar SQL Server optimalisearret himsels SQL query (in tuskenlizzende gegevensopslach tawize, de resultearjende query bouwe mei dizze opslach (d'r kinne ferskate wêze))
yn 'e lêste etappe, as basis de resultearjende SQL query, de struktuer wurdt ferboud LINQ fraach

De resultearjende LINQ fraach moat identyk wurde yn struktuer oan 'e identifisearre optimale SQL query fan punt 3.

Erkennings

Tige tank oan kollega's jobgemws и alex_ozr fan it bedriuw Fortis foar help by it tarieden fan dit materiaal.

Boarne: www.habr.com

Metoaden foar it optimalisearjen fan LINQ-fragen yn C#.NET