🥇Metodoj por optimumigi LINQ-demandojn en C#.NET

Enkonduko

В ĉi tiu artikolo iuj optimumigaj metodoj estis pripensitaj LINQ-demandoj.
Ĉi tie ni ankaŭ prezentas kelkajn pliajn alirojn al koda optimumigo rilate al LINQ-demandoj.

Oni scias tion LINQ(Lingvo-Integra Demando) estas simpla kaj oportuna lingvo por pridemandi datumfonton.

А LINQ al SQL estas teknologio por aliri datumojn en DBMS. Ĉi tio estas potenca ilo por labori kun datumoj, kie demandoj estas konstruitaj per deklara lingvo, kiu tiam estos konvertita en SQL-demandoj platformo kaj sendita al la datumbaza servilo por ekzekuto. En nia kazo, per DBMS ni celas MS SQL-Servilo.

Tamen, LINQ-demandoj ne estas konvertitaj al optimume skribitaj SQL-demandoj, kiun sperta DBA povus skribi kun ĉiuj nuancoj de optimumigo SQL-demandoj:

optimumaj ligoj (JOIN) kaj filtrante la rezultojn (WHERE)
multajn nuancojn en uzado de ligoj kaj grupkondiĉoj
multaj varioj en anstataŭigado de kondiĉoj IN sur EKZISTASи NE EN, <> on EKZISTAS
meza kaŝmemoro de rezultoj per provizoraj tabeloj, CTE, tabelvariabloj
uzo de frazo (OPPONO) kun instrukcioj kaj tabelaj sugestoj KUN (...)
uzante indeksitajn vidojn kiel unu el la rimedoj por forigi redundajn datenlegadojn dum elektoj

La ĉefaj rendimento proplempunktoj de la rezultanta SQL-demandoj dum kompilado LINQ-demandoj estas:

firmiĝo de la tuta elekta mekanismo de datumoj en unu peto
duobligante identajn blokojn de kodo, kio finfine kondukas al multoblaj nenecesaj datumlegadoj
grupoj de multkomponentaj kondiĉoj (logikaj "kaj" kaj "aŭ") - KAJ и OR, kombinante en kompleksajn kondiĉojn, kondukas al la fakto ke la optimumiganto, havante taŭgajn ne-amasigitajn indeksojn por la necesaj kampoj, finfine komencas skani kontraŭ la amasigita indekso (INDEX SCANADO) laŭ grupoj de kondiĉoj
profunda nestado de subdemandoj faras analizon tre problema SQL-deklaroj kaj analizo de la demanda plano fare de programistoj kaj DBA

Optimumigaj metodoj

Nun ni movu rekte al optimumigo-metodoj.

1) Plia indeksado

Plej bone estas konsideri filtrilojn sur la ĉefaj elektotabeloj, ĉar tre ofte la tuta konsulto estas konstruita ĉirkaŭ unu aŭ du ĉefaj tabloj (aplikaĵoj-homoj-operacioj) kaj kun norma aro de kondiĉoj (Estas Fermita, Nuligita, Ebligita, Statuso). Gravas krei taŭgajn indeksojn por la identigitaj specimenoj.

Ĉi tiu solvo havas sencon kiam elektante ĉi tiujn kampojn signife limigas la revenitan aron al la demando.

Ekzemple, ni havas 500000 aplikojn. Tamen, ekzistas nur 2000 aktivaj aplikoj. Tiam ĝuste elektita indekso savos nin de INDEX SCANADO sur granda tablo kaj permesos al vi rapide elekti datumojn per ne-amasigita indekso.

Ankaŭ, la manko de indeksoj povas esti identigita per instigoj por analizado de demandplanoj aŭ kolektado de sistemaj vidstatistikoj. MS SQL-Servilo:

Ĉiuj viddatumoj enhavas informojn pri mankantaj indeksoj, escepte de spacaj indeksoj.

