🥇Metodes LINQ vaicājumu optimizēšanai C#.NET

Ievads

В Šis raksts tika apsvērtas dažas optimizācijas metodes LINQ vaicājumi.
Šeit mēs piedāvājam vēl dažas koda optimizācijas pieejas, kas saistītas ar LINQ vaicājumi.

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

А LINQ uz SQL ir tehnoloģija piekļuvei datiem DBVS. Šis ir spēcīgs rīks darbam ar datiem, kur vaicājumi tiek veidoti, izmantojot deklaratīvu valodu, kas pēc tam tiks pārveidota SQL vaicājumi platformu un nosūta uz datu bāzes serveri izpildei. Mūsu gadījumā ar DBVS mēs domājam MS SQL serveris.

Tomēr, LINQ vaicājumi netiek pārvērsti par optimāli rakstītiem SQL vaicājumi, ko pieredzējis DBA varētu uzrakstīt ar visām optimizācijas niansēm SQL vaicājumi:

optimāli savienojumi (PIEVIENOJIES) un rezultātu filtrēšanu (KUR)
daudzas nianses savienojumu un grupas nosacījumu izmantošanā
daudzas variācijas aizstāšanas apstākļos IN par EXISTSи NAV IEKŠĀ, <> ieslēgts EXISTS
starpposma rezultātu saglabāšana kešatmiņā, izmantojot pagaidu tabulas, CTE, tabulu mainīgos
teikuma lietojums (OPTION) ar instrukcijām un tabulas padomiem AR (...)
izmantojot indeksētos skatus kā vienu no līdzekļiem, lai atlases laikā atbrīvotos no liekiem datu nolasījumiem

Galvenās darbības vājās vietas rezultātā SQL vaicājumi sastādot LINQ vaicājumi ir:

visa datu atlases mehānisma konsolidācija vienā pieprasījumā
identisku koda bloku dublēšana, kas galu galā izraisa vairākus nevajadzīgus datu nolasījumus
daudzkomponentu nosacījumu grupas (loģiski "un" un "vai") UN и OR, apvienojot sarežģītos apstākļos, noved pie tā, ka optimizētājs, kam nepieciešamajiem laukiem ir piemēroti negrupēti indeksi, galu galā sāk skenēt pret klasterēto indeksu (INDEX SCAN) pa nosacījumu grupām
apakšvaicājumu dziļa ligzdošana padara parsēšanu ļoti problemātisku SQL priekšraksti un vaicājumu plāna analīze no izstrādātāju puses un DBA

Optimizācijas metodes

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

1) Papildu indeksācija

Vislabāk ir apsvērt filtrus galvenajās atlases tabulās, jo ļoti bieži viss vaicājums tiek veidots ap vienu vai divām galvenajām tabulām (lietojumprogrammas-cilvēki-operācijas) un ar standarta nosacījumu kopu (IsClosed, Canceled, Enabled, Status). Ir svarīgi izveidot atbilstošus indeksus identificētajiem paraugiem.

Šis risinājums ir saprātīgs, ja atlasot šos laukus, tiek būtiski ierobežota vaicājuma atgrieztā kopa.

Piemēram, mums ir 500000 2000 pieteikumu. Tomēr ir tikai XNUMX aktīvo pieteikumu. Tad pareizi izvēlēts rādītājs mūs paglābs no INDEX SCAN uz lielas tabulas un ļaus ātri atlasīt datus, izmantojot negrupētu indeksu.

Arī indeksu trūkumu var noteikt, izmantojot uzvednes parsēt vaicājumu plānus vai apkopot sistēmas skatījumu statistiku. MS SQL serveris:

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

Tomēr indeksi un kešatmiņa bieži vien ir metodes, kā cīnīties ar slikti uzrakstītām sekām LINQ vaicājumi и SQL vaicājumi.

Kā liecina dzīves skarbā prakse, uzņēmumam bieži vien ir svarīgi ieviest biznesa iezīmes līdz noteiktiem termiņiem. Tāpēc smagie pieprasījumi bieži tiek pārsūtīti uz fonu, izmantojot kešatmiņu.

Tas ir daļēji pamatots, jo lietotājam ne vienmēr ir nepieciešami jaunākie dati un lietotāja interfeisa reakcijas līmenis ir pieņemams.

Šī pieeja ļauj atrisināt biznesa vajadzības, bet galu galā samazina informācijas sistēmas veiktspēju, vienkārši aizkavējot problēmu risinājumus.

Ir arī vērts atcerēties, ka, meklējot nepieciešamos indeksus, ko pievienot, ieteikumus MS SQL optimizācija var būt nepareiza, tostarp šādos apstākļos:

ja jau ir indeksi ar līdzīgu lauku kopu
ja tabulas laukus nevar indeksēt indeksēšanas ierobežojumu dēļ (aprakstīts sīkāk šeit).

2) Atribūtu apvienošana vienā jaunā atribūtā

Dažkārt dažus laukus no vienas tabulas, kas kalpo par pamatu nosacījumu grupai, var aizstāt, ieviešot vienu jaunu lauku.

