🥇LINQ vaicājumu optimizācijas metodes C#.NET

Ievads

No šajā rakstā tika apspriestas dažas optimizācijas metodes LINQ vaicājumiem.
Šeit mēs sniegsim dažus citus pieejas, kas saistītas ar koda optimizāciju LINQ vaicājumiem.

Ir zināms, ka LINQ(Language-Integrated Query) — tā ir vienkārša un ērta vaicājumu valoda datu avotam.

Un LINQ to SQL ir datu piekļuves tehnoloģija DBMS. Tā ir jaudīga datu apstrādes rīka, kur ar deklaratīvo valodu tiek konstruēti vaicājumi, kas pēc tam tiks pārveidoti SQL vaicājumos platformā un nosūtīti uz datu bāzes serveri izpildei. Mūsu gadījumā DBMS nozīmē MS SQL Server.

Tomēr, LINQ vaicājumi netiek pārveidoti optimāli uzrakstītajos SQL vaicājumos, kurus varētu uzrakstīt pieredzējis DBA, ņemot vērā visus optimizācijas nianses SQL vaicājumos:

optimizētās apvienošanās (JOIN) un rezultātu filtrēšanā (WHERE)
ieroču daudzas nianses apvienošanas un grupējošo apstākļu izmantošanā
daudzas variācijas nosacījumu aizstāšanā IN uz EXISTSun NOT IN, uz EXISTS
starpposma rezultātu kešatmiņu, izmantojot pagaidu tabulas, CTE, kā arī tabulu mainīgās
piedāvājuma izmantošana (OPTION) ar norādījumiem un tabulu hintiem WITH (…)
indeksētu skatu izmantošana kā viens no līdzekļiem, lai atbrīvotos no liekiem datu lasījumiem izvēlēs

Galvenie veiktspējas šaurie punkti, kas rodas SQL vaicājumos kompilācijas laikā LINQ vaicājumiem ir:

visu datu atlases mehānisma konsolidācija vienā vaicājumā
identisku koda bloku dublēšana, kas galu galā noved pie daudzkārtējas liekām datu lasīšanām
daudzdimensiju apstākļu grupas (loģiskie «un» un «vai») — AND un OR, apvienojoties sarežģītos apstākļos, noved pie tā, ka optimizators, kam ir izdevīgi nekaslēgmi indeksi, galu galā tomēr sāk veikt skenēšanu pa klasisko indeksu (INDEX SCAN) pēc nosacījumu grupām
dziļa apakšvaicājumu ievietošanas padara ļoti sarežģītu SQL instrukciju un vaicājumu plāna analīzi no izstrādātāju un DBA DBA

Tagad pāriesim tieši pie optimizācijas metodēm.

1) Papildu indeksācija

Vislabāk ir apsvērt filtrus uz galvenajām atlases tabulām, jo ļoti bieži visa pieprasījuma struktūra balstās uz vienu vai divām galvenajām tabulām (pieteikumi, cilvēki, operācijas) ar standartizētu nosacījumu kopumu (IsClosed, Canceled, Enabled, Status). Ir svarīgi izveidot atbilstošus indeksus konstatētajām atlasēm.

Šis risinājums ir jēgpilns, kad atlase pēc šiem laukiem būtiski ierobežo atgriežamo rezultātu kopu no pieprasījuma.

Piemēram, mums ir 500000 pieteikumu. Tomēr aktīvo pieteikumu ir tikai 2000 ierakstu. Tādā gadījumā pareizi izvēlēts indekss mūs atbrīvos no INDEX SCAN pār lielu tabulu un ļaus ātri atlasīt datus, izmantojot neklausterizētu indeksu.

Nepietiekamu indeksu var arī noteikt, izmantojot vaicājumu izpildes plānu analīzes ieteikumus vai sistēmas skatu statistiku MS SQL Server:

Visi skatu dati satur informāciju par trūkstošajiem indeksiem, izņemot telpiskos indeksus.

Tomēr indeksu un kešatmiņas izmantošana bieži ir metodes, kā cīnīties ar slikti uzrakstītu LINQ vaicājumiem un SQL vaicājumos.

Kā rāda skarba biznesa prakse, bieži ir svarīgi īstenot biznesa funkcijas noteiktos termiņos. Tāpēc smagos pieprasījumus bieži pārvieto fonā ar kešatmiņu.

Šis pieejas veids ļauj risināt biznesa pieprasījumus, taču galu galā samazina informācijas sistēmas veiktspēju, vienkārši atliekot problēmu risināšanu.

Arī jāatceras, ka, meklējot nepieciešamos jaunus indeksus, ieteikumi

MS SQL optimizācijai var būt nepareizi, tostarp sekojošos apstākļos: ja jau pastāv indeksi ar līdzīgu lauku kopumu

ja lauki tabulā nevar tikt indeksēti indeksēšanas ierobežojumu dēļ (par to sīkāk aprakstīts
2) Atribūtu apvienošana vienā jaunā atribūtā šeit).

Dažkārt dažus laukus no vienas tabulas, par kuriem tiek veidota nosacījumu grupa, var aizstāt ar vienas jaunas lauka ieviešanu.