Tamen, indeksoj kaj kaŝmemoro ofte estas metodoj por kontraŭbatali la sekvojn de malbone skribita LINQ-demandoj и SQL-demandoj.

Kiel la severa praktiko de vivo montras, estas ofte grave por komerco efektivigi komercajn funkciojn antaŭ certaj templimoj. Kaj tial pezaj petoj ofte estas translokigitaj al la fono kun kaŝmemoro.

Ĉi tio estas parte pravigita, ĉar la uzanto ne ĉiam bezonas la plej novajn datumojn kaj ekzistas akceptebla nivelo de respondeco de la uzantinterfaco.

Ĉi tiu aliro permesas solvi komercajn bezonojn, sed finfine reduktas la efikecon de la informsistemo simple prokrastante solvojn al problemoj.

Ankaŭ indas memori, ke en la procezo de serĉado de la necesaj indeksoj por aldoni, sugestoj MSSQL Optimumigo povas esti malĝusta, inkluzive sub la sekvaj kondiĉoj:

se jam ekzistas indeksoj kun simila aro de kampoj
se la kampoj en la tabelo ne povas esti indeksitaj pro indeksaj limigoj (priskribitaj pli detale tie).

2) Kunfandi atributojn en unu novan atributon

Foje iuj kampoj de unu tabelo, kiuj servas kiel bazo por grupo de kondiĉoj, povas esti anstataŭigitaj per enkonduko de unu nova kampo.

Ĉi tio validas precipe por statuskampoj, kiuj kutime estas aŭ bitaj aŭ entjeroj.

Ekzemplo:

Estas Fermita = 0 KAJ Nuligita = 0 KAJ Ebligita = 0 estas anstataŭigita per Statuso = 1.

Jen kie la entjera Status-atributo estas lanĉita por certigi, ke ĉi tiuj statusoj estas plenigitaj en la tabelo. Poste, ĉi tiu nova atributo estas indeksita.

Ĉi tio estas fundamenta solvo al la rendimenta problemo, ĉar Ni aliras datumojn sen nenecesaj kalkuloj.

3) Materiigo de la vido

Bedaŭrinde en LINQ-demandoj Provizoraj tabloj, CTEoj kaj tabelvariabloj ne povas esti uzataj rekte.

Tamen, ekzistas alia maniero optimumigi por ĉi tiu kazo - indeksitaj vidoj.

Kondiĉa grupo (el la supra ekzemplo) Estas Fermita = 0 KAJ Nuligita = 0 KAJ Ebligita = 0 (aŭ aro de aliaj similaj kondiĉoj) fariĝas bona elekto por uzi ilin en indeksita vido, konservante malgrandan tranĉaĵon de datumoj de granda aro.

Sed ekzistas kelkaj limigoj kiam realigas vidon:

uzo de subdemandoj, subfrazoj EKZISTAS devus esti anstataŭigita per uzado JOIN
vi ne povas uzi frazojn UNION, UNION ĈIUJ, ESCEPO, INTERSEKTI
Vi ne povas uzi tabelajn sugestojn kaj klaŭzojn OPPONO
neniu ebleco labori kun cikloj
Estas neeble montri datumojn en unu vido de malsamaj tabeloj

Gravas memori, ke la reala avantaĝo de uzado de indeksita vido povas esti atingita nur per efektive indeksado de ĝi.

Sed dum vokado de vido, ĉi tiuj indeksoj eble ne estas uzataj, kaj por uzi ilin eksplicite, vi devas specifi KUN (NENEKSPANDI).

Ekde en LINQ-demandoj Ne eblas difini tabelajn sugestojn, do vi devas krei alian reprezenton - "envolvaĵo" de la sekva formo:

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

4) Uzanta tabelajn funkciojn

Ofte en LINQ-demandoj Grandaj blokoj de subdemandoj aŭ blokoj uzantaj vidojn kun kompleksa strukturo formas finan demandon kun tre kompleksa kaj suboptimuma ekzekutstrukturo.