Tas jo īpaši attiecas uz statusa laukiem, kuriem parasti ir bitu vai veselu skaitļu tips.

Piemērs:

IsClosed = 0 UN Atcelts = 0 UN Iespējots = 0 tiek aizstāts ar Statuss = 1.

Šeit tiek ieviests atribūts vesels skaitlis Statuss, lai nodrošinātu, ka šie statusi tiek aizpildīti tabulā. Pēc tam šis jaunais atribūts tiek indeksēts.

Tas ir būtisks veiktspējas problēmas risinājums, jo mēs piekļūstam datiem bez liekiem aprēķiniem.

3) Skata materializācija

Diemžēl iekšā LINQ vaicājumi Pagaidu tabulas, CTE un tabulu mainīgos nevar izmantot tieši.

Tomēr šim gadījumam ir vēl viens optimizācijas veids - indeksēti skati.

Nosacījumu grupa (no iepriekš minētā piemēra) IsClosed = 0 UN Atcelts = 0 UN Iespējots = 0 (vai citu līdzīgu nosacījumu kopa) kļūst par labu iespēju tos izmantot indeksētā skatā, kešatmiņā saglabājot nelielu datu daļu no lielas kopas.

Taču, īstenojot skatu, ir vairāki ierobežojumi:

izmantojot apakšvaicājumus, klauzulas EXISTS jāaizstāj, izmantojot PIEVIENOJIES
jūs nevarat lietot teikumus SAVIENĪBA, SAVIENĪBA, IZŅĒMUMS, INTERSEKTS
Jūs nevarat izmantot tabulu ieteikumus un punktus OPTION
nav iespēju strādāt ar cikliem
Nav iespējams attēlot datus vienā skatā no dažādām tabulām

Ir svarīgi atcerēties, ka patieso ieguvumu no indeksēta skata izmantošanas var sasniegt, tikai to faktiski indeksējot.

Taču, izsaucot skatu, šos indeksus nedrīkst izmantot, un, lai tos izmantotu tieši, tas ir jānorāda AR (NOEXPAND).

Kopš LINQ vaicājumi Nav iespējams definēt tabulas ieteikumus, tāpēc jums ir jāizveido vēl viens attēlojums - šādas formas "iesaiņojums":

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

4) Tabulu funkciju izmantošana

Bieži vien iekšā LINQ vaicājumi Lieli apakšvaicājumu bloki vai bloki, kuros izmanto skatus ar sarežģītu struktūru, veido galīgo vaicājumu ar ļoti sarežģītu un neoptimālu izpildes struktūru.

Galvenās tabulas funkciju izmantošanas priekšrocības LINQ vaicājumi:

Iespēja, tāpat kā skatu gadījumā, tikt izmantota un norādīta kā objekts, bet jūs varat nodot ievades parametru kopu:
FROM FUNCTION(@param1, @param2...)
Rezultātā var panākt elastīgu datu paraugu ņemšanu
Tabulas funkcijas izmantošanas gadījumā nav tik stingru ierobežojumu kā iepriekš aprakstītajiem indeksētajiem skatiem:
1. Tabulas padomi:
  caur LINQ Vaicāšanas laikā nevar norādīt, kuri indeksi jāizmanto, un noteikt datu izolācijas līmeni.
  Bet funkcijai ir šīs iespējas.
  Izmantojot funkciju, jūs varat sasniegt diezgan nemainīgu izpildes vaicājuma plānu, kurā ir definēti noteikumi darbam ar indeksiem un datu izolācijas līmeņiem
2. Izmantojot funkciju, salīdzinājumā ar indeksētajiem skatiem var iegūt:
  - sarežģīta datu izlases loģika (pat izmantojot cilpas)
  - datu iegūšana no daudzām dažādām tabulām
  - izmantošana SAVIENĪBA и EXISTS
Priekšlikums OPTION ļoti noderīgi, ja mums ir jānodrošina vienlaicīguma kontrole IESPĒJA (MAXDOP N), vaicājuma izpildes plāna secība. Piemēram:
- varat norādīt vaicājuma plāna piespiedu atkārtotu izveidi IESPĒJA (PĀRKOMPILĒT)
- varat norādīt, vai piespiest vaicājuma plānu izmantot vaicājumā norādīto pievienošanās secību IESPĒJA (SPĒKA PASŪTĪJUMS)
Sīkāka informācija par OPTION aprakstīts šeit.
Izmantojot šaurāko un pieprasītāko datu šķēli:
Nav nepieciešams glabāt lielas datu kopas kešatmiņā (kā tas ir indeksēto skatu gadījumā), no kurām joprojām ir jāfiltrē dati pēc parametriem.
Piemēram, ir tabula, kuras filtrs KUR tiek izmantoti trīs lauki (a, b, c).

Parasti visiem pieprasījumiem ir nemainīgs nosacījums a = 0 un b = 0.

Tomēr pieprasījums pēc lauka c mainīgāks.