Tas īpaši attiecas uz stāvokļu laukiem, kas parasti ir vai nu bitu, vai veseli skaitļi.

Piemērs:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0

Status = 1 tiek aizstāta ar Status = 1.

Šeit tiek ievadīts veselības skaitļu atribūts Status, nodrošinot šo statusu aizpildīšanu tabulā. Pēc tam tiek veikta šī jaunā atribūta indeksēšana.

Tā ir fundamentāla risinājuma problēma veiktspējā, jo mēs pieprasām datus bez liekiem aprēķiniem.

3) Skata materializācija

Diemžēl LINQ vaicājumos nav iespējams tieši izmantot pagaidu tabulas, CTE un tabulu mainīgus.

Tomēr ir vēl viens veids, kā optimizēt šo gadījumu — indeksējami skati.

Nosacījumu grupa (no iepriekšējā piemērā) Status = 1 (vai komplekts citu līdzīgu nosacījumu) kļūst par labu variantu, lai tos izmantotu indeksējamā skatā, kešējot nelielu datu slāni no lielas masas.

Bet ir vairāki ierobežojumi, kad materializē skatu:

apakšvaicājumu izmantošana, piedāvājumi EXISTS jāaizstāj ar izmantošanu JOIN
nav iespējams izmantot piedāvājumus UNION, UNION ALL, IZŅEMŠANA, INTERSECT
nav iespējams izmantot tabulu padomus un piedāvājumus OPTION
nav iespējas strādāt ar cikliem
nav iespējams parādīt datus vienā skatā no dažādām tabulām

Ir svarīgi atcerēties, ka reāla ieguvums no indeksējamu skatu izmantošanas var tikt iegūts faktiski tikai tad, kad tas tiek indeksēts.

Bet, izsaucot skatu, šie indeksi var netikt izmantoti, un to izteiktai izmantošanai ir nepieciešams norādīt WITH (NOEXPAND).

Tā kā LINQ vaicājumos nav iespējams definēt tabulu padomus, tāpēc ir jāveido vēl viena skata „apvalks” šādā veidā:

CREATE VIEW VĀRDS_skata AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Tabulu funkciju izmantošana

Bieži LINQ vaicājumos lieli apakšvaicājumu bloki vai bloki, kas izmanto skatus ar sarežģītu struktūru, veido gala vaicājumu ar ļoti sarežģītu un neoptimālu izpildes struktūru.

Galvenās priekšrocības, izmantojot tabulu funkcijas LINQ vaicājumos:

Iespēja, tāpat kā gadījumā ar skatījumiem, izmantot un norādīt kā objektu, taču var pārsūtīt ieejas parametru kopumu:
FROM FUNCTION(@param1, @param2 …)
rezultātā var panākt elastīgu datu atlasi
Gadījumā, ja tiek izmantota tabulu funkcija, nav tādu stingru ierobežojumu kā gadījumā ar iepriekš aprakstītajiem indeksējamajiem skatiem:
1. Tabulu padomi:
  caur LINQ Nav iespējams norādīt, kuri indeksi ir jāizmanto un noteikt datu izolācijas līmeni pie pieprasījuma.
  Tomēr funkcijā šīs iespējas ir pieejamas.
  Izmantojot funkciju, var sasniegt salīdzinoši stabilu izpildes plāna struktūru, kurā ir noteikumi par darbu ar indeksi un datu izolācijas līmeņiem.
2. Funkcijas izmantošana ļauj, salīdzinot ar indeksētiem skatiem, iegūt:
  - sarežģītu datu atlasīšanas loģiku (pat līdz ciklu izmantošanai)
  - datu atlasi no dažādām tabulām
  - izmantošana UNION un EXISTS
Piedāvājums OPTION ir ļoti noderīgs, kad mums ir jānodrošina paralēlisma vadība. OPTION(MAXDOP N), pieprasījuma izpildes plāna kārtībā. Piemēram:
- var norādīt piespiedu atkārtotu izpildes plāna izveidi. OPTION (RECOMPILE)
- var norādīt nepieciešamību nodrošināt piespiedu kārtību savienošanas plānā, kas norādīts pieprasījumā. OPTION (FORCE ORDER)
Vairāk sīkāk par OPTION aprakstīts šeit.
Izmantojot šaurāko un nepieciešamo datu slāni:
Nav nepieciešams turēt lielus datu kopas kešatmiņā (kā indeksēto skatu gadījumā), no kuras vēl ir jāfiltrē dati pēc parametra.
Piemēram, ir tabula, kurā filtram WHERE izmanto trīs laukus (a, b, c).
Nosacīti visiem pieprasījumiem ir nemainīgs nosacījums a = 0 and b = 0.
Tomēr pieprasījums uz lauku c ir mainīgāks.
Pieņemsim, ka nosacījums a = 0 and b = 0 mums tiešām palīdz ierobežot iegūto kopu līdz tūkstoš ierakstiem, taču nosacījums pēc ar mums sašaurina atlasi līdz simt ierakstiem.
Šeit tabulu funkcija var būt izdevīgāka.
Turklāt tabulu funkcija ir paredzama un stabila izpildes laikā.