Ŝlosilaj Avantaĝoj de Uzado de Tabelaj Funkcioj en LINQ-demandoj:

La kapablo, kiel en la kazo de vidoj, esti uzata kaj specifita kiel objekto, sed vi povas pasi aron da eniga parametroj:
DE FUNCTION(@param1, @param2 ...)
Kiel rezulto, fleksebla datenspecimeno povas esti atingita
En la kazo de uzado de tabelfunkcio, ekzistas ne tiaj fortaj restriktoj kiel en la kazo de indeksitaj vidoj priskribitaj supre:
1. Tablaj sugestoj:
  tra LINQ Vi ne povas specifi kiuj indeksoj devas esti uzataj kaj determini la datumizolan nivelon dum demandado.
  Sed la funkcio havas ĉi tiujn kapablojn.
  Kun la funkcio, vi povas atingi sufiĉe konstantan ekzekutan demandplanon, kie reguloj por labori kun indeksoj kaj datumizolaj niveloj estas difinitaj.
2. Uzi la funkcion ebligas, kompare kun indeksitaj vidoj, akiri:
  - kompleksa datuma prova logiko (eĉ uzante buklojn)
  - preni datumojn de multaj malsamaj tabeloj
  - la uzo de UNION и EKZISTAS
Oferto OPPONO tre utila kiam ni bezonas provizi samtempan kontrolon OPCIO (MAXDOP N), la ordo de la demanda ekzekutplano. Ekzemple:
- vi povas specifi devigan rekreadon de la demandplano OPCIO (REKOMPILI)
- vi povas specifi ĉu devigi la demandplanon uzi la kunigordon specifitan en la demando OPCIO (DEVORGA ORDO)
Pliaj detaloj pri OPPONO priskribis tie.
Uzante la plej mallarĝan kaj plej postulatan datumtranĉaĵon:
Ne necesas stoki grandajn datumajn arojn en kaŝmemoroj (kiel estas la kazo kun indeksitaj vidoj), el kiuj vi ankoraŭ bezonas filtri la datumojn laŭ parametro.
Ekzemple, estas tabelo kies filtrilo WHERE tri kampoj estas uzataj (a, b, c).

Konvencie, ĉiuj petoj havas konstantan kondiĉon a = 0 kaj b = 0.

Tamen, la peto por la kampo c pli varia.

Lasu la kondiĉon a = 0 kaj b = 0 Ĝi vere helpas nin limigi la postulatan rezultan aron al miloj da rekordoj, sed la kondiĉo estas valida с malvastigas la elekton al cent rekordoj.

Ĉi tie la tabelfunkcio povas esti pli bona elekto.

Ankaŭ, tabelfunkcio estas pli antaŭvidebla kaj konsekvenca en ekzekuttempo.

ekzemploj

Ni rigardu ekzempla efektivigo uzante la Demandojn-datumbazon kiel ekzemplon.

Estas peto ELEKTU, kiu kombinas plurajn tabelojn kaj uzas unu vidon (OperativeQuestions), en kiu alligiteco estas kontrolita retpoŝte (per EKZISTAS) al "Operaciaj Demandoj":

Peto n-ro 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])
));

La vido havas sufiĉe kompleksan strukturon: ĝi enhavas kuniĝojn de subdemandoj kaj uzas ordigon DISTINKI, kiu ĝenerale estas sufiĉe rimed-intensa operacio.

Specimeno de OperativeQuestions estas proksimume dek mil rekordoj.

La ĉefa problemo kun ĉi tiu demando estas, ke por la rekordoj de la ekstera demando, interna subdemando estas ekzekutita sur la vido [OperativeQuestions], kiu devus por [Retpoŝto] = @p__linq__0 permesi al ni limigi la elig-elekton (per EKZISTAS) ĝis centoj da rekordoj.

Kaj povas ŝajni, ke la subdemando devus kalkuli la registrojn unufoje per [Retpoŝto] = @p__linq__0, kaj tiam ĉi tiuj du cent registroj devus esti konektitaj per Id kun Demandoj, kaj la demando estos rapida.