Ļaujiet nosacījumam a = 0 un b = 0 Tas patiešām palīdz mums ierobežot nepieciešamo rezultātu kopu līdz tūkstošiem ierakstu, taču nosacījums ir ieslēgts с sašaurina atlasi līdz simts ierakstiem.

Šeit tabulas funkcija var būt labāka izvēle.

Turklāt tabulas funkcija ir paredzamāka un konsekventāka izpildes laikā.

piemēri

Apskatīsim īstenošanas piemēru, kā piemēru izmantojot datu bāzi Jautājumi.

Ir lūgums SELECT, kas apvieno vairākas tabulas un izmanto vienu skatu (OperativeQuestions), kurā piederība tiek pārbaudīta pa e-pastu (izmantojot EXISTS) uz “Operatīvie jautājumi”:

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

Skatam ir diezgan sarežģīta struktūra: tajā ir apakšvaicājumu savienojumi un tiek izmantota šķirošana DISTINCT, kas kopumā ir diezgan resursietilpīga darbība.

OperativeQuestions paraugs ir aptuveni desmit tūkstoši ierakstu.

Galvenā problēma ar šo vaicājumu ir tāda, ka ārējā vaicājuma ierakstiem skatā [OperativeQuestions] tiek izpildīts iekšējais apakšvaicājums, kam [Email] = @p__linq__0 jāļauj mums ierobežot izvades atlasi (izmantojot EXISTS) līdz simtiem ierakstu.

Un var šķist, ka apakšvaicājumā ieraksti ir jāaprēķina vienreiz pēc [Email] = @p__linq__0, un tad šie pāris simti ierakstu jāsavieno ar Id ar jautājumiem, un vaicājums būs ātrs.

Faktiski visas tabulas ir savstarpēji savienotas secīgi: tiek pārbaudīta ID jautājumu atbilstība ar ID no OperativeQuestions un filtrēšana pēc e-pasta.

Faktiski pieprasījums darbojas ar visiem desmitiem tūkstošu OperativeQuestions ierakstu, taču pa e-pastu ir nepieciešami tikai interesējošie dati.

OperativeQuestions skata teksts:

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

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

Konkrētajā gadījumā mēs apsveram šīs problēmas risinājumu bez infrastruktūras izmaiņām, neieviešot atsevišķu tabulu ar gataviem rezultātiem (“Aktīvie vaicājumi”), kam būtu nepieciešams mehānisms tās aizpildīšanai ar datiem un atjaunināšanai. .

Lai gan tas ir labs risinājums, ir vēl viena iespēja šīs problēmas optimizēšanai.

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

Ieviest datubāzē tabulas funkciju [dbo].[OperativeQuestionsUserMail].

Nosūtot e-pastu kā ievades parametru, mēs saņemam vērtību tabulu:

Pieprasī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

Tas atgriež vērtību tabulu ar iepriekš noteiktu datu struktūru.

Lai vaicājumi pakalpojumam OperativeQuestionsUserMail būtu optimāli un tiem būtu optimāli vaicājumu plāni, ir nepieciešama stingra struktūra, nevis ATGRIEŠANAS TABULA KĀ ATGRIEŠANA...

Šajā gadījumā nepieciešamais 1. vaicājums tiek pārveidots par 4. vaicājumu:

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

Skatu un funkciju kartēšana pakalpojumā 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})");
}

Pēdējais 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 secība ir samazinājusies no 200-800 ms, uz 2-20 ms utt., t.i., desmitiem reižu ātrāk.

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

No acīmredzamajām priekšrocībām mēs iegūstam arī:

vispārējs lasīšanas slodzes samazinājums,
ievērojami samazināta bloķēšanas iespējamība
samazinot vidējo bloķēšanas laiku līdz pieņemamām vērtībām

secinājums

Datu bāzes zvanu optimizācija un precizēšana MS SQL caur LINQ ir problēma, kuru var atrisināt.

Šajā darbā ļoti svarīga ir vērība un konsekvence.

Procesa sākumā:

nepieciešams pārbaudīt datus, ar kuriem darbojas pieprasījums (vērtības, izvēlētie datu tipi)
pareizi indeksēt šos datus
pārbaudiet savienojuma nosacījumu pareizību starp tabulām

Nākamā optimizācijas iterācija atklāj:

pieprasījuma pamatā un definē galveno pieprasījuma filtru
līdzīgu vaicājumu bloku atkārtošana un nosacījumu krustpunktu analīze
SSMS vai citā GUI SQL Server optimizē sevi SQL vaicājums (starpposma datu krātuves piešķiršana, iegūtā vaicājuma izveidošana, izmantojot šo krātuvi (var būt vairākas))
pēdējā posmā, par pamatu ņemot iegūto SQL vaicājums, struktūra tiek pārbūvēta LINQ vaicājums

Iegūtais LINQ vaicājums pēc struktūras ir jākļūst identiskam noteiktajam optimālajam SQL vaicājums no 3. punkta.

Pateicības

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

Avots: www.habr.com

LINQ vaicājumu optimizēšanas metodes C#.NET