Piemēri

Apskatīsim piemēru īstenošanai, izmantojot datubāzi Questions.

Ir pieprasījums SELECT, kas apvieno vairākas tabulas un izmanto vienu skatu (OperativeQuestions), kur tiek pārbaudīta piederība (caur EXISTS) uz "Aktīviem pieprasījumiem" ([OperativeQuestions]):

Pieprasījums 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])
));

Pārskats ir diezgan sarežģīts: tajā ir apakšquery savienojumi un izmantošana kārtošana DISTINCT, kas vispārēji ir diezgan resursu ietilpīga operācija.

Izlasīšana no OperativeQuestions aptuveni desmit tūkstošu ierakstu apmērā.

Galvenā šī pieprasījuma problēma ir tāda, ka ārējā pieprasījumā esošajiem ierakstiem tiek veikts iekšējais apakšpieprasījums uz skatu [OperativeQuestions], kas mums, lai [Email] = @p__linq__0, ierobežo datus (caur EXISTS) līdz simtiem ierakstu.

Un varētu šķist, ka apakšpieprasījumam vienreiz vajadzētu aprēķināt ierakstus pēc [Email] = @p__linq__0, un pēc tam šīs pāris simts ierakstu jāapvieno pēc Id ar Questions, un pieprasījums būtu ātrs.

Patiesībā notiek secīga visu tabulu savienošana: un atbilstības pārbaude starp Id Questions un Id no OperativeQuestions, un filtrēšana pēc Email.

Faktiski pieprasījums strādā ar visiem desmitiem tūkstošu ierakstu no OperativeQuestions, bet mums nepieciešami tikai interesējošie dati pēc Email.

Teksts par OperativeQuestions skatu:

Pieprasījums 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));

Sākotnējā skata kartēšana DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().ToView("OperativeQuestions");
    }
}

Sākotnējais LINQ vaicājums

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

Šajā konkrētajā gadījumā apsveram risinājumu šai problēmai bez infrastruktūras izmaiņām, bez atsevišķas tabulas „Aktīvi vaicājumi”, kurai būtu nepieciešams mehānisms, lai to piepildītu ar datiem un uzturētu to aktuālā stāvoklī.

Lai gan tas ir labs risinājums, pastāv arī cits iespēju uzlabošanas variants.

Galvenais mērķis ir kešatmiņā saglabāt ierakstus pēc [Email] = @p__linq__0 no OperativeQuestions skata.

Ieviešam tabulas funkciju [dbo].[OperativeQuestionsUserMail] datu bāzē.

Nosūtot kā ieejas parametru Email, saņemam atpakaļ vērtību tabulu:

Vaicājums 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

Šeit tiek atgriezta vērtību tabula ar iepriekš noteiktu datu struktūru.

Lai vaicājumi uz OperativeQuestionsUserMail būtu optimāli un būtu optimāli vaicājumu plāni, nepieciešama stingra struktūra, nevis RETURNS TABLE AS RETURN…

Šajā gadījumā meklētais Vaicājums 1 tiek pārvērsts par Vaicājumu 4:

Vaicājums 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]);

Skata un funkcijas kartēšana 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})");
}

Galīgais LINQ vaicājums

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

Izpildes laika kārtība samazinājās no 200-800 ms līdz 2-20 ms, un tā tālāk, t.i., desmitiem reižu ātrāk.

Ja ņem vidēji, tad vietā 350 ms saņēmām 8 ms.

No acīmredzamajiem plusiem arī iegūstam:

kopējā slodzes samazināšanās lasīšanai,
ievērojams bloķēšanas iespēju samazinājums
vidējā bloķēšanas laika samazinājums līdz pieņemamām vērtībām

Izvade

Optimizācija un precizēšana datu bāzes izsaukumiem optimizācijai var būt nepareizi, tostarp sekojošos apstākļos: caur LINQ ir uzdevums, kuru var atrisināt.

Šajā darbā ļoti svarīga ir uzmanība un secība.

Procesa sākumā:

ir jāveic datu pārbaude, ar kuriem strādā vaicājums (vērtības, izvēlētie datu tipi)
pareizi indeksēt šos datus
pārbaudīt savienojuma nosacījumu pareizību starp tabulām

Nākamajā optimizācijas iterācijā tiek identificēts:

vaicājuma pamats un nosaka galveno vaicājuma filtru
atkārtojošies līdzīgi vaicājuma bloki un analizē nosacījumu pārklāšanās
SSMS vai citā GUI priekš SQL Server optimizējas pats SQL vaicājums (starpposma datu krātuves izdalīšana, rezultātu vaicājuma būvēšana, izmantojot šo krātuvi (var būt vairākas))
pēdējā posmā, ņemot par pamatu rezultātu SQL vaicājums, tiek pārstrukturēta LINQ vaicājuma struktūra

Galu galā iegūtais LINQ vaicājums jābūt strukturāli identiskam noteiktajam optimālajam SQL vaicājumam no punkta 3.

Pateicības

Liels paldies kolēģiem jobgemws un alex_ozr no uzņēmuma Fortis par palīdzību šī materiāla sagatavošanā.

Avots: habr.com