Fakte, ekzistas sinsekva konekto de ĉiuj tabeloj: kontrolado de la korespondado de Id Demandoj kun Id de OperativeQuestions, kaj filtrado per Retpoŝto.

Fakte, la peto funkcias kun ĉiuj dekoj da miloj da OperativeQuestions-rekordoj, sed nur la interesaj datumoj estas bezonataj per Retpoŝto.

OperativeQuestions rigardu tekston:

Peto n-ro 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));

Komenca vidmapado en 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");
    }
}

Komenca LINQ-demando

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

En ĉi tiu aparta kazo, ni pripensas solvon de ĉi tiu problemo sen infrastrukturaj ŝanĝoj, sen enkonduki apartan tabelon kun pretaj rezultoj ("Aktivaj Demandoj"), kiu postulus mekanismon por plenigi ĝin per datumoj kaj konservi ĝin ĝisdatigita. .

Kvankam ĉi tio estas bona solvo, ekzistas alia opcio por optimumigi ĉi tiun problemon.

La ĉefa celo estas kaŝmemori enskribojn per [Retpoŝto] = @p__linq__0 el la vido OperativeQuestions.

Enkonduku la tabelfunkcion [dbo].[OperativeQuestionsUserMail] en la datumbazon.

Sendante Retpoŝton kiel eniga parametro, ni ricevas tabelon de valoroj:

Peto n-ro 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

Ĉi tio resendas tabelon de valoroj kun antaŭdifinita datumstrukturo.

Por ke demandoj al OperativeQuestionsUserMail estu optimumaj kaj havu optimumajn demandplanojn, strikta strukturo estas postulata, kaj ne REVENAS TABLO KIEL REVENO...

En ĉi tiu kazo, la postulata Demando 1 estas konvertita en Demandon 4:

Peto n-ro 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]);

Mapado de vidoj kaj funkcioj en 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})");
}

Fina LINQ-demando

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

La ordo de ekzekuttempo falis de 200-800 ms, ĝis 2-20 ms, ktp., t.e. dekoble pli rapide.

Se ni prenas ĝin pli averaĝe, tiam anstataŭ 350 ms ni ricevis 8 ms.

El la evidentaj avantaĝoj ni ankaŭ ricevas:

ĝenerala redukto de legado,
grava redukto de la probableco de blokado
reduktante la mezan bloktempon al akcepteblaj valoroj

konkludo

Optimumigo kaj fajnagordado de datumbazaj vokoj MSSQL tra LINQ estas problemo solvebla.

Atentemo kaj konsistenco estas tre gravaj en ĉi tiu laboro.

Komence de la procezo:

necesas kontroli la datumojn kun kiuj funkcias la peto (valoroj, elektitaj datumtipoj)
fari taŭgan indeksadon de ĉi tiuj datumoj
kontroli la ĝustecon de kunigo de kondiĉoj inter tabeloj

La sekva optimumiga ripeto malkaŝas:

bazo de la peto kaj difinas la ĉefan petofiltrilon
ripetante similajn demandblokojn kaj analizante la intersekciĝon de kondiĉoj
en SSMS aŭ alia GUI por SQLa Servilo optimumigas sin SQL-demando (asignante mezan datumstokadon, konstruante la rezultan demandon uzante ĉi tiun stokadon (povas esti pluraj))
en la lasta etapo, prenante kiel bazon la rezultan SQL-demando, la strukturo estas rekonstruata LINQ-demando

La rezultanta LINQ-demando devus iĝi identa en strukturo al la identigita optimuma SQL-demando de la punkto 3.

Dankoj

Koran dankon al kolegoj jobgemws и alex_ozr de la kompanio Fortis por helpo en la preparado de ĉi tiu materialo.

fonto: www.habr.com

Metodoj por optimumigi LINQ-demandojn en C#.NET